Re: Handing off SLRU fsyncs to the checkpointer

On Sat, Aug 29, 2020 at 12:43 AM Jakub Wartak <Jakub.Wartak@tomtom.com> wrote:
> USER        PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
> postgres 120935  0.9  0.0 866052  3824 ?        Ss   09:47   0:00 postgres: checkpointer
> postgres 120936 61.9  0.0 865796  3824 ?        Rs   09:47   0:22 postgres: background writer
> postgres 120937 97.4  0.0 865940  5228 ?        Rs   09:47   0:36 postgres: startup recovering
000000010000000000000089
>
> speedup of 1.647x

Thanks for testing!  That's better than I expected.  I guess it wasn't
quite so good with default bgwriter settings.

> I have only one comment about those 2 WIP patches, bgwriter_lru_maxpages should be maybe called
standby_bgwriter_lru_maxpagesin this scenario or even more preferred there shouldn't be a maximum set during closed DB
recoveryscenario. 

I wish bgwriter could auto-tune itself better, so we wouldn't need to
contemplate adding more settings.

As for the second patch ("Optionally, don't wait for end-of-recovery
checkpoint."), that also looked quite useful in your test scenario:

> end_of_recovery_checkpoint_wait = off, before DB is open 15s faster

> I suppose a checkpoint for large shared_buffers (hundredths of GB) might take a lot of time and this 0002 patch
bypassesthat. I would find it quite useful in some scenarios (e.g. testing backups, PITR recoveries, opening DB from
storagesnapshots / storage replication, maybe with DWH-after-crash too). 

I suppose a third option that you might want is no checkpoint at all
(meaning leave it to regular checkpoint scheduling), like fast
promotion.  One thing missing from the patch is that we probably need
to log an end-of-recovery *record*, like fast promotion does.  I'm a
little fuzzy on the timeline stuff.  I wonder if any recovery experts
would like to weigh in on theoretical problems that might be lurking
here...