Re: WAL prefetch

On 06/19/2018 05:50 PM, Andres Freund wrote:
> On 2018-06-19 12:08:27 +0300, Konstantin Knizhnik wrote:
>> I do not think that prefetching in shared buffers requires much more efforts
>> and make patch more envasive...
>> It even somehow simplify it, because there is no to maintain own cache of
>> prefetched pages...
> 
>> But it will definitely have much more impact on Postgres performance:
>> contention for buffer locks, throwing away pages accessed by read-only
>> queries,...
> 
> These arguments seem bogus to me. Otherwise the startup process is going
> to do that work.
> 
> 
>> Also there are two points which makes prefetching into shared buffers more
>> complex:
>> 1. Need to spawn multiple workers to make prefetch in parallel and somehow
>> distribute work between them.
> 
> I'm not even convinced that's true. It doesn't seem insane to have a
> queue of, say, 128 requests that are done with posix_fadvise WILLNEED,
> where the oldest requests is read into shared buffers by the
> prefetcher. And then discarded from the page cache with WONTNEED.  I
> think we're going to want a queue that's sorted in the prefetch process
> anyway, because there's a high likelihood that we'll otherwise issue
> prfetch requets for the same pages over and over again.
> 
> That gets rid of most of the disadvantages: We have backpressure
> (because the read into shared buffers will block if not yet ready),
> we'll prevent double buffering, we'll prevent the startup process from
> doing the victim buffer search.
> 

I'm confused. I thought you wanted to prefetch directly to shared 
buffers, so that it also works with direct I/O in the future. But now 
you suggest to use posix_fadvise() to work around the synchronous buffer 
read limitation. I don't follow ...

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services