性能测试：pgpool做负载均衡的性能评估
1. 测试环境
2. 环境搭建
host all all 0.0.0.0/0 trusthost replication all 0.0.0.0/0 trust
listen_addresses = '*'max_wal_senders = 5wal_level = hot_standbypg_ctl -D /mnt/40g/pgdata/pgpool start
下载链接：https://pgpool.net/mediawiki/index.php/Downloads
mkdir /pgpoolchown postgres:postgres /pgpoolchown postgres:postgres /mnt/40g/software/pgpool-II-4.1.2.tar.gz
cd /mnt/40g/software/tar -xzvf pgpool-II-4.1.2.tar.gzcd pgpool-II-4.1.2./configure --prefix='/pgpool'--with-pgsql-libdir="/usr/local/hg_dist/lib"--with-pgsql="/usr/local/hg_dist/"makemake install
cd /mnt/40g/software/pgpool-II-4.1.2/src/sql/pgpool-regclass/makemake install
scppgpool-regclass.so postgres@maggie2:/usr/local/hg_dist/lib/postgresqlpsql -fpgpool-regclass.sql -h maggie2 -p 5432 template1
pg_basebackup -h 192.168.31.102 -p 5432 -U postgres -Fp -Xs -Pv -R -D /mnt/40g/pgdata/pgpoolpg_ctl start
[]password:e8a48653851e28c69d0506508fb27fc5
cd /usr/local/etc #pgpool的默认安装路径cp pgpool.conf.sample pgpool.conflisten_addresses='*' #接受所有的链接socket_dir='/var/run' #建立接受 UNIX 域套接字连接的目录，修改要重启pgpoolpcp_listen_addresses = '*'pcp_socket_dir = '/var/run'backend_hostname0 = 'maggie1'backend_port0 = 5432backend_weight0 = 1 #负载均衡的权限，两台backend权限相同时，做平均分配backend_data_directory0 = '/mnt/40g/pgdata/pgpool'backend_hostname1 = 'maggie2'backend_port1 = 5432backend_weight1 = 1backend_data_directory1 = '/mnt/40g/pgdata/pgpool'load_balance_mode = onmaster_slave_mode = onmaster_slave_sub_mode = 'stream'sr_check_period = 2sr_check_user = 'postgres'sr_check_database = 'postgres'delay_threshold = 10replication_mode = offreplicate_select = offnum_init_children = 150 // 从客户端发起的最大并发连接数max_pool = 4 //到PG的连接数为num_init_children * max_pool
pgpool -n > /tmp/pgpool.log 2>&1 &pgpool stop
3. 测试数据
3.1相同并发
3.1.1 pgbench直连pgpool
[postgres@maggie1 ~]$ pgbench -c 300 -j 300 -M prepared -n -P 2 -S -T 200 -r -h maggie1 -p 9999transaction type: <builtin: select only>scaling factor: 100query mode: preparednumber of clients: 300number of threads: 300duration: 200 snumber of transactions actually processed: 2898267latency average = 10.293 mslatency stddev = 5.346 mstps = 14445.955582 (including connections establishing)tps = 25308.963441 (excluding connections establishing)
statement latencies in milliseconds:
0.005 \set aid random(1,100000 * :scale)10.290 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
CPU: |
Use% |
Sys% |
Wai% |
Idle% |
CPU% |
Avg |
30.4 |
62.4 |
0.0 |
7.3 |
92.7 |
Max |
34.3 |
68.7 |
0.0 |
99.6 |
100.0 |
Max:Avg |
1.1 |
1.1 |
0.0 |
13.7 |
1.1 |
|
|
Use% |
Sys% |
Wai% |
Idle% |
CPU% |
Avg |
42.0 |
37.1 |
0.0 |
20.9 |
79.1 |
Max |
45.8 |
44.4 |
0.0 |
99.8 |
87.5 |
Max:Avg |
1.1 |
1.2 |
0.0 |
4.8 |
1.1 |
3.1.2 pgbench直连主节点
[postgres@maggie1 ~]$ pgbench -c 300 -j 300 -M prepared -n -P 2 -S -T 200 -r -h maggie2 -p 5432transaction type: <builtin: select only>scaling factor: 100query mode: preparednumber of clients: 300number of threads: 300duration: 200 snumber of transactions actually processed: 4662318latency average = 12.758 mslatency stddev = 3.460 mstps = 23281.878597 (including connections establishing)tps = 23473.435897 (excluding connections establishing)
0.003 \set aid random(1, 100000 * :scale)12.762 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
CPU: |
Use% |
Sys% |
Wai% |
Idle% |
CPU% |
Avg |
12.2 |
16.2 |
0.0 |
71.6 |
28.4 |
Max |
14.0 |
20.1 |
0.2 |
99.6 |
34.1 |
Max:Avg |
1.1 |
1.2 |
100.0 |
1.4 |
1.2 |
|
|
Use% |
Sys% |
Wai% |
Idle% |
CPU% |
Avg |
46.2 |
26.6 |
0.0 |
27.3 |
72.7 |
Max |
64.8 |
39.4 |
0.1 |
99.8 |
100.0 |
Max:Avg |
1.4 |
1.5 |
52.0 |
3.7 |
1.4 |
3.2 CPU达到最满
3.2.1 pgbench直连pgpool
[]$ pgbench -c 400 -j 300 -M prepared -n -P 2 -S -T 200 -r -h maggie1 -p 9999transaction type: <builtin: select only>scaling factor: 100query mode: preparednumber of clients: 400number of threads: 300duration: 200 snumber of transactions actually processed: 2879549latency average = 6.795 mslatency stddev = 3.551 mstps = 14357.998016 (including connections establishing)tps = 20360.802379 (excluding connections establishing)
0.004 \set aid random(1, 100000 * :scale)6.792 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
CPU: |
Use% |
Sys% |
Wai% |
Idle% |
CPU% |
Avg |
25.4 |
57.5 |
0.0 |
17.1 |
82.9 |
Max |
32.1 |
70.6 |
0.0 |
99.5 |
100.0 |
Max:Avg |
1.3 |
1.2 |
0.0 |
5.8 |
1.2 |
|
|
Use% |
Sys% |
Wai% |
Idle% |
CPU% |
Avg |
36.2 |
37.1 |
0.0 |
26.7 |
73.3 |
Max |
43.4 |
53.5 |
0.1 |
99.8 |
87.1 |
Max:Avg |
1.2 |
1.4 |
100.0 |
3.7 |
1.2 |
3.2.2 pgbench直连主节点
[postgres@maggie1 ~]$ pgbench -c 400 -j 400 -M prepared -n -P 2 -S -T 200 -r -h maggie2 -p 5432transaction type: <builtin: select only>scaling factor: 100query mode: preparednumber of clients: 400number of threads: 400duration: 200 snumber of transactions actually processed: 4523310latency average = 17.420 mslatency stddev = 4.249 mstps = 22587.966748 (including connections establishing)tps = 22910.531557 (excluding connections establishing)
0.004 \set aid random(1, 100000 * :scale)17.424 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
CPU: |
Use% |
Sys% |
Wai% |
Idle% |
CPU% |
Avg |
11.6 |
16.4 |
0.0 |
72.0 |
28.0 |
Max |
13.4 |
20.1 |
0.1 |
99.5 |
32.7 |
Max:Avg |
1.2 |
1.2 |
100.0 |
1.4 |
1.2 |
|
|
Use% |
Sys% |
Wai% |
Idle% |
CPU% |
Avg |
59.6 |
39.5 |
0.0 |
0.9 |
99.1 |
Max |
62.8 |
43.1 |
0.0 |
34.5 |
100.0 |
Max:Avg |
1.1 |
1.1 |
0.0 |
38.8 |
1.0 |
4. 测试结论
I Love PG
关于我们
中国开源软件推进联盟PostgreSQL分会（简称：中国PG分会）于2017年成立，由国内多家PostgreSQL生态企业所共同发起，业务上接受工信部中国电子信息产业发展研究院指导。中国PG分会是一个非盈利行业协会组织。我们致力于在中国构建PostgreSQL产业生态，推动PostgreSQL产学研用发展。
欢迎投稿
做你的舞台，show出自己的才华 。
投稿邮箱：[email protected]
——愿能安放你不羁的灵魂
技术文章 精彩 回顾
PG活动 精彩 回顾
PG培训认证精彩回顾