负载均衡服务器性能评估,性能测试：pgpool做负载均衡的性能评估

1.测试环境maggie1：IP：192.168.31.101角色：pgpool_II + 流复制备库maggie2:IP:：192.168.31.102角色：流复制主库

2. 环境搭建1. maggie2上安装pg12.2到/usr/local/hg_dist/pg_ctl -D mnt/40g/pgdata/pgpool initpg_hba.conf:host all all 0.0.0.0/0 trust

host replication all 0.0.0.0/0 trustpostgresql.conf:listen_addresses = '*'

max_wal_senders = 5

wal_level = hot_standby

pg_ctl -D mnt/40g/pgdata/pgpool start2. maggie1上安装pgpool_II

下载链接：https://pgpool.net/mediawiki/index.php/Downloadsroot用户：mkdir pgpool

chown postgres:postgres pgpool

chown postgres:postgres mnt/40g/software/pgpool-II-4.1.2.tar.gzpostgres用户：cd mnt/40g/software/

tar -xzvf pgpool-II-4.1.2.tar.gz

cd pgpool-II-4.1.2

./configure --prefix='/pgpool'

--with-pgsql-libdir="/usr/local/hg_dist/lib"

--with-pgsql="/usr/local/hg_dist/"

make

make install3. 安装pgpool_regclasscd mnt/40g/software/pgpool-II-4.1.2/src/sql/pgpool-regclass/

make

make install#因为pgpool和PG不在同一台服务器，需要把library拷贝到目标服务器scppgpool-regclass.so postgres@maggie2:/usr/local/hg_dist/lib/postgresqlpsql -f

pgpool-regclass.sql -h maggie2 -p 5432 template14. 官方还推荐安装pgpool_recovery和insert_lock，分别用于在线恢复和复制功能。此外，pgpool还有很多工具，比如pcp,  watchdog,  healthcheck, 可以根据需求分别配置使用。本文仅做性能测试，只用到了负载均衡和连接池的功能，配置也只配置了这两个功能。5. 在maggie1上安装备库，建立流复制安装pg12.2pg_basebackup -h 192.168.31.102 -p 5432 -U postgres -Fp -Xs -Pv -R -D /mnt/40g/pgdata/pgpool

pg_ctl start6. 配置pcp.conf[root@maggie1 etc]# pg_md5 -p postgres

password:

e8a48653851e28c69d0506508fb27fc5按照用户名:MD5值的格式写入pcp.conf中说明：pcp.conf 对于运行 pgpool-II 的用户必须可读7.配置pgpool.confcd usr/local/etc #pgpool的默认安装路径

cp pgpool.conf.sample pgpool.conf

listen_addresses='*' #接受所有的链接

socket_dir='/var/run' #建立接受 UNIX 域套接字连接的目录，修改要重启pgpool

pcp_listen_addresses = '*'

pcp_socket_dir = '/var/run'

backend_hostname0 = 'maggie1'

backend_port0 = 5432

backend_weight0 = 1 #负载均衡的权限，两台backend权限相同时，做平均分配

backend_data_directory0 = '/mnt/40g/pgdata/pgpool'

backend_hostname1 = 'maggie2'

backend_port1 = 5432

backend_weight1 = 1

backend_data_directory1 = '/mnt/40g/pgdata/pgpool'

load_balance_mode = on

master_slave_mode = on

master_slave_sub_mode = 'stream'

sr_check_period = 2

sr_check_user = 'postgres'

sr_check_database = 'postgres'

delay_threshold = 10

replication_mode = off

replicate_select = off

num_init_children = 150 // 从客户端发起的最大并发连接数

max_pool = 4 //到PG的连接数为num_init_children * max_pool8. 启动/停止pgpoolpgpool -n  > /tmp/pgpool.log 2>&1 &

pgpool stop

3. 测试数据测试中使用pgbench select-only的sql语句

3.1相同并发在相同的并发量时，通过对比各个服务器的CPU利用率

3.1.1 pgbench直连pgpool[postgres@maggie1 ~]$ pgbench -c 300 -j 300 -M prepared -n -P 2 -S -T 200 -r -h maggie1 -p 9999

transaction type:

scaling factor: 100

query mode: prepare

dnumber of clients: 300

number of threads: 300

duration: 200 s

number of transactions actually processed: 2898267

latency average = 10.293 ms

latency stddev = 5.346 ms

tps = 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;

