Horton

Re: 040_pg_createsubscriber.pl is slow and unstable (was Re: speed up a logical replica setup)

Amit Kapila <amit.kapila16@gmail.com>

View thread
From: Amit Kapila <amit.kapila16@gmail.com>
To: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Robert Haas <robertmhaas@gmail.com>, Peter Eisentraut <peter@eisentraut.org>, Noah Misch <noah@leadboat.com>, Alexander Lakhin <exclusion@gmail.com>, Euler Taveira <euler@eulerto.com>, Shlok Kyal <shlok.kyal.oss@gmail.com>, Tomas Vondra <tomas.vondra@enterprisedb.com>, "kuroda.hayato@fujitsu.com" <kuroda.hayato@fujitsu.com>, Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>, "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>, Michael Paquier <michael@paquier.xyz>, Andres Freund <andres@anarazel.de>, Fabrízio de Royes Mello <fabriziomello@gmail.com>, vignesh C <vignesh21@gmail.com>
Date: 2024-07-30T06:22:15Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. pg_createsubscriber: Remove obsolete comment

  2. pg_createsubscriber: Fix an unpredictable recovery wait time.

  3. Fix unstable test in 040_pg_createsubscriber.

  4. Fix the testcase introduced in commit 81d20fbf7a.

  5. Further weaken new pg_createsubscriber test on Windows.

  6. Temporarily(?) weaken new pg_createsubscriber test on Windows.

  7. Make pg_createsubscriber warn if publisher has two-phase commit enabled.

  8. Make pg_createsubscriber more wary about quoting connection parameters.

  9. pg_createsubscriber: Remove failover replication slots on subscriber

  10. pg_createsubscriber: Remove replication slot check on primary

  11. pg_createsubscriber: Only --recovery-timeout controls the end of recovery process

  12. pg_createsubscriber: creates a new logical replica from a standby server

  13. Add some const decorations

  14. Add option force_initdb to PostgreSQL::Test::Cluster:init()

  15. Remove MSVC scripts

On Tue, Jul 30, 2024 at 11:28 AM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
>
> On Tue, Jul 30, 2024 at 9:25 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
> >
> > On Tue, Jul 30, 2024 at 1:48 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > >
> > > Robert Haas <robertmhaas@gmail.com> writes:
> > > > On Sun, Jun 30, 2024 at 2:40 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > > >> ... However, I added a new open item about how the
> > > >> 040_pg_createsubscriber.pl test is slow and still unstable.
> > >
> > > > But that said, I see no commits in the commit history which purport to
> > > > improve performance, so I guess the performance is probably still not
> > > > what you want, though I am not clear on the details.
> > >
> > > My concern is described at [1]:
> > >
> > > >> I have a different but possibly-related complaint: why is
> > > >> 040_pg_createsubscriber.pl so miserably slow?  On my machine it
> > > >> runs for a bit over 19 seconds, which seems completely out of line
> > > >> (for comparison, 010_pg_basebackup.pl takes 6 seconds, and the
> > > >> other test scripts in this directory take much less).  It looks
> > > >> like most of the blame falls on this step:
> > > >>
> > > >> [12:47:22.292](14.534s) ok 28 - run pg_createsubscriber on node S
> > > >>
> > > >> AFAICS the amount of data being replicated is completely trivial,
> > > >> so that it doesn't make any sense for this to take so long --- and
> > > >> if it does, that suggests that this tool will be impossibly slow
> > > >> for production use.  But I suspect there is a logic flaw causing
> > > >> this.  Speculating wildly, perhaps that is related to the failure
> > > >> Alexander spotted?
> > >
> > > The followup discussion in that thread made it sound like there's
> > > some fairly fundamental deficiency in how wait_for_end_recovery()
> > > detects end-of-recovery.  I'm not too conversant with the details
> > > though, and it's possible that pg_createsubscriber is just falling
> > > foul of a pre-existing infelicity.
> > >
> > > If the problem can be correctly described as "pg_createsubscriber
> > > takes 10 seconds or so to detect end-of-stream",
> > >
> >
> > The problem can be defined as: "pg_createsubscriber waits for an
> > additional (new) WAL record to be generated on primary before it
> > considers the standby is ready for becoming a subscriber". Now, on
> > busy systems, this shouldn't be a problem but for idle systems, the
> > time to detect end-of-stream can't be easily defined.
>
> AFAIU, the server will emit running transactions WAL record at least
> 15 seconds.
>

AFAICU, this is not true because the code suggests that the running
xacts record is inserted by bgwriter only when enough time has passed
and interesting records have been inserted since the last snapshot.
Please take a look at the following comment and code in bgwriter.c
"Only log if enough time has passed and interesting records have been
inserted since the last snapshot...".

-- 
With Regards,
Amit Kapila.