22.12.2016, 06:31, "Adrian Klaver" <adrian.klaver@aklaver.com>:
>
> 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@aklaver.com
