Re: Re: pgsql: Add URLs for : * Speed WAL recovery by allowing more than one

Bruce Momjian wrote:
> Gregory Stark wrote:
>> "Bruce Momjian" <bruce(at)momjian(dot)us> writes:
>>
>>>>>> On Tue, 2008-03-18 at 03:59 +0000, Bruce Momjian wrote:
>>>>>>> * Speed WAL recovery by allowing more than one page to be prefetched
>>>>>>>
>>>>>>> This involves having a separate process that can be told which pages
>>>>>>> the recovery process will need in the near future.
>>> Are you reading the same thread I am? See:
>>>
>>> http://archives.postgresql.org/pgsql-hackers/2008-02/msg01301.php
>> I don't think there's any consensus for the approach you describe above. If
>> anything it seemed the least objectionable form was something involving
>> posix_fadvise or libaio.

The least objectionable form is probably Aidan Van Dyk's suggestion of
doing it in restore_command, completely outside the backend. It would
need deep knowledge of the WAL format, but other than that, you could
implement it as a pgfoundry project, side-stepping any objections we on
pgsql-hackers might have :-).

>> Tom did wave us off from Simon's approach on the basis of it being hard to
>> test and Heikki seemed to be agreeing on the basis that it would be better to
>> reuse infrastructure useful in other cases as well. So I guess that's some
>> kind of consensus... of two.
>
> Yep, that was my analysis too.

A "separate process that can be told which pages the recovery process
will need in the future" doesn't imply posix_fadvise or libaio or
anything like that to me. It sounds like a background reader process,
but only one of those is hardly enough.

And it doesn't mention the point Tom raised that we shouldn't invent
anything specific to WAL replay.

How about:

* Speed WAL recovery by allowing more than one page to be prefetched

This should be done utilizing the same infrastructure used for
prefetching in general, to avoid introducing complex, error-prone code
to WAL replay codepath, which doesn't get much testing compared to the
rest of the system.

There's already this TODO item:

> Experiment with multi-threaded backend better resource utilization
>
> This would allow a single query to make use of multiple CPU's or multiple I/O channels simultaneously. One idea is to create a background reader that can pre-fetch sequential and index scan pages needed by other backends. This could be expanded to allow concurrent reads from multiple devices in a partitioned table.

This should probably be split into two. Using multiple CPUs for
satisfying one query is quite different from implementing some kind of a
pre-fetching mechanism using posix_fadvise(), libaio, or background
reader processes.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com