On Tue, Nov 22, 2016 at 10:34 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
On 11/22/2016 12:51 PM, Torsten Förtsch wrote:
Are you talking about the recycled files?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?
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.
