让 sysbench 支持 PostgreSQL 服务端绑定变量-阿里云开发者社区

让 sysbench 支持 PostgreSQL 服务端绑定变量

2016-04-27 3895

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云原生数据库 PolarDB MySQL 版，Serverless 5000PCU 100GB

云数据库 Redis 版，社区版 2GB

云数据库 RDS MySQL Serverless，0.5-2RCU 50GB

简介： 首先介绍一下几种数据库绑定变量的语义。.1. PostgreSQL 绑定变量的语义是$n来表示位置n的变量，例如 : select info from test where id=$1 and c1=$2; .2. Oracle 使用:var来表示变量，例如： stmt =

首先介绍一下几种数据库绑定变量的语义。
.1. PostgreSQL 绑定变量的语义是使用?来表示任意位置的变量, 例如 :

select info from test where id=? and c1=?;

.2. Oracle 使用:var来表示变量，例如：

   stmt = db_prepare("UPDATE ".. table_name .." SET k=k+1 WHERE id=to_number(:x) and 'a' = :y")
   params = {}
   params[1] = '444'
   params[2] = 'a'
   db_bind_param(stmt, params)

.3. MySQL 使用?来表示变量，例如：

   points = ""
   for i = 1,random_points do
      points = points .. "?, "
   end
   
   -- Get rid of last comma and space.
   points = string.sub(points, 1, string.len(points) - 2)
   
   stmt = db_prepare([[
        SELECT id, k, c, pad
          FROM sbtest
          WHERE k IN (]] .. points .. [[)
        ]])

   params = {}
   for j = 1,random_points do
      params[j] = 1
   end

   db_bind_param(stmt, params)

但是sysbench 对PG绑定变量的支持并不好，例如：

vi lua/oltp_pg.lua

pathtest = string.match(test, "(.*/)") or ""

dofile(pathtest .. "common.lua")

function thread_init(thread_id)
   set_vars()

   stmt = db_prepare([[
        SELECT info
          FROM test
          WHERE id = ? and 'a' = ?
        ]])

   params = {}
   params[1] = 1
   params[2] = 'a'

   db_bind_param(stmt, params)
end

function event(thread_id)
   params[1] = string.format("%d", sb_rand(1, oltp_table_size))
   params[2] = 'a'
   db_query('BEGIN')
   db_execute(stmt)
   db_query('COMMIT')
end

测试

./sysbench_pg \
--test=lua/oltp_pg.lua   \
--db-driver=pgsql   \
--pgsql-host=127.0.0.1   \
--pgsql-port=1921   \
--pgsql-user=postgres   \
--pgsql-password=postgres   \
--pgsql-db=postgres   \
--oltp-tables-count=1   \
--oltp-table-size=1000000   \
--num-threads=1    \
--max-time=120    \
--max-requests=0   \
--report-interval=1   \
run

报错如下

sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1
Report intermediate results every 1 second(s)
Random number generator seed is 0 and will be ignored


Threads started!

