pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedUse of max_slot_wal_keep_size parameter
4+ messages / 2 participants
[nested] [flat]
* Use of max_slot_wal_keep_size parameter
@ 2024-03-26 13:49 Don Seiler <don@seiler.us>
2024-03-26 14:09 ` Re: Use of max_slot_wal_keep_size parameter Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Don Seiler @ 2024-03-26 13:49 UTC (permalink / raw)
To: pgsql-general
Good morning,
With our recent upgrade to PG 15, we've put the max_slot_wal_keep_size into
place, setting it about 3% lower than the size of the volume dedicated to
pg_wal. However last night we had an incident where the volume filled up
while we were performing a massive insert from one table into another.
I don't believe the replica was lagging, and we have pgbackrest configured
for async archiving, which I assume was fine. It seemed to just be a matter
of PG not cleaning up the WAL. Our understanding was that
max_slot_wal_keep_size would prevent this but perhaps it only deals with
situations where the replication slot lag is a factor?
Don.
--
Don Seiler
www.seiler.us
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Use of max_slot_wal_keep_size parameter
2024-03-26 13:49 Use of max_slot_wal_keep_size parameter Don Seiler <don@seiler.us>
@ 2024-03-26 14:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-03-26 14:16 ` Re: Use of max_slot_wal_keep_size parameter Don Seiler <don@seiler.us>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2024-03-26 14:09 UTC (permalink / raw)
To: Don Seiler <don@seiler.us>; +Cc: pgsql-general
Don Seiler <don@seiler.us> writes:
> With our recent upgrade to PG 15, we've put the max_slot_wal_keep_size into
> place, setting it about 3% lower than the size of the volume dedicated to
> pg_wal. However last night we had an incident where the volume filled up
> while we were performing a massive insert from one table into another.
> I don't believe the replica was lagging, and we have pgbackrest configured
> for async archiving, which I assume was fine. It seemed to just be a matter
> of PG not cleaning up the WAL. Our understanding was that
> max_slot_wal_keep_size would prevent this but perhaps it only deals with
> situations where the replication slot lag is a factor?
My immediate reaction is that 3% is a mighty small margin for error.
I don't know exactly how max_slot_wal_keep_size is enforced these
days, but in the past restrictions like that were implemented by
deciding during a checkpoint whether to unlink a no-longer-needed WAL
file (if we had too much WAL) or rename/recycle it to become a future
WAL segment (if not). So you could overshoot the specified target by
more or less the amount of WAL that could be emitted between two
checkpoints. Perhaps it's tighter nowadays, but I really doubt that
it's exact-to-the-kilobyte-at-all-times.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Use of max_slot_wal_keep_size parameter
2024-03-26 13:49 Use of max_slot_wal_keep_size parameter Don Seiler <don@seiler.us>
2024-03-26 14:09 ` Re: Use of max_slot_wal_keep_size parameter Tom Lane <tgl@sss.pgh.pa.us>
@ 2024-03-26 14:16 ` Don Seiler <don@seiler.us>
2024-03-26 14:29 ` Re: Use of max_slot_wal_keep_size parameter Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Don Seiler @ 2024-03-26 14:16 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general
On Tue, Mar 26, 2024 at 9:09 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> My immediate reaction is that 3% is a mighty small margin for error.
> I don't know exactly how max_slot_wal_keep_size is enforced these
> days, but in the past restrictions like that were implemented by
> deciding during a checkpoint whether to unlink a no-longer-needed WAL
> file (if we had too much WAL) or rename/recycle it to become a future
> WAL segment (if not). So you could overshoot the specified target by
> more or less the amount of WAL that could be emitted between two
> checkpoints. Perhaps it's tighter nowadays, but I really doubt that
> it's exact-to-the-kilobyte-at-all-times.
>
In this case, the total volume size was 60GB and we had the parameter set
to 58GB but I imagine that can still be overwhelmed quickly. Maybe we
should target a 20% buffer zone? We have wal_keep_size defaulted at 0.
Thanks,
Don.
--
Don Seiler
www.seiler.us
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Use of max_slot_wal_keep_size parameter
2024-03-26 13:49 Use of max_slot_wal_keep_size parameter Don Seiler <don@seiler.us>
2024-03-26 14:09 ` Re: Use of max_slot_wal_keep_size parameter Tom Lane <tgl@sss.pgh.pa.us>
2024-03-26 14:16 ` Re: Use of max_slot_wal_keep_size parameter Don Seiler <don@seiler.us>
@ 2024-03-26 14:29 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2024-03-26 14:29 UTC (permalink / raw)
To: Don Seiler <don@seiler.us>; +Cc: pgsql-general
Don Seiler <don@seiler.us> writes:
> On Tue, Mar 26, 2024 at 9:09 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> ... So you could overshoot the specified target by
>> more or less the amount of WAL that could be emitted between two
>> checkpoints. Perhaps it's tighter nowadays, but I really doubt that
>> it's exact-to-the-kilobyte-at-all-times.
> In this case, the total volume size was 60GB and we had the parameter set
> to 58GB but I imagine that can still be overwhelmed quickly. Maybe we
> should target a 20% buffer zone? We have wal_keep_size defaulted at 0.
Can you measure your WAL emission rate? What you want I think is some
small multiple of the max amount of WAL emitted between checkpoints.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-03-26 14:29 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-03-26 13:49 Use of max_slot_wal_keep_size parameter Don Seiler <don@seiler.us>
2024-03-26 14:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-03-26 14:16 ` Don Seiler <don@seiler.us>
2024-03-26 14:29 ` Tom Lane <tgl@sss.pgh.pa.us>
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