On 12/21/2016 06:53 PM, neos@olansoft.com wrote:
> 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:
>
>> 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)
So a max of 80MB over 24hrs, to me that is not enough to make a
difference in recovery time.
>
>>
>> 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
nightlol.
Given your slow rate of WAL production I would not expect that to make a
difference.
Next time Postgres is in recovery and assuming you can catch it you
might want to:
ps ax to see what is running.
Run iostat
Check the OS system logs for any clues.
>
>>
>> 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
>
--
Adrian Klaver
adrian.klaver@aklaver.com