PG-Pool-II 读写分离使用体验

• PG-Pool-II功能
• PG-Pool-II 安装
  • PG-Pool-II 配置使用
• PG-Pool-II 读写分离测试
• PG-Pool-II 性能和SQL测试
  • 只读测试：
  • 读写测试：
• 初步结论

PG-Pool-II功能

• 连接池

PG是多进程结构，连接池可以降低连接空置的问题，提高资源利用率。

• 主从复制 online recovery auto failover

通过pgpool 管理 主从关系，实现PG的主从高可用能力。

• 负载均衡

当存在多个从库的时候，可以通过权重设置自动负载不同节点的请求，使性能达到最优。

• 连接数限制

设置连接数控制，避免过高的连接导致访问报错，当超出连接数上线后，对后续的访问进行排队等待。

• 高可用

通过pgpool 的管理能力，可实现自身的高可用，避免连接池本身性能问题。

• 查询缓存

在内存中缓存SQL结果。 如果一个相同的SQL，则可从缓存中返回值，提高查询性能。

PG-Pool-II 安装

资源准备：

CVM 1台，centos 7.2 64位，配置 32C64GB。

云数据库PostgreSQL，主实例 1c2GB，200GB 磁盘，只读实例2个，1c2GB，200GB磁盘，组合成一个只读实例组。

1. 根据操作系统安装pg的官方yum源。yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdgredhat-repo-latest.noarch.rpm
2. 安装pgpool-II 的安装包，这里使用的是 4.1.4示例，可以选用孞的4.2.4版本。 yum install -y pgpool-II-11 yum install -y pgpool-II-11-extensions yum install -y pgpool-II-11-devel
3. 查看文件夹下：/etc/pgpool-II-11/，是否存在文件即可。

PG-Pool-II 配置使用

主要配置文件如下：

/etc/pgpool-II-11/pgpool.conf  ，主配置文件

/etc/pgpool-II-11/pool_hba.conf，连接配置文件类似于pg_hba.conf

/etc/pgpool-II-11/pool_passwd，访问用户和密码文件，主要是配置访问用户名和密码，用于连接数据库的认证。

/etc/pgpool-II-11/pgpool.conf主要配置信息如下：

#基本连接信息

# - pgpool Connection Settings -

listen_addresses = '*'

port = 9999

socket_dir='/var/run/pgpool-II-11'

# -管理接口配置信息，可不用配置 -

pcp_listen_addresses = ''

pcp_port = 5432

pcp_socket_dir = '/var/run/pgpool-II-11'

# - 数据库主节点连接信息配置，重要

backend_hostname0 = '172.27.32.11'

backend_port0 = 5432

backend_weight0 = 1

backend_data_directory0 = '/data/pgsql11/master'

backend_flag0 = 'ALWAYS_MASTER'

backend_application_name0 = 'master'

#只读实例或者只读实例组的连接信息, 配置PG只读组的VIP

backend_hostname1 = '172.27.32.12'

backend_port1 = 5432

backend_weight1 = 1

backend_data_directory1 = '/data/pgsql11/ro'

backend_flag1 = 'DISALLOW_TO_FAILOVER'

backend_application_name1 = 'ro'

# - 认证登录信息 -

enable_pool_hba = on

pool_passwd = 'pool_passwd'

# 相关文件信息

pid_file_name = '/var/run/pgpool-II-11/pgpool.pid'

# 复制模式

replication_mode = off

#load balance模式是否开启

load_balance_mode = on

#  主从模式是否开起（重要，读写分离相关）

master_slave_mode = on

master_slave_sub_mode = 'stream'

# 健康检查是否开启，主要用于fail over功能

health_check_period = 0

health_check_timeout = 20

health_check_user = 'nobody'

health_check_password = ''

health_check_database = ''

# 日志相关配置

log_destination = 'syslog'

log_connections = on

log_hostname = on

log_statement = on

log_per_node_statement = on

