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