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

逻辑复制parallel并发参数测试

news 2025/7/7 16:30:34

逻辑复制parallel并发参数测试

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

1.1、测试结果

  • cpu表中有1000万条数据，大小为1652MB,当更新的数据量多于10万条的时候有明显变化，多余30万条的时候相差2倍。

  • 更新的数据量较多时，逻辑复制使用并发参数相比于使用默认参数性能更高。

  • 更新的数据量较少时，逻辑复制使用并发参数相比于使用默认参数性能相差不大。

更新记录条数streaming影响到订阅端耗时执行SQL语句耗时
1000parallel71ms12.077 ms
1000-45 ms15.496 ms
50000parallel1199 ms357.201 ms
50000-1396 ms586.545 ms
100000parallel4637 ms3227.055 ms
100000-6153 ms4591.671 ms
300000parallel6815 ms5567.591 ms
300000-11850 ms6337.377 ms
1000000parallel24214 ms12734.563 ms
1000000-46474 ms15579.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_levellogical设置 WAL 日志的级别，logical 表示启用逻辑复制功能。
max_replication_slots10最大复制槽的数量，用于逻辑和物理复制。
max_wal_senders10最大 WAL 发送进程的数量，用于将 WAL 日志发送给订阅者或备用服务器。
wal_sender_timeout60000WAL 发送进程等待复制确认的超时时间（单位为毫秒）。
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_slots10最大复制槽的数量，用于逻辑和物理复制。
max_logical_replication_workers4最大逻辑复制工作进程的数量，包括主应用工作进程、表同步工作进程和并行应用工作进程。
max_worker_processes8最大工作进程的数量，用于容纳逻辑复制、并行查询等。
max_sync_workers_per_subscription2每个订阅的最大同步工作进程数量，用于初始数据复制的并行程度。
max_parallel_apply_workers_per_subscription2每个订阅的最大并行应用工作进程数量，用于流式传输正在进行的事务。
参数名称当前值含义
wal_receiver_timeout60000WAL 接收进程在等待主库响应时的最大超时时间（单位为毫秒）。
wal_receiver_status_interval10WAL 接收进程向主库报告状态的最小间隔时间（单位为秒）。
wal_retrieve_retry_interval5000在尝试重新获取 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可能不会记录在服务器日志中。

参数名类型默认值
connectbooleantrue
create_slotbooleantrue
enabledbooleantrue
slot_namestring订阅名
binarybooleanfalse
copy_databooleantrue
streamingenumoff
synchronous_commitenumoff
two_phasebooleanfalse
disable_on_errorbooleanfalse
password_requiredbooleantrue
run_as_ownerbooleanfalse
originstringany

修改传输方式

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

浏览器访问 AWS ECS 上部署的 Docker 容器（监听 80 端口）

✅ 一、ECS 服务配置 Dockerfile 确保监听 80 端口 EXPOSE 80 CMD ["nginx", "-g", "daemon off;"]或 EXPOSE 80 CMD ["python3", "-m", "http.server", "80"]任务定义（Task Definition&…...

编程新知 2025/7/7 1:45:57

基于算法竞赛的c++编程（28）结构体的进阶应用

结构体的嵌套与复杂数据组织 在C中，结构体可以嵌套使用，形成更复杂的数据结构。例如，可以通过嵌套结构体描述多层级数据关系： struct Address {string city;string street;int zipCode; };struct Employee {string name;int id;…...

编程新知 2025/6/17 5:30:27

C++初阶-list的底层

目录 1.std::list实现的所有代码 2.list的简单介绍 2.1实现list的类 2.2_list_iterator的实现 2.2.1_list_iterator实现的原因和好处 2.2.2_list_iterator实现 2.3_list_node的实现 2.3.1. 避免递归的模板依赖 2.3.2. 内存布局一致性 2.3.3. 类型安全的替代方案 2.3.…...

编程新知 2025/7/6 20:01:50

华为OD机试-食堂供餐-二分法

import java.util.Arrays; import java.util.Scanner;public class DemoTest3 {public static void main(String[] args) {Scanner in new Scanner(System.in);// 注意 hasNext 和 hasNextLine 的区别while (in.hasNextLine()) { // 注意 while 处理多个 caseint a in.nextIn…...

编程新知 2025/7/7 6:49:47

【Android】Android 开发 ADB 常用指令

查看当前连接的设备 adb devices 连接设备 adb connect 设备IP 断开已连接的设备 adb disconnect 设备IP 安装应用 adb install 安装包的路径 卸载应用 adb uninstall 应用包名 查看已安装的应用包名 adb shell pm list packages 查看已安装的第三方应用包名 adb shell pm list…...

编程新知 2025/6/16 12:37:53

Golang——9、反射和文件操作

反射和文件操作 1、反射1.1、reflect.TypeOf()获取任意值的类型对象1.2、reflect.ValueOf()1.3、结构体反射 2、文件操作2.1、os.Open()打开文件2.2、方式一：使用Read()读取文件2.3、方式二：bufio读取文件2.4、方式三：os.ReadFile读取2.5、写…...

编程新知 2025/7/5 23:34:09

人工智能--安全大模型训练计划：基于Fine-tuning + LLM Agent

安全大模型训练计划：基于Fine-tuning LLM Agent 1. 构建高质量安全数据集 目标：为安全大模型创建高质量、去偏、符合伦理的训练数据集，涵盖安全相关任务（如有害内容检测、隐私保护、道德推理等）。 1.1 数据收集 描…...

编程新知 2025/6/22 5:40:16

Oracle11g安装包

Oracle 11g安装包 适用于windows系统，64位 下载路径 oracle 11g 安装包...

编程新知 2025/6/21 10:49:31

论文阅读：Matting by Generation

今天介绍一篇关于 matting 抠图的文章，抠图也算是计算机视觉里面非常经典的一个任务了。从早期的经典算法到如今的深度学习算法，已经有很多的工作和这个任务相关。这两年 diffusion 模型很火，大家又开始用 diffusion 模型做各种 CV 任务了&am…...

编程新知 2025/7/4 15:32:20

aardio 自动识别验证码输入

技术尝试 上周在发学习日志时有网友提议“在网页上识别验证码”，于是尝试整合图像识别与网页自动化技术，完成了这套模拟登录流程。核心思路是：截图验证码→OCR识别→自动填充表单→提交并验证结果。 代码在这里 import soImage; import we…...

编程新知 2025/7/6 5:24:04