On Thursday, March 24, 2022 11:19 AM Hou, Zhijie/侯 志杰 <houzj.f...@fujitsu.com> wrote: > > Attach the new version patch which include the following changes: > > - Fix a typo > - Change the requestreply flag of the newly added WalSndKeepalive to false, > because the subscriber can judge whether it's necessary to post a reply > based > on the received LSN. > - Add a testcase to make sure there is no data in subscriber side when the > transaction is skipped. > - Change the name of flag skipped_empty_xact to skipped_xact which seems > more > understandable. > - Merge Amit's suggested changes. >
Hi, This patch skips sending BEGIN/COMMIT messages for empty transactions and saves network bandwidth. So I tried to do a test to see how does it affect bandwidth. This test refers to the previous test by Peter[1]. I temporarily modified the code in worker.c to log the length of the data received by the subscriber (after calling walrcv_receive()). At the conclusion of the test run, the logs are processed to extract the numbers. [1] https://www.postgresql.org/message-id/CAHut%2BPuyqcDJO0X2BxY%2B9ycF%2Bew3x77FiCbTJQGnLDbNmMASZQ%40mail.gmail.com The number of transactions is fixed (1000), and I tested different mixes of empty and not-empty transactions sent - 0%, 25%, 50%, 100%. The patch will send keepalive message when skipping empty transaction in synchronous replication mode, so I tested both synchronous replication and asynchronous replication. The results are as follows, and attach the bar chart. Sync replication - size of sending data -------------------------------------------------------------------- 0% 25% 50% 75% 100% HEAD 335211 281655 223661 170271 115108 patched 335217 256617 173878 98095 18108 Async replication - size of sending data -------------------------------------------------------------------- 0% 25% 50% 75% 100% HEAD 339379 285835 236343 184227 115000 patched 335077 260953 180022 113333 18126 The details of the test is also attached. Summary of result: In both synchronous replication mode and asynchronous replication mode, as more empty transactions, the improvement is more obvious. Even if when there is no empty transaction, I can't see any overhead. Regards, Shi yu
-- create table CREATE TABLE test_tab (a int primary key, b text, c timestamptz DEFAULT now(), d bigint DEFAULT 999); CREATE TABLE test_tab_nopub (a int primary key, b text, c timestamptz DEFAULT now(), d bigint DEFAULT 999); test_empty_not_published.sql: BEGIN; INSERT INTO test_tab_nopub VALUES(1, 'foo'); UPDATE test_tab_nopub SET b = 'bar' WHERE a = 1; DELETE FROM test_tab_nopub WHERE a = 1; COMMIT; test_empty_published.sql: BEGIN; INSERT INTO test_tab VALUES(1, 'foo'); UPDATE test_tab SET b = 'bar' WHERE a = 1; DELETE FROM test_tab WHERE a = 1; COMMIT; -- create publication create publication pub for table test_tab; -- create subscription CREATE SUBSCRIPTION sub CONNECTION 'host=localhost port=5432 dbname=postgres' PUBLICATION pub;" -- empty transaction: 0% pgbench -n -p 5432 -s 100 -t 1000 -c 1 -f test_empty_published.sql postgres -- empty transaction: 25% pgbench -n -p 5432 -s 100 -t 1000 -c 1 -f test_empty_not_published.sql@5 -f test_empty_published.sql@15 postgres -- empty transaction: 50% pgbench -n -p 5432 -s 100 -t 1000 -c 1 -f test_empty_not_published.sql@10 -f test_empty_published.sql@10 postgres -- empty transaction: 75% pgbench -n -p 5432 -s 100 -t 1000 -c 1 -f test_empty_not_published.sql@15 -f test_empty_published.sql@5 postgres -- empty transaction: 100% pgbench -n -p 5432 -s 100 -t 1000 -c 1 -f test_empty_not_published.sql postgres
