Horton

Re: Handing off SLRU fsyncs to the checkpointer

Thomas Munro <thomas.munro@gmail.com>

View thread View on postgr.es
From: Thomas Munro <thomas.munro@gmail.com>
To: Jakub Wartak <Jakub.Wartak@tomtom.com>
Cc: "alvherre@2ndquadrant.com" <alvherre@2ndquadrant.com>, Robert Haas <robertmhaas@gmail.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2020-08-28T21:26:42Z
Lists: pgsql-hackers 
On Sat, Aug 29, 2020 at 12:43 AM Jakub Wartak <Jakub.Wartak@tomtom.com> wrote:
> ... %CPU ... COMMAND
> ... 97.4 ... postgres: startup recovering 000000010000000000000089

So, what else is pushing this thing off CPU, anyway?  For one thing, I
guess it might be stalling while reading the WAL itself, because (1)
we only read it 8KB at a time, relying on kernel read-ahead, which
typically defaults to 128KB I/Os unless you cranked it up, but for
example we know that's not enough to saturate a sequential scan on
NVME system, so maybe it hurts here too (2) we keep having to switch
segment files every 16MB.  Increasing WAL segment size and kernel
readahead size presumably help with that, if indeed it is a problem,
but we could also experiment with a big POSIX_FADV_WILLNEED hint for a
future segment every time we cross a boundary, and also maybe increase
the size of our reads.

Commits

  1. Remove unused function prototypes.

  2. Defer flushing of SLRU files.

  3. Improve the vacuum error context phase information.

  4. Cache smgrnblocks() results in recovery.

  5. Refactor the fsync queue for wider use.

  6. Increase maximum number of clog buffers.

  7. Make the number of CLOG buffers adaptive, based on shared_buffers.

  8. Replace implementation of pg_log as a relation accessed through the