X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4B31DD82E9 for ; Thu, 13 Oct 2005 13:07:19 -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 42163-01 for ; Thu, 13 Oct 2005 16:07:16 +0000 (GMT) Received: from smtp103.rog.mail.re2.yahoo.com (smtp103.rog.mail.re2.yahoo.com [206.190.36.81]) by svr1.postgresql.org (Postfix) with SMTP id AAF60D82F6 for ; Thu, 13 Oct 2005 13:07:16 -0300 (ADT) Received: (qmail 772 invoked from network); 13 Oct 2005 16:07:16 -0000 Received: from unknown (HELO phlogiston.dydns.org) (a.sullivan@rogers.com@209.222.54.227 with login) by smtp103.rog.mail.re2.yahoo.com with SMTP; 13 Oct 2005 16:07:15 -0000 Received: by phlogiston.dydns.org (Postfix, from userid 1000) id AA8CE413D; Thu, 13 Oct 2005 12:07:14 -0400 (EDT) Date: Thu, 13 Oct 2005 12:07:14 -0400 From: Andrew Sullivan To: postgres performance list Subject: Re: Help tuning postgres Message-ID: <20051013160714.GG16317@phlogiston.dyndns.org> Mail-Followup-To: postgres performance list References: <1129128315.2995.174.camel@coppola.muc.ecircle.de> <1129136130.2995.188.camel@coppola.muc.ecircle.de> <20051012220036.GE13571@phlogiston.dyndns.org> <1129191303.2995.192.camel@coppola.muc.ecircle.de> <20051013124007.GA15592@phlogiston.dyndns.org> <1129209283.2995.226.camel@coppola.muc.ecircle.de> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1129209283.2995.226.camel@coppola.muc.ecircle.de> User-Agent: Mutt/1.5.9i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.033 required=5 tests=[AWL=-0.017, FORGED_RCVD_HELO=0.05] X-Spam-Level: X-Archive-Number: 200510/227 X-Sequence-Number: 14995 On Thu, Oct 13, 2005 at 03:14:44PM +0200, Csaba Nagy wrote: > In any case, I suppose that those disk pages should be in OS cache > pretty soon and stay there, so I still don't understand why the disk > usage is 100% in this case (with very low CPU activity, the CPUs are > mostly waiting/idle)... the amount of actively used data is not that > big. Ah, but if the sum of all the dead rows is large enough that they start causing your shared memory (== Postgres buffers) to thrash, then you start causing the memory subsystem to thrash on the box, which means less RAM is available for disk buffers because the OS is doing more work; and the disk buffers are full of a lot of garbage _anyway_, so then you may find that you're ending up hitting the disk for some of these reads after all. Around the office I have called this the "buffer death spiral". And note that once you've managed to get into a vacuum-starvation case, your free space map might be exceeded, at which point your database performance really won't recover until you've done VACUUM FULL (prior to 7.4 there's also an index problem that's even worse, and that needs occasional REINDEX to solve; I forget which version you said you were using). The painful part about tuning a production system is really that you have to keep about 50 variables juggling in your head, just so you can uncover the one thing that you have to put your finger on to make it all play nice. A -- Andrew Sullivan | ajs@crankycanuck.ca A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton