​​欢迎大家踊跃投稿，投稿信箱： press@postgres.cn

目录

一、测试机器及部署环境说明

二、postgresql运行参数配置

三、pgpool-II部署及运行参数配置

四、pgbench性能测试对比

五、性能分析及应用总结

------------------------------------------------------------正文-----------------------------------------------------------------

一、测试机器及部署环境说明

1、Postgresql主备节点部署硬件

2、pgpool部署机器硬件

​和

3、pgbench运行机器硬件

4、各应用软件版本

二、postgresql运行参数配置

1、posgresql.conf配置

listen_addresses = '0.0.0.0'

port = 9610

max_connections = 500

shared_buffers = 16384MB

work_mem = 4MB

maintenance_work_mem = 512MB

autovacuum_work_mem = -1

checkpoint_timeout = 60min              

max_wal_size = 6GB

min_wal_size = 2GB

checkpoint_warning = 30s

wal_level = replica

max_wal_senders = 3            

wal_keep_segments = 8000       

hot_standby = on

max_standby_archive_delay = 1800s

max_standby_streaming_delay = 1800s                  

log_destination = 'csvlog'

logging_collector = on

log_min_duration_statement = 1000

log_checkpoints = on

autovacuum = on

log_autovacuum_min_duration = 0

autovacuum_max_workers = 3

timezone = 'PRC'

2、pg_hba.conf配置

host    all             all             127.0.0.1/32            md5

host    all             all             192.168.1.0/24          md5

host    replication       postgres        192.168.1.0/24          md5

3、recovery.conf配置

standby_mode = 'on'

primary_conninfo = 'host=192.168.1.146 port=9610 user=postgres password=pgsql'

recovery_target_timeline = 'latest'

三、pgpool-II部署及运行参数配置

1、下载pgpool源码

[root@pgpool-01 source]# wget http://www.pgpool.net/download.php?f=pgpool-II-3.6.4.tar.gz

2、编译安装pgpool

[root@pgpool-01 source]# tar zxf pgpool-II-3.6.4.tar.gz

[root@pgpool-01 source]# cd pgpool-II-3.6.4

[root@pgpool-01 pgpool-II-3.6.4]# ./configure --prefix=/usr/local/pgpool-II-3.6.4/ --with-pgsql=/usr/local/pgsql9.6.1 --with-openssl

[root@pgpool-01 source]# gmake

[root@pgpool-01 source]# gmake install

为pgpool 软连接一个标准运行目录

[root@pgpool-01 pgpool-II-3.6.4]# ln -s /usr/local/pgpool-II-3.6.4 /usr/local/pgpool

3、配置pgpool.conf

[root@pgpool-01 pgpool-II-3.6.4]# cd /usr/local/pgpool/etc

[root@pgpool-01 etc]# cp pgpool.conf.sample-stream pgpool.conf

[root@pgpool-01 etc]# vim /usr/local/pgpool/etc/pgpool.conf

#监听参数配置

listen_addresses = '*'

port = 9999

socket_dir = '/tmp'

pcp_listen_addresses = '*'

pcp_port = 9898

pcp_socket_dir = '/tmp'

#节点参数配置

#节点一

backend_hostname0 = '192.168.1.146'

backend_port0 = 9610

backend_weight0 = 0

backend_data_directory0 = '/home/postgres/data9.6.1'

backend_flag0 = 'ALLOW_TO_FAILOVER'

#节点二

backend_hostname1 = '192.168.1.12'

backend_port1 = 9610

backend_weight1 = 0

backend_data_directory1 = '/home/postgres/data9.6.1'

backend_flag1 = 'ALLOW_TO_FAILOVER'

#认证方法配置

enable_pool_hba = on

pool_passwd = 'pool_passwd'

#连接池配置

num_init_children = 200

max_pool = 4

#日志配置

log_destination = 'syslog'

log_connections = off

log_hostname = off

log_statement = off

log_per_node_statement = off

syslog_facility = 'LOCAL0'

syslog_ident = 'pgpool'

#pid文件及状态文件存放路径

pid_file_name = '/usr/local/pgpool/pgpool.pid

logdir = '/usr/local/pgpool'

#pgpool工作方式，负载+复制（流复制方式）

load_balance_mode = on

master_slave_mode = on

master_slave_sub_mode = 'stream'

#连接状态检查

sr_check_period = 10

sr_check_user = 'postgres'

sr_check_password = 'pgsql'

sr_check_database = 'postgres'

4、配置pcp.conf

生成pcp工具连接用户名及密码

[root@pgpool-01 etc]# pg_md5 -u pgpool -p

password:

ba777e4c2f15c11ea8ac3be7e0440aa0

配置pcp.conf文件

[root@pgpool-01 etc]# cp pcp.conf.sample pcp.conf

[root@pgpool-01 etc]# vim pcp.conf

# USERID:MD5PASSWD

pgpool:ba777e4c2f15c11ea8ac3be7e0440aa0

5、配置pool_hba.conf

[root@pgpool-01 etc]# vim pool_hba.conf

host    all         all         127.0.0.1/32          md5

host    all         all         192.168.1.0/24        md5

6、生成pool_passwd配置文件

[root@pgpool-01 etc]# pg_md5 -m -u postgres pgsql

[root@pgpool-01 etc]# cat pool_passwd

postgres:md5859b0f43555758bfa7e9cc24a8a964c1

[root@pgpool-01 etc]#

7、配置pgpool使用环境

在/etc/profile文件中增加下面内容

PATH=/usr/local/pgpool/bin:$PATH

export PATH

环境变量生效

[root@pgpool-01 etc]# source /etc/profile

[root@pgpool-01 etc]# which pgpool

/usr/local/pgpool/bin/pgpool

8、配置pgpool日志

[root@pgpool-01 etc]# vim /etc/rsyslog.conf

local0.*    /var/log/pgpool.log

[root@pgpool-01 etc]# service rsyslog restart

关闭系统日志记录器：                                       [确定]

启动系统日志记录器：                                       [确定]

[root@pgpool-01 etc]#

9、配置.pgpass密码文件

[postgres@gpool-01 etc]$ vim /home/postgres/.pgpass

*.:9610:*:postgres:pgsql

*.:9999:*:postgres:pgsql

[postgres@ppool-01 etc]$ chmod 0600 /home/postgres/.pgpass

10、启动pgpool服务

[root@pgpool-01 etc]# su postgres

[postgres@pgpool-01 etc]$ pgpool

四、pgbench性能测试对比

1、pgbench使用注意点

1）、发单进程的pgbench使用的CPU为100%时需要分成三个进程的pgbench同时跑

2）、跑pgbench需要在另外一台机器上执行，否则会占用比较多的pgpool或postgresql的系统资源

3）、pgbench的latency average计算是包括向测试终端输出信息事个过程，所以测试时需要把pgbench输出导向文件，才能获取比较准确的tps和qps,否则相差几十倍的性能值都有可能。

4）、跑pgbench前先用 ethtool em1检查网卡当前工作速率，有些网卡是10/100/1000MB自适用，以确认网卡带宽是否足够，pgbench测试过程中网络带宽经常也会成为瓶颈。

2、测试节点上配置.pgpass文件

[postgres@pgbench-01 ~]$ vim /home/postgres/.pgpass

*:9610:*:postgres:pgsql

*:9999:*:postgres:pgsql

[postgres@pgbench-01~]$ chmod 0600 /home/postgres/.pgpass

3、低迟延qps测试(每个query小于0.5)

1）、生成测试数据

create table t (id serial not null unique ,remark text);

insert into t (remark) select md5(random()::text) from generate_series(1,1000000);

vacuum;

analyze;

2）、编写测试脚本

[postgres@pgbench-01 shell]$ vim sele_bench.sql

\set id random(1, 1000000)

select * from t where id=:id;

执行的开销少于0.1ms

postgres=# explain (analyze,buffers) select * from t where id=1000000;

                                                 QUERY PLAN                                                  

-------------------------------------------------------------------------------------------------------------

 Index Scan using t_id_key on t  (cost=0.42..8.44 rows=1 width=37) (actual time=0.020..0.021 rows=1 loops=1)

   Index Cond: (id = 1000000)

   Buffers: shared hit=4

 Planning time: 0.107 ms

 Execution time: 0.055 ms

(5 rows)

3）、测试结果

直接连接pg

/usr/local/pgsql9.6.1/bin/pgbench -h 192.168.1.146 -U postgres -d postgres -p 9610 -M prepared -c 8 -T 60 -f /home/postgres/shell/sele_bench.sql > /home/postgres/146bench_8c_60s_1.txt 2>&1

连接pgpool

/usr/local/pgsql9.6.1/bin/pgbench -h 192.168.1.21 -U postgres -d postgres -p 9999 -M prepared -c 8 -T 60 -f /home/postgres/shell/sele_bench.sql > /home/postgres/21bench_8c_60s_1.txt 2>&1 

4、中迟延qps测试(每个query 1ms至3ms以内)

1）、生成测试数据

drop table t;

create table t (id serial not null ,remark text);

insert into t (remark) select md5(random()::text) from generate_series(1,10000);

vacuum;

analyze;

2）、编写测试脚本

