Horton

RE: Logical replication timeout problem

Wei Wang (Fujitsu) <wangw.fnst@fujitsu.com>

View thread View on postgr.es
From: "wangw.fnst@fujitsu.com" <wangw.fnst@fujitsu.com>
To: Amit Kapila <amit.kapila16@gmail.com>, Fabrice Chapuis <fabrice636861@gmail.com>, Simon Riggs <simon.riggs@enterprisedb.com>, Petr Jelinek <petr.jelinek@enterprisedb.com>
Cc: "tanghy.fnst@fujitsu.com" <tanghy.fnst@fujitsu.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2022-01-26T03:37:28Z
Lists: pgsql-hackers

Attachments

On Thu, Jan 22, 2022 at 7:12 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> Now, one idea to solve this problem could be that whenever we skip
> sending any change we do try to update the plugin progress via
> OutputPluginUpdateProgress(for walsender, it will invoke
> WalSndUpdateProgress), and there it tries to process replies and send
> keep_alive if necessary as we do when we send some data via
> OutputPluginWrite(for walsender, it will invoke WalSndWriteData). I
> don't know whether it is a good idea to invoke such a mechanism for
> every change we skip to send or we should do it after we skip sending
> some threshold of continuous changes. I think later would be
> preferred. Also, we might want to introduce a new parameter
> send_keep_alive to this API so that there is flexibility to invoke
> this mechanism as we don't need to invoke it while we are actually
> sending data and before that, we just update the progress via this
> API.

I tried out the patch according to your advice.
I found if I invoke ProcessRepliesIfAny and WalSndKeepaliveIfNecessary in
function OutputPluginUpdateProgress, the running time of the newly added
function OutputPluginUpdateProgress invoked in pgoutput_change brings notable
overhead:
--11.34%--pgoutput_change
          |          
          |--8.94%--OutputPluginUpdateProgress
          |          |          
          |           --8.70%--WalSndUpdateProgress
          |                     |          
          |                     |--7.44%--ProcessRepliesIfAny

So I tried another way of sending keepalive message to the standby machine
based on the timeout without asking for a reply(see attachment), the running
time of the newly added function OutputPluginUpdateProgress invoked in
pgoutput_change also brings slight overhead:
--3.63%--pgoutput_change
          |          
          |--1.40%--get_rel_sync_entry
          |          |          
          |           --1.14%--hash_search
          |          
           --1.08%--OutputPluginUpdateProgress
                     |          
                      --0.85%--WalSndUpdateProgress

Based on above, I think the second idea that sending some threshold of
continuous changes might be better, I will do some research about this
approach.

Regards,
Wang wei

Commits

  1. Fix the logical replication timeout during large DDLs.

  2. Fix the logical replication timeout during large transactions.

  3. Rethink the delay-checkpoint-end mechanism in the back-branches.

  4. Revert "Logical decoding of sequences"

  5. Skip empty transactions for logical replication.

  6. Allow specifying column lists for logical replication

  7. Add decoding of sequences to built-in replication

  8. Fix ABI break introduced by commit 4daa140a2f.

  9. Lag tracking for logical replication