min/max_wal_size

From: Torsten Förtsch <tfoertsch123(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: min/max_wal_size
Date: 2016-11-22 20:51:00
Message-ID: CAKkG4_m+sfh3F68E0gq7RW-6m+D8J0K_EUcH2BHYh-87SZbPOA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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
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 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?

Thanks,
Torsten

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2016-11-22 21:34:39 Re: min/max_wal_size
Previous Message Charles Clavadetscher 2016-11-22 18:48:20 max_connections limit violation not showing in pg_stat_activity