Horton
Menu

Re: WIP: WAL prefetch (another approach)

Thomas Munro <thomas.munro@gmail.com>

View thread
From: Thomas Munro <thomas.munro@gmail.com>
To: Tomas Vondra <tomas.vondra@enterprisedb.com>
Cc: Jakub Wartak <Jakub.Wartak@tomtom.com>, Stephen Frost <sfrost@snowman.net>, Alvaro Herrera <alvherre@2ndquadrant.com>, Tomas Vondra <tomas.vondra@2ndquadrant.com>, Dmitry Dolgov <9erthalion6@gmail.com>, David Steele <david@pgmasters.net>, Andres Freund <andres@anarazel.de>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2021-02-10T08:26:49Z
Lists: pgsql-hackers 
On Thu, Feb 4, 2021 at 1:40 PM Tomas Vondra
<tomas.vondra@enterprisedb.com> wrote:
> With master, it'd take ~16000 seconds to catch up. I don't have the
> exact number, because I got tired of waiting, but the estimate is likely
> accurate (judging by other tests and how regular the progress is).
>
> With WAL prefetching enabled (I bumped up the buffer to 2MB, and
> prefetch limit to 500, but that was mostly just arbitrary choice), it
> finishes in ~3200 seconds. This includes replication of the pgbench
> initialization, which took ~200 seconds and where prefetching is mostly
> useless. That's a damn pretty improvement, I guess!

Hi Tomas,

Sorry for my slow response -- I've been catching up after some
vacation time.  Thanks very much for doing all this testing work!
Those results are very good, and it's nice to see such compelling
cases even with FPI enabled.

I'm hoping to commit this in the next few weeks.  There are a few
little todos to tidy up, and I need to do some more review/testing of
the error handling and edge cases.  Any ideas on how to battle test it
are very welcome.  I'm also currently testing how it interacts with
some other patches that are floating around.  More soon.

> #recovery_prefetch = on      # whether to prefetch pages logged with FPW
> #recovery_prefetch_fpw = off # whether to prefetch pages logged with FPW
>
> but clearly that comment is only for recovery_prefetch_fpw, the first
> GUC enables prefetching in general.

Ack, thanks.

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Fix recovery_prefetch docs.

  2. Prefetch data referenced by the WAL, take II.

  3. Add circular WAL decoding buffer, take II.

  4. Fix generation of ./INSTALL for the distribution tarball

  5. Revert recovery prefetching feature.

  6. Sync guc.c and postgresql.conf.sample with the SGML docs.

  7. Add information of total data processed to replication slot stats.

  8. Doc: Review for "Optionally prefetch referenced data in recovery."

  9. Add circular WAL decoding buffer.

  10. Optionally prefetch referenced data in recovery.

  11. Remove read_page callback from XLogReader.

  12. Provide ReadRecentBuffer() to re-pin buffers by ID.

  13. Provide recovery_init_sync_method=syncfs.

  14. Mark factorial operator, and postfix operators in general, as deprecated.

  15. Rationalize GetWalRcv{Write,Flush}RecPtr().

  16. Support PrefetchBuffer() in recovery.

  17. Prevent hard failures of standbys caused by recycled WAL segments