上篇 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% 左右。
参考
原创文章，作者：Maggie-Hunter，如若转载，请注明出处：https://blog.ytso.com/tech/database/239587.html
