RE: logical replication empty transactions

On Thursday, March 24, 2022 11:19 AM Hou, Zhijie/侯 志杰 <houzj(dot)fnst(at)fujitsu(dot)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