Re: ice-broker scan thread

On Tue, 29 Nov 2005, Andrew Piskorski wrote:

> On Tue, Nov 29, 2005 at 03:14:38PM +1100, Gavin Sherry wrote:
> > On Mon, 28 Nov 2005, David Boreham wrote:
> > > Gavin Sherry wrote:
> > > > MySQL, Oracle and others implement read-ahead threads to simulate async IO
> > >
> > > I always believed that Oracle used async file I/O. Not that I've seen their
>
> > The paper I linked to seemed to suggest that they weren't using async IO
> > in 9.2 -- which is fairly old.
>
> http://www.vldb2005.org/program/paper/wed/p1116-hall.pdf
> "Getting Priorities Straight: Improving Linux Support for Database I/O"
> by Hall and Bonnet
> Proceedings of the 31st VLDB Conference, Trondheim, Norway, 2005
>
> I think you've misread that paper. AFAICT it neither says nor even
> suggests that Oracle 9.2 does not use asynchronous I/O on Linux. In
> fact, it seems to strongly suggest exactly the opposite, that Oracle
> does use async I/O whereever it can.
>
> Note they also reference this document, which as of 2002 and Linux
> kernel 2.4.x, was urging Oracle DBAs to use Oracle's kernel-based
> asynchronous I/O support whenever possible:
>
> http://www.ixora.com.au/tips/use_asynchronous_io.htm
>
> What Hall and Bonnet's paper DOES say, is that both Oracle and MySQL
> InnoDB appear to use a "conservative" I/O submission policy, but
> Oracle does so more efficiently. They also argue that both Oracle and
> MySQL fail to utilize the "full potential" of Linux async I/O because
> of their conservative submission policies, and that an "agressive" I/O
> submissions policy would work better, but only if support for
> Prioritized I/O is added to Linux. They then proceed to add that
> support, and make some basic changes to InnoDB to partially take
> advantage of it.
>
> Also interesting is their casual mention that for RDBMS workloads, the
> default Linux 2.6 disk scheduler "anticipatory" is inferior to the
> "deadline" scheduler. They base their (simple sounding) Prioritized
> I/O support on the deadline scheduler.
>

Right. I had seemed to recall that they configured Oracle to use a kind of
readahead thread not native async IO, but I am wrong. That's not material
to the discussion at hand.

What we need to find out is if we can easily integrate prefetching into
PostgreSQL for some subset of the work we do, find non-trivial performance
gains and demonstrate it on more than one OS. Ideally, we'd see some
non-trivial gain irrespective of the IO scheduler being used.

Thanks,

Gavin