当前位置：首页 > news >正文

逻辑复制parallel并发参数测试

news 2025/6/26 11:15:40

逻辑复制parallel并发参数测试

一、测试结果、测试环境描述

1.1、测试结果

cpu表中有1000万条数据，大小为1652MB,当更新的数据量多于10万条的时候有明显变化，多余30万条的时候相差2倍。
更新的数据量较多时，逻辑复制使用并发参数相比于使用默认参数性能更高。
更新的数据量较少时，逻辑复制使用并发参数相比于使用默认参数性能相差不大。

更新记录条数	streaming	影响到订阅端耗时	执行SQL语句耗时
1000	parallel	71ms	12.077 ms
1000	-	45 ms	15.496 ms
50000	parallel	1199 ms	357.201 ms
50000	-	1396 ms	586.545 ms
100000	parallel	4637 ms	3227.055 ms
100000	-	6153 ms	4591.671 ms
300000	parallel	6815 ms	5567.591 ms
300000	-	11850 ms	6337.377 ms
1000000	parallel	24214 ms	12734.563 ms
1000000	-	46474 ms	15579.122 ms

表中streaming列说明

-- streaming列为 “-”
SELECT substream FROM pg_subscription WHERE subname = 'sub_cpu';substream 
-----------f
(1 row)

-- streaming列为parallel
SELECT substream FROM pg_subscription WHERE subname = 'sub_cpu';substream 
-----------p
(1 row)

1.2、测试环境

数据库版本	PostgreSQL-16.6
内核数	4 core (x86-64)
内存大小	4 GB
OS 系统版本	rhel7
架构	x86-64

二、逻辑复制环境搭建

2.1、发布端环境部署

-- 创建一个表
CREATE TABLE cpu (id SERIAL PRIMARY KEY,   time                   TIMESTAMPTZ NOT NULL,           -- 时间戳，带时区tags_id                INTEGER,                         -- 标签ID，整数类型hostname               TEXT,                            -- 主机名，文本类型usage_user             DOUBLE PRECISION,               -- 用户CPU使用率，双精度浮点数usage_system           DOUBLE PRECISION,               -- 系统CPU使用率，双精度浮点数usage_idle             DOUBLE PRECISION,               -- 空闲CPU使用率，双精度浮点数usage_nice             DOUBLE PRECISION,               -- Nice CPU使用率，双精度浮点数usage_iowait           DOUBLE PRECISION,               -- I/O等待CPU使用率，双精度浮点数usage_irq              DOUBLE PRECISION,               -- 硬中断CPU使用率，双精度浮点数usage_softirq          DOUBLE PRECISION,               -- 软件中断CPU使用率，双精度浮点数usage_steal            DOUBLE PRECISION,               -- 偷取CPU使用率，双精度浮点数usage_guest            DOUBLE PRECISION,               -- Guest CPU使用率，双精度浮点数usage_guest_nice       DOUBLE PRECISION              -- Guest Nice CPU使用率，双精度浮点数
);
-- 给表添加逻辑复制标识
ALTER TABLE cpu REPLICA IDENTITY DEFAULT;-- 创建发布
CREATE PUBLICATION pub_cpu FOR TABLE cpu;-- 创建逻辑复制槽
SELECT pg_create_logical_replication_slot('fd_logical', 'pgoutput');-- 查看逻辑复制槽、发布
SELECT * FROM pg_publication WHERE pubname = 'pub_cpu';
SELECT * FROM pg_replication_slots WHERE slot_name = 'fd_logical';

2.2、订阅端环境部署

-- 订阅端创建表
CREATE TABLE cpu (id SERIAL PRIMARY KEY,   time                   TIMESTAMPTZ NOT NULL,           -- 时间戳，带时区tags_id                INTEGER,                         -- 标签ID，整数类型hostname               TEXT,                            -- 主机名，文本类型usage_user             DOUBLE PRECISION,               -- 用户CPU使用率，双精度浮点数usage_system           DOUBLE PRECISION,               -- 系统CPU使用率，双精度浮点数usage_idle             DOUBLE PRECISION,               -- 空闲CPU使用率，双精度浮点数usage_nice             DOUBLE PRECISION,               -- Nice CPU使用率，双精度浮点数usage_iowait           DOUBLE PRECISION,               -- I/O等待CPU使用率，双精度浮点数usage_irq              DOUBLE PRECISION,               -- 硬中断CPU使用率，双精度浮点数usage_softirq          DOUBLE PRECISION,               -- 软件中断CPU使用率，双精度浮点数usage_steal            DOUBLE PRECISION,               -- 偷取CPU使用率，双精度浮点数usage_guest            DOUBLE PRECISION,               -- Guest CPU使用率，双精度浮点数usage_guest_nice       DOUBLE PRECISION              -- Guest Nice CPU使用率，双精度浮点数
);-- 创建订阅
CREATE SUBSCRIPTION sub_cpu
CONNECTION 'host=192.168.6.108 port=9432 dbname=test user=fbase password=fbase'
PUBLICATION pub_cpu
WITH (slot_name = 'fd_logical',create_slot = false,streaming = 'parallel'
);-- 查看订阅端信息
SELECT * FROM pg_subscription WHERE subname = 'sub_cpu';
-- 查看订阅状态
SELECT * FROM pg_stat_subscription WHERE subid IN (SELECT oid FROM pg_subscription WHERE subname = 'sub_cpu');

