public inbox for [email protected]
help / color / mirror / Atom feedRe: wal segment size
5+ messages / 5 participants
[nested] [flat]
* Re: wal segment size
@ 2025-12-17 15:25 Laurenz Albe <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2025-12-17 15:25 UTC (permalink / raw)
To: Colin 't Hart <[email protected]>; PostgreSQL General <[email protected]>
On Wed, 2025-12-17 at 16:13 +0100, Colin 't Hart wrote:
> I see very little advice on tuning WAL segment size.
>
> One of my clients has a few datawarehouses at around 8 - 16 TB
>
> On one of the nodes there are approx 15000 WAL segments of 16MB each, totalling
> approx 230GB. The archiver is archiving approx one per second, so approx 4 hours to clear.
>
> Would we gain anything by bumping the WAL segment size?
Very likely yes, if the problem is the overhead of starting the archive_command.
Another thing that can slow down archiving is if you compress these segments
too aggressively.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: wal segment size
@ 2025-12-17 16:10 Colin 't Hart <[email protected]>
parent: Laurenz Albe <[email protected]>
0 siblings, 3 replies; 5+ messages in thread
From: Colin 't Hart @ 2025-12-17 16:10 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: PostgreSQL General <[email protected]>
Thanks Laurenz, that confirms what I was assuming. Archiving is via
pgbackrest to a backup server, over SSH. Approx 750ms to archive each
segment is crazy -- I'll check compression parameters too.
Any reason not to bump it up to 1GB? Or is that overkill?
/Colin
On Wed, 17 Dec 2025 at 16:25, Laurenz Albe <[email protected]> wrote:
> On Wed, 2025-12-17 at 16:13 +0100, Colin 't Hart wrote:
> > I see very little advice on tuning WAL segment size.
> >
> > One of my clients has a few datawarehouses at around 8 - 16 TB
> >
> > On one of the nodes there are approx 15000 WAL segments of 16MB each,
> totalling
> > approx 230GB. The archiver is archiving approx one per second, so approx
> 4 hours to clear.
> >
> > Would we gain anything by bumping the WAL segment size?
>
> Very likely yes, if the problem is the overhead of starting the
> archive_command.
>
> Another thing that can slow down archiving is if you compress these
> segments
> too aggressively.
>
> Yours,
> Laurenz Albe
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: wal segment size
@ 2025-12-17 16:31 Adrian Klaver <[email protected]>
parent: Colin 't Hart <[email protected]>
2 siblings, 0 replies; 5+ messages in thread
From: Adrian Klaver @ 2025-12-17 16:31 UTC (permalink / raw)
To: Colin 't Hart <[email protected]>; Laurenz Albe <[email protected]>; +Cc: PostgreSQL General <[email protected]>
On 12/17/25 08:10, Colin 't Hart wrote:
> Thanks Laurenz, that confirms what I was assuming. Archiving is via
> pgbackrest to a backup server, over SSH. Approx 750ms to archive each
> segment is crazy -- I'll check compression parameters too.
How much of that time is network travel?
What are the configuration settings for the archiving portion of pgBackRest?
>
> Any reason not to bump it up to 1GB? Or is that overkill?
>
> /Colin
>
> On Wed, 17 Dec 2025 at 16:25, Laurenz Albe <[email protected]
> <mailto:[email protected]>> wrote:
>
> On Wed, 2025-12-17 at 16:13 +0100, Colin 't Hart wrote:
> > I see very little advice on tuning WAL segment size.
> >
> > One of my clients has a few datawarehouses at around 8 - 16 TB
> >
> > On one of the nodes there are approx 15000 WAL segments of 16MB
> each, totalling
> > approx 230GB. The archiver is archiving approx one per second, so
> approx 4 hours to clear.
> >
> > Would we gain anything by bumping the WAL segment size?
>
> Very likely yes, if the problem is the overhead of starting the
> archive_command.
>
> Another thing that can slow down archiving is if you compress these
> segments
> too aggressively.
>
> Yours,
> Laurenz Albe
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: wal segment size
@ 2025-12-17 17:21 Greg Sabino Mullane <[email protected]>
parent: Colin 't Hart <[email protected]>
2 siblings, 0 replies; 5+ messages in thread
From: Greg Sabino Mullane @ 2025-12-17 17:21 UTC (permalink / raw)
To: Colin 't Hart <[email protected]>; +Cc: Laurenz Albe <[email protected]>; PostgreSQL General <[email protected]>
On Wed, Dec 17, 2025 at 11:10 AM Colin 't Hart <[email protected]> wrote:
> Thanks Laurenz, that confirms what I was assuming. Archiving is via
> pgbackrest to a backup server, over SSH. Approx 750ms to archive each
> segment is crazy -- I'll check compression parameters too.
>
Switch to archive-async = on. When doing that, the typical time drops to
10ms or less. Also use a compress-type of lz4 or zst, which perform way
better than the default gz. If you are encrypting, that's a bottleneck you
just have to deal with, no shortcuts there. :)
tl;dr try other things before messing with the WAL size. The current size
can work very well even on very large and very, very busy systems.
Cheers,
Greg
--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: wal segment size
@ 2025-12-17 17:23 Ron Johnson <[email protected]>
parent: Colin 't Hart <[email protected]>
2 siblings, 0 replies; 5+ messages in thread
From: Ron Johnson @ 2025-12-17 17:23 UTC (permalink / raw)
To: pgsql-general
Adding this to ~/.ssh/config will almost totally eliminate the cost of ssh
authentication:
Host *
ControlMaster auto
#ControlPath /run/user/%i/%L_%r_at_%n:%p
ControlPath ~/.ssh/%L_%r_at_%n:%p
ControlPersist 5m
Pointing ControlPath to /run/user/%i is even faster, but it doesn't always
exist if you sudo into the postgres service account.
On Wed, Dec 17, 2025 at 11:10 AM Colin 't Hart <[email protected]> wrote:
> Thanks Laurenz, that confirms what I was assuming. Archiving is via
> pgbackrest to a backup server, over SSH. Approx 750ms to archive each
> segment is crazy -- I'll check compression parameters too.
>
> Any reason not to bump it up to 1GB? Or is that overkill?
>
> /Colin
>
> On Wed, 17 Dec 2025 at 16:25, Laurenz Albe <[email protected]>
> wrote:
>
>> On Wed, 2025-12-17 at 16:13 +0100, Colin 't Hart wrote:
>> > I see very little advice on tuning WAL segment size.
>> >
>> > One of my clients has a few datawarehouses at around 8 - 16 TB
>> >
>> > On one of the nodes there are approx 15000 WAL segments of 16MB each,
>> totalling
>> > approx 230GB. The archiver is archiving approx one per second, so
>> approx 4 hours to clear.
>> >
>> > Would we gain anything by bumping the WAL segment size?
>>
>> Very likely yes, if the problem is the overhead of starting the
>> archive_command.
>>
>> Another thing that can slow down archiving is if you compress these
>> segments
>> too aggressively.
>>
>> Yours,
>> Laurenz Albe
>>
>
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-12-17 17:23 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-12-17 15:25 Re: wal segment size Laurenz Albe <[email protected]>
2025-12-17 16:10 ` Colin 't Hart <[email protected]>
2025-12-17 16:31 ` Adrian Klaver <[email protected]>
2025-12-17 17:21 ` Greg Sabino Mullane <[email protected]>
2025-12-17 17:23 ` Ron Johnson <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox