Thread: min/max_wal_size

On 11/22/2016 12:51 PM, Torsten Förtsch wrote:
> Hi,
>
> I am a bit confused about min_wal_size and max_wal_size. Previously,
> there was this formula to estimate the max number of WAL segment files
> in pg_xlog/
> (https://www.postgresql.org/docs/9.4/static/wal-configuration.html):
>
>   (2 + checkpoint_completion_target) * checkpoint_segments + 1 or
> checkpoint_segments + wal_keep_segments + 1
>
> I don't exactly know what the operation "or" means. Before writing this

'Or' distinguishes between the case where wal_keep_segments is the
default of 0 and the case where you set it to some value > 0. In the
second case you are forcing Postgres to keep segments it would not by
default keep.

> email I always thought of wal_keep_segments as a parameter that
> configures how many segments to keep that would otherwise be deleted and
> checkpoint_segments as the number of WAL files the database is allowed
> to work with within a checkpoint_timeout interval.
>
> The formula above makes more or less sense. The database is allowed to
> write one set of WAL files during the checkpoint interval. While
> performing the checkpoint it needs the previous set of WAL files. I
> don't know where that checkpoint_completion_target comes in. But I trust

See the paragraph above the one with the equation for how
checkpoint_completion_target applies.

> the wisdom of the author of the documentation.
>
> Now, I have a database with very low write activity. Archive_command is
> called about once per hour to archive one segment. When the database was
> moved to PG 9.5, it was initially configured with insanely high settings
> for max_wal_size, min_wal_size and wal_keep_segments. I reset
> min/max_wal_size to the default settings of 80MB and 1GB and reduced
> wal_keep_segments to 150.
>
> I am seeing in pg_xlog the WAL segments from
>
> -rw------- 1 postgres postgres 16777216 Nov 17 04:01
> pg_xlog/0000000100000004000000F9
> ...
> -rw------- 1 postgres postgres 16777216 Nov 22 20:00
> pg_xlog/00000001000000050000008E
> -rw------- 1 postgres postgres 16777216 Nov 22 20:19
> pg_xlog/00000001000000050000008F
> -rw------- 1 postgres postgres 16777216 Nov 15 07:50
> pg_xlog/000000010000000500000090
> ...
> -rw------- 1 postgres postgres 16777216 Nov 15 07:52
> pg_xlog/000000010000000600000017
>
> As you can see, the files from 1/4/F9 to 1/5/8E are old. That are 150
> files which matches exactly wal_keep_segments. If I understand
> correctly, the file 1/5/8F is currently written. Further, the files from
> 1/5/90 to 1/6/17 seem to be old WAL files that have been renamed to be
> reused in the future. Their count is 136.
>
> Why does a database that generates a little more than 1 WAL file per
> hour and has a checkpoint_timeout of 30 minutes with a
> completion_target=0.7 need so many of them? The default value for
> min_wal_size is 80MB which amounts to 5 segments. That should be totally
> enough for this database.
>
> Is this because of the previously insanely high setting (min=1GB,
> max=9GB)? Should I expect this value to drop in a week's time? Or is
> there anything that I am not aware of?

Are you talking about the recycled files?

>
> Thanks,
> Torsten
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Torsten Förtsch wrote:
> Now, I have a database with very low write activity. Archive_command is called about once per hour to
> archive one segment. When the database was moved to PG 9.5, it was initially configured with insanely
> high settings for max_wal_size, min_wal_size and wal_keep_segments. I reset min/max_wal_size to the
> default settings of 80MB and 1GB and reduced wal_keep_segments to 150.
> 
> Why does a database that generates a little more than 1 WAL file per hour and has a checkpoint_timeout
> of 30 minutes with a completion_target=0.7 need so many of them? The default value for min_wal_size is
> 80MB which amounts to 5 segments. That should be totally enough for this database.
> 
> Is this because of the previously insanely high setting (min=1GB, max=9GB)? Should I expect this value
> to drop in a week's time? Or is there anything that I am not aware of?

The size of pg_xlog will shrink slowly if there is little activity on the database.
Whenever a WAL segment has been archived, it will be deleted rather than recycled
as long as WAL size exceeds max_wal_size.

Yours,
Laurenz Albe