给已有的发布端添加表

-- 查看复制状态、逻辑复制槽状态
select * from pg_replication_slots;
select * from pg_stat_replication;-- 发布端、订阅端创建表
CREATE TABLE memory (id SERIAL PRIMARY KEY,   time TIMESTAMPTZ NOT NULL,tags_id INTEGER,hostname TEXT,total_memory BIGINT,        -- 总内存used_memory BIGINT,         -- 已用内存free_memory BIGINT,         -- 空闲内存memory_usage DOUBLE PRECISION -- 内存使用率
);ALTER TABLE memory REPLICA IDENTITY DEFAULT;
-- 添加表到发布端
ALTER PUBLICATION pub_cpu ADD TABLE memory;-- 查看发布端中的表
SELECT * FROM pg_publication_tables WHERE pubname = 'pub_cpu';-- 订阅中建表
CREATE TABLE memory (id SERIAL PRIMARY KEY,   time TIMESTAMPTZ NOT NULL,tags_id INTEGER,hostname TEXT,total_memory BIGINT,        -- 总内存used_memory BIGINT,         -- 已用内存free_memory BIGINT,         -- 空闲内存memory_usage DOUBLE PRECISION -- 内存使用率
);-- 确保订阅端在运行
SELECT * FROM pg_stat_subscription WHERE subid IN (SELECT oid FROM pg_subscription WHERE subname = 'sub_cpu');-- 刷新订阅端ALTER SUBSCRIPTION sub_cpu REFRESH PUBLICATION;-- 查看订阅端信息
SELECT * FROM pg_subscription WHERE subname = 'sub_cpu';

2.3、数据同步验证

-- 发布端插入数据
INSERT INTO cpu (time, tags_id, hostname, usage_user, usage_system, usage_idle, usage_nice, usage_iowait, usage_irq, usage_softirq, usage_steal, usage_guest, usage_guest_nice) VALUES ('2025-01-23 12:00:00+08', 1, 'server1', 20.5, 10.3, 65.2, 2.1, 1.5, 0.3, 0.2, 0.1, 0.5, 0.4);INSERT INTO memory (time, tags_id, hostname, total_memory, used_memory, free_memory, memory_usage) VALUES ('2025-01-23 13:00:00+08', 1, 'server1', 16384, 8192, 8192, 50.0);-- 更新
UPDATE cpu SET hostname = 'server2' WHERE hostname = 'server1';
UPDATE memory SET memory_usage = 60.0 WHERE hostname = 'server1';-- 删除
DELETE FROM cpu WHERE hostname = 'server2';
DELETE FROM memory WHERE hostname = 'server1';-- 订阅端
select count(*) from cpu;
select count(*) from memory;

2.4、给cpu表放入1000万条数据

-- 在发布端给cpu表添加1千万条数据
INSERT INTO cpu (time, tags_id, hostname, usage_user, usage_system, usage_idle, usage_nice, usage_iowait, usage_irq, usage_softirq, usage_steal, usage_guest, usage_guest_nice)
SELECT-- 以当前时间为基准，添加一个 0 到 1 天的随机时间间隔current_timestamp + (random() * interval '1 day'),1 + floor(random() * 10),'host_' || floor(random() * 100), random() * 100,random() * 100, random() * 100, random() * 100,random() * 100,random() * 100,random() * 100, random() * 100,random() * 100,random() * 100
FROM generate_series(1, 10000000);-- 在发布端给memory表添加10万条数据
INSERT INTO memory (time, tags_id, hostname, total_memory, used_memory, free_memory, memory_usage)
SELECTcurrent_timestamp + (random() * interval '1 day'),  -- 随机时间1 + floor(random() * 10),                           -- 随机 tags_id'host_' || floor(random() * 100),                   -- 随机主机名16384 + floor(random() * 1024),                    -- 随机总内存 (单位: MB)floor(random() * 8192),                             -- 随机已用内存floor(random() * 8192),                             -- 随机空闲内存random() * 100                                      -- 随机内存使用率
FROM generate_series(1, 100000);

三、测试

3.1、更新1000条数据

UPDATE cpu
SET hostname = 'server6'
WHERE id IN (SELECT idFROM cpuLIMIT 1000
);

3.1.1、开启并发参数

测试结果

SQL执行耗时，duration: 12.077 ms
逻辑复制耗时 71ms

2025-01-25 09:17:05.912 CST,"fbase","test",2797,"192.168.6.110:58025",67941f5a.aed,1274,"START_REPLICATION",2025-01-25 07:16:42 CST,3/0,0,DEBUG,00000,"sending replication keepalive",,,,,,,,"WalSndKeepalive, walsender.c:3698","sub_cpu","walsender",,02025-01-25 09:17:05.983 CST,"fbase","test",2797,"192.168.6.110:58025",67941f5a.aed,1283,"START_REPLICATION",2025-01-25 07:16:42 CST,3/0,0,DEBUG,00000,"write 3/DBA394C8 flush 3/DBA394C8 apply 3/DBA394C8 reply_time 2025-01-25 09:17:05.983149+08",,,,,,,,"ProcessStandbyReplyMessage, walsender.c:2101","sub_cpu","walsender",,0

在这里插入图片描述

完整的日志内容

2025-01-25 09:17:05.907 CST,"fbase","test",4500,"::1:61697",67943a8e.1194,29,"idle",2025-01-25 09:12:46 CST,5/50,0,DEBUG,00000,"StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0",,,,,,,,"ShowTransactionStateRec, xact.c:5520","psql","client backend",,0
2025-01-25 09:17:05.908 CST,"fbase","test",4500,"::1:61697",67943a8e.1194,30,"idle",2025-01-25 09:12:46 CST,5/50,0,LOG,00000,"statement: UPDATE cpu
SET hostname = 'server9'
WHERE id IN (SELECT idFROM cpuLIMIT 1000
);",,,,,,,,"exec_simple_query, postgres.c:1078","psql","client backend",,0
2025-01-25 09:17:05.911 CST,,,2790,,67941f4e.ae6,13,,2025-01-25 07:16:30 CST,,0,DEBUG,00000,"snapshot of 1+0 running transaction ids (lsn 3/DB9D9A18 oldest xid 858 latest complete 857 next xid 859)",,,,,,,,"LogCurrentRunningXacts, standby.c:1386","","background writer",,02025-01-25 09:17:05.912 CST,"fbase","test",2797,"192.168.6.110:58025",67941f5a.aed,1274,"START_REPLICATION",2025-01-25 07:16:42 CST,3/0,0,DEBUG,00000,"sending replication keepalive",,,,,,,,"WalSndKeepalive, walsender.c:3698","sub_cpu","walsender",,0
2025-01-25 09:17:05.913 CST,"fbase","test",2797,"192.168.6.110:58025",67941f5a.aed,1275,"START_REPLICATION",2025-01-25 07:16:42 CST,3/0,0,DEBUG,00000,"write 3/DB9D7FF0 flush 3/DB9D7FF0 apply 3/DB9D7FF0 reply_time 2025-01-25 09:17:05.913414+08",,,,,,,,"ProcessStandbyReplyMessage, walsender.c:2101","sub_cpu","walsender",,02025-01-25 09:17:05.918 CST,"fbase","test",4500,"::1:61697",67943a8e.1194,31,"UPDATE",2025-01-25 09:12:46 CST,5/50,858,DEBUG,00000,"CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 858/1/0 (used)",,,,,,,,"ShowTransactionStateRec, xact.c:5520","psql","client backend",,-4673810378594060932
2025-01-25 09:17:05.920 CST,"fbase","test",4500,"::1:61697",67943a8e.1194,32,"UPDATE",2025-01-25 09:12:46 CST,5/0,0,LOG,00000,"duration: 12.077 ms",,,,,,,,"exec_simple_query, postgres.c:1370","psql","client backend",,-4673810378594060932
2025-01-25 09:17:05.920 CST,"fbase","test",2797,"192.168.6.110:58025",67941f5a.aed,1276,"START_REPLICATION",2025-01-25 07:16:42 CST,3/0,0,DEBUG,00000,"serializing snapshot to pg_logical/snapshots/3-DB9D99E0.snap",,,,,,,,"SnapBuildSerialize, snapbuild.c:1685","sub_cpu","walsender",,0
2025-01-25 09:17:05.921 CST,"fbase","test",2797,"192.168.6.110:58025",67941f5a.aed,1277,"START_REPLICATION",2025-01-25 07:16:42 CST,3/0,0,DEBUG,00000,"purged committed transactions from 0 to 0, xmin: 858, xmax: 843",,,,,,,,"SnapBuildPurgeOlderTxn, snapbuild.c:974","sub_cpu","walsender",,0
2025-01-25 09:17:05.921 CST,"fbase","test",2797,"192.168.6.110:58025",67941f5a.aed,1278,"START_REPLICATION",2025-01-25 07:16:42 CST,3/0,0,DEBUG,00000,"xmin: 858, xmax: 843, oldest running: 858, oldest xmin: 842",,,,,,,,"SnapBuildProcessRunningXacts, snapbuild.c:1262","sub_cpu","walsender",,0
2025-01-25 09:17:05.922 CST,"fbase","test",2797,"192.168.6.110:58025",67941f5a.aed,1279,"START_REPLICATION",2025-01-25 07:16:42 CST,3/20,0,DEBUG,00000,"StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0",,,,,,,,"ShowTransactionStateRec, xact.c:5520","sub_cpu","walsender",,0
2025-01-25 09:17:05.928 CST,"fbase","test",2797,"192.168.6.110:58025",67941f5a.aed,1280,"START_REPLICATION",2025-01-25 07:16:42 CST,3/0,0,DEBUG,00000,"UpdateDecodingStats: updating stats 0x1815fd0 0 0 0 0 0 0 1 240000",,,,,,,,"UpdateDecodingStats, logical.c:1935","sub_cpu","walsender",,0
2025-01-25 09:17:05.928 CST,"fbase","test",2797,"192.168.6.110:58025",67941f5a.aed,1281,"START_REPLICATION",2025-01-25 07:16:42 CST,3/0,0,DEBUG,00000,"sending replication keepalive",,,,,,,,"WalSndKeepalive, walsender.c:3698","sub_cpu","walsender",,0
2025-01-25 09:17:05.950 CST,"fbase","test",2797,"192.168.6.110:58025",67941f5a.aed,1282,"START_REPLICATION",2025-01-25 07:16:42 CST,3/0,0,DEBUG,00000,"write 3/DBA394C8 flush 3/DB9D7FF0 apply 3/DBA394C8 reply_time 2025-01-25 09:17:05.950609+08",,,,,,,,"ProcessStandbyReplyMessage, walsender.c:2101","sub_cpu","walsender",,0
2025-01-25 09:17:05.983 CST,"fbase","test",2797,"192.168.6.110:58025",67941f5a.aed,1283,"START_REPLICATION",2025-01-25 07:16:42 CST,3/0,0,DEBUG,00000,"write 3/DBA394C8 flush 3/DBA394C8 apply 3/DBA394C8 reply_time 2025-01-25 09:17:05.983149+08",,,,,,,,"ProcessStandbyReplyMessage, walsender.c:2101","sub_cpu","walsender",,0
2025-01-25 09:17:12.806 CST,,,2793,,67941f4e.ae9,728,,2025-01-25 07:16:30 CST,1/363,0,DEBUG,00000,"StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0",,,,,,,,"ShowTransactionStateRec, xact.c:5520","","autovacuum launcher",,0
2025-01-25 09:17:12.807 CST,,,2793,,67941f4e.ae9,729,,2025-01-25 07:16:30 CST,1/363,0,DEBUG,00000,"CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0",,,,,,,,"ShowTransactionStateRec, xact.c:5520","","autovacuum launcher",,0

3.1.2、使用默认参数

SQL语句耗时，duration: 15.496 ms
逻辑复制耗时45 ms

duration: 15.496 ms
相差45 ms2025-01-25 09:45:59.339 CST,"fbase","test",5080,"192.168.6.110:17521",67943eb1.13d8,314,"START_REPLICATION",2025-01-25 09:30:25 CST,3/0,0,DEBUG,00000,"sending replication keepalive",,,,,,"START_REPLICATION SLOT ""fd_logical"" LOGICAL 1/910B8DF8 (proto_version '4', origin 'any', publication_names '""pub_cpu""')",,"WalSndKeepalive, walsender.c:3698","sub_cpu","walsender",,02025-01-25 09:45:59.384 
CST,"fbase","test",5080,"192.168.6.110:17521",67943eb1.13d8,316,"START_REPLICATION",2025-01-25 09:30:25 CST,3/0,0,DEBUG,00000,"write 1/92ACA520 flush 1/92ACA520 apply 1/92ACA520 reply_time 2025-01-25 09:45:59.384228+08",,,,,,"START_REPLICATION SLOT ""fd_logical"" LOGICAL 1/910B8DF8 (proto_version '4', origin 'any', publication_names '""pub_cpu""')",,"ProcessStandbyReplyMessage, walsender.c:2101","sub_cpu","walsender",,0

在这里插入图片描述

3.2、更新5万条数据

UPDATE cpu
SET hostname = 'server7'
WHERE id IN (SELECT idFROM cpuLIMIT 50000
);

3.2.1、开启并发参数

SQL执行耗时，duration: 357.201 ms
逻辑复制耗时1199 ms

2025-01-25 04:12:34.667 CST,"fbase","test",3434,"192.168.6.110:54263",6793f309.d6a,350,"START_REPLICATION",2025-01-25 04:07:37 CST,3/0,0,DEBUG,00000,"sending replication keepalive",,,,,,,,"WalSndKeepalive, walsender.c:3698","sub_cpu","walsender",,0
2025-01-25 04:12:35.866 CST,"fbase","test",3434,"192.168.6.110:54263",6793f309.d6a,411,"START_REPLICATION",2025-01-25 04:07:37 CST,3/0,0,DEBUG,00000,"write 3/DA3B0468 flush 3/DA3B0468 apply 3/DA3B0468 reply_time 2025-01-25 04:12:35.866369+08",,,,,,,,"ProcessStandbyReplyMessage, walsender.c:2101","sub_cpu","walsender",,0

3.2.2、使用默认参数

SQL执行耗时，duration: 586.545 ms
逻辑复制耗时1396 ms

2025-01-25 03:56:24.050 CST,"fbase","test",2475,"192.168.6.110:42787",6793ea24.9ab,3146,"START_REPLICATION",2025-01-25 03:29:40 CST,3/0,0,DEBUG,00000,"sending replication keepalive",,,,,,"START_REPLICATION SLOT ""fd_logical"" LOGICAL 1/697B9D18 (proto_version '4', origin 'any', publication_names '""pub_cpu""')",,"WalSndKeepalive, walsender.c:3698","sub_cpu","walsender",,02025-01-25 03:56:25.446 CST,"fbase","test",2475,"192.168.6.110:42787",6793ea24.9ab,3234,"START_REPLICATION",2025-01-25 03:29:40 CST,3/0,0,DEBUG,00000,"write 1/910B8DF8 flush 1/910B8DF8 apply 1/910B8DF8 reply_time 2025-01-25 03:56:25.446197+08",,,,,,"START_REPLICATION SLOT ""fd_logical"" LOGICAL 1/697B9D18 (proto_version '4', origin 'any', publication_names '""pub_cpu""')",,"ProcessStandbyReplyMessage, walsender.c:2101","sub_cpu","walsender",,0

3.3、更新10万条数据

UPDATE cpu
SET hostname = 'server5'
WHERE id IN (SELECT idFROM cpuLIMIT 100000
);

3.3.1、开启并发参数

SQL执行耗时，duration: 3227.055 ms
逻辑复制耗时4637 ms

2025-01-24 13:07:13.016 CST,"fbase","test",5800,"192.168.6.110:59635",67931e75.16a8,11192,"START_REPLICATION",2025-01-24 13:00:37 CST,3/0,0,DEBUG,00000,"sending replication keepalive",,,,,,,,"WalSndKeepalive, walsender.c:3698","sub_cpu","walsender",,02025-01-24 13:07:17.653 CST,"fbase","test",5800,"192.168.6.110:59635",67931e75.16a8,11307,"START_REPLICATION",2025-01-24 13:00:37 CST,3/0,0,DEBUG,00000,"write 3/9EEAF098 flush 3/9EEAF098 apply 3/9EEAF098 reply_time 2025-01-24 13:07:17.652963+08",,,,,,,,"ProcessStandbyReplyMessage, walsender.c:2101","sub_cpu","walsender",,0

3.3.2、使用默认参数

SQL执行耗时，duration: 4591.671 ms
逻辑复制耗时6153 ms

2025-01-24 15:13:50.985 CST,"fbase","test",8435,"192.168.6.110:10523",67933c84.20f3,406,"START_REPLICATION",2025-01-24 15:08:52 CST,3/0,0,DEBUG,00000,"sending replication keepalive",,,,,,"START_REPLICATION SLOT ""fd_logical"" LOGICAL 1/5EB27398 (proto_version '4', origin 'any', publication_names '""pub_cpu""')",,"WalSndKeepalive, walsender.c:3698","sub_cpu","walsender",,02025-01-24 15:13:57.138 CST,"fbase","test",8435,"192.168.6.110:10523",67933c84.20f3,487,"START_REPLICATION",2025-01-24 15:08:52 CST,3/0,0,DEBUG,00000,"write 1/697B9D18 flush 1/697B9D18 apply 1/697B9D18 reply_time 2025-01-24 15:13:57.137669+08",,,,,,"START_REPLICATION SLOT ""fd_logical"" LOGICAL 1/5EB27398 (proto_version '4', origin 'any', publication_names '""pub_cpu""')",,"ProcessStandbyReplyMessage, walsender.c:2101","sub_cpu","walsender",,0

