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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
