Re: WAL prefetch

On Sat, Jun 16, 2018 at 10:47 AM, Konstantin Knizhnik
<k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
>
>
> 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.
>

Okay, but I am suggesting to make it optional so that it can be
enabled when helpful (say when the user has enough shared buffers to
hold the data).

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com