[postgres@pgbench-01 shell]$ vim sele_bench.sql

\set id random(1, 10000)

select * from t where id=:id;

执行的开销少于2ms

postgres=#  explain (analyze,buffers) select * from t where id=10000;

                                          QUERY PLAN                                         

----------------------------------------------------------------------------------------------

 Seq Scan on t  (cost=0.00..209.00 rows=1 width=37) (actual time=1.720..1.720 rows=1 loops=1)

   Filter: (id = 10000)

   Rows Removed by Filter: 9999

   Buffers: shared hit=84

 Planning time: 0.057 ms

 Execution time: 1.743 ms

(6 rows)

3）测试结果

5、高迟延qps测试(每个query 10ms以上)

1）、生成测试数据

drop table t;

create table t (id serial not null ,remark text);

insert into t (remark) select md5(random()::text) from generate_series(1,60000);

vacuum;

analyze;

2）、编写测试脚本

[postgres@pgbench-01 shell]$ vim sele_bench.sql

\set id random(1, 60000)

select * from t where id=:id;

执行的开销10ms以上

postgres=# explain (analyze,buffers) select * from t where id=60000;

                                           QUERY PLAN                                           

-------------------------------------------------------------------------------------------------

 Seq Scan on t  (cost=0.00..1250.00 rows=1 width=37) (actual time=10.371..10.372 rows=1 loops=1)

   Filter: (id = 60000)

   Rows Removed by Filter: 59999

   Buffers: shared hit=500

 Planning time: 0.076 ms

 Execution time: 10.399 ms

(6 rows)

3）测试结果

​ 

6、返回大数据量的qps

1）、生成测试数据

drop table t;

create table t (id serial not null unique ,remark text);

insert into t (remark) select repeat('hello pg,我是阿弟', 1000) from generate_series(1,10000);   

vacuum;

analyze;

每条数据大约12K

2）、编写测试脚本

[postgres@pgbench-01 shell]$ vim sele_bench.sql

\set id random(1, 10000)

select * from t where id=:id;

3）测试结果

​

7、低延迟的INSERT测试

1）、生成测试数据

drop table t;

create table t (id serial not null unique,remark text);

vacuum;

analyze;

2）、编写测试脚本

[postgres@pgbench-01 shell]$ vim insert_bench.sql

insert into t(remark) values(md5(random()::text));

3）测试结果

8、中延迟的UPDATE测试

1）、生成测试数据

drop table t;

create table t (id serial not null ,remark text);

insert into t (remark) select md5(random()::text) from generate_series(1,10000);

vacuum;

analyze;

2）、编写测试脚本

[postgres@pgbench-01 shell]$ vim update_bench.sql

\set id random(1, 10000)

update t set remark=md5(random()::text) where id=:id

执行开销

explain (analyze,buffers) update t set remark =md5(random()::text) where id=1;

                                             QUERY PLAN                                            

----------------------------------------------------------------------------------------------------

 Update on t  (cost=0.00..209.01 rows=1 width=42) (actual time=1.885..1.885 rows=0 loops=1)

   Buffers: shared hit=85

   ->  Seq Scan on t  (cost=0.00..209.01 rows=1 width=42) (actual time=1.856..1.857 rows=1 loops=1)

         Filter: (id = 1)

         Rows Removed by Filter: 9999

         Buffers: shared hit=84

 Planning time: 0.086 ms

 Execution time: 1.924 ms

3）测试结果

9、高延迟的UPDATE测试

1）、生成测试数据

drop table t;

create table t (id serial not null ,remark text);

insert into t (remark) select md5(random()::text) from generate_series(1,60000);

vacuum;

analyze;

2）、编写测试脚本

[postgres@pgbench-01 shell]$ vim update_bench.sql

\set id random(1, 60000)

update t set remark=md5(random()::text) where id=:id

执行开销

postgres=# explain (analyze,buffers) update t set remark =md5(random()::text) where id=60000;

                                              QUERY PLAN                                              

-------------------------------------------------------------------------------------------------------

 Update on t  (cost=0.00..1250.01 rows=1 width=42) (actual time=10.616..10.616 rows=0 loops=1)

   Buffers: shared hit=506 read=1 dirtied=1

   ->  Seq Scan on t  (cost=0.00..1250.01 rows=1 width=42) (actual time=10.485..10.487 rows=1 loops=1)

         Filter: (id = 60000)

         Rows Removed by Filter: 59999

         Buffers: shared hit=500

 Planning time: 0.124 ms

 Execution time: 10.656 ms

(8 rows)

3）测试结果

五、性能分析及应用总结

1、select通信捉包分析

发送select * from t where id=100这样的语句

客户端－〉pgpool

