22.12.2016, 06:31, "Adrian Klaver" <adrian.klaver@xxxxxxxxxxx>: > > Alright looks like it doing the correct thing. > > Now if I am following you say you see the issue starting with 9.5+. As > it so happens that is when checkpoint_segments was replaced with > max_wal_size: Yes, in 9.4 recovery process began instantly(or near that). I thought that was a bug in 9.5.1 and waited for fix. > > https://www.postgresql.org/docs/9.5/static/release-9-5.html > > " > > Replace configuration parameter checkpoint_segments with min_wal_size > and max_wal_size (Heikki Linnakangas) > > If you previously adjusted checkpoint_segments, the following formula > will give you an approximately equivalent setting: > > max_wal_size = (3 * checkpoint_segments) * 16MB > > Note that the default setting for max_wal_size is much higher than the > default checkpoint_segments used to be, so adjusting it might no longer > be necessary. > " > > If you had not changed the checkpoint_settings in your 9.4- servers it > would have been set at 3 or roughly 48 MB of WALs. In 9.5 max_wal_size > is set at 1GB. In either case the default checkpoint_timeout would be 5 > minutes. > > Did you change the checkpoint_timeout setting or is you machine > generating something close to 1GB of WAL files before the 5 minutes? Hm... No, i have never changed checkpoint_timeout setting (and wal_size in 9.5\9.6 too). In 9.4 config i have had checkpoint_segments = 32 and checkpoint_completion_target = 0.9 In 9.3 checkpoint_segments = 8 and checkpoint_completion_target = 0.7 Generally i have about 3 to 5 WAL files per 24h (copied to destination by archive_command) > > Either case could lead to longer start up times as Postgres would have > to process more WAL files. > > There is also your checkpoint_completion_target = 0.85. The default is > 0.5. Per the docs below: > "The disadvantage of this is that prolonging checkpoints affects > recovery time, because more WAL segments will need to be kept around for > possible use in recovery." Hmm, i don't think about that. I set it to "0.5". Now i'm waiting for the next failure, or do it manually nearest night lol. > > For more information see 9.4-: > > https://www.postgresql.org/docs/9.4/static/wal-configuration.html > > 9.5+: > > https://www.postgresql.org/docs/9.5/static/wal-configuration.html > > > -- > 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
