X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 5D4C39DCD0C for ; Tue, 29 Nov 2005 12:21:52 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 02787-06-2 for ; Tue, 29 Nov 2005 12:21:46 -0400 (AST) X-Greylist: delayed 00:06:38.667646 by SQLgrey- Received: from tehun.pair.com (tehun.pair.com [209.68.2.71]) by postgresql.org (Postfix) with SMTP id 3EF709DCCD0 for ; Tue, 29 Nov 2005 11:29:31 -0400 (AST) Received: (qmail 26128 invoked by uid 3106); 29 Nov 2005 15:22:56 -0000 Date: Tue, 29 Nov 2005 10:22:56 -0500 From: Andrew Piskorski To: Gavin Sherry Cc: David Boreham , pgsql-hackers@postgresql.org Subject: Re: ice-broker scan thread Message-ID: <20051129152256.GA16183@tehun.pair.com> References: <438BD493.3070507@boreham.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.4.2.1i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200511/1479 X-Sequence-Number: 76761 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. -- Andrew Piskorski http://www.piskorski.com/