17:38:25.026869 IP 192.168.1.11.36426 > 192.168.1.21.distinct: Flags [P.], seq 35:70, ack 124, win 29, length 35

Pgpool－〉pg

17:38:25.085689 IP 192.168.1.21.hexarc > 192.168.1.146.9610: Flags [P.], seq 1:36, ack 1, win 31, length 35

执行结果返回

Pg->pgpool

17:38:25.086287 IP 192.168.1.146.9610 > 192.168.1.21.hexarc: Flags [P.], seq 1:124, ack 36, win 39, length 123

17:38:25.086299 IP 192.168.1.21.hexarc > 192.168.1.146.9610: Flags [.], ack 124, win 31, length 0

Pgpool－〉客户端

17:38:25.027795 IP 192.168.1.21.distinct > 192.168.1.11.36426: Flags [P.], seq 124:177, ack 70, win 29, length 53

17:38:25.027812 IP 192.168.1.11.36426 > 192.168.1.21.distinct: Flags [.], ack 177, win 29, length 0

17:38:25.027819 IP 192.168.1.21.distinct > 192.168.1.11.36426: Flags [P.], seq 177:241, ack 70, win 29, length 64

17:38:25.027826 IP 192.168.1.11.36426 > 192.168.1.21.distinct: Flags [.], ack 241, win 29, length 0

17:38:25.027832 IP 192.168.1.21.distinct > 192.168.1.11.36426: Flags [P.], seq 241:247, ack 70, win 29, length 6

17:38:25.027839 IP 192.168.1.11.36426 > 192.168.1.21.distinct: Flags [.], ack 247, win 29, length 0

当客户端向pgpool发出select * from t where id=100;这样的查询时，pgool会把语句转发给pg－－这个通信没问题。但当数据返回时pgpool的处理就有问题了，pg直接一次返回给pgpool，但pgpool竟然分三次返回。

2、insert通信捉包分析

发送这样的insert into t(remark) values(md5(random()::text));语句

客户端－〉pgpool

17:11:40.851896 IP 192.168.1.11.42150 > 192.168.1.21.distinct: Flags [P.], seq 196:252, ack 582, win 29, length 56

Pgpool－〉pg

17:11:40.898114 IP 192.168.1.21.dtserver-port > 192.168.1.146.9610: Flags [P.], seq 1185:1241, ack 1023, win 31, length 56

执行结果返回

Pg->pgpool

17:11:40.906169 IP 192.168.1.146.9610 > 192.168.1.21.dtserver-port: Flags [P.], seq 1023:1045, ack 1241, win 39, length 22

17:11:40.906183 IP 192.168.1.21.dtserver-port > 192.168.1.146.9610: Flags [.], ack 1045, win 31, length 0

Pgpool－〉客户端

17:11:40.860338 IP 192.168.1.21.distinct > 192.168.1.11.42150: Flags [P.], seq 582:598, ack 252, win 29, length 16

17:11:40.860353 IP 192.168.1.11.42150 > 192.168.1.21.distinct: Flags [.], ack 598, win 29, length 0

17:11:40.860360 IP 192.168.1.21.distinct > 192.168.1.11.42150: Flags [P.], seq 598:604, ack 252, win 29, length 6

17:11:40.860364 IP 192.168.1.11.42150 > 192.168.1.21.distinct: Flags [.], ack 604, win 29, length 0

当客户端向pgpool发出insert into t(remark) values(md5(random()::text));这样的插入语句时，pgool会把语句转发给pg－－这个通信没问题。但当返回执行结果通知时就有问题了，pg直接一次返回给pgpool，但pgpool这次分二次返回，update、delete的通信也跟insert一致。

3、pgpool测试应用总结

1）、pgpool返回结果时会拆包，这个跟pgboucner或者haproxy不拆包有区别。

2）、pgpool及时发送零碎小数据包，减少数据存入缓冲区，通常情况下系统性能会更高,但如果高并发时，由于每个包都带有包头和交互次数增加，反而就会占用更大的网络io。

3）、就测试来看，pgpool比较适合于对于查询开销比较大（1ms以上），或者查询返回的流量比较大的应用。

社区资源

Pgpool相关

http://www.pgpool.net

http://www.pgpool.net/docs/latest/tutorial-zh_cn.html

http://francs3.blog.163.com/blog/static/4057672720149285445881/

Pgbench使用文档

http://www.postgres.cn/docs/9.6/pgbench.html

社区网站

PostgreSQL全球社区网：http://www.postgresql.org

PostgreSQL中国社区网：http://www.postgres.cn

社区QQ群

技术群#1：5276420

技术群#2：3336901

文档翻译群： 309292849​​​​