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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Nov 22, 2016 at 10:34 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 11/22/2016 12:51 PM, 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.

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?

Yes, I was talking about recycled files and I think the suspicion that the
number of recycled files will drop over time to min_wal_size is correct.
Over night the number of recycled files dropped to 123 and according to the
log, many checkpoints have removed a file and none was added.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general