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

逻辑复制parallel并发参数测试

news 2025/7/6 15:42:41

逻辑复制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)

逻辑复制parallel并发参数测试

逻辑复制parallel并发参数测试一、测试结果、测试环境描述 1.1、测试结果 cpu表中有1000万条数据，大小为1652MB,当更新的数据量多于10万条的时候有明显变化，多余30万条的时候相差2倍。更新的数据量较多时，逻辑复制使用并发参数相比于使用…...

编程日记 2025/1/27 21:19:37

Cursor 帮你写一个小程序

Cursor注册地址首先下载客户端点击链接下载 1 打开微信开发者工具创建一个小程序项目选择TS-基础模版官方 2 然后使用Cursor打开小程序创建的项目 3 在CHAT聊天框输入自己的需求比如小程序功能描述：吃什么助手项目名称： 吃什么小程序功能目标…...

编程日记 2025/1/27 21:18:36

WordPress免费证书插件

为了在您的网站上启用HTTPS，您可以使用本插件快速获取Let’s Encrypt免费证书。主要功能： 支持快速申请Let’s Encrypt免费证书支持通配符证书申请，每个证书最多可以绑定100个域名支持自动续期证书支持重颁发证书，证书过期或失…...

编程日记 2025/1/27 21:15:30

Linux：多线程[2] 线程控制

了解： Linux底层提供创建轻量级进程/进程的接口clone，通过选择是否共享资源创建。 vfork和fork都调用的clone进行实现，vfork和父进程共享地址空间-轻量级进程。库函数pthread_create调用的也是底层的clone。 POSIX线程库与线程有关的函数构…...

编程日记 2025/1/27 21:13:21

C++——list的了解和使用

目录引言 forward_list与list 标准库中的list 一、list的常用接口 1.list的迭代器 2.list的初始化 3.list的容量操作 4.list的访问操作 5.list的修改操作 6.list的其他操作二、list与vector的对比结束语引言本篇博客要介绍的是STL中的list。求点赞收藏评论…...

编程日记 2025/1/27 21:11:05

Agent群舞，在亚马逊云科技搭建数字营销多代理(Multi-Agent)（下篇）

在本系列的上篇中，小李哥为大家介绍了如何在亚马逊云科技上给社交数字营销场景创建AI代理的方案，用于社交动态的生成和对文章进行推广曝光。在本篇中小李哥将继续本系列的介绍，为大家介绍如何创建主代理，将多个子代理挂载到主代理…...

编程日记 2025/1/27 21:09:48

DBeaver连接MySQL数据库

打开DBeaver，点击“新建数据库连接”选项。点击“测试连接”，首次连接mysql会提示下载对应的JDBC驱动，点击下载即可。填写服务器地址（这里是本地测试）、mysql的用户名（root）和密码&#xff…...

编程日记 2025/1/27 21:02:30

Leetcode40：组合总和 II

题目描述： 给定一个候选人编号的集合 candidates 和一个目标数 target ，找出 candidates 中所有可以使数字和为 target 的组合。 candidates 中的每个数字在每个组合中只能使用一次。注意：解集不能包含重复的组合。代码思路&#xff…...

编程日记 2025/1/27 21:01:26

win32汇编环境,对话框程序中使用进度条控件

;运行效果 ;win32汇编环境,对话框程序中使用进度条控件 ;进度条控件主要涉及的是长度单位，每步步长，推进的时间。 ;比如你的长度是1000，步长是100，每秒走1次，则10秒走完全程 ;比如你的长度是1000，步长是10，每秒走1次，则100秒走完全程，但每格格子的长度与上面一样 ;以下…...

编程日记 2025/1/27 20:58:20

AIGC时代下的Vue组件开发深度探索

文章目录一、AIGC时代对Vue组件开发的深远影响二、Vue组件开发基础与最佳实践三、AIGC技术在Vue组件开发中的具体应用四、结论与展望随着人工智能技术的飞速发展，AIGC（人工智能生成内容）时代已经悄然来临。在这个时代背景下，软件…...

编程日记 2025/1/27 20:57:13

在Kubernets Cluster中部署LVM类型的StorageClass - 上

适用场景看到B站技术部门的文章，是关于如何在k8s集群部署Elastic Search和Click House等IO密集型数据库应用的。因为要充分利用NvME SSD盘的IOPS，所有数据库应用都直接调用本地SSD盘做为stateful application的 Persistent Volumes。为了可用动态的分…...

编程日记 2025/1/27 20:52:59

一次StarRocks分析的经历

起因有人反馈说SR，在系统资源还有空闲的时候，被操作系统杀掉了。没有日志，怀疑是bug，如果要解决这个bug。据说在网上查到要升级。请我准备一下升级。质疑 StarRocks是一款分析型数据库，2021年正式开源&#xff0c…...

编程日记 2025/1/27 20:50:51

Django网站搭建流程

使用Django搭建网站是一个系统的过程，涉及从环境搭建到部署上线的多个步骤。以下是详细的流程： 1. 环境搭建 （1）安装Python Django是基于Python的Web框架，因此需要先安装Python。建议安装Python 3.8及以上版本。下载地…...

编程日记 2025/1/27 20:48:38

Vue-day2

7.Vue的生命周期 mounted函数：在页面加载完毕时，发送异步请求，加载数据，渲染页面 createApp({date(){},methods:{},mounted:function(){console.log(Vue挂载完毕，发送请求获取数据)} }).mount(#{app}) 8.ajax函数库…...

编程日记 2025/1/27 20:44:16

Day44：列表元素的修改

在 Python 中，列表是一种可变的数据结构，意味着我们可以对列表中的元素进行修改。修改列表元素的方式有很多种，包括通过索引修改、切片修改、使用 append() 和 extend() 添加新元素、以及删除元素等。今天，我们将学习如何在列表中…...

编程日记 2025/1/27 20:43:11

在 AMD GPU 上使用 vLLM 的 Triton 推理服务器

Triton Inference Server with vLLM on AMD GPUs — ROCm Blogs 2025年1月8日，作者：Fabricio Flores，Tiffany Mintz，Eliot Li，Yao Liu，Ted Themistokleous，Brian Pickrell，Vish Vadl…...

编程日记 2025/1/27 20:39:03

day7手机拍照装备

对焦对不上：1、光太暗；2、离太近；3、颜色太单一没有区分点滤镜可以后期P 渐变灰滤镜：均衡色彩，暗的地方亮一些，亮的地方暗一些中灰滤镜：减少光差手机支架：最基本70cm即可手…...

编程日记 2025/1/27 20:26:37

HarmonyOS：创建应用静态快捷方式

一、前言静态快捷方式是一种在系统中创建的可以快速访问应用程序或特定功能的链接。它通常可以在长按应用图标，以图标和相应的文字出现在应用图标的上方，用户可以迅速启动对应应用程序的组件。使用快捷方式，可以提高效率，节省了查…...

编程日记 2025/1/27 20:21:54

[SUCTF 2018]MultiSQL1

进去题目页面如下发现可能注入点只有登录和注册,那么我们先注册一个用户，发现跳转到了/user/user.php， 查看用户信息,发现有传参/user/user.php?id1 用?id1 and 11,和?id1 and 12,判断为数字型注入原本以为是简单的数字型注入，看到大…...

编程日记 2025/1/27 20:15:44

kafka-部署安装

一. 简述： Kafka 是一个分布式流处理平台，常用于构建实时数据管道和流应用。二. 安装部署： 1. 依赖： a). Java：Kafka 需要 Java 8 或更高版本。 b). zookeeper： #tar fxvz zookeeper-3.7.0.tar.gz #…...

编程日记 2025/1/27 20:11:39

Linux 文件类型，目录与路径，文件与目录管理

文件类型后面的字符表示文件类型标志普通文件：-（纯文本文件，二进制文件，数据格式文件） 如文本文件、图片、程序文件等。目录文件：d（directory） 用来存放其他文件或子目录。设备…...

编程新知 2025/7/6 7:57:15

盘古信息PCB行业解决方案：以全域场景重构，激活智造新未来

一、破局：PCB行业的时代之问在数字经济蓬勃发展的浪潮中，PCB（印制电路板）作为 “电子产品之母”，其重要性愈发凸显。随着 5G、人工智能等新兴技术的加速渗透，PCB行业面临着前所未有的挑战与机遇。产品迭代…...

