On 11/22/2016 12:51 PM, Torsten Förtsch wrote:
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
'Or' distinguishes between the case where wal_keep_segments is the default of 0 and the case where you set it to some value > 0. In the second case you are forcing Postgres to keep segments it would not by default keep.
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
See the paragraph above the one with the equation for how checkpoint_completion_target applies.
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?
Are you talking about the recycled files?
Thanks, Torsten
-- Adrian Klaver adrian.klaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
