PostgreSQL数据库性能压力测试实验---pgbench

  


实验环境：
数据库版本：PostgeSQL 9.4.1
操作系统：RHEL 6
测试工具：pgbench


测试内容：
PostgreSQL默认测试脚本，含UPDATE、INSERT还有SELECT等操作，模拟一次简短的 “查询---交易---确认”过程。

测试模型：TCP-B


关注指标：TPS


实验一：

100个用户，每个用户10个事务：
-bash-4.1$ /usr/pgsql-9.4/bin/pgbench -c 100 -t 10 pgbench
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 100
number of threads: 1
number of transactions per client: 10
number of transactions actually processed: 1000/1000
latency average: 0.000 ms
tps = 200.897611 (including connections establishing)
tps = 240.591952 (excluding connections establishing)

500个用户，每个用户10个事务：
-bash-4.1$ /usr/pgsql-9.4/bin/pgbench -c 500 -t 10 pgbench
starting vacuum...end.
Connection to database "pgbench" failed:   
FATAL:  sorry, too many clients already          ---发现报错了，因为超出了数据库的配置上限。
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 500
number of threads: 1
number of transactions per client: 10
number of transactions actually processed: 0/5000

查看一下默认的连接数上限：
postgres=# show max_connections;
 max_connections 
-----------------
 100
(1 row)

原来如此，咱们改成1000再来测~~~



800个用户，每个用户一个事务：
-bash-4.1$ /usr/pgsql-9.4/bin/pgbench -c 800 -t 1 pgbench
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 800
number of threads: 1
number of transactions per client: 1
number of transactions actually processed: 800/800
latency average: 0.000 ms
tps = 88.271654 (including connections establishing)
tps = 138.571954 (excluding connections establishing)

800个用户，每个用户5个事务：
-bash-4.1$ /usr/pgsql-9.4/bin/pgbench -c 800 -t  5 pgbench
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 800
number of threads: 1
number of transactions per client: 5
number of transactions actually processed: 4000/4000
latency average: 0.000 ms
tps = 137.635256 (including connections establishing)
tps = 148.666073 (excluding connections establishing)


算上链接建立时间，单纯增加事务量对性能改善不算特别明显。

那我们加一下并行看一下：

500个用户，每用户5个事务，4线程并发处理：
-bash-4.1$ /usr/pgsql-9.4/bin/pgbench -c 500 -t 5 -j 4  pgbench
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 500
number of threads: 4
number of transactions per client: 5
number of transactions actually processed: 2500/2500
latency average: 0.000 ms
tps = 239.428467 (including connections establishing)
tps = 268.057348 (excluding connections establishing)


把用户数提升到800看一下：
-bash-4.1$ /usr/pgsql-9.4/bin/pgbench -c 800 -t 5 -j 4  pgbench
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 800
number of threads: 4
number of transactions per client: 5
number of transactions actually processed: 4000/4000
latency average: 0.000 ms
tps = 167.058844 (including connections establishing)
tps = 180.042970 (excluding connections establishing)    ---看来略吃力



再回到500用户，把并发增加一倍：
-bash-4.1$ /usr/pgsql-9.4/bin/pgbench -c 496 -t 5 -j 8  pgbench
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 496
number of threads: 8
number of transactions per client: 5
number of transactions actually processed: 2480/2480
latency average: 0.000 ms
tps = 293.699321 (including connections establishing)
tps = 397.022039 (excluding connections establishing)    ---大品牌，疗效好，腰也不疼了，腿也不酸了~


或许客户要求把用户数提高一下，那么可以看一下具体在哪个环节延迟比较高，然后调整一下语句后者是逻辑思路：
-bash-4.1$ /usr/pgsql-9.4/bin/pgbench -c 800 -t 5 -j 4  -r pgbench
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 800
number of threads: 4
number of transactions per client: 5
number of transactions actually processed: 4000/4000
latency average: 0.000 ms
tps = 217.188339 (including connections establishing)
tps = 243.570070 (excluding connections establishing)
statement latencies in milliseconds:
0.007152 \set nbranches 1 * :scale
0.001217 \set ntellers 10 * :scale
0.000891 \set naccounts 100000 * :scale
0.001649 \setrandom aid 1 :naccounts
0.000924 \setrandom bid 1 :nbranches
0.000974 \setrandom tid 1 :ntellers
0.002318 \setrandom delta -5000 5000
43.159543 BEGIN;
67.412356 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
19.172284 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
2483.177474 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;                          -----延迟非常明显，可以从此入手
37.104961 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
0.466527 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
3.609726 END;




模拟交易测试：

平时我们在网上买东西、团购卷、订机票等等的时候，一般最后结账的时候看一眼信息、输入以下支付密码，然后不经意的点一下付款成功的信息，我们假设平均大概在20秒左右。


-bash-4.1$ /usr/pgsql-9.4/bin/pgbench -c 500  -j 4  -T 20  pgbench
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 500
number of threads: 4
duration: 20 s
number of transactions actually processed: 5915
latency average: 1690.617 ms
tps = 276.500363 (including connections establishing)
tps = 286.207582 (excluding connections establishing)


效果还是不错的，500个用户并发操作，平均延迟在3秒以内，符合心理和生理的需求。



注：
1.由于涉及业务内容不同、软硬件配置不同等原因，在不同的环境和时间段测试结果可能会有很大的出入。
2.压力工具除了pgbench还有很多优秀的作品，pgbench的功能也是很丰富的，在此没有一一展现。
3.Oracle、MySQL还有MongoDB等优秀的数据库产品也有自己的性能测试工具，开源的和第三方的都有，可以去尝试，欢迎交流和分享。
4.在应用层面进行压力测试也是很好的选择；如果有足够的数据库开发和管理经验，自己编写测试脚本测试也不错。