X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E97E2D1B181 for ; Mon, 7 Jun 2004 19:49:45 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 84535-04 for ; Mon, 7 Jun 2004 22:49:47 +0000 (GMT) Received: from bast.unixathome.org (bast.unixathome.org [66.11.174.150]) by svr1.postgresql.org (Postfix) with ESMTP id B1E6FD1B434 for ; Mon, 7 Jun 2004 19:49:43 -0300 (ADT) Received: from wocker (wocker.unixathome.org [192.168.0.99]) by bast.unixathome.org (Postfix) with ESMTP id C0CE33D3D; Mon, 7 Jun 2004 18:49:46 -0400 (EDT) From: "Dan Langille" To: Rod Taylor Date: Mon, 07 Jun 2004 18:49:46 -0400 MIME-Version: 1.0 Subject: Re: seq scan woes Cc: Postgresql Performance Message-ID: <40C4B8CA.5077.4983460F@localhost> In-reply-to: <1086640698.86807.61.camel@jester> References: <40C493F8.1181.48F37175@localhost> X-mailer: Pegasus Mail for Windows (v4.12a) Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: 7BIT Content-description: Mail message body X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/77 X-Sequence-Number: 7135 On 7 Jun 2004 at 16:38, Rod Taylor wrote: > On Mon, 2004-06-07 at 16:12, Dan Langille wrote: > > I grep'd postgresql.conf: > > > > #effective_cache_size = 1000 # typically 8KB each > > #random_page_cost = 4 # units are one sequential page fetch cost > > This would be the issue. You haven't told PostgreSQL anything about your > hardware. The defaults are somewhat modest. > > http://www.postgresql.org/docs/7.4/static/runtime-config.html > > Skim through the run-time configuration parameters that can be set in > postgresql.conf. > > Pay particular attention to: > * shared_buffers (you may be best with 2000 or 4000) I do remember increasing this in the past. It was now at 1000 and is now at 2000. see http://rafb.net/paste/results/VbXQcZ87.html > * effective_cache_size (set to 50% of ram size if dedicated db > machine) The machine has 512MB RAM. effective_cache_size was at 1000. So let's try a 256MB cache. Does that the match a 32000 setting? I tried it. The query went to 1.5s. At 8000, the query was 1s. At 2000, the query was about 950ms. This machine is a webserver/database/mail server, but the FreshPorts database is by far its biggest task. > * random_page_cost (good disks will bring this down to a 2 from a > 4) I've got mine set at 4. Increasing it to 6 gave me a 1971ms query. At 3, it was a 995ms. Setting it to 2 gave me a 153ms query. How interesting. For camparison, I reset shared_buffers and effective_cache_size back to their original value (both at 1000). This gave me a 130-140ms query. The disks in question is: ad0: 19623MB [39870/16/63] at ata0-master UDMA100 I guess that might be this disk: http://www.harddrives4less.com/ibmdes6020ua2.html I invite comments upon my findings. Rod: thanks for the suggestions. > > > -- > Rod Taylor > > Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL > PGP Key: http://www.rbt.ca/signature.asc > > -- Dan Langille : http://www.langille.org/ BSDCan - http://www.bsdcan.org/