Re: WAL prefetch

On 16.06.2018 06:33, Amit Kapila wrote:
> On Fri, Jun 15, 2018 at 11:31 PM, Andres Freund <andres(at)anarazel(dot)de> 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
>>>> <k(dot)knizhnik(at)postgrespro(dot)ru> 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.