编程新知 2025/7/5 22:08:35

【网络安全产品大调研系列】2. 体验漏洞扫描

前言 2023 年漏洞扫描服务市场规模预计为 3.06（十亿美元）。漏洞扫描服务市场行业预计将从 2024 年的 3.48（十亿美元）增长到 2032 年的 9.54（十亿美元）。预测期内漏洞扫描服务市场 CAGR（增长率&…...

编程新知 2025/7/6 14:29:40

C++八股 —— 单例模式

文章目录 1. 基本概念2. 设计要点3. 实现方式4. 详解懒汉模式 1. 基本概念线程安全（Thread Safety） 线程安全是指在多线程环境下，某个函数、类或代码片段能够被多个线程同时调用时，仍能保证数据的一致性和逻辑的正确性&#xf…...

编程新知 2025/7/5 15:58:41

论文笔记——相干体技术在裂缝预测中的应用研究

目录相关地震知识补充地震数据的认识地震几何属性相干体算法定义基本原理第一代相干体技术：基于互相关的相干体技术（Correlation）第二代相干体技术：基于相似的相干体技术（Semblance）基于多道相似的相干体…...

编程新知 2025/7/6 1:39:49

浪潮交换机配置track检测实现高速公路收费网络主备切换NQA

浪潮交换机track配置项目背景高速网络拓扑网络情况分析通信线路收费网络路由收费汇聚交换机相应配置收费汇聚track配置项目背景在实施省内一条高速公路时遇到的需求，本次涉及的主要是收费汇聚交换机的配置，浪潮网络设备在高速项目很少，通…...

编程新知 2025/7/6 13:07:42

【Linux】Linux 系统默认的目录及作用说明

博主介绍：✌全网粉丝23W，CSDN博客专家、Java领域优质创作者，掘金/华为云/阿里云/InfoQ等平台优质作者、专注于Java技术领域✌ 技术范围：SpringBoot、SpringCloud、Vue、SSM、HTML、Nodejs、Python、MySQL、PostgreSQL、大数据、物…...

编程新知 2025/7/4 18:12:21

【LeetCode】算法详解#6 ---除自身以外数组的乘积

1.题目介绍给定一个整数数组 nums，返回数组 answer ，其中 answer[i] 等于 nums 中除 nums[i] 之外其余各元素的乘积。题目数据保证数组 nums之中任意元素的全部前缀元素和后缀的乘积都在 32 位整数范围内。请不要使用除法，且在 O…...

编程新知 2025/7/2 6:00:06

macOS 终端智能代理检测

🧠 终端智能代理检测：自动判断是否需要设置代理访问 GitHub 在开发中，使用 GitHub 是非常常见的需求。但有时候我们会发现某些命令失败、插件无法更新，例如： fatal: unable to access https://github.com/ohmyzsh/oh…...

编程新知 2025/7/6 3:27:49

Java详解LeetCode 热题 100(26):LeetCode 142. 环形链表 II（Linked List Cycle II）详解

文章目录 1. 题目描述1.1 链表节点定义 2. 理解题目2.1 问题可视化2.2 核心挑战 3. 解法一：HashSet 标记访问法3.1 算法思路3.2 Java代码实现3.3 详细执行过程演示3.4 执行结果示例3.5 复杂度分析3.6 优缺点分析 4. 解法二：Floyd 快慢指针法（…...

编程新知 2025/7/5 8:45:56

逻辑复制parallel并发参数测试

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

1.1、测试结果

1.2、测试环境

二、逻辑复制环境搭建

2.1、发布端环境部署

2.2、订阅端环境部署

2.3、数据同步验证

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

三、测试

3.1、更新1000条数据

3.1.1、开启并发参数

3.1.2、使用默认参数

3.2、更新5万条数据

3.2.1、开启并发参数

3.2.2、使用默认参数

3.3、更新10万条数据

3.3.1、开启并发参数

3.3.2、使用默认参数

3.4、更新30万条数据

3.4.1、开启并发参数

3.4.2、使用默认参数

3.5、更新100万条数据

3.5.1、开启并发参数

3.5.2、使用默认参数

四、逻辑复制参考文献

相关文章：