FATAL: query execution failed: -872378608
FATAL: failed to execute function `event': (null)

数据库端的错误信息，bind失败，未正确的提交2个参数。

2016-04-28 22:44:12.330 CST,"postgres","postgres",10763,"[local]",572221bc.2a0b,1,"BIND",2016-04-28 22:44:12 CST,1/4646740,0,ERROR,22P02,"invalid input syntax for integer: """"",,,,,,"        SELECT info
          FROM test
          WHERE id = $1 and 'a' = $2
        ",,"pg_atoi, numutils.c:52",""

使用gdb跟踪到报错的代码：

int sb_lua_db_execute(lua_State *L)@sysbench/scripting/script_lua.c

在fix这个bug之前，本文的目的是要让sysbench支持服务端的绑定变量。
需要用到PostgreSQL的prepare语句和execute语句。
例子，在thread_init中定义一次prepare，在event中执行execute。
为了让sysbench能统计每秒的tps，必须使用显示的begin; 和 commit;

vi lua/oltp_pg.lua

pathtest = string.match(test, "(.*/)") or ""

dofile(pathtest .. "common.lua")

function thread_init(thread_id)
   set_vars()

   db_query('prepare p' .. thread_id .. '(int) as select * from test where id=$1')
end

function event(thread_id)
   local rs
   local i

   i = sb_rand(1, oltp_table_size)
   rs = db_query("begin" )
   rs = db_query("execute p".. thread_id .. "(" .. i .. ")" )
   -- rs = db_query("select * from test where id=" .. i )
   rs = db_query("commit" )

end

测试

create table test (id int primary key, info text);
insert into test select generate_series(1,1000000),'test';

sysbench_pg

./sysbench_pg \
--test=lua/oltp_pg.lua   \
--db-driver=pgsql   \
--pgsql-host=127.0.0.1   \
--pgsql-port=1921   \
--pgsql-user=postgres   \
--pgsql-password=postgres   \
--pgsql-db=postgres   \
--oltp-tables-count=1   \
--oltp-table-size=1000000   \
--num-threads=1    \
--max-time=120    \
--max-requests=0   \
--report-interval=1   \
run


[   1s] threads: 128, tps: 468353.17, reads/s: 0.00, writes/s: 0.00, response time: 0.33ms (95%)
[   2s] threads: 128, tps: 474536.37, reads/s: 0.00, writes/s: 0.00, response time: 0.32ms (95%)
[   3s] threads: 128, tps: 476768.82, reads/s: 0.00, writes/s: 0.00, response time: 0.32ms (95%)
[   4s] threads: 128, tps: 477219.36, reads/s: 0.00, writes/s: 0.00, response time: 0.32ms (95%)
[   5s] threads: 128, tps: 476848.04, reads/s: 0.00, writes/s: 0.00, response time: 0.32ms (95%)

连接unix socket

./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=1   \
--oltp-table-size=1000000   \
--num-threads=1    \
--max-time=120    \
--max-requests=0   \
--report-interval=1   \
run

[   1s] threads: 128, tps: 534132.82, reads/s: 0.00, writes/s: 0.00, response time: 0.29ms (95%)
[   2s] threads: 128, tps: 539569.98, reads/s: 0.00, writes/s: 0.00, response time: 0.29ms (95%)
[   3s] threads: 128, tps: 542427.96, reads/s: 0.00, writes/s: 0.00, response time: 0.29ms (95%)
[   4s] threads: 128, tps: 542168.03, reads/s: 0.00, writes/s: 0.00, response time: 0.28ms (95%)

测试一下未使用服务端绑定变量的性能，把oltp_pg.lua的内容修改一下 :

   -- rs = db_query("execute p".. thread_id .. "(" .. i .. ")" )
   rs = db_query("select * from test where id=" .. i )

测试结果

./sysbench_pg \
--test=lua/oltp_pg.lua   \
--db-driver=pgsql   \
--pgsql-host=127.0.0.1   \
--pgsql-port=1921   \
--pgsql-user=postgres   \
--pgsql-password=postgres   \
--pgsql-db=postgres   \
--oltp-tables-count=1   \
--oltp-table-size=1000000   \
--num-threads=1    \
--max-time=120    \
--max-requests=0   \
--report-interval=1   \
run

[   1s] threads: 128, tps: 367946.22, reads/s: 367985.22, writes/s: 0.00, response time: 0.40ms (95%)
[   2s] threads: 128, tps: 371138.13, reads/s: 371137.13, writes/s: 0.00, response time: 0.40ms (95%)
[   3s] threads: 128, tps: 371514.94, reads/s: 371525.94, writes/s: 0.00, response time: 0.40ms (95%)
[   4s] threads: 128, tps: 371680.18, reads/s: 371663.18, writes/s: 0.00, response time: 0.40ms (95%)

./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=1   \
--oltp-table-size=1000000   \
--num-threads=1    \
--max-time=120    \
--max-requests=0   \
--report-interval=1   \
run

[   1s] threads: 128, tps: 410439.59, reads/s: 410484.59, writes/s: 0.00, response time: 0.37ms (95%)
[   2s] threads: 128, tps: 414555.41, reads/s: 414568.41, writes/s: 0.00, response time: 0.36ms (95%)
[   3s] threads: 128, tps: 415483.61, reads/s: 415468.61, writes/s: 0.00, response time: 0.36ms (95%)
[   4s] threads: 128, tps: 416120.30, reads/s: 416125.30, writes/s: 0.00, response time: 0.36ms (95%)

对比pgbench：

vi test.sql

\setrandom id 1 1000000
begin;
select info from test where id=:id;
commit;

未使用绑定变量

pgbench -M simple -n -r -P 1 -f ./test.sql -c 128 -j 128 -T 100 -h 127.0.0.1
progress: 2.0 s, 406356.7 tps, lat 0.314 ms stddev 0.065
progress: 3.0 s, 408601.2 tps, lat 0.312 ms stddev 0.053
progress: 4.0 s, 409713.9 tps, lat 0.311 ms stddev 0.048
progress: 5.0 s, 410598.9 tps, lat 0.311 ms stddev 0.046

pgbench -M simple -n -r -P 1 -f ./test.sql -c 128 -j 128 -T 100 -h $PGDATA
progress: 2.0 s, 455661.7 tps, lat 0.279 ms stddev 0.042
progress: 3.0 s, 456656.3 tps, lat 0.279 ms stddev 0.078
progress: 4.0 s, 458107.1 tps, lat 0.278 ms stddev 0.033
progress: 5.0 s, 458687.4 tps, lat 0.278 ms stddev 0.033

使用绑定变量

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 128 -j 128 -T 100 -h 127.0.0.1
progress: 2.0 s, 575148.0 tps, lat 0.222 ms stddev 0.057
progress: 3.0 s, 577477.6 tps, lat 0.221 ms stddev 0.060
progress: 4.0 s, 578402.7 tps, lat 0.220 ms stddev 0.058
progress: 5.0 s, 580408.2 tps, lat 0.220 ms stddev 0.043

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 128 -j 128 -T 100 -h $PGDATA
progress: 2.0 s, 650961.8 tps, lat 0.195 ms stddev 0.033
progress: 3.0 s, 653079.1 tps, lat 0.195 ms stddev 0.027
progress: 4.0 s, 653964.2 tps, lat 0.194 ms stddev 0.034
progress: 5.0 s, 655027.3 tps, lat 0.194 ms stddev 0.027
progress: 6.0 s, 655417.3 tps, lat 0.194 ms stddev 0.039

使用auto commit

vi test.sql

begin;
\setrandom id 1 1000000
select info from test where id=:id;

未使用绑定变量

pgbench -M simple -n -r -P 1 -f ./test.sql -c 128 -j 128 -T 100 -h 127.0.0.1
progress: 2.0 s, 582766.5 tps, lat 0.218 ms stddev 0.034
progress: 3.0 s, 585359.1 tps, lat 0.217 ms stddev 0.033
progress: 4.0 s, 585994.5 tps, lat 0.217 ms stddev 0.070

pgbench -M simple -n -r -P 1 -f ./test.sql -c 128 -j 128 -T 100 -h $PGDATA
progress: 2.0 s, 623373.9 tps, lat 0.204 ms stddev 0.397
progress: 3.0 s, 626771.3 tps, lat 0.203 ms stddev 0.260
progress: 4.0 s, 623826.0 tps, lat 0.204 ms stddev 0.590
progress: 5.0 s, 625747.0 tps, lat 0.203 ms stddev 0.679

使用绑定变量

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 128 -j 128 -T 100 -h 127.0.0.1
progress: 2.0 s, 1024293.8 tps, lat 0.124 ms stddev 0.024
progress: 3.0 s, 1027868.7 tps, lat 0.123 ms stddev 0.026
progress: 4.0 s, 1030192.5 tps, lat 0.123 ms stddev 0.024
progress: 5.0 s, 1031413.8 tps, lat 0.123 ms stddev 0.022

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 128 -j 128 -T 100 -h $PGDATA
progress: 3.0 s, 1134774.5 tps, lat 0.112 ms stddev 0.018
progress: 4.0 s, 1136322.2 tps, lat 0.111 ms stddev 0.020
progress: 5.0 s, 1138087.2 tps, lat 0.111 ms stddev 0.020
progress: 6.0 s, 1138184.3 tps, lat 0.111 ms stddev 0.018

毫无疑问，最好的性能是，使用绑定变量，auto commit，unix socket。
下面是以上几组按KEY查询的TPS性能对比图：

下一篇介绍一下TPS瓶颈分析之网络瓶颈。

让 sysbench 支持 PostgreSQL 服务端绑定变量

数据库

热门文章

最新文章

相关产品

相关课程

相关电子书

相关实验场景

推荐镜像