详见
https://www.postgresql.org/docs/current/pgbench.html
由于pgbench支持客户端并行,可以开启多个链接进行测试。每个链接有一个唯一的标示:
client_id :
unique number identifying the client session (starts from zero)
或者将client_id作为动态identify的suffix组成,实现不同线程操作不同表的需求。(pgbench暂时还不支持这个功能, 可以参考这里说明 需要修改pgbench代码parseQuery)
upsert,确保不同的会话一定相互不会出现行级锁冲突干扰。
数据ID范围1亿,64个并发操作。确保不同并发操作的ID相互绝对不会重叠
说明:
\set id random(1,100000000)/64+:client_id
random(1,100000000) 返回1到1亿之间的随机int
/64除以64得到trunc int
+:client_id , 加每个线程的number,
得到的值,赋予给id, 从而不同的线程绝对不会有重复的id出现
性能杠杠的
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -T 120
transaction type: ./test.sql
scaling factor: 1
number of clients: 64
duration: 120 s
number of transactions actually processed: 25803874
latency average = 0.297 ms
latency stddev = 0.251 ms
tps = 215005.904762 (including connections establishing)
tps = 215027.544261 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set id random(1,100000000)/64+:client_id
0.296 insert into t ...........................
如果没有使用client_id,那锁冲突就会比较严重,造成等待影响性能。从25万qps下降到了18万qps。
vi test.sql
\set id random(0,31)
insert into t values (:id, md5(random()::text), now()) on conflict (id) do update set info=excluded.info , crt_time=excluded.crt_time;
transaction type: ./test.sql
scaling factor: 1
query mode: prepared
number of clients: 32
number of threads: 32
duration: 120 s
number of transactions actually processed: 21619627
latency average = 0.177 ms
latency stddev = 0.138 ms
tps = 180162.287114 (including connections establishing)
tps = 180174.521514 (excluding connections establishing)
statement latencies in milliseconds:
0.177 insert into t values (:id, ............
期待pgbench支持在identify字段中支持变量,而不仅仅是非identify内容中支持变量。