我的书签
添加书签
移除书签背景
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