3.4、更新30万条数据

UPDATE cpu
SET hostname = 'server5'
WHERE id IN (SELECT idFROM cpuLIMIT 300000
);

3.4.1、开启并发参数

SQL执行耗时，duration: 5567.591 ms
逻辑复制耗时6815 ms

2025-01-24 14:05:10.572 CST,"fbase","test",5800,"192.168.6.110:59635",67931e75.16a8,14675,"START_REPLICATION",2025-01-24 13:00:37 CST,3/0,0,DEBUG,00000,"sending replication keepalive",,,,,,,,"WalSndKeepalive, walsender.c:3698","sub_cpu","walsender",,02025-01-24 14:05:17.387 
CST,"fbase","test",5800,"192.168.6.110:59635",67931e75.16a8,14920,"START_REPLICATION",2025-01-24 13:00:37 CST,3/0,0,DEBUG,00000,"write 3/D493FC30 flush 3/D493FC30 apply 3/D493FC30 reply_time 2025-01-24 14:05:17.386781+08",,,,,,,,"ProcessStandbyReplyMessage, walsender.c:2101","sub_cpu","walsender",,0

3.4.2、使用默认参数

SQL执行耗时，duration: 6337.377 ms
逻辑复制耗时11850 ms

2025-01-25 03:36:10.653 CST,"fbase","test",2475,"192.168.6.110:42787",6793ea24.9ab,1517,"START_REPLICATION",2025-01-25 03:29:40 CST,3/0,0,DEBUG,00000,"sending replication keepalive",,,,,,"START_REPLICATION SLOT ""fd_logical"" LOGICAL 1/697B9D18 (proto_version '4', origin 'any', publication_names '""pub_cpu""')",,"WalSndKeepalive, walsender.c:3698","sub_cpu","walsender",,02025-01-25 03:36:22.503 CST,"fbase","test",2475,"192.168.6.110:42787",6793ea24.9ab,1779,"START_REPLICATION",2025-01-25 03:29:40 CST,3/0,0,DEBUG,00000,"write 1/892E2370 flush 1/892E2370 apply 1/892E2370 reply_time 2025-01-25 03:36:22.502836+08",,,,,,"START_REPLICATION SLOT ""fd_logical"" LOGICAL 1/697B9D18 (proto_version '4', origin 'any', publication_names '""pub_cpu""')",,"ProcessStandbyReplyMessage, walsender.c:2101","sub_cpu","walsender",,0

3.5、更新100万条数据

test=# UPDATE cpu
SET hostname = 'server1'
WHERE id IN (SELECT idFROM cpuLIMIT 1000000
);

3.5.1、开启并发参数

SQL执行耗时，duration: 12734.563 ms
逻辑复制耗时24214 ms

2025-01-24 10:55:58.105 CST,"fbase","test",4844,"192.168.6.110:59629",67930081.12ec,79,"START_REPLICATION",2025-01-24 10:52:49 CST,3/0,0,DEBUG,00000,"serializing snapshot to pg_logical/snapshots/3-48005F70.snap",,,,,,,,"SnapBuildSerialize, snapbuild.c:1685","sub_cpu","walsender",,02025-01-24 10:56:22.319 CST,"fbase","test",4844,"192.168.6.110:59629",67930081.12ec,21047,"START_REPLICATION",2025-01-24 10:52:49 CST,3/0,0,DEBUG,00000,"write 3/6429B010 flush 3/6429B010 apply 3/6429B010 reply_time 2025-01-24 10:56:22.306827+08",,,,,,,,"ProcessStandbyReplyMessage, walsender.c:2101","sub_cpu","walsender",,0

3.5.2、使用默认参数

SQL执行耗时，duration: 15579.122 ms
逻辑复制耗时46474 ms

2025-01-24 09:55:40.783 CST,"fbase","test",2430,"192.168.6.110:10501",6792e20a.97e,2368,"START_REPLICATION",2025-01-24 08:42:50 CST,3/0,0,DEBUG,00000,"sending replication keepalive",,,,,,"START_REPLICATION SLOT ""fd_logical"" LOGICAL 1/1B005368 (proto_version '4', origin 'any', publication_names '""pub_cpu""')",,"WalSndKeepalive, walsender.c:3698","sub_cpu","walsender",,0
2025-01-24 09:56:16.241 CST,"fbase","test",2430,"192.168.6.110:10501",6792e20a.97e,4158,"START_REPLICATION",2025-01-24 08:42:50 CST,3/0,0,DEBUG,00000,"write 1/58CEB118 flush 1/58CEB118 apply 1/58CEB118 reply_time 2025-01-24 09:56:16.241509+08",,,,,,"START_REPLICATION SLOT ""fd_logical"" LOGICAL 1/1B005368 (proto_version '4', origin 'any', publication_names '""pub_cpu""')",,"ProcessStandbyReplyMessage, walsender.c:2101","sub_cpu","walsender",,0

