改写 sysbench oltp.lua 支持PostgreSQL绑定变量
背景
源码在这里
https://github.com/digoal/sysbench_lua/tree/master/lua
已经把oltp.lua改掉了，支持10条SQL，(有需要可以再自行调整)包括
但是由于sysbench不能识别execute语句，所以都算成了other query， 实际上就是这些使用了服务端绑定变量的query。
在一台普通的X86机器上测试了一下，15GB数据，跑以下SQL能达到47万QPS。
-- select c from tbl where id = $1;
-- select id,k,c,pad from tbl where id in ($1,...$n);
-- select c from tbl where id between $1 and $2;
-- select sum(k) from tbl where id between $1 and $2;
-- select c from tbl where id between $1 and $2 order by c;
-- select distinct c from tbl where id between $1 and $2 order by c;
-- update tbl set k=k+1 where id = $1;
-- update tbl set c=$2 where id = $1;
-- delete from tbl where id = $1;
-- insert into tbl(id, k, c, pad) values ($1,$2,$3,$4);
oltp_pg.lua源码
-- use case
-- ./sysbench_pg --test=lua/parallel_init_pg.lua \
-- --db-driver=pgsql \
-- --pgsql-host=$PGDATA \
-- --pgsql-port=1921 \
-- --pgsql-user=postgres \
-- --pgsql-password=postgres \
-- --pgsql-db=postgres \
-- --oltp-tables-count=64 \
-- --oltp-table-size=1000000 \
-- --num-threads=64 \
-- cleanup
-- ./sysbench_pg --test=lua/parallel_init_pg.lua \
-- --db-driver=pgsql \
-- --pgsql-host=$PGDATA \
-- --pgsql-port=1921 \
-- --pgsql-user=postgres \
-- --pgsql-password=postgres \
-- --pgsql-db=postgres \
-- --oltp-tables-count=64 \
-- --oltp-table-size=1000000 \
-- --num-threads=64 \
-- run
-- ./sysbench_pg \
-- --test=lua/oltp_pg.lua \
-- --db-driver=pgsql \
-- --pgsql-host=$PGDATA \
-- --pgsql-port=1921 \
-- --pgsql-user=postgres \
-- --pgsql-password=postgres \
-- --pgsql-db=postgres \
-- --oltp-tables-count=64 \
-- --oltp-table-size=1000000 \
-- --num-threads=64 \
-- --max-time=120 \
-- --max-requests=0 \
-- --report-interval=1 \
-- run
pathtest = string.match(test, "(.*/)") or ""
dofile(pathtest .. "common.lua")
function thread_init(thread_id)
set_vars()
oltp_point_selects = 10 -- query 10 times
random_points = 10 -- query id in (10 vars)
oltp_simple_ranges = 1 -- query 1 times
oltp_sum_ranges = 1 -- query 1 times
oltp_order_ranges = 1 -- query 1 times
oltp_distinct_ranges = 1 -- query 1 times
oltp_index_updates = 1 -- query 1 times
oltp_non_index_updates = 1 -- query 1 times
oltp_range_size = 100 -- query between $1 and $1+100-1
oltp_read_only = false -- query delete,update,insert also
local table_name
local pars
local vars
local i
begin_query = "BEGIN"
commit_query = "COMMIT"
table_name = "sbtest" .. (thread_id+1)
-- select c from tbl where id = $1;
db_query("prepare p1(int) as select c from " .. table_name .. " WHERE id=$1")
-- select id,k,c,pad from tbl where id in ($1,...$n);
pars = ""
vars = ""
for i = 1,random_points do
pars = pars .. "int, "
vars = vars .. "$" .. i .. ", "
end
pars = string.sub(pars, 1, string.len(pars) - 2)
vars = string.sub(vars, 1, string.len(vars) - 2)
db_query("prepare p2(" .. pars .. ") as select id,k,c,pad from " .. table_name .. " WHERE id in (" .. vars .. ")")
-- select c from tbl where id between $1 and $2;
db_query("prepare p3(int,int) as SELECT c FROM " .. table_name .. " WHERE id BETWEEN $1 and $2")
-- select sum(k) from tbl where id between $1 and $2;
db_query("prepare p4(int,int) as SELECT sum(k) FROM " .. table_name .. " WHERE id BETWEEN $1 and $2")
-- select c from tbl where id between $1 and $2 order by c;
db_query("prepare p5(int,int) as SELECT c FROM " .. table_name .. " WHERE id BETWEEN $1 and $2 order by c")
-- select distinct c from tbl where id between $1 and $2 order by c;
db_query("prepare p6(int,int) as SELECT distinct c FROM " .. table_name .. " WHERE id BETWEEN $1 and $2 order by c")
-- update tbl set k=k+1 where id = $1;
db_query("prepare p7(int) as update " .. table_name .. " set k=k+1 where id = $1")
-- update tbl set c=$2 where id = $1;
db_query("prepare p8(int,text) as update " .. table_name .. " set c=$2 where id = $1")
-- delete from tbl where id = $1;
db_query("prepare p9(int) as delete from " .. table_name .. " where id = $1")
-- insert into tbl(id, k, c, pad) values ($1,$2,$3,$4);
db_query("prepare p10(int,int,text,text) as insert into " .. table_name .. "(id, k, c, pad) values ($1,$2,$3,$4)")
end
function event(thread_id)
local i
local evars
local range_start
local c_val
local pad_val
db_query(begin_query)
for i=1, oltp_point_selects do
db_query("execute p1(" .. sb_rand(1, oltp_table_size) .. ")")
end
evars = ""
for i = 1,random_points do
evars = evars .. sb_rand(1, oltp_table_size) .. ", "
end
evars = string.sub(evars, 1, string.len(evars) - 2)
db_query("execute p2(" .. evars .. ")")
for i=1, oltp_simple_ranges do
range_start = sb_rand(1, oltp_table_size)
db_query("execute p3(" .. range_start .. "," .. (range_start + oltp_range_size - 1) .. ")")
end
for i=1, oltp_sum_ranges do
range_start = sb_rand(1, oltp_table_size)
db_query("execute p4(" .. range_start .. "," .. (range_start + oltp_range_size - 1) .. ")")
end
for i=1, oltp_order_ranges do
range_start = sb_rand(1, oltp_table_size)
db_query("execute p5(" .. range_start .. "," .. (range_start + oltp_range_size - 1) .. ")")
end
for i=1, oltp_distinct_ranges do
range_start = sb_rand(1, oltp_table_size)
db_query("execute p6(" .. range_start .. "," .. (range_start + oltp_range_size - 1) .. ")")
end
if not oltp_read_only then
for i=1, oltp_index_updates do
db_query("execute p7(" .. sb_rand(1, oltp_table_size) .. ")")
end
for i=1, oltp_non_index_updates do
c_val = sb_rand_str("###########-###########-###########-###########-###########-###########-###########-###########-###########-###########")
db_query("execute p8(" .. sb_rand(1, oltp_table_size) .. ", '" .. c_val .. "')")
end
-- delete then insert
i = sb_rand(1, oltp_table_size)
c_val = sb_rand_str([[
###########-###########-###########-###########-###########-###########-###########-###########-###########-###########]])
pad_val = sb_rand_str([[
###########-###########-###########-###########-###########]])
db_query("execute p9(" .. i .. ")")
db_query("execute p10" .. string.format("(%d, %d, '%s', '%s')",i, sb_rand(1, oltp_table_size) , c_val, pad_val) )
end -- oltp_read_only
db_query(commit_query)
end