Horton

Re: Background writer and checkpointer in crash recovery

Thomas Munro <thomas.munro@gmail.com>

View thread View on postgr.es
From: Thomas Munro <thomas.munro@gmail.com>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-hackers <pgsql-hackers@postgresql.org>, Heikki Linnakangas <hlinnaka@iki.fi>, Jakub Wartak <Jakub.Wartak@tomtom.com>
Date: 2020-11-12T04:26:39Z
Lists: pgsql-hackers 
On Wed, Nov 11, 2020 at 9:57 PM Simon Riggs <simon@2ndquadrant.com> wrote:
> Having said that, we did raise the checkpoint_timeout by a lot, so the
> situation today might be quite different. A large checkpoint_timeout
> could eventually overflow shared buffers, with the right workload.

FWIW Jakuk Wartak did manage to show a 1.64x speedup while running
crash recovery of an insert-only workload (with a variant of this
patch that I shared in another thread), albeit with aggressive tuning:

https://www.postgresql.org/message-id/VI1PR0701MB6960EEB838D53886D8A180E3F6520%40VI1PR0701MB6960.eurprd07.prod.outlook.com

> We don't have any stats to show whether this patch is worthwhile or
> not, so I suggest adding the attached instrumentation patch as well so
> we can see on production systems whether checkpoint_timeout is too
> high by comparison with pg_stat_bgwriter. The patch is written in the
> style of log_checkpoints.

Very useful.  I've also been wondering how to get that sort of
information in hot standby.

Commits

  1. Clear ps display of startup process at the end of recovery

  2. Further simplify a bit of logic in StartupXLOG().

  3. Run checkpointer and bgwriter in crash recovery.

  4. Add some checkpoint/restartpoint status to ps display

  5. Start background writer during archive recovery. Background writer now performs