四、逻辑复制参考文献

与逻辑复制相关的参数，官网介绍

31.10.1. 发布者

wal_level 必须设置为 logical。
max_replication_slots 必须设置为至少预期连接的订阅数量，再加上一些预留用于表同步。
max_wal_senders 应设置为至少与 max_replication_slots 相同，再加上同时连接的物理副本数量。
逻辑复制的 WAL 发送进程也会受到 wal_sender_timeout 的影响。

108机器发布端参数配置

参数名称	当前值	含义
`wal_level`	logical	设置 WAL 日志的级别，`logical` 表示启用逻辑复制功能。
`max_replication_slots`	10	最大复制槽的数量，用于逻辑和物理复制。
`max_wal_senders`	10	最大 WAL 发送进程的数量，用于将 WAL 日志发送给订阅者或备用服务器。
`wal_sender_timeout`	60000	WAL 发送进程等待复制确认的超时时间（单位为毫秒）。

test=# SELECT name, setting
FROM pg_settings
WHERE name IN ('wal_level', 'max_replication_slots', 'max_wal_senders', 'wal_sender_timeout');name          | setting 
-----------------------+---------max_replication_slots | 10max_wal_senders       | 10wal_level             | logicalwal_sender_timeout    | 60000
(4 rows)

31.10.2. 订阅者

max_replication_slots 必须设置为至少将添加到订阅者的订阅数量，再加上一些预留用于表同步。
max_logical_replication_workers 必须设置为至少订阅数量（用于主应用工作进程），再加上一些预留用于表同步工作进程和并行应用工作进程。
max_worker_processes 可能需要调整以容纳复制工作进程，至少为 (max_logical_replication_workers + 1)。注意，某些扩展和并行查询也会占用 max_worker_processes 的工作槽。
max_sync_workers_per_subscription 控制订阅初始化时或添加新表时初始数据复制的并行程度。
max_parallel_apply_workers_per_subscription 控制订阅参数 streaming = parallel 时，正在进行的事务流的并行程度。
逻辑复制工作进程也会受到 wal_receiver_timeout、wal_receiver_status_interval 和 wal_retrieve_retry_interval 的影响。

110机器订阅端参数配置

以下是这些参数的表格：

参数名称	当前值	含义
`max_replication_slots`	10	最大复制槽的数量，用于逻辑和物理复制。
`max_logical_replication_workers`	4	最大逻辑复制工作进程的数量，包括主应用工作进程、表同步工作进程和并行应用工作进程。
`max_worker_processes`	8	最大工作进程的数量，用于容纳逻辑复制、并行查询等。
`max_sync_workers_per_subscription`	2	每个订阅的最大同步工作进程数量，用于初始数据复制的并行程度。
`max_parallel_apply_workers_per_subscription`	2	每个订阅的最大并行应用工作进程数量，用于流式传输正在进行的事务。

参数名称	当前值	含义
`wal_receiver_timeout`	60000	WAL 接收进程在等待主库响应时的最大超时时间（单位为毫秒）。
`wal_receiver_status_interval`	10	WAL 接收进程向主库报告状态的最小间隔时间（单位为秒）。
`wal_retrieve_retry_interval`	5000	在尝试重新获取 WAL 日志失败后的重试间隔时间（单位为毫秒）。

test=# SELECT name, setting
FROM pg_settings
WHERE name IN ('max_replication_slots','max_logical_replication_workers','max_worker_processes','max_sync_workers_per_subscription','max_parallel_workers','max_parallel_apply_workers_per_subscription'
);name                     | setting 
---------------------------------------------+---------max_logical_replication_workers             | 4max_parallel_apply_workers_per_subscription | 4max_parallel_workers                        | 8max_replication_slots                       | 10max_sync_workers_per_subscription           | 4max_worker_processes                        | 16
(6 rows)test=# SELECT name, setting
FROM pg_settings
WHERE name IN ('wal_receiver_timeout','wal_receiver_status_interval','wal_retrieve_retry_interval'
);name             | setting 
------------------------------+---------wal_receiver_status_interval | 10wal_receiver_timeout         | 60000wal_retrieve_retry_interval  | 5000
(3 rows)

