pgpool ii在lightdb下的性能测试

1、从https://www.pgpool.net/下载最新版pgpool ii，如4.3.2。

2、假设安装了postgresql或lightdb，百度一搜即可

3、解压包，执行./configure  && make && make install

4、修改配置pgpool.conf，拷贝一个pgpool.conf.sample即可。

listen_addresses = '*'

backend_hostname0 = 'localhost'
backend_port0 = 23456
backend_weight0 = 1
backend_flag0 = 'ALWAYS_PRIMARY'

pid_file_name = '/home/zjh/pgpool4.3.2/bin/pgpool.pid'

sr_check_user = 'zjh'   # 如果不是用zjh用户安装，需要改成具体安装lightdb的用户名如lightdb

sr_check_password = 'zjh'

health_check_user = 'zjh'

health_check_password = 'zjh'

启动pgpool ii，如下：

[zjh@hs-10-20-30-193 bin]$ 2022-11-05 16:18:19.139: main pid 227171: LOG:  health_check_stats_shared_memory_size: requested size: 12288
2022-11-05 16:18:19.139: main pid 227171: LOG:  memory cache initialized
2022-11-05 16:18:19.139: main pid 227171: DETAIL:  memcache blocks :64
2022-11-05 16:18:19.139: main pid 227171: LOG:  allocating (136981824) bytes of shared memory segment
2022-11-05 16:18:19.139: main pid 227171: LOG:  allocating shared memory segment of size: 136981824 
2022-11-05 16:18:19.221: main pid 227171: LOG:  health_check_stats_shared_memory_size: requested size: 12288
2022-11-05 16:18:19.221: main pid 227171: LOG:  health_check_stats_shared_memory_size: requested size: 12288
2022-11-05 16:18:19.221: main pid 227171: LOG:  memory cache initialized
2022-11-05 16:18:19.221: main pid 227171: DETAIL:  memcache blocks :64
2022-11-05 16:18:19.223: main pid 227171: LOG:  pool_discard_oid_maps: discarded memqcache oid maps
2022-11-05 16:18:19.231: main pid 227171: LOG:  Setting up socket for 0.0.0.0:9999
2022-11-05 16:18:19.231: main pid 227171: LOG:  Setting up socket for :::9999
2022-11-05 16:18:19.236: main pid 227171: LOG:  find_primary_node_repeatedly: waiting for finding a primary node
2022-11-05 16:18:19.236: pcp_main pid 227206: LOG:  PCP process: 227206 started
2022-11-05 16:18:19.236: sr_check_worker pid 227207: LOG:  process started
2022-11-05 16:18:19.236: health_check pid 227208: LOG:  process started
2022-11-05 16:18:19.237: main pid 227171: LOG:  pgpool-II successfully started. version 4.3.2 (tamahomeboshi)
2022-11-05 16:18:19.237: main pid 227171: LOG:  node status[0]: 0

测试pgpool ii连接：

[zjh@hs-10-20-30-193 ~]$ ltsql -h127.0.0.1 -p9999 postgres
ltsql (13.8-22.3)
Type "help" for help.

zjh@postgres=# select * from pg_stat_activity ;

造数据：

