Re: [SPAM?] Re: Asynchronous I/O Support

On Fri, Oct 20, 2006 at 10:05:01AM -0400, mark(at)mark(dot)mielke(dot)cc wrote:
> Only if it can be shown that async I/O actually results in an improvement.
>
> Currently, it's speculation, with the one trial implementation showing
> little to no improvement. Support is a big word in the face of this
> initial evidence... :-)

Yeah, the single test so far on a system that didn't support
asyncronous I/O doesn't prove anything. It would help if there was a
reasonable system that did support async i/o so it could be tested
properly.

> Point being, async I/O isn't a magic bullet. There is no evidence that it
> would improve the situation on any platform.

I think it's likely to help with index scan. Prefetching index leaf
pages I think could be good. As would prefectching pages from a
(bitmap) index scan.

It won't help much on very simple queries, but where it should shine is
a merge join across two index scans. Currently postgresql would do
something like:

Loop
Fetch left tuple for join
Fetch btree leaf
Fetch tuple off disk
Fetch right tuples for join
Fetch btree leaf
Fetch tuple off disk

Currently it fetches a block fro one file, then a block from the other,
back and forth. with async i/o you could read from both files and the
indexes simultaneously, thus is theory leading to better i/o
throughput.

> One would need to consider the PostgreSQL architecture, determine where
> the bottleneck actually is, and understand why it is a bottleneck fully,
> before one could decide how to fix it. So, what is the bottleneck? Is
> PostgreSQL unable to max out the I/O bandwidth? Where? Why?

For systems where postgresql is unable to saturate the i/o bandwidth,
this is the proposed solution. Are there others?

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.