Обсуждение: Parallel WAL Archival Options

Hi Team,

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?

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

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 <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

Regards,

Michael Vitale

Michaeldba@sqlexec.com

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