Re: min/max_wal_size

On Tue, Nov 22, 2016 at 10:34 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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.