understanding max_wal_size,wal_keep_segments and checkpoints

From: Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com>
To: PostgreSQL mailing lists <pgsql-performance(at)postgresql(dot)org>
Subject: understanding max_wal_size,wal_keep_segments and checkpoints
Date: 2019-02-13 08:34:17
Message-ID: CA+t6e1nEtfCWAiVRAttVjtpP9EweU_-QNJuT-ft+jmp_aiUQOg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hey,
I'm trying to understand the logic behind all of these so I would be happy
if you can confirm what I understood or correct me if I'm wrong :
-The commit command writes all the data in the wal_buffers is written into
the wal files.
-Checkpoints writes the data itself (blocks that were changed) into the
data files in the base dir. Just to make sure, as part of the checkpoint,
it needs to read the wal files that were generated since the last
checkpoint right ?
-max_wal_size is a soft limit for the total size of all the wals that were
generated. When the total_size of the pg_xlog dir reaches max_wal_size(can
increase it because of peaks and some other issues..) the db will force a
checkpoint to write the changes from the wals into the disk and then it
will start recycling old wals (all of them ? or only those who were written
?).
-wal_keep_segments is meant to help standbys that didn't receive the wals,
so it allow us to keep wal_keep_segments wals in our pg_xlog dir.
- in case we have a collision between wal_keep_segments and max_wal_size
the wal_keep_segments will be the one that be used right ?. For example,
lets say my wal_size is default(16MB). I set max_wal_size to 1GB which is
1600/16=100 wals. However, my wal_keep_segments is set to 300. It means
that when the total_size of the pg_xlog directory will reach 1GB,
checkpoint will be forced but old wal files wont be recycled/deleted ?

Thanks.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Laurenz Albe 2019-02-13 09:43:36 Re: understanding max_wal_size,wal_keep_segments and checkpoints
Previous Message Thomas Munro 2019-02-13 00:52:45 Re: dsa_allocate() faliure