Re: WAL prefetch

On 06/19/2018 11:08 AM, Konstantin Knizhnik wrote:
>
>
> On 18.06.2018 23:47, Andres Freund wrote:
>> On 2018-06-18 16:44:09 -0400, Robert Haas wrote:
>>> On Sat, Jun 16, 2018 at 3:41 PM, Andres Freund <andres(at)anarazel(dot)de>
>>> wrote:
>>>>> The posix_fadvise approach is not perfect, no doubt about that. But it
>>>>> works pretty well for bitmap heap scans, and it's about 13249x better
>>>>> (rough estimate) than the current solution (no prefetching).
>>>> Sure, but investing in an architecture we know might not live long also
>>>> has it's cost. Especially if it's not that complicated to do better.
>>> My guesses are:
>>>
>>> - Using OS prefetching is a very small patch.
>>> - Prefetching into shared buffers is a much bigger patch.
>> Why?\302\240 The majority of the work is standing up a bgworker that does
>> prefetching (i.e. reads WAL, figures out reads not in s_b, does
>> prefetch). Allowing a configurable number + some synchronization between
>> them isn't that much more work.
>
> 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,...
>
> 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.
> 2. Synchronize work of recovery process with prefetch to prevent
> prefetch to go too far and doing useless job.
> The same problem exists for prefetch in OS cache, but here risk of false
> prefetch is less critical.
>

I think the main challenge here is that all buffer reads are currently
synchronous (correct me if I'm wrong), while the posix_fadvise() allows
a to prefetch the buffers asynchronously.

I don't think simply spawning a couple of bgworkers to prefetch buffers
is going to be equal to async prefetch, unless we support some sort of
async I/O. Maybe something has changed recently, but every time I looked
for good portable async I/O API/library I got burned.

Now, maybe a couple of bgworkers prefetching buffers synchronously would
be good enough for WAL refetching - after all, we only need to prefetch
data fast enough for the recovery not to wait. But I doubt it's going to
be good enough for bitmap heap scans, for example.

We need a prefetch that allows filling the I/O queues with hundreds of
requests, and I don't think sync prefetch from a handful of bgworkers
can achieve that.

regards

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