Re: Handing off SLRU fsyncs to the checkpointer

On Sat, Aug 29, 2020 at 12:43 AM Jakub Wartak <Jakub(dot)Wartak(at)tomtom(dot)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_maxpages in this scenario or even more preferred there shouldn't be a maximum set during closed DB recovery scenario.

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 bypasses that. I would find it quite useful in some scenarios (e.g. testing backups, PITR recoveries, opening DB from storage snapshots / 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...