PostgreSQL10:逻辑复制(Logical Replication)之二

上篇博客介绍了 PostgreSQL10重量级新特性逻辑复制并进行了演示,详见PostgreSQL10:逻辑复制(Logical Replication)之一, 逻辑复制的延迟如何呢?以下做个压测。

INSERT 压测

测试准备

发布节点创建测试表并赋权

1
2
3
4
5
6
7
8
francs=> create table test_per2(id int4,name text, create_time timestamp(0) without time zone DEFAULT '2000-01-01 00:00:00'::timestamp without time zone);
CREATE TABLE

francs=# grant select on francs.test_per2 to repuser ;
GRANT

francs=# ALTER PUBLICATION pub1 ADD TABLE francs.test_per2;
ALTER PUBLICATION

订阅节点创建表结构

1
2
3
4
5
6
des=# create table francs.test_per2(id int4,name text, create_time timestamp(0) without time zone DEFAULT '2000-01-01 00:00:00'::timestamp without time zone);
CREATE TABLE

des=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION ;
NOTICE: added subscription for table francs.test_per2
ALTER SUBSCRIPTION

Insert 压力测试

insert 脚本

1
2
3
[pg10@csv-tfcs01 loadtest]$ cat insert_1.sql
set v_id random(1,1000000)
insert into francs.test_per2(id,name) values (:vid,:vid||'_a');

pgbench

1
2
[pg10@csv-tfcs01 loadtest]$ nohup pgbench -c 5  -T 120  -d francs -U francs -n N -M prepared -f insert_1.sql > insert_1.out  &
[2] 11911

发布节点查看延时

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
francs=# select * from pg_stat_replication ;
-[ RECORD 1 ]----+------------------------------
pid | 14790
usesysid | 24809
usename | repuser
application_name | sub1
client_addr | 127.0.0.1
client_hostname |
client_port | 56547
backend_start | 2017-05-28 13:24:58.254526+08
backend_xmin |
state | streaming
sent_lsn | 3/1A26FFE0
write_lsn | 3/1A26FFE0
flush_lsn | 3/1A23AE70
replay_lsn | 3/1A26FFE0
write_lag | 00:00:00.069027
flush_lag | 00:00:00.069027
replay_lag | 00:00:00.069027
sync_priority | 0
sync_state | async

pgbench 过程中持续执行以下命令,取最大值

1
2
3
4
5
francs=# select pg_wal_lsn_diff(sent_lsn,replay_lsn) as "Delay bytes" from pg_stat_replication;
Delay bytes
-------------
433296
(1 row)

备注:通过 pg_wal_lsn_diff 函数查看发布节点最大延时值为 433KB

订阅节点查看延时

1
2
3
4
5
6
7
8
9
10
11
12
13
des=# \d pg_stat_subscription
View "pg_catalog.pg_stat_subscription"
Column | Type | Collation | Nullable | Default
-----------------------+--------------------------+-----------+----------+---------
subid | oid | | |
subname | name | | |
pid | integer | | |
relid | oid | | |
received_lsn | pg_lsn | | |
last_msg_send_time | timestamp with time zone | | |
last_msg_receipt_time | timestamp with time zone | | |
latest_end_lsn | pg_lsn | | |
latest_end_time | timestamp with time zone | | |

pgbench 过程中持续执行以下命令,取最大值

1
2
3
4
5
des=# select pg_wal_lsn_diff(received_lsn,latest_end_lsn) from pg_stat_subscription ;
pg_wal_lsn_diff
-----------------
532264
(1 row)

备注: 订阅节点最大延时为 532 KB。

查看 INSERT 压测TPS

1
2
3
4
5
6
7
8
9
10
11
[pg10@csv-tfcs01 loadtest]$ tail -f insert_1.out
transaction type: insert_1.sql
scaling factor: 1
query mode: prepared
number of clients: 5
number of threads: 1
duration: 120 s
number of transactions actually processed: 1621615
latency average = 0.370 ms
tps = 13512.982187 (including connections establishing)
tps = 13513.233304 (excluding connections establishing)

备注:以上逻辑复制延时 WAL 字节数在几百KB范围内,INSERT 场景下逻辑复制延时可控,不算大。

UPDATE 压测

测试准备

测试表,一千万数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
francs=# \d francs.test_per1
Table "francs.test_per1"
Column | Type | Collation | Nullable | Default
------------+--------------------------------+-----------+----------+----------------------------------------------------
id | bigint | | not null |
name | text | | |
creat_time | timestamp(0) without time zone | | | '2000-01-01 00:00:00'::timestamp without time zone
Indexes:
"test_per1_pkey" PRIMARY KEY, btree (id)
Publications:
"pub1"
francs=# select count(*) from francs.test_per1;
count
----------
10000000
(1 row)

Update 压力测试

update 脚本

1
2
3
4
[pg10@csv-tfcs01 loadtest]$ cat update_1.sql
set v_id random(1,1000000)

update test_per1 set creat_time=clock_timestamp() where id=:v_id;

pgbench

1
2
[pg10@csv-tfcs01 loadtest]$ nohup pgbench -c 5  -T 120  -d francs -U francs -n N -M prepared -f update_1.sql > update_1.out  &
[2] 12536

发布结点查看最大延时

1
2
3
4
5
francs=# select pg_wal_lsn_diff(sent_lsn,replay_lsn) as "Delay bytes" from pg_stat_replication;
Delay bytes
-------------
15152304
(1 row)

订阅节点查看最大延时

1
2
3
4
5
des=# select pg_wal_lsn_diff(received_lsn,latest_end_lsn) from pg_stat_subscription ;
pg_wal_lsn_diff
-----------------
18757656
(1 row)

查看 UPDATE 压测TPS

1
2
3
4
5
6
7
8
9
10
11
[pg10@csv-tfcs01 loadtest]$ tail -f update_1.out
transaction type: update_1.sql
scaling factor: 1
query mode: prepared
number of clients: 5
number of threads: 1
duration: 120 s
number of transactions actually processed: 1302798
latency average = 0.461 ms
tps = 10856.066911 (including connections establishing)
tps = 10856.228038 (excluding connections establishing)

备注:发布结点最大延时达到 15MB, 订阅节点最大延时达到 18MB,可见 UPDATE 场景下延时较大。

参考

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

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

PostgreSQL实战
感谢支持!
0%