Re: WAL prefetch

On 16.06.2018 06:33, Amit Kapila wrote:
> On Fri, Jun 15, 2018 at 11:31 PM, Andres Freund  wrote:
>> On 2018-06-14 10:13:44 +0300, Konstantin Knizhnik wrote:
>>>
>>> On 14.06.2018 09:52, Thomas Munro wrote:
>>>> On Thu, Jun 14, 2018 at 1:09 AM, Konstantin Knizhnik
>>>>  wrote:
>>>>> pg_wal_prefetch function will infinitely traverse WAL and prefetch block
>>>>> references in WAL records
>>>>> using posix_fadvise(WILLNEED) system call.
>>>> Hi Konstantin,
>>>>
>>>> Why stop at the page cache...  what about shared buffers?
>>>>
>>> It is good question. I thought a lot about prefetching directly to shared
>>> buffers.
>> I think that's definitely how this should work.  I'm pretty strongly
>> opposed to a prefetching implementation that doesn't read into s_b.
>>
> We can think of supporting two modes (a) allows to read into shared
> buffers or (b)  allows to read into OS page cache.
>
Unfortunately I afraid that a) requires different approach: unlike 
posix_fadvise,  reading data to shared buffer is blocking operation. If 
we do it by one worker, then it will read it with the same speed as redo 
process. So to make prefetch really efficient,  in this case we have to 
spawn multiple workers to perform prefetch in parallel (as pg_prefaulter 
does).

Another my concern against prefetching to shared buffers is that it may 
flush away from cache pages which are most frequently used by read only 
queries at hot standby replica.