Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id E6A4B64FCF0 for ; Fri, 2 Jan 2009 16:40:58 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 14425-05 for ; Fri, 2 Jan 2009 16:40:55 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from yx-out-2324.google.com (yx-out-2324.google.com [74.125.44.29]) by mail.postgresql.org (Postfix) with ESMTP id 9620C64FCAC for ; Fri, 2 Jan 2009 16:40:55 -0400 (AST) Received: by yx-out-2324.google.com with SMTP id 8so2072460yxb.73 for ; Fri, 02 Jan 2009 12:40:54 -0800 (PST) Received: by 10.65.163.8 with SMTP id q8mr14087720qbo.55.1230928854010; Fri, 02 Jan 2009 12:40:54 -0800 (PST) Received: from oxford.xeocode.com.enterprisedb.com (76-10-172-69.dsl.teksavvy.com [76.10.172.69]) by mx.google.com with ESMTPS id k8sm35103135qba.5.2009.01.02.12.40.51 (version=TLSv1/SSLv3 cipher=RC4-MD5); Fri, 02 Jan 2009 12:40:52 -0800 (PST) To: Tom Lane Cc: Greg Smith , Robert Haas , ITAGAKI Takahiro , Postgres Subject: Re: posix_fadvise v22 In-Reply-To: <7537.1230926505@sss.pgh.pa.us> (Tom Lane's message of "Fri\, 02 Jan 2009 15\:01\:45 -0500") User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux) X-Draft-From: ("nnimap+imap.gmail.com:HACKERS" 52287) References: <8347.1229012970@sss.pgh.pa.us> <4136ffa0812110911x4914a951o6ca502879e3dede7@mail.gmail.com> <87k5a6tmy7.fsf@oxford.xeocode.com> <603c8f070901011230t1afa6644racb715b975078054@mail.gmail.com> <10658.1230843315@sss.pgh.pa.us> <603c8f070901011843p121e40afu35c4a7bdc51c051c@mail.gmail.com> <24481.1230910405@sss.pgh.pa.us> <7537.1230926505@sss.pgh.pa.us> From: Gregory Stark Organization: EnterpriseDB Date: Fri, 02 Jan 2009 15:40:49 -0500 Message-ID: <878wptv3u6.fsf@oxford.xeocode.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200901/73 X-Sequence-Number: 130717 Tom Lane writes: > In principle you should be able to adjust the constant so that vmstat > shows about 50% CPU busy, and then enabling fadvise should improve > matters significantly. I think in practice individual queries don't interleave much cpu with i/o work. A single random page fetch is 5ms which is an awful lot of cpu cycles to be sinking somewhere. In practice I think this is going to be single-digit percentages. Aside from big sorts and such which tend not to be interleaved with i/o the main time I've seen queries have a significant cpu load is when the data is mostly in cache. In which case prefetching would be hard pressed to help at all. We could construct a synthetic case but the main point of this feature is to make use of raid arrays that are currently going idle, not to pick up a few percentage points for single spindle systems. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning