Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id C26ED9FB545 for ; Thu, 5 Apr 2007 15:56:21 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 69243-06 for ; Thu, 5 Apr 2007 15:56:06 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.244]) by postgresql.org (Postfix) with ESMTP id 3A18D9FB4CF for ; Thu, 5 Apr 2007 15:56:14 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id c24so798078ana for ; Thu, 05 Apr 2007 11:56:13 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=LyKlqAOSpe8wJ3nwIHdlaOuV746O2j+OgBQ30ISdWEIkzAifjiGgkr9fugDWMVkG+B+ibxRt1UTZHx/GqERCTq0O0gVm7K/oKG4882xudSw8LU+sbnHNNT6UllBMGLjpRa7/LRPie3t+ZNYV8ntoadPMO1ezpZ85Qrwt1vM6FHs= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=JjdDBYMSp/Ig40aeZ+cIsqI+uwIXE3PlsBisYCz32RJpTs34E/Y/43v3ELrPEwlrKI1qcuILJ+tKj898xn8AiQeIzV/Ng8aDxIl6My3rfFBsvNSzyi/o3ICFwMMMNjMf9UR7bLCkMTLlz7j7HyVbZyE6lGSJpx8PZcRvZ94r3B0= Received: by 10.100.44.13 with SMTP id r13mr1511176anr.1175799373043; Thu, 05 Apr 2007 11:56:13 -0700 (PDT) Received: by 10.100.247.7 with HTTP; Thu, 5 Apr 2007 11:56:13 -0700 (PDT) Message-ID: Date: Thu, 5 Apr 2007 14:56:13 -0400 From: "Alex Deucher" To: "Erik Jones" Subject: Re: a question about Direct I/O and double buffering Cc: "Xiaoning Ding" , pgsql-performance@postgresql.org In-Reply-To: <0F61A03E-DF77-46C2-B7E1-2E7A5F985FB8@myemma.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <46152D5D.5030602@cse.ohio-state.edu> <4C4C834E-9304-4E23-AD15-1756921500B5@myemma.com> <46153E7D.7030208@cse.ohio-state.edu> <0F61A03E-DF77-46C2-B7E1-2E7A5F985FB8@myemma.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/118 X-Sequence-Number: 23995 On 4/5/07, Erik Jones wrote: > > On Apr 5, 2007, at 1:22 PM, Xiaoning Ding wrote: > > Erik Jones wrote: > On Apr 5, 2007, at 12:09 PM, Xiaoning Ding wrote: > Hi, > > A page may be double buffered in PG's buffer pool and in OS's buffer cache. > Other DBMS like DB2 and Oracle has provided Direct I/O option to eliminate > double buffering. I noticed there were discusses on the list. But > I can not find similar option in PG. Does PG support direct I/O now? > > The tuning guide of PG usually recommends a small shared buffer pool > (compared > to the size of physical memory). I think it is to avoid swapping. If > there were > swapping, OS kernel may swap out some pages in PG's buffer pool even PG > want to keep them in memory. i.e. PG would loose full control over > buffer pool. > A large buffer pool is not good because it may > 1. cause more pages double buffered, and thus decrease the efficiency of > buffer > cache and buffer pool. > 2. may cause swapping. > Am I right? > > If PG's buffer pool is small compared with physical memory, can I say > that the > hit ratio of PG's buffer pool is not so meaningful because most misses > can be > satisfied by OS Kernel's buffer cache? > > Thanks! > To the best of my knowledge, Postgres itself does not have a direct IO > option (although it would be a good addition). So, in order to use direct > IO with postgres you'll need to consult your filesystem docs for how to set > the forcedirectio mount option. I believe it can be set dynamically, but if > you want it to be permanent you'll to add it to your fstab/vfstab file. > > I use Linux. It supports direct I/O on a per-file basis only. To bypass OS > buffer cache, > files should be opened with O_DIRECT option. I afraid that I have to modify > PG. > > Xiaoning > Looks like it. I just did a cursory search of the archives and it seems > that others have looked at this before so you'll probably want to start > there if your up to it. > Linux used to have (still does?) a RAW interface which might also be useful. I think the original code was contributed by oracle so they could support direct IO. Alex