Pgpool-II 流复制模式压力测试

上篇 blog: PostgreSQL 流复制 + Pgpool-II 实现高可用 HA 介绍了使用 pgpool 结合 PostgreSQL 自身的流复制功能搭建 HA 环境,之前听说 pgpool 会影响性能,那么今天利用上篇 blog 刚搭好的 HA 环境,简单做下测试,环境信息请参考上篇 blog。

测试数据准备

创建测试用户、测试表,并插入测试数据,如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
建用户  
REATE ROLE pgpool_db1 LOGIN ENCRYPTED PASSWORD 'pgpool_db1' nosuperuser noinherit nocreatedb nocreaterole ;

创建表空间 ( 两节点操作 )
mkdir -p /database/pg93/pg_tbs/tbs_pgpool_db1

create tablespace tbs_pgpool_db1 owner postgres LOCATION '/database/pg93/pg_tbs/tbs_pgpool_db1';

创建数据库
CREATE DATABASE pgpool_db1 WITH OWNER = pgpool_db1 TEMPLATE = template1 ENCODING = 'UTF8'TABLESPACE = tbs_pgpool_db1;

创建模式
postgres=# c pgpool_db1 pgpool_db1
You are now connected to database "pgpool_db1" as user "pgpool_db1".

pgpool_db1=> create schema pgpool_db1;
CREATE SCHEMA

创建测试表,并插入 500 万测试数据。
pgpool_db1=> create table test_1(id int8,name text,creat_time timestamp(0) without time zone default clock_timestamp());
CREATE TABLE

pgpool_db1=> insert into test_1(id,name) select n,n||'_test' from generate_series(1,5000000) n;
INSERT 0 5000000

pgpool_db1=> alter table test_1 add primary key(id);
ALTER TABLE

Pgpool 配置

查询 pgpool_db1 md5 密码

1
2
3
4
5
postgres=# select rolname,rolpassword from pg_authid where rolname='pgpool_db1';  
rolname | rolpassword
------------+-------------------------------------
pgpool_db1 | md594f532461c9e3e3a591e77a373da0493
(1 row)

pool_passwd 中加入密码信息

1
2
3
4
[pg93@db2 load_test]$ echo "pgpool_db1:md594f532461c9e3e3a591e77a373da0493"  >>  /opt/pgpool/etc/pool_passwd

reload pgpool
[pg93@db2 load_test]$ pgpool reload

性能测试

测试 SQL

1
2
3
4
[pg93@db2 load_test]$ cat get_name.sql  
setrandom v_id 1 5000000

select name from test_1 where id=:v_id;

直连 PostgreSQL 测试

测试不同连接数时此SQL的TPS。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
pgbench -c 2  -T 20  -n -N -M prepared -d pgpool_db1 -U pgpool_db1 -f get_name.sql > get_name.out  2>&1  &  

连接数 2
pghost: pgport: 1921 nclients: 2 duration: 20 dbName: pgpool_db1
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 2
number of threads: 1
duration: 20 s
number of transactions actually processed: 61021
tps = 3050.684070 (including connections establishing)
tps = 3051.633320 (excluding connections establishing)

连接数 4
pghost: pgport: 1921 nclients: 4 duration: 20 dbName: pgpool_db1
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 4
number of threads: 1
duration: 20 s
number of transactions actually processed: 64743
tps = 3236.352239 (including connections establishing)
tps = 3239.085678 (excluding connections establishing)

连接数 8
pghost: pgport: 1921 nclients: 8 duration: 20 dbName: pgpool_db1
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 1
duration: 20 s
number of transactions actually processed: 47789
tps = 2387.004156 (including connections establishing)
tps = 2390.072375 (excluding connections establishing)

连接数 16
pghost: pgport: 1921 nclients: 16 duration: 20 dbName: pgpool_db1
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 16
number of threads: 1
duration: 20 s
number of transactions actually processed: 44099
tps = 2201.601035 (including connections establishing)
tps = 2206.286789 (excluding connections establishing)

连接数 32
pghost: pgport: 1921 nclients: 32 duration: 20 dbName: pgpool_db1
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 32
number of threads: 1
duration: 20 s
number of transactions actually processed: 42541
tps = 2125.029203 (including connections establishing)
tps = 2135.919727 (excluding connections establishing)

连接 Pgpool 测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
pgbench -h 127.0.0.1  -p 9999  -c 2  -T 20  -n -N -M prepared -d pgpool_db1 -U pgpool_db1 -f get_name.sql > get_name.out  2>&1  &  

连接数 2
pghost: 127.0.0.1 pgport: 9999 nclients: 2 duration: 20 dbName: pgpool_db1
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 2
number of threads: 1
duration: 20 s
number of transactions actually processed: 13570
tps = 678.426052 (including connections establishing)
tps = 678.522323 (excluding connections establishing)

连接数 4
pghost: 127.0.0.1 pgport: 9999 nclients: 4 duration: 20 dbName: pgpool_db1
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 4
number of threads: 1
duration: 20 s
number of transactions actually processed: 20307
tps = 1015.210206 (including connections establishing)
tps = 1016.593395 (excluding connections establishing)

连接数 8
pghost: 127.0.0.1 pgport: 9999 nclients: 8 duration: 20 dbName: pgpool_db1
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 1
duration: 20 s
number of transactions actually processed: 35957
tps = 1796.691134 (including connections establishing)
tps = 1799.634067 (excluding connections establishing)

连接数 16
pghost: 127.0.0.1 pgport: 9999 nclients: 16 duration: 20 dbName: pgpool_db1
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 16
number of threads: 1
duration: 20 s
number of transactions actually processed: 33079
tps = 1653.351817 (including connections establishing)
tps = 1661.947374 (excluding connections establishing)

连接数 32
pghost: 127.0.0.1 pgport: 9999 nclients: 32 duration: 20 dbName: pgpool_db1
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 32
number of threads: 1
duration: 20 s
number of transactions actually processed: 32259
tps = 1611.528149 (including connections establishing)
tps = 1615.327601 (excluding connections establishing)

压测数据统计

拉张图更直观些

备注:从图中看到使用 pgpool 性能比直连方式降低很多,特别是在并发连接数少的情况差距越明显,达到 70% 左右,随着并发连接数数增加,性能差距缩小到 20%- 30% 左右。

参考

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

购买链接:https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持!
0%