创建订阅

test=# \h create subscription 
Command:     CREATE SUBSCRIPTION
Description: define a new subscription
Syntax:
CREATE SUBSCRIPTION subscription_nameCONNECTION 'conninfo'PUBLICATION publication_name [, ...][ WITH ( subscription_parameter [= value] [, ... ] ) ]URL: https://www.postgresql.org/docs/16/sql-createsubscription.html

以下是 CREATE SUBSCRIPTION 命令中 WITH 子句支持的参数的简写表格：

streaming（枚举类型）

指定是否为此订阅启用正在进行的事务的流式传输。默认值是 off，意味着所有事务都在发布者上完全解码，然后才作为整体发送给订阅者。

如果设置为 on，则传入的更改将写入临时文件，并且只有在发布者上提交事务并由订阅者接收后才应用。

如果设置为 parallel，则传入的更改将直接通过一个并行应用工作线程应用（如果有可用的话）。如果没有空闲的并行应用工作线程来处理流式事务，那么更改将写入临时文件，并在事务提交后应用。请注意，如果并行应用工作线程中发生错误，远程事务的完成LSN可能不会记录在服务器日志中。

参数名	类型	默认值
connect	boolean	true
create_slot	boolean	true
enabled	boolean	true
slot_name	string	订阅名
binary	boolean	false
copy_data	boolean	true
streaming	enum	off
synchronous_commit	enum	off
two_phase	boolean	false
disable_on_error	boolean	false
password_required	boolean	true
run_as_owner	boolean	false
origin	string	any

修改传输方式

substream由原来的f改为了p

test=# ALTER SUBSCRIPTION sub_cpu SET (streaming = 'parallel');
ALTER SUBSCRIPTION
test=# SELECT * FROM pg_subscription WHERE subname = 'sub_cpu';
-[ RECORD 1 ]-------+-------------------------------------------------------------------
oid                 | 21954
subdbid             | 21947
subskiplsn          | 0/0
subname             | sub_cpu
subowner            | 10
subenabled          | t
subbinary           | f
substream           | p
subtwophasestate    | d
subdisableonerr     | f
subpasswordrequired | t
subrunasowner       | f
subconninfo         | host=192.168.6.108 port=8432 dbname=test user=fbase password=fbase
subslotname         | fd_logical
subsynccommit       | off
subpublications     | {pub_cpu}
suborigin           | any

表

test=# select * from pg_stat_subscription;
-[ RECORD 1 ]---------+------------------------------
subid                 | 16399
subname               | sub_cpu
pid                   | 5954
leader_pid            | 4985
relid                 | 
received_lsn          | 
last_msg_send_time    | 
last_msg_receipt_time | 
latest_end_lsn        | 
latest_end_time       | 
-[ RECORD 2 ]---------+------------------------------
subid                 | 16399
subname               | sub_cpu
pid                   | 4985
leader_pid            | 
relid                 | 
received_lsn          | 2/D2917D80
last_msg_send_time    | 2025-01-23 15:13:46.327977+08
last_msg_receipt_time | 2025-01-23 15:13:46.328158+08
latest_end_lsn        | 2/D2917D80
latest_end_time       | 2025-01-23 15:13:46.327977+08

事件

test=# SELECT pid, wait_event_type, wait_event
FROM pg_stat_activity
WHERE wait_event_type IS NOT NULL;pid  | wait_event_type |        wait_event        
------+-----------------+--------------------------4982 | Activity        | AutoVacuumMain4984 | Activity        | LogicalLauncherMain4985 | Activity        | LogicalApplyMain5954 | Activity        | LogicalParallelApplyMain6125 | Timeout         | VacuumDelay4979 | Activity        | BgWriterHibernate4983 | Activity        | ArchiverMain4978 | Timeout         | CheckpointWriteDelay4981 | Activity        | WalWriterMain
(9 rows)

27977+08


**事件**```sql
test=# SELECT pid, wait_event_type, wait_event
FROM pg_stat_activity
WHERE wait_event_type IS NOT NULL;pid  | wait_event_type |        wait_event        
------+-----------------+--------------------------4982 | Activity        | AutoVacuumMain4984 | Activity        | LogicalLauncherMain4985 | Activity        | LogicalApplyMain5954 | Activity        | LogicalParallelApplyMain6125 | Timeout         | VacuumDelay4979 | Activity        | BgWriterHibernate4983 | Activity        | ArchiverMain4978 | Timeout         | CheckpointWriteDelay4981 | Activity        | WalWriterMain
(9 rows)

查看全文

http://www.lryc.cn/news/527249.html