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

Поиск
Список
Период
Сортировка

Parallel WAL Archival Options

От
Nikhil Shetty
Дата:
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

Re: Parallel WAL Archival Options

От
Ron
Дата:
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.

Re: Parallel WAL Archival Options

От
"Michaeldba@sqlexec.com"
Дата:
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




Re: Parallel WAL Archival Options

От
Nikhil Shetty
Дата:
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.

Re: Parallel WAL Archival Options

От
Nikhil Shetty
Дата:
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

Re: Parallel WAL Archival Options

От
MichaelDBA
Дата:
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 


Вложения

Re: Parallel WAL Archival Options

От
Stephen Frost
Дата:
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

Вложения

Re: Parallel WAL Archival Options

От
Nikhil Shetty
Дата:
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