逻辑复制parallel并发参数测试
逻辑复制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倍。 更新的数据量较多时，逻辑复制使用并发参数相比于使用…...
Cursor 帮你写一个小程序
Cursor注册地址 首先下载客户端 点击链接下载 1 打开微信开发者工具创建一个小程序项目 选择TS-基础模版 官方 2 然后使用Cursor打开小程序创建的项目 3 在CHAT聊天框输入自己的需求 比如 小程序功能描述：吃什么助手 项目名称： 吃什么小程序 功能目标…...
WordPress免费证书插件
为了在您的网站上启用HTTPS，您可以使用本插件快速获取Let’s Encrypt免费证书。 主要功能： 支持快速申请Let’s Encrypt免费证书支持通配符证书申请，每个证书最多可以绑定100个域名支持自动续期证书支持重颁发证书，证书过期或失…...
Linux：多线程[2] 线程控制
了解： Linux底层提供创建轻量级进程/进程的接口clone，通过选择是否共享资源创建。 vfork和fork都调用的clone进行实现，vfork和父进程共享地址空间-轻量级进程。 库函数pthread_create调用的也是底层的clone。 POSIX线程库 与线程有关的函数构…...
C++——list的了解和使用
目录 引言 forward_list与list 标准库中的list 一、list的常用接口 1.list的迭代器 2.list的初始化 3.list的容量操作 4.list的访问操作 5.list的修改操作 6.list的其他操作 二、list与vector的对比 结束语 引言 本篇博客要介绍的是STL中的list。 求点赞收藏评论…...
Agent群舞，在亚马逊云科技搭建数字营销多代理(Multi-Agent)（下篇）
在本系列的上篇中，小李哥为大家介绍了如何在亚马逊云科技上给社交数字营销场景创建AI代理的方案，用于社交动态的生成和对文章进行推广曝光。在本篇中小李哥将继续本系列的介绍，为大家介绍如何创建主代理，将多个子代理挂载到主代理…...
DBeaver连接MySQL数据库
打开DBeaver，点击“新建数据库连接”选项。 点击“测试连接”，首次连接mysql会提示下载对应的JDBC驱动，点击下载即可。 填写服务器地址（这里是本地测试）、mysql的用户名（root）和密码ÿ…...
Leetcode40： 组合总和 II
题目描述： 给定一个候选人编号的集合 candidates 和一个目标数 target ，找出 candidates 中所有可以使数字和为 target 的组合。 candidates 中的每个数字在每个组合中只能使用 一次 。 注意：解集不能包含重复的组合。 代码思路ÿ…...
win32汇编环境,对话框程序中使用进度条控件
;运行效果 ;win32汇编环境,对话框程序中使用进度条控件 ;进度条控件主要涉及的是长度单位，每步步长，推进的时间。 ;比如你的长度是1000，步长是100，每秒走1次，则10秒走完全程 ;比如你的长度是1000，步长是10，每秒走1次，则100秒走完全程，但每格格子的长度与上面一样 ;以下…...
AIGC时代下的Vue组件开发深度探索
文章目录 一、AIGC时代对Vue组件开发的深远影响二、Vue组件开发基础与最佳实践三、AIGC技术在Vue组件开发中的具体应用四、结论与展望 随着人工智能技术的飞速发展，AIGC（人工智能生成内容）时代已经悄然来临。在这个时代背景下，软件…...
在Kubernets Cluster中部署LVM类型的StorageClass - 上
适用场景 看到B站技术部门的文章，是关于如何在k8s集群部署Elastic Search和Click House等IO密集型数据库应用的。 因为要充分利用NvME SSD盘的IOPS，所有数据库应用都直接调用本地SSD盘做为stateful application的 Persistent Volumes。为了可用动态的分…...
一次StarRocks分析的经历
起因 有人反馈说SR，在系统资源还有空闲的时候，被操作系统杀掉了。没有日志，怀疑是bug，如果要解决这个bug。据说在网上查到要升级。请我准备一下升级。 质疑 StarRocks是一款分析型数据库，2021年正式开源，…...
Django网站搭建流程
使用Django搭建网站是一个系统的过程，涉及从环境搭建到部署上线的多个步骤。以下是详细的流程： 1. 环境搭建 （1）安装Python Django是基于Python的Web框架，因此需要先安装Python。建议安装Python 3.8及以上版本。 下载地…...
Vue-day2
7.Vue的生命周期 mounted函数：在页面加载完毕时，发送异步请求，加载数据，渲染页面 createApp({date(){},methods:{},mounted:function(){console.log(Vue挂载完毕，发送请求获取数据)} }).mount(#{app}) 8.ajax函数库…...
Day44：列表元素的修改
在 Python 中，列表是一种可变的数据结构，意味着我们可以对列表中的元素进行修改。修改列表元素的方式有很多种，包括通过索引修改、切片修改、使用 append() 和 extend() 添加新元素、以及删除元素等。今天，我们将学习如何在列表中…...
在 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…...
day7手机拍照装备
对焦对不上：1、光太暗；2、离太近；3、颜色太单一没有区分点 滤镜可以后期P 渐变灰滤镜：均衡色彩，暗的地方亮一些，亮的地方暗一些 中灰滤镜：减少光差 手机支架：最基本70cm即可 手…...
HarmonyOS：创建应用静态快捷方式
一、前言 静态快捷方式是一种在系统中创建的可以快速访问应用程序或特定功能的链接。它通常可以在长按应用图标，以图标和相应的文字出现在应用图标的上方，用户可以迅速启动对应应用程序的组件。使用快捷方式，可以提高效率，节省了查…...
[SUCTF 2018]MultiSQL1
进去题目页面如下 发现可能注入点只有登录和注册,那么我们先注册一个用户，发现跳转到了/user/user.php， 查看用户信息,发现有传参/user/user.php?id1 用?id1 and 11,和?id1 and 12,判断为数字型注入 原本以为是简单的数字型注入，看到大…...
kafka-部署安装
一. 简述： Kafka 是一个分布式流处理平台，常用于构建实时数据管道和流应用。 二. 安装部署： 1. 依赖： a). Java：Kafka 需要 Java 8 或更高版本。 b). zookeeper： #tar fxvz zookeeper-3.7.0.tar.gz #…...
VUE3 使用路由守卫函数实现类型服务器端中间件效果
vue3中的router组件，有一个函数 router.beforeEach，可以实现请求中间件效果 使用方法如下： 前提已经在Vue3 项目中引入router组件，在router.js文件中加入router.beforeEach //路由守卫函数，类似于中间件session效果…...
|Python新手小白中级教程|第二十九章：面向对象编程（Python类的拓展延伸与10道实操题目）（5）
文章目录 前言1.类变量与实例变量2.静态方法和类方法1.静态方法2.类方法 3.实操使用1. 创建一个名为Person的类，包含属性name和age，并且有一个方法introduce()用于介绍自己的名字和年龄。2. 创建一个名为Circle的类，包含属性radius和color&am…...
项目概述与规划 (I)
项目概述与规划 (I) JavaScript的学习已经接近尾声了，最后我们将通过一个项目来讲我们在JavaScript中学习到的所有都在这个项目中展现出来，这个项目的DEMO来自于Udemy中的课程，作者是Jonas Schmedtmann； 项目规划 项目步骤 用户…...
mysql学习笔记-数据库的设计规范
1、范式简介 在关系型数据库中，关于数据表设计的基本原则、规则就称为范式。 1.1键和相关属性的概念 超键:能唯一标识元组的属性集叫做超键。 候选键:如果超键不包括多余的属性，那么这个超键就是候选键 主键:用户可以从候选键中选择一个作为主键。 外…...
实现B-树
一、概述 1.历史 B树（B-Tree）结构是一种高效存储和查询数据的方法，它的历史可以追溯到1970年代早期。B树的发明人Rudolf Bayer和Edward M. McCreight分别发表了一篇论文介绍了B树。这篇论文是1972年发表于《ACM Transactions on Database S…...
论文笔记（六十三）Understanding Diffusion Models: A Unified Perspective（四）
Understanding Diffusion Models: A Unified Perspective（四） 文章概括学习扩散噪声参数（Learning Diffusion Noise Parameters）三种等效的解释（Three Equivalent Interpretations） 文章概括 引用…...
C# 中 default 使用详解
总目录 前言 在C#中，default 关键字用于表示类型默认值。它可以根据上下文推断出适用的类型，并返回该类型的默认值。随着C#版本的发展，default 的用法也变得更加丰富和灵活。本文将详细介绍 default 在不同场景下的使用方法及其最佳实践。 一…...
Day21-【软考】短文，计算机网络开篇，OSI七层模型有哪些协议？
文章目录 OSI七层模型有哪些？有哪些协议簇？TCP/IP协议簇中的TCP协议三次握手是怎样的？基于UDP的DHCP协议是什么情况？基于UDP的DNS协议是什么情况？ OSI七层模型有哪些？ 题目会考广播域 有哪些协议簇&#x…...
电力晶体管（GTR）全控性器件
电力晶体管（Giant Transistor，GTR）是一种全控性器件，以下是关于它的详细介绍：（模电普通晶体管三极管进行对比学习） 基本概念 GTR是一种耐高电压、大电流的双极结型晶体管（BJT&am…...
C语言------指针从入门到精通
第一部分: 前言: 本篇文章主要划分为两大部分: 第一部分适合零基础的同学,主要学习了解指针的概念，对指针大概有个概念。如果你已经有基础,即可跳过第一部分的内容。 第二部分主要是分解指针的实现逻辑,通过19个例子,再结合代码公式把不同类型的指针及指针的应用详细…...