[zjh@hs-10-20-30-193 ~]$ ltbench -i -s 100 -h127.0.0.1 -p9999 postgres
dropping old tables...
creating tables...
generating data (client-side)...
10000000 of 10000000 tuples (100%) done (elapsed 12.68 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 25.65 s (drop tables 0.05 s, create tables 0.00 s, client-side generate 13.27 s, vacuum 7.15 s, primary keys 5.19 s).

[zjh@hs-10-20-30-193 pgpool4.3.2]$ ltbench -c 64 -j 8 -M prepared -T 30 -P 1 -h127.0.0.1 -p23456 postgres
starting vacuum...end.
progress: 1.0 s, 51105.5 tps, lat 1.201 ms stddev 0.459
progress: 2.0 s, 53287.1 tps, lat 1.189 ms stddev 0.397
progress: 3.0 s, 54185.8 tps, lat 1.169 ms stddev 0.372
progress: 4.0 s, 53884.5 tps, lat 1.176 ms stddev 0.384
progress: 5.0 s, 54041.0 tps, lat 1.173 ms stddev 0.379
progress: 6.0 s, 54867.7 tps, lat 1.155 ms stddev 0.381
progress: 7.0 s, 54614.3 tps, lat 1.159 ms stddev 0.400
progress: 8.0 s, 55060.0 tps, lat 1.151 ms stddev 0.384
progress: 9.0 s, 53635.5 tps, lat 1.181 ms stddev 0.431
progress: 10.0 s, 54466.5 tps, lat 1.164 ms stddev 0.408
progress: 11.0 s, 55076.2 tps, lat 1.150 ms stddev 0.374
progress: 12.0 s, 55039.3 tps, lat 1.151 ms stddev 0.392
progress: 13.0 s, 55673.1 tps, lat 1.138 ms stddev 0.373
progress: 14.0 s, 55915.3 tps, lat 1.133 ms stddev 0.361
progress: 15.0 s, 55872.1 tps, lat 1.135 ms stddev 0.359
^C
[zjh@hs-10-20-30-193 pgpool4.3.2]$ ltbench -c 64 -j 8 -M prepared -T 30 -P 1 -h127.0.0.1 -p9999 postgres
starting vacuum...end.
^C
[zjh@hs-10-20-30-193 pgpool4.3.2]$ vim etc/pgpool.conf
[zjh@hs-10-20-30-193 pgpool4.3.2]$ ltbench -c 64 -j 8 -M prepared -T 30 -P 1 -h127.0.0.1 -p9999 postgres
starting vacuum...end.
progress: 1.0 s, 22249.6 tps, lat 2.590 ms stddev 1.101
progress: 2.0 s, 25069.8 tps, lat 2.525 ms stddev 0.598
progress: 3.0 s, 24785.7 tps, lat 2.545 ms stddev 0.733
progress: 4.0 s, 24571.2 tps, lat 2.570 ms stddev 0.700
progress: 5.0 s, 24742.1 tps, lat 2.554 ms stddev 0.698
progress: 6.0 s, 24826.1 tps, lat 2.539 ms stddev 0.852
progress: 7.0 s, 24976.2 tps, lat 2.532 ms stddev 0.647
progress: 8.0 s, 24898.4 tps, lat 2.533 ms stddev 0.696
progress: 9.0 s, 22762.0 tps, lat 2.531 ms stddev 0.617
progress: 10.0 s, 15740.2 tps, lat 4.403 ms stddev 29.140
progress: 11.0 s, 24799.6 tps, lat 2.552 ms stddev 0.671
progress: 12.0 s, 24920.7 tps, lat 2.542 ms stddev 0.584
progress: 13.0 s, 24706.1 tps, lat 2.561 ms stddev 0.641
progress: 14.0 s, 24754.4 tps, lat 2.555 ms stddev 0.636
progress: 15.0 s, 24738.1 tps, lat 2.553 ms stddev 0.677
progress: 16.0 s, 24649.5 tps, lat 2.564 ms stddev 0.642
progress: 17.0 s, 24670.9 tps, lat 2.563 ms stddev 0.649
progress: 18.0 s, 24740.9 tps, lat 2.558 ms stddev 0.656
progress: 19.0 s, 24699.1 tps, lat 2.562 ms stddev 0.615
progress: 20.0 s, 24691.8 tps, lat 2.565 ms stddev 0.612
progress: 21.0 s, 24900.2 tps, lat 2.544 ms stddev 0.575
progress: 22.0 s, 24753.3 tps, lat 2.557 ms stddev 0.586
progress: 23.0 s, 24624.5 tps, lat 2.569 ms stddev 0.642
progress: 24.0 s, 24807.4 tps, lat 2.551 ms stddev 0.621
progress: 25.0 s, 24633.2 tps, lat 2.565 ms stddev 0.667
==========pgpool在同一台机器的时候，只有45%多，因为pgpool进程模式，占cpu。接下去把pgpool挪到另外一台机器。

[lightdb@hs-10-20-30-199 pgpool4.3.2]$ ltbench -c 512 -j 16 -M prepared -T 30 -P 1 -h127.0.0.1 -p9991 -U zjh postgres
Password: 
2022-11-05 17:13:04.034: ltbench pid 85361: LOG:  pool_reuse_block: blockid: 0
2022-11-05 17:13:04.034: ltbench pid 85361: CONTEXT:  while searching system catalog, When relcache is missed
starting vacuum...end.
progress: 1.0 s, 17013.6 tps, lat 21.051 ms stddev 30.641
progress: 2.0 s, 27289.2 tps, lat 18.825 ms stddev 30.250
progress: 3.0 s, 26776.7 tps, lat 18.910 ms stddev 33.194
progress: 4.0 s, 26275.5 tps, lat 19.573 ms stddev 42.446
progress: 5.0 s, 28107.6 tps, lat 18.291 ms stddev 26.154
progress: 6.0 s, 27320.0 tps, lat 18.380 ms stddev 27.171
progress: 7.0 s, 27883.0 tps, lat 18.451 ms stddev 30.227
progress: 8.0 s, 28157.0 tps, lat 18.183 ms stddev 29.302
progress: 9.0 s, 27569.1 tps, lat 18.535 ms stddev 28.920
progress: 10.0 s, 28618.0 tps, lat 17.790 ms stddev 29.134
progress: 11.0 s, 27420.2 tps, lat 18.776 ms stddev 29.725
progress: 12.0 s, 26256.8 tps, lat 19.450 ms stddev 29.975
progress: 13.0 s, 27080.9 tps, lat 18.619 ms stddev 29.961
progress: 14.0 s, 27452.7 tps, lat 18.644 ms stddev 30.268
progress: 15.0 s, 27996.6 tps, lat 18.368 ms stddev 29.259
progress: 16.0 s, 26520.8 tps, lat 18.868 ms stddev 34.222
progress: 17.0 s, 26929.4 tps, lat 18.809 ms stddev 37.797
progress: 18.0 s, 26335.1 tps, lat 19.656 ms stddev 41.457
progress: 19.0 s, 27041.1 tps, lat 19.162 ms stddev 31.872
progress: 20.0 s, 27224.0 tps, lat 18.490 ms stddev 28.900
progress: 21.0 s, 26229.7 tps, lat 19.544 ms stddev 36.353
progress: 22.0 s, 26655.6 tps, lat 19.195 ms stddev 37.241
progress: 23.0 s, 26387.9 tps, lat 18.933 ms stddev 32.933
progress: 24.0 s, 28120.1 tps, lat 18.664 ms stddev 32.798
progress: 25.0 s, 27672.6 tps, lat 18.565 ms stddev 31.806
progress: 26.0 s, 26428.1 tps, lat 18.764 ms stddev 31.797
progress: 27.0 s, 28564.3 tps, lat 17.808 ms stddev 31.522
progress: 28.0 s, 26952.8 tps, lat 19.330 ms stddev 32.065
progress: 29.0 s, 26619.2 tps, lat 19.019 ms stddev 27.161
progress: 30.0 s, 27838.6 tps, lat 18.702 ms stddev 30.454
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: prepared
number of clients: 512
number of threads: 16
duration: 30 s
number of transactions actually processed: 807251
latency average = 18.855 ms
latency stddev = 32.118 ms
tps = 26701.918926 (including connections establishing)
tps = 26707.354610 (excluding connections establishing)

[lightdb@hs-10-20-30-199 pgpool4.3.2]$ ltbench -c 512 -j 16 -M prepared -T 30 -P 1 -h10.20.137.41 -p23456 -U zjh postgres
Password: 
starting vacuum...end.
progress: 1.0 s, 29197.3 tps, lat 14.348 ms stddev 15.536
progress: 2.0 s, 38413.2 tps, lat 13.321 ms stddev 14.813
progress: 3.0 s, 38465.5 tps, lat 13.335 ms stddev 14.202
progress: 4.0 s, 37983.9 tps, lat 13.448 ms stddev 15.062
progress: 5.0 s, 39129.1 tps, lat 13.087 ms stddev 14.536
progress: 6.0 s, 38570.4 tps, lat 13.286 ms stddev 14.855
progress: 7.0 s, 38887.1 tps, lat 13.147 ms stddev 14.742
progress: 8.0 s, 38704.2 tps, lat 13.221 ms stddev 14.707
progress: 9.0 s, 39336.7 tps, lat 13.011 ms stddev 14.310
progress: 10.0 s, 38826.0 tps, lat 13.190 ms stddev 15.312
progress: 11.0 s, 39681.0 tps, lat 12.925 ms stddev 14.245
progress: 12.0 s, 33457.4 tps, lat 12.906 ms stddev 14.718
progress: 13.0 s, 26760.9 tps, lat 22.054 ms stddev 67.103
progress: 14.0 s, 38784.7 tps, lat 13.253 ms stddev 14.501
progress: 15.0 s, 39880.0 tps, lat 12.816 ms stddev 13.290
progress: 16.0 s, 39646.1 tps, lat 12.942 ms stddev 14.372
progress: 17.0 s, 40276.1 tps, lat 12.637 ms stddev 13.476
progress: 18.0 s, 39712.0 tps, lat 12.939 ms stddev 13.913
progress: 19.0 s, 39881.1 tps, lat 12.808 ms stddev 14.087
progress: 20.0 s, 39847.8 tps, lat 12.889 ms stddev 14.839
progress: 21.0 s, 39819.3 tps, lat 12.823 ms stddev 13.601
progress: 22.0 s, 39515.4 tps, lat 12.986 ms stddev 14.683
progress: 23.0 s, 39776.4 tps, lat 12.852 ms stddev 14.294
progress: 24.0 s, 40024.0 tps, lat 12.831 ms stddev 13.358
progress: 25.0 s, 40556.1 tps, lat 12.616 ms stddev 12.943
progress: 26.0 s, 39626.6 tps, lat 12.898 ms stddev 14.478
progress: 27.0 s, 40264.4 tps, lat 12.691 ms stddev 13.442
progress: 28.0 s, 39510.9 tps, lat 12.947 ms stddev 14.228
progress: 29.0 s, 40018.3 tps, lat 12.793 ms stddev 14.836
progress: 30.0 s, 39736.4 tps, lat 12.882 ms stddev 14.417
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: prepared
number of clients: 512
number of threads: 16
duration: 30 s
number of transactions actually processed: 1154800
latency average = 13.264 ms
latency stddev = 17.691 ms
tps = 38140.522517 (including connections establishing)
tps = 38146.319602 (excluding connections establishing)

从上可见，即使pgpool-ii单独机器，其性能也只有直连的2/3。本质上还是因为pgpool在7层，同时还会执行SQL解析，deparse，应答反序列化、序列化，再反序列化给客户端所致。执行计划那一步并没有那么耗时。

pgpool ii相比直连性能有明显的下降，其实有很多讨论和测试的结果均如此。https://www.highgo.ca/2019/09/06/can-you-gain-performance-with-pgpool-ii-as-a-load-balancer/、https://dba.stackexchange.com/questions/59784/poor-performance-in-my-pgpool-cluster、https://stackoverflow.blog/2020/10/14/improve-database-performance-with-connection-pooling/

https://www.pgpool.net/docs/latest/en/html/index.html

https://www.pgpool.net/mediawiki/index.php/Main_Page