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.
