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，应答反序列化、序列化，再反序列化给客户端所致。执行计划那一步并没有那么耗时。
再来测试单笔时延，
----- from pgpool [zjh@hs-10-20-30-193 ~]$ ltsql -h127.0.0.1 -p9999 postgres ltsql (13.8-22.3) Type "help" for help. zjh@postgres=# explain verbose select version() from employee where id>10 and id<20; QUERY PLAN ------------------------------------------------------------------------------------- Index Only Scan using idx_emp on public.employee (cost=0.42..1.63 rows=9 width=32) Output: version() Index Cond: ((employee.id > 10) AND (employee.id < 20)) (3 rows) zjh@postgres=# \timing on Timing is on. zjh@postgres=# select version() from employee where id>10 and id<20; version ----------------------------------------------------------------------------------------------------------- LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit (9 rows) Time: 0.489 ms # n次去中位值 -----------direct from ltsql [zjh@hs-10-20-30-193 ~]$ ltsql -p23456 postgres ltsql (13.8-22.3) Type "help" for help. zjh@postgres=# \timing on Timing is on. zjh@postgres=# select version() from employee where id>10 and id<20; version ----------------------------------------------------------------------------------------------------------- LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit (9 rows) Time: 0.285 ms ----- 可以发现直连相比pgpool快0.2ms，pgpool ii要低40%左右
再来测试大数据量的copy，
----------direct from ltsql [zjh@hs-10-20-30-193 ~]$ ltsql -p23456 postgres ltsql (13.8-22.3) Type "help" for help. zjh@postgres=# \timing on Timing is on. zjh@postgres=# copy (select * from employee limit 100000) to '/home/zjh/employ.dat'; COPY 100000 Time: 27.257 ms ----------from pgpool [zjh@hs-10-20-30-193 ~]$ ltsql -h127.0.0.1 -p9999 postgres ltsql (13.8-22.3) Type "help" for help. zjh@postgres=# \timing on Timing is on. zjh@postgres=# copy (select * from employee limit 100000) to '/home/zjh/employ.dat'; COPY 100000 Time: 27.821 ms ------------ 效果相当
对于稍微复杂一点的SQL，可以说是基本无感知的。非时延极端场景是可以考虑的。
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
