pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedParallel WAL Archival Options
8+ messages / 5 participants
[nested] [flat]
* Parallel WAL Archival Options
@ 2023-08-06 07:43 Nikhil Shetty <nikhil.dba04@gmail.com>
2023-08-06 08:20 ` Re: Parallel WAL Archival Options Ron <ronljohnsonjr@gmail.com>
2023-08-06 08:26 ` Re: Parallel WAL Archival Options Michaeldba@sqlexec.com <Michaeldba@sqlexec.com>
2023-08-07 14:42 ` Re: Parallel WAL Archival Options Stephen Frost <sfrost@snowman.net>
0 siblings, 3 replies; 8+ messages in thread
From: Nikhil Shetty @ 2023-08-06 07:43 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi Team,
I would like to know which backup&restore tools will be better for
scenarios where the database is generating around 400 WALs per minute.
We are using wal-g but it is not able to keep pace with the wal generation.
We increased the upload streams to 256 but no luck
Thanks,
Nikhil
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Parallel WAL Archival Options
2023-08-06 07:43 Parallel WAL Archival Options Nikhil Shetty <nikhil.dba04@gmail.com>
@ 2023-08-06 08:20 ` Ron <ronljohnsonjr@gmail.com>
2023-08-06 10:54 ` Re: Parallel WAL Archival Options Nikhil Shetty <nikhil.dba04@gmail.com>
2 siblings, 1 reply; 8+ messages in thread
From: Ron @ 2023-08-06 08:20 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 8/6/23 02:43, Nikhil Shetty wrote:
> Hi Team,
>
> I would like to know which backup&restore tools will be better for
> scenarios where the database is generating around 400 WALs per minute.
If my math is correct, 400x 16MB WAL files per minute is 400*(16*2^20)/60*8
/ 10^6 = 895 MBits *per second*. Plus overhead.
That's about *1Gbit/second*. Definitely nothing to sneeze at.
> We are using wal-g but it is not able to keep pace with the wal
> generation. We increased the upload streams to 256 but no luck
Uploads to a *remote* server?
Does wal-g compress files before sending them across the wire? By how
much? Are you CPU or IO bound by having to compress that much data?
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Parallel WAL Archival Options
2023-08-06 07:43 Parallel WAL Archival Options Nikhil Shetty <nikhil.dba04@gmail.com>
2023-08-06 08:20 ` Re: Parallel WAL Archival Options Ron <ronljohnsonjr@gmail.com>
@ 2023-08-06 10:54 ` Nikhil Shetty <nikhil.dba04@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Nikhil Shetty @ 2023-08-06 10:54 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Hi Ron,
Uploads to a *remote* server?
— Yes, to S3
Does wal-g compress files before sending them across the wire? By how
much? Are you CPU or IO bound by having to compress that much data?
— Yes, we use default compression i.e lz4. There is no pressure on
resources but the number of wal files that are processed in parallel, if we
increase the streams it uses up a lot of memory.
Thanks,
Nikhil
On Sun, 6 Aug 2023 at 13:51, Ron <ronljohnsonjr@gmail.com> wrote:
> On 8/6/23 02:43, Nikhil Shetty wrote:
>
> Hi Team,
>
> I would like to know which backup&restore tools will be better for
> scenarios where the database is generating around 400 WALs per minute.
>
>
> If my math is correct, 400x 16MB WAL files per minute is
> 400*(16*2^20)/60*8 / 10^6 = 895 MBits *per second*. Plus overhead.
>
> That's about *1Gbit/second*. Definitely nothing to sneeze at.
>
> We are using wal-g but it is not able to keep pace with the wal
> generation. We increased the upload streams to 256 but no luck
>
>
> Uploads to a *remote* server?
>
> Does wal-g compress files before sending them across the wire? By how
> much? Are you CPU or IO bound by having to compress that much data?
>
>
> --
> Born in Arizona, moved to Babylonia.
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Parallel WAL Archival Options
2023-08-06 07:43 Parallel WAL Archival Options Nikhil Shetty <nikhil.dba04@gmail.com>
@ 2023-08-06 08:26 ` Michaeldba@sqlexec.com <Michaeldba@sqlexec.com>
2023-08-06 10:56 ` Re: Parallel WAL Archival Options Nikhil Shetty <nikhil.dba04@gmail.com>
2 siblings, 1 reply; 8+ messages in thread
From: Michaeldba@sqlexec.com @ 2023-08-06 08:26 UTC (permalink / raw)
To: Nikhil Shetty <nikhil.dba04@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
I am using PGbackrest and I am generating many WALs per second too, and I have no problems keeping up and I am backing them up remotely.
Sent from my iPad
> On Aug 6, 2023, at 3:43 AM, Nikhil Shetty <nikhil.dba04@gmail.com> wrote:
>
> Hi Team,
>
> I would like to know which backup&restore tools will be better for scenarios where the database is generating around 400 WALs per minute.
>
> We are using wal-g but it is not able to keep pace with the wal generation. We increased the upload streams to 256 but no luck
>
> Thanks,
> Nikhil
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Parallel WAL Archival Options
2023-08-06 07:43 Parallel WAL Archival Options Nikhil Shetty <nikhil.dba04@gmail.com>
2023-08-06 08:26 ` Re: Parallel WAL Archival Options Michaeldba@sqlexec.com <Michaeldba@sqlexec.com>
@ 2023-08-06 10:56 ` Nikhil Shetty <nikhil.dba04@gmail.com>
2023-08-06 12:55 ` Re: Parallel WAL Archival Options MichaelDBA <MichaelDBA@sqlexec.com>
0 siblings, 1 reply; 8+ messages in thread
From: Nikhil Shetty @ 2023-08-06 10:56 UTC (permalink / raw)
To: Michaeldba@sqlexec.com <MichaelDBA@sqlexec.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi Michael,
May I know what is wal generation rate pm for you? I may give this a try.
Thanks,
Nikhil
On Sun, 6 Aug 2023 at 13:56, Michaeldba@sqlexec.com <Michaeldba@sqlexec.com>
wrote:
> I am using PGbackrest and I am generating many WALs per second too, and I
> have no problems keeping up and I am backing them up remotely.
>
> Sent from my iPad
>
> > On Aug 6, 2023, at 3:43 AM, Nikhil Shetty <nikhil.dba04@gmail.com>
> wrote:
> >
> > Hi Team,
> >
> > I would like to know which backup&restore tools will be better for
> scenarios where the database is generating around 400 WALs per minute.
> >
> > We are using wal-g but it is not able to keep pace with the wal
> generation. We increased the upload streams to 256 but no luck
> >
> > Thanks,
> > Nikhil
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Parallel WAL Archival Options
2023-08-06 07:43 Parallel WAL Archival Options Nikhil Shetty <nikhil.dba04@gmail.com>
2023-08-06 08:26 ` Re: Parallel WAL Archival Options Michaeldba@sqlexec.com <Michaeldba@sqlexec.com>
2023-08-06 10:56 ` Re: Parallel WAL Archival Options Nikhil Shetty <nikhil.dba04@gmail.com>
@ 2023-08-06 12:55 ` MichaelDBA <MichaelDBA@sqlexec.com>
0 siblings, 0 replies; 8+ messages in thread
From: MichaelDBA @ 2023-08-06 12:55 UTC (permalink / raw)
To: Nikhil Shetty <nikhil.dba04@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
I do about 2-10 WALs per second. I have a remote pgbackrest repo, and I
do faster WAL archiving using the archive-async=y parameter, which
enables WALs to be archived asynchronously, really speeding it up. I
also increase the archive-push-queue-max=100GB.
For fetching the WALs, like when a standby gets behind and it needs to
read WALs to catch up to get back into SR mode, I increase the size of
the pgBackRest queue on the standby ( archive_get-queue-max = 100GB).
Both queue max parms are only enabled when archive-async is turned on.
Regards,
Michael Vitale
Nikhil Shetty wrote on 8/6/2023 6:56 AM:
> Hi Michael,
>
> May I know what is wal generation rate pm for you? I may give this a try.
>
>
> Thanks,
> Nikhil
>
> On Sun, 6 Aug 2023 at 13:56, Michaeldba@sqlexec.com
> <mailto:Michaeldba@sqlexec.com> <Michaeldba@sqlexec.com
> <mailto:Michaeldba@sqlexec.com>> wrote:
>
> I am using PGbackrest and I am generating many WALs per second
> too, and I have no problems keeping up and I am backing them up
> remotely.
>
> Sent from my iPad
>
> > On Aug 6, 2023, at 3:43 AM, Nikhil Shetty
> <nikhil.dba04@gmail.com <mailto:nikhil.dba04@gmail.com>> wrote:
> >
> > Hi Team,
> >
> > I would like to know which backup&restore tools will be better
> for scenarios where the database is generating around 400 WALs per
> minute.
> >
> > We are using wal-g but it is not able to keep pace with the wal
> generation. We increased the upload streams to 256 but no luck
> >
> > Thanks,
> > Nikhil
>
Regards,
Michael Vitale
Michaeldba@sqlexec.com <mailto:michaelvitale@sqlexec.com>
703-600-9343
Attachments:
[image/jpeg] pgadvanced3.jpg (20.6K, ../../12e141e5-8d87-349b-7c9a-88dd7974eee9@sqlexec.com/3-pgadvanced3.jpg)
download | view image
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Parallel WAL Archival Options
2023-08-06 07:43 Parallel WAL Archival Options Nikhil Shetty <nikhil.dba04@gmail.com>
@ 2023-08-07 14:42 ` Stephen Frost <sfrost@snowman.net>
2023-08-08 14:43 ` Re: Parallel WAL Archival Options Nikhil Shetty <nikhil.dba04@gmail.com>
2 siblings, 1 reply; 8+ messages in thread
From: Stephen Frost @ 2023-08-07 14:42 UTC (permalink / raw)
To: Nikhil Shetty <nikhil.dba04@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Greetings,
* Nikhil Shetty (nikhil.dba04@gmail.com) wrote:
> I would like to know which backup&restore tools will be better for
> scenarios where the database is generating around 400 WALs per minute.
>
> We are using wal-g but it is not able to keep pace with the wal generation.
> We increased the upload streams to 256 but no luck
pgbackrest also does parallel WAL archiving and is able to handle
large volumes of WAL and we'd love to hear how well it works for you.
Feel free to reach out via a github issue if you have trouble making it
able to keep up.
Do be sure to also test that you're able to actually perform restores in
whatever timeframe you expect to be able to with that kind of WAL
generation.
Thanks,
Stephen
Attachments:
[application/pgp-signature] signature.asc (832B, ../../ZNEC18tW%2FKAyYGlG@tamriel.snowman.net/2-signature.asc)
download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Parallel WAL Archival Options
2023-08-06 07:43 Parallel WAL Archival Options Nikhil Shetty <nikhil.dba04@gmail.com>
2023-08-07 14:42 ` Re: Parallel WAL Archival Options Stephen Frost <sfrost@snowman.net>
@ 2023-08-08 14:43 ` Nikhil Shetty <nikhil.dba04@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Nikhil Shetty @ 2023-08-08 14:43 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Thanks Stephen , I'll check it out.
On Mon, 7 Aug 2023 at 20:12, Stephen Frost <sfrost@snowman.net> wrote:
> Greetings,
>
> * Nikhil Shetty (nikhil.dba04@gmail.com) wrote:
> > I would like to know which backup&restore tools will be better for
> > scenarios where the database is generating around 400 WALs per minute.
> >
> > We are using wal-g but it is not able to keep pace with the wal
> generation.
> > We increased the upload streams to 256 but no luck
>
> pgbackrest also does parallel WAL archiving and is able to handle
> large volumes of WAL and we'd love to hear how well it works for you.
>
> Feel free to reach out via a github issue if you have trouble making it
> able to keep up.
>
> Do be sure to also test that you're able to actually perform restores in
> whatever timeframe you expect to be able to with that kind of WAL
> generation.
>
> Thanks,
>
> Stephen
>
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2023-08-08 14:43 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-08-06 07:43 Parallel WAL Archival Options Nikhil Shetty <nikhil.dba04@gmail.com>
2023-08-06 08:20 ` Ron <ronljohnsonjr@gmail.com>
2023-08-06 10:54 ` Nikhil Shetty <nikhil.dba04@gmail.com>
2023-08-06 08:26 ` Michaeldba@sqlexec.com <Michaeldba@sqlexec.com>
2023-08-06 10:56 ` Nikhil Shetty <nikhil.dba04@gmail.com>
2023-08-06 12:55 ` MichaelDBA <MichaelDBA@sqlexec.com>
2023-08-07 14:42 ` Stephen Frost <sfrost@snowman.net>
2023-08-08 14:43 ` Nikhil Shetty <nikhil.dba04@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox