Horton

Re: speed up a logical replica setup

Amit Kapila <amit.kapila16@gmail.com>

View thread
From: Amit Kapila <amit.kapila16@gmail.com>
To: Shubham Khanna <khannashubham1197@gmail.com>
Cc: Euler Taveira <euler@eulerto.com>, Michael Paquier <michael@paquier.xyz>, Peter Eisentraut <peter@eisentraut.org>, pgsql-hackers@lists.postgresql.org, Andres Freund <andres@anarazel.de>, Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Date: 2024-01-18T09:43:56Z
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, Jan 16, 2024 at 11:58 AM Shubham Khanna
<khannashubham1197@gmail.com> wrote:
>
> On Thu, Dec 21, 2023 at 11:47 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
> >
> >
> > 4. Can we see some numbers with various sizes of databases (cluster)
> > to see how it impacts the time for small to large-size databases as
> > compared to the traditional method? This might help us with giving
> > users advice on when to use this tool. We can do this bit later as
> > well when the patch is closer to being ready for commit.
>
> I have done the Performance testing and attached the results to
> compare the 'Execution Time' between 'logical replication' and
> 'pg_subscriber' for 100MB, 1GB and 5GB data:
>                             | 100MB | 1GB      | 5GB
> Logical rep (2 w) | 1.815s  | 14.895s | 75.541s
> Logical rep (4 w) | 1.194s  | 9.484s   | 46.938s
> Logical rep (8 w) | 0.828s  | 6.422s   | 31.704s
> Logical rep(10 w)| 0.646s  | 3.843s   | 18.425s
> pg_subscriber     | 3.977s  | 9.988s   | 12.665s
>
> Here, 'w' stands for 'workers'. I have included the tests to see the
> test result variations with different values for
> 'max_sync_workers_per_subscription' ranging from 2 to 10. I ran the
> tests for different data records; for 100MB I put  3,00,000 Records,
> for 1GB I put 30,00,000 Records and for 5GB I put 1,50,00,000 Records.
> It is observed that 'pg_subscriber' is better when the table size is
> more.
>

Thanks for the tests. IIUC, it shows for smaller data this tool can
take more time. Can we do perf to see if there is something we can do
about reducing the overhead?

> Next I plan to run these tests for 10GB and 20GB to see if this trend
> continues or not.
>

Okay, that makes sense.

With Regards,
Amit Kapila.