Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2339378
  • 博文数量: 194
  • 博客积分: 3409
  • 博客等级: 中校
  • 技术积分: 3944
  • 用 户 组: 普通用户
  • 注册时间: 2010-12-24 10:21
个人简介

● ITPUB名人堂嘉宾 ● ChinaUnix社区博客专家 ● ChinaUnix社区Oracle板块版主 ● 优酷网认证音乐牛人:EricGuitar ● SDOUG 核心成员 ●E-mail:gaoqiangdba@163.com

文章分类

全部博文(194)

文章存档

2020年(6)

2019年(7)

2016年(5)

2015年(36)

2014年(23)

2013年(15)

2012年(23)

2011年(61)

2010年(18)

分类: Mysql/postgreSQL

2015-03-13 17:13:39

  


实验环境:
数据库版本: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.在应用层面进行压力测试也是很好的选择;如果有足够的数据库开发和管理经验,自己编写测试脚本测试也不错。


阅读(9868) | 评论(2) | 转发(1) |
给主人留下些什么吧!~~

jackson1985742015-03-17 09:38:31

skykiker:关于“延迟非常明显”的那条SQL,我研究了一下,是正常的并发争用导致的,请参考:  http://blog.chinaunix.net/xmlrpc.php?r=blog/article&uid=20726500&id=4897249

回复 | 举报

skykiker2015-03-16 20:41:12

关于“延迟非常明显”的那条SQL,我研究了一下,是正常的并发争用导致的,请参考:  http://blog.chinaunix.net/xmlrpc.php?r=blog/article&uid=20726500&id=4897249