Re: automatic analyze: readahead - add "IO read time" log message

From: Stephen Frost <sfrost(at)snowman(dot)net>
To: Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
Cc: Jakub Wartak <Jakub(dot)Wartak(at)tomtom(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: automatic analyze: readahead - add "IO read time" log message
Date: 2020-11-04 16:02:14
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


* Tomas Vondra (tomas(dot)vondra(at)2ndquadrant(dot)com) wrote:
> >If you highlight "738754560" in the output it appears to duplicate the
> >syscalls issued until it preads() - in case of "738754560" offset it was
> >asked for 3 times. Also I wouldn't imagine in wildest dreams that
> >posix_fadvise(POSIX_FADV_WILLNEED) is such a cheap syscall.
> IMHO that'a a bug in the patch, which always tries to prefetch all
> "future" blocks, including those that were already prefetched. It
> probably needs to do something like bitmap heap scan where we track
> what was already prefetched and only issue the new blocks.

Updated patch attached which:

- Starts out by pre-fetching the first effective_io_concurrency number
of blocks we are going to want, hopefully making it so the kernel will
trust our fadvise's over its own read-ahead, right from the start.
- Makes sure the prefetch iterator is pushed forward whenever the
regular interator is moved forward.
- After each page read, issues a prefetch, similar to BitmapHeapScan, to
hopefully avoiding having the prefetching get in the way of the
regular i/o.
- Added some comments, ran pgindent, added a commit message.

I do think we should also include patch that Jakub wrote previously
which adds information about the read rate of ANALYZE.

I'll look at integrating that into this patch and then look at a new
patch to do something similar for VACUUM in a bit.

If you're doing further benchmarking of ANALYZE though, this would
probably be the better patch to use. Certainly improved performance
here quite a bit with effective_io_concurrency set to 16.



Attachment Content-Type Size
analyze_prefetch_v2.patch text/x-diff 3.8 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2020-11-04 16:32:56 Re: Getting rid of aggregate_dummy()
Previous Message Shay Rojansky 2020-11-04 15:50:08 Making cancellations safe