阿里云ApsaraDB RDS for PostgreSQL 最佳实践 - 5 长短连接测试
背景
本文将教你测试长连接和短连接的性能。
我们在连接阿里云RDS for PostgreSQL时，实际上并不是直接连接数据库的，而是通过了SLB。
那么这个代理有没有连接池功能呢？通过测试发现，即使有连接池的功能，也是会话级别的，所以如果你的业务系统如果是高并发的短事务，建议你在应用层启用连接池，如果不能启用，那么请在应用层自己假设一个连接池例如pgbouncer。
测试：
3433代理并不是全代理，所以我们看到客户端IP地址就是实际的客户端IP，而不是代理的IP。
postgres@xxx-> psql -h xxxxxx.pg.rds.aliyuncs.com -p 3433 -U digoal postgres
psql (9.4.3, server 9.4.1)
Type "help" for help.
postgres=> select inet_server_addr(),inet_server_port(),inet_client_addr(),inet_client_port();
inet_server_addr | inet_server_port | inet_client_addr | inet_client_port
------------------+------------------+------------------+------------------
10.151.133.24 | 3006 | 10.172.180.141 | 48520
(1 row)
postgres@xxx-> netstat -anp|grep 3433
tcp 0 0 10.172.180.141:48520 100.99.60.159:3433 ESTABLISHED 29955/psql
代理的IP是100.99.60.159
postgres@xxx-> dig xxxxxx.pg.rds.aliyuncs.com
; <<>> DiG 9.8.2rc1-RedHat-9.8.2-0.23.rc1.el6_5.1 <<>> xxxxxx.pg.rds.aliyuncs.com
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 33061
;; flags: qr aa rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 3, ADDITIONAL: 4
;; QUESTION SECTION:
;xxxxxx.pg.rds.aliyuncs.com. IN A
;; ANSWER SECTION:
xxxxxx.pg.rds.aliyuncs.com. 60 IN A 100.99.60.159
;; AUTHORITY SECTION:
rds.aliyuncs.com. 432000 IN NS ns5.aliyun.com.
rds.aliyuncs.com. 432000 IN NS ns3.aliyun.com.
rds.aliyuncs.com. 432000 IN NS ns4.aliyun.com.
;; ADDITIONAL SECTION:
ns3.aliyun.com. 432000 IN A 115.124.17.155
ns4.aliyun.com. 432000 IN A 110.75.20.27
ns5.aliyun.com. 432000 IN A 110.75.38.28
ns5.aliyun.com. 432000 IN A 198.11.138.248
;; Query time: 0 msec
;; SERVER: 10.202.72.118#53(10.202.72.118)
;; WHEN: Tue Jun 16 08:36:46 2015
;; MSG SIZE rcvd: 200
短连接TPS测试结果：
postgres@xxx-> vi test.sql
select 1;
pgbench -M extended -C -n -r -f ./test.sql -P 1 -c 88 -j 88 -T 30 -h xxxxxx.pg.rds.aliyuncs.com -p 3433 -U digoal
progress: 1.0 s, 3201.8 tps, lat 2.234 ms stddev 0.372
progress: 2.0 s, 3291.6 tps, lat 2.241 ms stddev 0.349
progress: 3.0 s, 3352.2 tps, lat 2.250 ms stddev 0.370
progress: 4.0 s, 3310.7 tps, lat 2.253 ms stddev 0.361
progress: 5.0 s, 3316.0 tps, lat 2.369 ms stddev 0.528
progress: 6.0 s, 3320.8 tps, lat 2.385 ms stddev 0.484
progress: 7.0 s, 3310.0 tps, lat 2.398 ms stddev 0.480
progress: 8.0 s, 3382.5 tps, lat 2.409 ms stddev 0.465
progress: 9.0 s, 3333.0 tps, lat 2.412 ms stddev 0.475
progress: 10.0 s, 3333.7 tps, lat 2.400 ms stddev 0.464
postgres@xxx-> pgbench -M extended -C -n -r -f ./test.sql -P 1 -c 88 -j 88 -T 30 -h 100.99.60.159 -p 3433 -U digoal
Password:
progress: 1.0 s, 3433.3 tps, lat 2.290 ms stddev 0.378
progress: 2.0 s, 3330.3 tps, lat 2.257 ms stddev 0.342
progress: 3.0 s, 3326.6 tps, lat 2.273 ms stddev 0.453
progress: 4.0 s, 3293.9 tps, lat 2.244 ms stddev 0.358
progress: 5.0 s, 3343.6 tps, lat 2.276 ms stddev 0.352
progress: 6.0 s, 3421.8 tps, lat 2.322 ms stddev 0.398
progress: 7.0 s, 3611.0 tps, lat 2.475 ms stddev 0.493
progress: 8.0 s, 3599.6 tps, lat 2.454 ms stddev 0.480
progress: 9.0 s, 3554.5 tps, lat 2.458 ms stddev 0.476
progress: 10.0 s, 3590.4 tps, lat 2.466 ms stddev 0.476
长连接TPS测试结果：
postgres@xxx-> pgbench -M extended -n -r -f ./test.sql -P 1 -c 88 -j 88 -T 30 -h xxxxxx.pg.rds.aliyuncs.com -p 3433 -U digoal
progress: 1.0 s, 46156.5 tps, lat 1.847 ms stddev 0.370
progress: 2.0 s, 46477.1 tps, lat 1.892 ms stddev 0.238
progress: 3.0 s, 46863.0 tps, lat 1.877 ms stddev 0.233
progress: 4.0 s, 47023.7 tps, lat 1.870 ms stddev 0.282
progress: 5.0 s, 44680.1 tps, lat 1.968 ms stddev 0.649
progress: 6.0 s, 44693.4 tps, lat 1.967 ms stddev 0.600
progress: 7.0 s, 46783.2 tps, lat 1.880 ms stddev 0.286
progress: 8.0 s, 46629.4 tps, lat 1.886 ms stddev 0.249
progress: 9.0 s, 46894.5 tps, lat 1.875 ms stddev 0.249
progress: 10.0 s, 46838.3 tps, lat 1.877 ms stddev 0.274
progress: 11.0 s, 46993.2 tps, lat 1.871 ms stddev 0.272
progress: 12.0 s, 46889.7 tps, lat 1.875 ms stddev 0.248
postgres@xxx-> pgbench -M extended -n -r -f ./test.sql -P 1 -c 88 -j 88 -T 30 -h 100.99.60.159 -p 3433 -U digoal
Password:
progress: 1.0 s, 45056.3 tps, lat 1.907 ms stddev 0.669
progress: 2.0 s, 46966.6 tps, lat 1.872 ms stddev 0.361
progress: 3.0 s, 47453.7 tps, lat 1.853 ms stddev 0.249
progress: 4.0 s, 46933.7 tps, lat 1.873 ms stddev 0.388
progress: 5.0 s, 47332.4 tps, lat 1.858 ms stddev 0.267
progress: 6.0 s, 46756.9 tps, lat 1.880 ms stddev 0.389
progress: 7.0 s, 45291.0 tps, lat 1.942 ms stddev 0.628
progress: 8.0 s, 47250.0 tps, lat 1.861 ms stddev 0.280
progress: 9.0 s, 45621.5 tps, lat 1.927 ms stddev 0.581
progress: 10.0 s, 45244.7 tps, lat 1.944 ms stddev 0.726
进程模式相比线程模式，fork process开销大一点，所以高并发的短事务请求，建议使用连接池。
参考
1. http://git.postgresql.org/gitweb/?p=pgbouncer.git;a=summary