怎样仿制网站,短视频分享网站开发,可以制作视频的软件有哪些,手机浏览器主页网站推荐逻辑复制parallel并发参数测试
一、测试结果、测试环境描述
1.1、测试结果 cpu表中有1000万条数据#xff0c;大小为1652MB,当更新的数据量多于10万条的时候有明显变化#xff0c;多余30万条的时候相差2倍。 更新的数据量较多时#xff0c;逻辑复制使用并发参数相比于使用…逻辑复制parallel并发参数测试
一、测试结果、测试环境描述
1.1、测试结果 cpu表中有1000万条数据大小为1652MB,当更新的数据量多于10万条的时候有明显变化多余30万条的时候相差2倍。 更新的数据量较多时逻辑复制使用并发参数相比于使用默认参数性能更高。 更新的数据量较少时逻辑复制使用并发参数相比于使用默认参数性能相差不大。
更新记录条数streaming影响到订阅端耗时执行SQL语句耗时1000parallel71ms12.077 ms1000-45 ms15.496 ms50000parallel1199 ms357.201 ms50000-1396 ms586.545 ms100000parallel4637 ms3227.055 ms100000-6153 ms4591.671 ms300000parallel6815 ms5567.591 ms300000-11850 ms6337.377 ms1000000parallel24214 ms12734.563 ms1000000-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 GBOS 系统版本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 host192.168.6.108 port9432 dbnametest userfbase passwordfbase
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:0008, 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:0008, 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_idhost_ || 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.98314908,,,,,,,,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 10 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.91341408,,,,,,,,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.95060908,,,,,,,,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.98314908,,,,,,,,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,,03.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.38422808,,,,,,START_REPLICATION SLOT fd_logical LOGICAL 1/910B8DF8 (proto_version 4, origin any, publication_names pub_cpu),,ProcessStandbyReplyMessage, walsender.c:2101,sub_cpu,walsender,,03.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.86636908,,,,,,,,ProcessStandbyReplyMessage, walsender.c:2101,sub_cpu,walsender,,03.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.44619708,,,,,,START_REPLICATION SLOT fd_logical LOGICAL 1/697B9D18 (proto_version 4, origin any, publication_names pub_cpu),,ProcessStandbyReplyMessage, walsender.c:2101,sub_cpu,walsender,,03.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.65296308,,,,,,,,ProcessStandbyReplyMessage, walsender.c:2101,sub_cpu,walsender,,03.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.13766908,,,,,,START_REPLICATION SLOT fd_logical LOGICAL 1/5EB27398 (proto_version 4, origin any, publication_names pub_cpu),,ProcessStandbyReplyMessage, walsender.c:2101,sub_cpu,walsender,,03.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.38678108,,,,,,,,ProcessStandbyReplyMessage, walsender.c:2101,sub_cpu,walsender,,03.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.50283608,,,,,,START_REPLICATION SLOT fd_logical LOGICAL 1/697B9D18 (proto_version 4, origin any, publication_names pub_cpu),,ProcessStandbyReplyMessage, walsender.c:2101,sub_cpu,walsender,,03.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.30682708,,,,,,,,ProcessStandbyReplyMessage, walsender.c:2101,sub_cpu,walsender,,03.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.24150908,,,,,,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 conninfoPUBLICATION publication_name [, ...][ WITH ( subscription_parameter [ value] [, ... ] ) ]URL: https://www.postgresql.org/docs/16/sql-createsubscription.html以下是 CREATE SUBSCRIPTION 命令中 WITH 子句支持的参数的简写表格
streaming枚举类型
指定是否为此订阅启用正在进行的事务的流式传输。默认值是 off意味着所有事务都在发布者上完全解码然后才作为整体发送给订阅者。
如果设置为 on则传入的更改将写入临时文件并且只有在发布者上提交事务并由订阅者接收后才应用。
如果设置为 parallel则传入的更改将直接通过一个并行应用工作线程应用如果有可用的话。如果没有空闲的并行应用工作线程来处理流式事务那么更改将写入临时文件并在事务提交后应用。请注意如果并行应用工作线程中发生错误远程事务的完成LSN可能不会记录在服务器日志中。
参数名类型默认值connectbooleantruecreate_slotbooleantrueenabledbooleantrueslot_namestring订阅名binarybooleanfalsecopy_databooleantruestreamingenumoffsynchronous_commitenumofftwo_phasebooleanfalsedisable_on_errorbooleanfalsepassword_requiredbooleantruerun_as_ownerbooleanfalseoriginstringany
修改传输方式 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 | host192.168.6.108 port8432 dbnametest userfbase passwordfbase
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.32797708
last_msg_receipt_time | 2025-01-23 15:13:46.32815808
latest_end_lsn | 2/D2917D80
latest_end_time | 2025-01-23 15:13:46.32797708
事件
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)2797708 **事件**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)
