On 12/21/2016 06:53 PM, neos@xxxxxxxxxxxx wrote:
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:
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 night lol.
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@xxxxxxxxxxx
--
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
