Обсуждение: Parallel WAL Archival Options
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
On 8/6/23 02:43, Nikhil Shetty wrote:
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.
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?
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.
Born in Arizona, moved to Babylonia.
I am using PGbackrest and I am generating many WALs per second too, and I have no problems keeping up and I am backing themup 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 400WALs 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 butno luck > > Thanks, > Nikhil
Hi Ron,
Uploads to a remote server?
— Yes, to S3
— 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.
Hi Michael,
May I know what is wal generation rate pm for you? I may give this a try.
Thanks,
Nikhil
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
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:
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,NikhilI 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
Regards,
Michael Vitale
703-600-9343
Вложения
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
Вложения
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