Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id B07F464FC04 for ; Sat, 3 Jan 2009 00:26:50 -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 88058-03 for ; Sat, 3 Jan 2009 00:26:44 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from wf-out-1314.google.com (wf-out-1314.google.com [209.85.200.174]) by mail.postgresql.org (Postfix) with ESMTP id 48A4664FC6F for ; Sat, 3 Jan 2009 00:26:42 -0400 (AST) Received: by wf-out-1314.google.com with SMTP id 25so6523375wfc.28 for ; Fri, 02 Jan 2009 20:26:41 -0800 (PST) Received: by 10.142.177.5 with SMTP id z5mr7609597wfe.40.1230956801483; Fri, 02 Jan 2009 20:26:41 -0800 (PST) Received: by 10.143.10.5 with HTTP; Fri, 2 Jan 2009 20:26:41 -0800 (PST) Message-ID: <4136ffa0901022026yfe11975yb45575259f796a39@mail.gmail.com> Date: Fri, 2 Jan 2009 23:26:41 -0500 From: "Greg Stark" To: "Robert Haas" Subject: Re: posix_fadvise v22 Cc: "Greg Smith" , "Tom Lane" , "ITAGAKI Takahiro" , Postgres In-Reply-To: <603c8f070901022013u242179fbq4ba97e2f5aea3715@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <603c8f070901011230t1afa6644racb715b975078054@mail.gmail.com> <10658.1230843315@sss.pgh.pa.us> <603c8f070901011843p121e40afu35c4a7bdc51c051c@mail.gmail.com> <603c8f070901021436v71f10188g1d747b493c89f2f5@mail.gmail.com> <4136ffa0901021502w78f0611dx63929ac0fbbdc2f4@mail.gmail.com> <603c8f070901021742s61dcbaf2r51deff1219196944@mail.gmail.com> <4136ffa0901021902m3d9565e8v6d62a75c4a81511a@mail.gmail.com> <603c8f070901022013u242179fbq4ba97e2f5aea3715@mail.gmail.com> 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/104 X-Sequence-Number: 130748 On Fri, Jan 2, 2009 at 11:13 PM, Robert Haas wrote: > When I did that, it when back from 50 s to 33 s, which I think means > that posix_fadvise is getting called and that that is what is making > it slower. > >> And is this on a system with multiple spindles? How many? > > Latitude D830 laptop. Single disk. Fedora 9. kernel-2.6.27.9-73.fc9.x86_64. > >> And how much of the data is in shared buffers or in filesystem cache? >> Is this consistent for repeated queries? Is it only when you're >> repeating a query for dates that you've already selected? > > I stopped the cluster, dropped the page cache, and restarted the > cluster just before testing. Repeated tests are fast due to caching > effects. shared_buffers is 240MB. System has 2GB RAM, steady state > is about 1GB of page cache. Ahhh. So this is a test of how much impact the extra syscalls and buffer lookups have on a system where they're not really helping. I'm still surprised, a 50% performance penalty is a lot worse than I would have expected, especially when the buffers aren't in cache. I did one quick test and saw about 10% performance penalty in that test. -- greg