prefetching and asynchronous io

On 08/18/2012 10:11 AM, John Lumby wrote:>
> I've recently tried extending the postgresql prefetch mechanism on linux
> to use the posix (i.e. librt)
> aio_read and friends where possible.     In other words,   in
> PrefetchBuffer(),   try getting a buffer
> and issuing aio_read before falling back to fposix_advise().     It
> gives me about 8% improvement
> in throughput relative to the fposix-advise variety, for a workload of
> 16 highly-disk-read-intensive applications running to 16 backends.
> For my test each application runs a query chosen to have plenty of
> bitmap heap scans.
>
> I can provide more details on my changes if interested.
>
> On whether this technique might improve sort performance  :
>
> First,  the disk access pattern for sorting is mostly sequential
> (although I think
> the sort module does some tricky work with reuse of pages in its
> "logtape" files
> which maybe is random-like),  and there are several claims on the net
> that linux buffered file  handling
> already does a pretty good job of read-ahead for a sequential access
> pattern
> without any need for the application to help it.
> I can half-confirm that in that I tried adding calls to PrefetchBuffer
> in regular heap scan
> and did not see much improvement.    But I am still pursuing that area.
>
> But second,  it would be easy enough to add some fposix_advise calls to
> sort and see whether
> that helps.    (Can't make use of PrefetchBuffer since sort does not use
> the regular relation buffer pool)
>

I have also added prefetching calls to regular index scans
(non-bitmap,  non-index-only,  for btree only)
and see a 25% reduction in total elapsed time for a heavy
index-scan workload.     That 25% is with just the
basic posix_fadvise,  and then extending that with asynch io
gives a small extra improvement again.

John