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
