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

智能血压计实时监测血压并记录历史数据 #生活知识# #科技生活# #健康生活技巧# #智能健康监测#

实验环境：
数据库版本：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.159543BEGIN;
67.412356UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
19.172284SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
2483.177474UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;                          -----延迟非常明显，可以从此入手
37.104961UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
0.466527INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
3.609726END;

模拟交易测试：

平时我们在网上买东西、团购卷、订机票等等的时候，一般最后结账的时候看一眼信息、输入以下支付密码，然后不经意的点一下付款成功的信息，我们假设平均大概在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.在应用层面进行压力测试也是很好的选择；如果有足够的数据库开发和管理经验，自己编写测试脚本测试也不错。

阅读(15612) | 评论(2) | 转发(1) |

网址：PostgreSQL数据库性能压力测试实验 https://www.yuejiaxmz.com/news/view/429679

相关内容

如何在 PostgreSQL 中实现数据的压缩存储以节省空间？
跟着外包干了3天，学会了PostgreSQL压测的精髓
MySQL 之压力测试工具的使用方法
网络压力测试软件是什么（分析十款Web服务器性能压力测试工具）
(史上最全最详细教程)压力测试实战，压力测试步骤压力测试数据
电商系统怎么优化性能，如何优化数据库性能
sysbench压力测试工具简介
[性能测试] Benchmarks(压力测试工具汇总)
性能测试、负载测试、压力测试之间的差异（超详细）
安卓Android压力测试与性能测试详解！

随便看看