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@2ndquadrant.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2020-03-02T05:43:23Z
Lists: pgsql-hackers

Attachments

On Wed, Feb 12, 2020 at 7:52 PM Thomas Munro <thomas.munro@gmail.com> wrote:
> 1.  It now uses effective_io_concurrency to control how many
> concurrent prefetches to allow.  It's possible that we should have a
> different GUC to control "maintenance" users of concurrency I/O as
> discussed elsewhere[1], but I'm staying out of that for now; if we
> agree to do that for VACUUM etc, we can change it easily here.  Note
> that the value is percolated through the ComputeIoConcurrency()
> function which I think we should discuss, but again that's off topic,
> I just want to use the standard infrastructure here.

I started a separate thread[1] to discuss that GUC, because it's
basically an independent question.  Meanwhile, here's a new version of
the WAL prefetch patch, with the following changes:

1.  A monitoring view:

  postgres=# select * from pg_stat_wal_prefetcher ;
   prefetch | skip_hit | skip_new | skip_fpw | skip_seq | distance | queue_depth
  ----------+----------+----------+----------+----------+----------+-------------
      95854 |   291458 |      435 |        0 |    26245 |   261800 |          10
  (1 row)

That shows a bunch of counters for blocks prefetched and skipped for
various reasons.  It also shows the current read-ahead distance (in
bytes of WAL) and queue depth (an approximation of how many I/Os might
be in flight, used for rate limiting; I'm struggling to come up with a
better short name for this).  This can be used to see the effects of
experiments with different settings, eg:

  alter system set effective_io_concurrency = 20;
  alter system set wal_prefetch_distance = '256kB';
  select pg_reload_conf();

2.  A log message when WAL prefetching begins and ends, so you can see
what it did during crash recovery:

  LOG:  WAL prefetch finished at 0/C5E98758; prefetch = 1112628,
skip_hit = 3607540,
    skip_new = 45592, skip_fpw = 0, skip_seq = 177049, avg_distance =
247907.942532,
    avg_queue_depth = 22.261352

3.  A bit of general user documentation.

[1] https://www.postgresql.org/message-id/flat/CA%2BhUKGJUw08dPs_3EUcdO6M90GnjofPYrWp4YSLaBkgYwS-AqA%40mail.gmail.com

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