Pgpool-II
是一个成熟的数据库中间件产品，当前最新版本是4.2.5，主要提供如下特性，为了验证强大的负载均衡功能，我们基于pgbench进行验证。
|
1 |
连接池功能 |
|
2 |
负载均衡器 |
|
3 |
自动故障转移 |
|
4 |
在线恢复 |
|
5 |
复制 |
|
6 |
限制连接超载 |
|
7 |
监视狗功能 |
|
8 |
基于内存的查询缓存 |
软件准备
|
软件 |
版本 |
|
Pgpool |
-bash-4.2$ pgpool -v pgpool-II version
4.2.4 (chichiriboshi) |
|
Postgresql |
-bash-4.2$ psql -version psql (13.3) |
|
Os |
-bash-4.2$ cat /etc/redhat-release CentOS Linux
release 7.2.1511 (Core) |
环境准备
|
主机 |
Ip |
|
centos140 |
192.168.40.140（主库） |
|
centos141 |
192.168.40.141（备库） |
|
|
192.168.40.138（vip） |
分别启动并检查主从库信息：
/usr/pgsql-13/bin/pg_ctl -D /home/pgdata -l /tmp/logfile
start
pgpool -n >
/tmp/pgpool.log 2>&1 &
基于pgbench测试
先检查统计情况
|
[postgres@centos141
~]$PGPASSWORD=123456 psql -h 192.168.40.138 -p 9999 -U postgres -c "show pool_nodes;" node_id |
hostname | port | status |
lb_weight | role | select_cnt | load_balance_node |
replication_delay | replication_sta te |
replication_sync_state | last_status_change
---------+----------------+------+--------+-----------+---------+------------+-------------------+-------------------+---------------- ---+------------------------+--------------------- 0
| 192.168.40.140 | 5432 | up
| 0.500000 | standby | 0 | true | 0 | | | 2021-10-13 17:41:25 1 | 192.168.40.141 | 5432 | up | 0.500000 | primary | 0 | false | 0 | |
测试：
/usr/pgsql-13/bin/pgbench
-h 192.168.40.138 -p 9999 -c 10 -j 10 -S
-T 60 postgres
|
-h, --host=HOSTNAME database server host or socket
directory
-p, --port=PORT
database server port number
-U, --username=USERNAME connect
as specified database user
-c, --client=NUM number
of concurrent database clients (default: 1)
并发客户端连接数
-j, --jobs=NUM number
of threads (default: 1) 并发线程数
-S, --select-only perform
SELECT-only transactions (same as "-b
select-only") -T,
--time=NUM duration of
benchmark test in seconds 测试时长 |
测试完成发现偏移非常大；从库才3万多，主库竟然达到58万多
|
[postgres@centos141 ~]$
/usr/pgsql-13/bin/pgbench -h 192.168.40.138
-p 9999 -c 10 -j 10 -S -T 60 postgres Password: starting vacuum...end. transaction type: <builtin:
select only> scaling factor: 1 query mode: simple number of clients: 10 number of threads: 10 duration: 60 s number of transactions actually
processed: 676374 latency average = 0.887 ms tps = 11269.735544 (including
connections establishing) tps = 11277.405062 (excluding
connections establishing) [postgres@centos141
~]$PGPASSWORD=123456 psql -h 192.168.40.138 -p 9999 -U postgres -c "show pool_nodes;" node_id |
hostname | port | status |
lb_weight | role | select_cnt | load_balance_node |
replication_delay | replication_sta te | replication_sync_state |
last_status_change ---------+----------------+------+--------+-----------+---------+------------+-------------------+-------------------+---------------- ---+------------------------+--------------------- 0
| 192.168.40.140 | 5432 | up
| 0.500000 | standby |
30089 | true | 0 |
| |
2021-10-13 17:41:25 1
| 192.168.40.141 | 5432 | up
| 0.500000 | primary | 582234 | false | 0 |
| |
2021-10-13 17:41:25 [postgres@centos141
~]$ |
主要原因是pgpool-II默认基于是基于会话的，在会话开始时决定，在会话结束之前不会更改连接。
pgbench加个参数-C
|
[postgres@centos141
pgsql-13]$/usr/pgsql-13/bin/pgbench -h 192.168.40.138 -p 9999 -c 10 -j 10 -S -C -T 60 postgres Password: [postgres@centos141
pgsql-13]$PGPASSWORD=123456 psql -h 192.168.40.138 -p 9999 -U postgres -c "show pool_nodes;" node_id |
hostname | port | status |
lb_weight | role | select_cnt | load_balance_node |
replication_delay | replication_sta te |
replication_sync_state | last_status_change
---------+----------------+------+--------+-----------+---------+------------+-------------------+-------------------+---------------- ---+------------------------+--------------------- 0
| 192.168.40.140 | 5432 | up
| 0.500000 | standby |
3537 | true | 0 | | | 2021-10-13 21:12:38 1
| 192.168.40.141 | 5432 | up
| 0.500000 | primary |
3638 | false | 0 | | | 2021-10-13 21:12:38 (2 rows) |
-C, --connect establish new connection for each
transaction
此时select_cnt的数值就相对平衡了很多
是否使用连接池的巨大差距
使用连接池的交易量是不使用连接池交易量的35.8倍；每一次交易都从db中获取连接是非常消耗资源、非常影响性能的过程。
|
测试语句 |
交易量 |
比例 |
|
/usr/pgsql-13/bin/pgbench
-h 192.168.40.138 -p 9999 -c 10 -j 10
-S -C -T 10 postgres |
number of
transactions actually processed: 1145 |
1 |
|
/usr/pgsql-13/bin/pgbench
-h 192.168.40.138 -p 9999 -c 10 -j 10
-S -T 10 postgres |
number of
transactions actually processed: 41028 |
35.8 |
|
测试语句 |
Tps |
比例 |
|
/usr/pgsql-13/bin/pgbench
-h 192.168.40.138 -p 9999 -c 10 -j 10
-S -C -T 10 postgres |
tps = 114.018437
(including connections establishing) tps = 331.763019
(excluding connections establishing) |
1 |
|
/usr/pgsql-13/bin/pgbench
-h 192.168.40.138 -p 9999 -c 10 -j 10
-S -T 10 postgres |
tps = 4099.177477 (including connections establishing) tps = 4123.659603
(excluding connections establishing) |
35.9 |
测试过程：
|
[postgres@centos141
~]$/usr/pgsql-13/bin/pgbench -h 192.168.40.138 -p 9999 -c 10 -j 10 -S -C -T 10 postgres Password: starting
vacuum...end. transaction
type: <builtin: select only> scaling
factor: 1 query
mode: simple number of
clients: 10 number of
threads: 10 duration:
10 s number of
transactions actually processed: 1145 latency
average = 87.705 ms tps =
114.018437 (including connections establishing) tps =
331.763019 (excluding connections establishing) [postgres@centos141
~]$/usr/pgsql-13/bin/pgbench -h 192.168.40.138 -p 9999 -c 10 -j 10 -S -T 10 postgres Password: starting
vacuum...end. transaction
type: <builtin: select only> scaling
factor: 1 query
mode: simple number of
clients: 10 number of
threads: 10 duration:
10 s number of
transactions actually processed: 41028 latency
average = 2.440 ms tps =
4099.177477 (including connections establishing) tps =
4123.659603 (excluding connections establishing) [postgres@centos141 ~]$ |
pgbench加个参数-d
可以看到一序列debug日志，可以看到是基于自建的pgbench_accounts表进行压测
|
[postgres@centos141
pgsql-13]$/usr/pgsql-13/bin/pgbench -h 192.168.40.138 -p 9999 -c 1 -j 1 -S -T 1 -C -d postgres pgbench:
pghost: 192.168.40.138 pgport: 9999 nclients: 1 duration: 1 dbName: postgres Password: starting
vacuum...end. pgbench:
client 0 executing script "<builtin: select only>" pgbench:
client 0 executing \set aid pgbench:
client 0 sending SELECT abalance FROM pgbench_accounts WHERE aid = 88472; pgbench:
client 0 receiving pgbench:
client 0 receiving pgbench:
client 0 receiving pgbench:
client 0 executing script "<builtin: select only>" pgbench:
client 0 executing \set aid pgbench:
client 0 sending SELECT abalance FROM pgbench_accounts WHERE aid = 88448; pgbench:
client 0 receiving pgbench:
client 0 receiving pgbench:
client 0 executing script "<builtin: select only>" pgbench:
client 0 executing \set aid pgbench:
client 0 sending SELECT abalance FROM pgbench_accounts WHERE aid = 35514; pgbench:
client 0 receiving pgbench:
client 0 receiving 。。。。。。 |
statement_level_load_balance的作用
pgpool.conf 中的statement_level_load_balance设置基于sql语句的平衡，默认为off,设置为on后
使用/usr/pgsql-13/bin/pgbench
-h 192.168.40.138 -p 9999 -c 10 -j 10
-S -T 10
进行负载测试此时检查2个节点的负载会非常接近。
|
[postgres@centos141
pgsql-13]$/usr/pgsql-13/bin/pgbench -h 192.168.40.138 -p 9999 -c 10 -j 10 -S -T 10 postgres Password: starting
vacuum...end. transaction
type: <builtin: select only> scaling
factor: 1 query
mode: simple number of
clients: 10 number of
threads: 10 duration:
10 s number of
transactions actually processed: 36250 latency
average = 2.760 ms tps =
3623.108078 (including connections establishing) tps =
3645.539003 (excluding connections establishing) [postgres@centos141
pgsql-13]$PGPASSWORD=123456 psql -h 192.168.40.138 -p 9999 -U postgres -c "show pool_nodes;" node_id |
hostname | port | status |
lb_weight | role | select_cnt | load_balance_node |
replication_delay | replication_sta te |
replication_sync_state | last_status_change
---------+----------------+------+--------+-----------+---------+------------+-------------------+-------------------+---------------- ---+------------------------+--------------------- 0
| 192.168.40.140 | 5432 | up
| 0.500000 | standby |
18078 | true | 0 | | | 2021-10-13 21:22:19 1
| 192.168.40.141 | 5432 | up
| 0.500000 | primary | 18185 | false | 0 | | | 2021-10-13 21:22:19 |
Pgpool最大连接数设置考虑
pgpool.conf 中的num_init_children此参数决定池的最大并发连接数，默认32，一般来说此值配置=主库的max_connections
每一个连接都会启动一个进程，例如统计从192.168.40.1过来的连接数
|
[postgres@centos141
~]$ps -ef |grep 'postgres 192.168.40.1(' |grep -v grep postgres 431
8254 0 21:55 pts/2 00:00:00 pgpool: postgres postgres
192.168.40.1(56169) idle postgres 432
8254 0 21:55 pts/2 00:00:00 pgpool: postgres postgres
192.168.40.1(56167) idle postgres 802
8254 0 21:57 pts/2 00:00:00 pgpool: postgres postgres
192.168.40.1(56174) idle postgres 1481
8254 0 22:00 pts/2 00:00:00 pgpool: postgres postgres
192.168.40.1(56191) idle postgres 1683
8254 0 22:01 pts/2 00:00:00 pgpool: postgres postgres
192.168.40.1(56189) idle postgres 1684
8254 0 22:01 pts/2 00:00:00 pgpool: postgres postgres
192.168.40.1(56160) idle postgres 2648
8254 0 22:06 pts/2 00:00:00 pgpool: postgres postgres
192.168.40.1(56188) idle postgres 2676
8254 0 22:06 pts/2 00:00:00 pgpool: postgres postgres
192.168.40.1(56164) idle postgres 3302
8254 0 21:14 pts/2 00:00:00 pgpool: postgres postgres
192.168.40.1(56161) idle …… |
参考
https://www.pgpool.net/docs/latest/en/html/tutorial-testing-load-balance.html
https://www.pgpool.net/docs/latest/en/html/runtime-config-load-balancing.html
