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 init pg_hba.conf: 

host   all             all             0.0.0.0/0            trusthost   replication     all             0.0.0.0/0            trust


postgresql.conf: 

listen_addresses = '*'max_wal_senders = 5wal_level = hot_standbypg_ctl -D /mnt/40g/pgdata/pgpool start


2. maggie1上安装pgpool_II

下载链接：https://pgpool.net/mediawiki/index.php/Downloads


root用户： 

mkdir /pgpoolchown postgres:postgres  /pgpoolchown postgres:postgres /mnt/40g/software/pgpool-II-4.1.2.tar.gz


postgres用户： 

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


3. 安装pgpool_regclass 

cd /mnt/40g/software/pgpool-II-4.1.2/src/sql/pgpool-regclass/makemake install


#因为pgpool和PG不在同一台服务器，需要把library拷贝到目标服务器 

scppgpool-regclass.so postgres@maggie2:/usr/local/hg_dist/lib/postgresqlpsql -f pgpool-regclass.sql -h maggie2 -p 5432 template1


4. 官方还推荐安装pgpool_recovery和insert_lock，分别用于在线恢复和复制功能。此外，pgpool还有很多工具，比如pcp,  watchdog,  healthcheck, 可以根据需求分别配置使用。本文仅做性能测试，只用到了负载均衡和连接池的功能，配置也只配置了这两个功能。   5. 在maggie1上安装备库，建立流复制 安装pg12.2 

pg_basebackup -h 192.168.31.102 -p 5432 -U postgres -Fp -Xs -Pv -R -D /mnt/40g/pgdata/pgpoolpg_ctl start


6. 配置pcp.conf 

[root@maggie1 etc]# pg_md5 -p postgrespassword:e8a48653851e28c69d0506508fb27fc5


按照用户名:MD5值的格式写入pcp.conf中 说明：pcp.conf 对于运行 pgpool-II 的用户必须可读   7.配置pgpool.conf 

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


8. 启动/停止pgpool 

pgpool -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 9999transaction type: 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;


Maggie1备节点/pgpool:

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


Maggie2-主节点:

CPU:

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: 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)


       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%

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


Maggie2:

CPU:

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达到最满


在尽量把服务器的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 9999transaction type: 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)


statement latencies in milliseconds:     

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%

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


  Maggie2：

CPU:

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: 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)


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%

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


Maggie2：

CPU:

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. 测试结论


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

  作者：孙惠惠