背景

pgbench是 PG内置的一款压测工具，效率非常高。内置tpcb测试模型，并且支持自定义压测模型（内置了非常丰富的变量生成函数，操作符，函数，变量。同时支持shell 调用结果作为变量传输。支持多个压测文件，文件权重设置等）。

详见

https://www.postgresql.org/docs/current/pgbench.html

由于pgbench支持客户端并行，可以开启多个链接进行测试。每个链接有一个唯一的标示：

client_id :

unique number identifying the client session (starts from zero)

采用client_id，可以模拟数据隔离的更新操作（防止多个链接相互更新到相同记录，导致锁问题，与真实场景不符，或影响更新测试性能）

或者将client_id作为动态identify的suffix组成，实现不同线程操作不同表的需求。（pgbench暂时还不支持这个功能, 可以参考这里说明 《PostgreSQL 使用 pgbench 测试 sysbench 相关case - pg_oltp_bench》 需要修改pgbench代码parseQuery）

  1. vi test.sql  
  2. \set id1 random(1, 10000000)  
  3. SELECT pad FROM "sbtest:client_id" WHERE id = :id1;  
  5. $ pgbench -M prepared -n -r -P 1 -f ./test.sql -h xxx.xxx.xxx.xxx -p 1924 -U postgres postgres -c 3 -j 3 -T 100   
  7. 目前会报错，只支持simple模式。  如果要让prepared模式支持，建议改pgbench代码来支持. 例如使用:::varname时，拼接identifid。     
  8. ERROR:  relation "sbtest$1" does not exist  
  9. LINE 1: SELECT pad FROM "sbtest$1" WHERE id = $2;  
  10.                         ^  
  11. client 0 aborted in state 1: ERROR:  prepared statement "P0_1" does not exist  
  12. ERROR:  relation "sbtest$1" does not exist  
  13. LINE 1: SELECT pad FROM "sbtest$1" WHERE id = $2;  
  14.                         ^  
  15. client 1 aborted in state 1: ERROR:  prepared statement "P0_1" does not exist  
  16. ERROR:  relation "sbtest$1" does not exist  
  17. LINE 1: SELECT pad FROM "sbtest$1" WHERE id = $2;  
  18.                         ^  
  19. client 2 aborted in state 1: ERROR:  prepared statement "P0_1" does not exist

例子

upsert，确保不同的会话一定相互不会出现行级锁冲突干扰。

  1. create table t(id int primary key, info text, crt_time timestamp);

数据ID范围1亿，64个并发操作。确保不同并发操作的ID相互绝对不会重叠

  1. vi test.sql  
  3. \set id random(1,100000000)/64+:client_id  
  4. insert into t values (:id, md5(random()::text), now()) on conflict (id) do update set info=excluded.info , crt_time=excluded.crt_time;

说明：

  1. \set id random(1,100000000)/64+:client_id  
  3. random(1,100000000) 返回1到1亿之间的随机int  
  4. /64除以64得到trunc int  
  5. +:client_id ， 加每个线程的number，  
  7. 得到的值，赋予给id， 从而不同的线程绝对不会有重复的id出现
  1. postgres=# select * from t limit 10;  
  2.    id    |               info               |          crt_time            
  3. ---------+----------------------------------+----------------------------  
  4.   259017 | 1d55b352a6d0505bd9f5f7d4c445233b | 2019-08-28 22:27:38.123068  
  5.  1472003 | 493446240b69fd241c135a238d70eab4 | 2019-08-28 22:27:35.934951  
  6.  1001450 | 74d8334822be81483bffe7da3b5f0253 | 2019-08-28 22:27:26.06475  
  7.   985969 | f9790129e9f4fe2da6f0d887abc5bb1c | 2019-08-28 22:27:37.722908  
  8.  1140661 | d8214e5c1994549b612b7e4194c63bcb | 2019-08-28 22:27:37.205729  
  9.  1252023 | 6d8fbeb3d039749e6594b8913955cde1 | 2019-08-28 22:27:29.841494  
  10.   727159 | 68fa51af79d7c01502a79b8873aeb8fa | 2019-08-28 22:27:31.892077  
  11.   687989 | 00b95072f38ffc73fb9e5b0ace009b5d | 2019-08-28 22:27:15.524358  
  12.  1029162 | 113a44124e08be8105690a24b456863e | 2019-08-28 22:27:23.566686  
  13.  1204224 | c6ea8e3c66790ccf3dd22b9feab2f4a6 | 2019-08-28 22:27:29.696627  
  14. (10 rows)

性能杠杠的

  1. pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -T 120  
  3. transaction type: ./test.sql  
  4. scaling factor: 1  
  5. query mode: prepared  
  6. number of clients: 64  
  7. number of threads: 64  
  8. duration: 120 s  
  9. number of transactions actually processed: 25803874  
  10. latency average = 0.297 ms  
  11. latency stddev = 0.251 ms  
  12. tps = 215005.904762 (including connections establishing)  
  13. tps = 215027.544261 (excluding connections establishing)  
  14. statement latencies in milliseconds:  
  15.          0.001  \set id random(1,100000000)/64+:client_id  
  16.          0.296  insert into t ...........................

即使id取值范围就是0-31，性能也是杠杠的。

  1. vi test.sql  
  2. insert into t values (:client_id, md5(random()::text), now()) on conflict (id) do update set info=excluded.info , crt_time=excluded.crt_time;  
  4. pgbench -M prepared -n -r -P 1 -f ./test.sql -c 32 -j 32 -T 120  
  6. transaction type: ./test.sql  
  7. scaling factor: 1  
  8. query mode: prepared  
  9. number of clients: 32  
  10. number of threads: 32  
  11. duration: 120 s  
  12. number of transactions actually processed: 30045516  
  13. latency average = 0.127 ms  
  14. latency stddev = 0.073 ms  
  15. tps = 250377.400798 (including connections establishing)  
  16. tps = 250393.055448 (excluding connections establishing)  
  17. statement latencies in milliseconds:  
  18.          0.127  insert into t values (:client_id, ...........

如果没有使用client_id，那锁冲突就会比较严重，造成等待影响性能。从25万qps下降到了18万qps。

  1. vi test.sql  
  2. \set id random(0,31)  
  3. insert into t values (:id, md5(random()::text), now()) on conflict (id) do update set info=excluded.info , crt_time=excluded.crt_time;  
  5. pgbench -M prepared -n -r -P 1 -f ./test.sql -c 32 -j 32 -T 120  
  7. transaction type: ./test.sql  
  8. scaling factor: 1  
  9. query mode: prepared  
  10. number of clients: 32  
  11. number of threads: 32  
  12. duration: 120 s  
  13. number of transactions actually processed: 21619627  
  14. latency average = 0.177 ms  
  15. latency stddev = 0.138 ms  
  16. tps = 180162.287114 (including connections establishing)  
  17. tps = 180174.521514 (excluding connections establishing)  
  18. statement latencies in milliseconds:  
  19.          0.000  \set id random(0,31)  
  20.          0.177  insert into t values (:id, ............

小结

期待pgbench支持在identify字段中支持变量，而不仅仅是非identify内容中支持变量。

参考

《PostgreSQL 使用 pgbench 测试 sysbench 相关case - pg_oltp_bench》

《PostgreSQL 10.0 preview 性能增强 - 2PC事务恢复阶段性能提升》

《PostgreSQL native partition 分区表性能优化之 - 动态SQL+服务端绑定变量》

《PostgreSQL Oracle 兼容性之 - DBMS_SQL(存储过程动态SQL中使用绑定变量-DB端prepare statement)》

https://www.postgresql.org/docs/current/pgbench.html