Maggie1备节点/pgpool:CPU:Use%Sys%Wai%Idle%CPU%

Avg30.462.40.07.392.7

Max34.368.70.099.6100.0

Max:Avg1.11.10.013.71.1Maggie2-主节点:CPU:Use%Sys%Wai%Idle%CPU%

Avg42.037.10.020.979.1

Max45.844.40.099.887.5

Max:Avg1.11.20.04.81.1

3.1.2 pgbench直连主节点[postgres@maggie1 ~]$ pgbench -c 300 -j 300 -M prepared -n -P 2 -S -T 200 -r -h maggie2 -p 5432

transaction type:

scaling factor: 100

query mode: prepared

number of clients: 300

number of threads: 300

duration: 200 s

number of transactions actually processed: 4662318

latency average = 12.758 ms

latency stddev = 3.460 ms

tps = 23281.878597 (including connections establishing)

tps = 23473.435897 (excluding connections establishing)statement latencies in milliseconds:0.003  \set aid random(1, 100000 * :scale)

12.762  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;Maggie1 :CPU:Use%Sys%Wai%Idle%CPU%

Avg12.216.20.071.628.4

Max14.020.10.299.634.1

Max:Avg1.11.2100.01.41.2Maggie2:CPU:Use%Sys%Wai%Idle%CPU%

Avg46.226.60.027.372.7

Max64.839.40.199.8100.0

Max:Avg1.41.552.03.71.4

3.2 CPU达到最满在尽量把服务器的CPU打满的情况下，对比支持的并发数

3.2.1 pgbench直连pgpool[postgres@maggie1 ~]$ pgbench -c 400 -j 300 -M prepared -n -P 2 -S -T 200 -r -h maggie1 -p 9999

transaction type:

scaling factor: 100

query mode: prepared

number of clients: 400

number of threads: 300

duration: 200 s

number of transactions actually processed: 2879549

latency average = 6.795 ms

latency stddev = 3.551 ms

tps = 14357.998016 (including connections establishing)

tps = 20360.802379 (excluding connections establishing)statement latenciesinmilliseconds:0.004 \set aid random(1, 100000 * :scale)

6.792  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;Maggie1：CPU:Use%Sys%Wai%Idle%CPU%

Avg25.457.50.017.182.9

Max32.170.60.099.5100.0

Max:Avg1.31.20.05.81.2Maggie2：CPU:Use%Sys%Wai%Idle%CPU%

Avg36.237.10.026.773.3

Max43.453.50.199.887.1

Max:Avg1.21.4100.03.71.2

3.2.2 pgbench直连主节点[postgres@maggie1 ~]$ pgbench -c 400 -j 400 -M prepared -n -P 2 -S -T 200 -r -h maggie2 -p 5432

transaction type:

scaling factor: 100

query mode: prepared

number of clients: 400

number of threads: 400

duration: 200 s

number of transactions actually processed: 4523310

latency average = 17.420 ms

latency stddev = 4.249 ms

tps = 22587.966748 (including connections establishing)

tps = 22910.531557 (excluding connections establishing)statement latencies in milliseconds:0.004 \set aid random(1, 100000 * :scale)

17.424 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;Maggie1：CPU:Use%Sys%Wai%Idle%CPU%

Avg11.616.40.072.028.0

Max13.420.10.199.532.7

Max:Avg1.21.2100.01.41.2Maggie2：CPU:Use%Sys%Wai%Idle%CPU%

Avg59.639.50.00.999.1

Max62.843.10.034.5100.0

Max:Avg1.11.10.038.81.0

4. 测试结论1. 在相同的并发的情况下。应用程序直连主库时，备库的CPU利用率只有28.4%，而且占用资源的主要是pgpool。应用程序通过pgpool连接主备库做负载均衡，能充分利用两台机器的资源，不会造成所有的traffic压在同一台机器上的情况，主库和备库所在的服务器CPU利用率都在80%~90%。2. 在相同的max_connections时，通过增加并发数把服务器的CPU利用到最大的情况下，使用pgpool能支持更多的并发，但是TPS会有相应损耗，网络延迟也会增加3. 使用pgpool与直连数据库主库相比TPS优势并不明显，甚至有些情况下TPS会降低。说明pgpool的主要优势不在于会显著提高TPS，而是充分利用各个主机的性能，在相对低端的硬件服务器上活得一个相对满意的性能。作者：孙惠惠瀚高基础软件研发工程师I Love PG