On Tue, Nov 22, 2016 at 10:34 PM, Adrian Klaver <adrian.kla...@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.