pool_hba.conf文件配置，类似于pg_hba.conf文件：

pool_passwd用户名和密码信息，使用下列语句执行即可生成此文件：

完成配置后，执行服务启动语句即可启动pg_pool。

systemctl start pgpool-II-11.service

启动服务后，可通过 systemctl status pgpool-II-11.service 看到服务的启动情况。

PG-Pool-II 读写分离测试

1. 通过pgbench 通过pgpool 对数据库进行基础数据灌数。 /usr/pgsql-11/bin/pgbench -U haha -p 9999 -h 172.27.32.12 test -i -s 10
2. 新建一个sql文件来进行测试vi test.sql

set aid random_gaussian(1, :range, 10.0)

set bid random(1, 1 * :scale)

set tid random(1, 10 * :scale)

set delta random(-5000, 5000)

SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

SELECT tbalance FROM pgbench_tellers WHERE tid = :tid;

SELECT bid,aid,delta,mtime FROM pgbench_history WHERE tid = :tid;

UPDATE pgbench_accounts SET abalance = abalance :delta WHERE aid = :aid; 

SELECT abalance FROM pgbench_accounts WHERE aid = :aid; 

UPDATE pgbench_tellers SET tbalance = tbalance :delta WHERE tid = :tid; 

UPDATE pgbench_branches SET bbalance = bbalance :delta WHERE bid = :bid; 

INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);

1. 通过pgpool 2 进行数据库读写数据测试： /usr/pgsql-11/bin/pgbench -U haha -p 9999 -h 172.27.32.9 test -v -r -P 1 -f ./test.sql -c 300 -j 300 -T 120 -D scale=10000 -D range=100000000
2. 因上面配置文件中，我们开启了日志记录SQL语句，所以可以通过日志查看不同节点执行了哪些SQL。 cat /var/log/messages |grep "DB node id: 1" cat /var/log/messages |grep "DB node id: 0"
3. 其中 节点ID 为1 的就是 我们backend_hostname1 配置的 节点1，为我们的只读实例组地址。
4. 其中节点ID 为0 的则是我们的backend_hostname0 配置的节点0，为我们的主节点。
5. 可以看到读取和写入语句都分发到了不同的节点。成功实现了读写分离的功能。

cat /var/log/messages |grep "DB node id: 0"

Aug 30 22:24:49 VM-32-9-centos pgpool[3968]: [25447-1] 2021-08-30 22:24:49: pid 3968: LOG:  DB node id: 0 backend pid: 33553 statement: BEGIN;

Aug 30 22:24:49 VM-32-9-centos pgpool[5126]: [25656-1] 2021-08-30 22:24:49: pid 5126: LOG:  DB node id: 0 backend pid: 33531 statement: UPDATE pgbench_tellers SET tbalance = tbalance -651 WHERE tid = 42973;

Aug 30 22:24:49 VM-32-9-centos pgpool[3974]: [25559-1] 2021-08-30 22:24:49: pid 3974: LOG:  DB node id: 0 backend pid: 33551 statement: END;

……

cat /var/log/messages |grep "DB node id: 1"

Sep  1 12:03:25 VM-32-9-centos pgpool[20505]: [26250-1] 2021-09-01 12:03:25: pid 20505: LOG:  DB node id: 1 backend pid: 20410 statement: SELECT abalance FROM pgbench_accounts WHERE aid = 45648796;

Sep  1 12:03:25 VM-32-9-centos pgpool[20520]: [27210-1] 2021-09-01 12:03:25: pid 20520: LOG:  DB node id: 1 backend pid: 20411 statement: SELECT abalance FROM pgbench_accounts WHERE aid = 56012538;

Sep  1 12:03:25 VM-32-9-centos pgpool[20522]: [28362-1] 2021-09-01 12:03:25: pid 20522: LOG:  DB node id: 1 backend pid: 20401 statement: SELECT abalance FROM pgbench_accounts WHERE aid = 53695003;

……