Hi all, A couple of months back is has been reported to pgsql-bugs that WAL segments were always switched with a low value of archive_timeout even if a system is completely idle: http://www.postgresql.org/message-id/20151016203031.3019.72...@wrigleys.postgresql.org In short, a closer look at the problem has showed up that the logic in charge of checking if a checkpoint should be skipped or not is currently broken, because it completely ignores standby snapshots in its calculation of the WAL activity. So a checkpoint always occurs after checkpoint_timeout on an idle system since hot_standby has been introduced as wal_level. This did not get better from 9.4, since standby snapshots are logged every 15s by the background writer process. In 9.6, since wal_level = 'archive' and 'hot_standby' actually has the same meaning, the skip logic that worked with wal_level = 'archive' does not do its job anymore.
One solution that has been discussed is to track the progress of WAL activity when doing record insertion by being able to mark some records as not updating the progress of WAL. Standby snapshot records enter in this category, making the checkpoint skip logic more robust. Attached is a patch implementing a solution for it, by adding in WALInsertLock a new field that gets updated for each record to track the LSN progress. This allows to reliably skip the generation of standby snapshots in the bgwriter or checkpoints on an idle system. Per discussion with Andres at PGcon, we decided that this is an optimization, only for 9.7~ because this has been broken for a long time. I have also changed XLogIncludeOrigin() to use a more generic routine to set of status flags for a record being inserted: XLogSetFlags(). This routine can use two flags: - INCLUDE_ORIGIN to decide if the origin should be logged or not - NO_PROGRESS to decide at insertion if a record should update the LSN progress or not. Andres mentioned me that we'd want to have something similar to XLogIncludeOrigin, but while hacking I noticed that grouping both things under the same umbrella made more sense. I am adding that to the commit fest of September. Regards, -- Michael
hs-checkpoints-v11.patch
Description: invalid/octet-stream
hs-checkpoints-v11-2.patch
Description: invalid/octet-stream
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers