Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id D79D79F9603 for ; Wed, 12 Sep 2007 15:28:19 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 78333-04 for ; Wed, 12 Sep 2007 15:28:14 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.168]) by postgresql.org (Postfix) with ESMTP id E36A79F9D53 for ; Wed, 12 Sep 2007 15:28:15 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id j3so299817ugf for ; Wed, 12 Sep 2007 11:28:14 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; 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; bh=QXSaPXYlX216VMrqtbM/gLFhuNs+TyEpgJiEqGJfoaw=; b=eSalKESbmHCuYqLK2t8a+2QX4c7wpRJ5q1MLKp+FijuQaj3ibDOlnnZ3nYElxismoeBTBTbVWRa6JfyWA+j/7pEk2C8tzZkpm6IKs3OfLkDxbcp/LyUyz2YEP54OOBbMaz2pR0jMvxkTtTAyIzc9Q+TwusZVhLqednLYQEWoiLM= 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=ax4K6+1yPySzKkuLMJMUjnAATBsqpeCjs5fWW9V85NHq5jUFZM6m53p4RpdtUxec6xziHzw8VFh1/0Rfs/u7Dk9M2OgW979O8EQVgZ9lsR/5fresdIXMSnpzkbqMGfhsra9KTHmRU8BuEN0RN01fizMDfaalawx9IUwFsNhX7Wc= Received: by 10.66.219.16 with SMTP id r16mr2104586ugg.1189621694552; Wed, 12 Sep 2007 11:28:14 -0700 (PDT) Received: by 10.66.220.7 with HTTP; Wed, 12 Sep 2007 11:28:14 -0700 (PDT) Message-ID: Date: Wed, 12 Sep 2007 13:28:14 -0500 From: "Scott Marlowe" To: "ruben@rentalia.com" Subject: Re: [Again] Postgres performance problem Cc: Decibel! , db@zigo.dhs.org, pgsql-performance@postgresql.org In-Reply-To: <46E79865.906@rentalia.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <46E636CB.8080008@rentalia.com> <43393.192.121.104.48.1189495883.squirrel@zigo.dhs.org> <46E64891.9080005@rentalia.com> <20070911225242.GG38801@decibel.org> <46E79865.906@rentalia.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/226 X-Sequence-Number: 26771 On 9/12/07, ruben@rentalia.com wrote: > > Decibel! escribi=F3: > > On Tue, Sep 11, 2007 at 09:49:37AM +0200, Ruben Rubio wrote: > >> -----BEGIN PGP SIGNED MESSAGE----- > >> Hash: SHA1 > >> > >> db@zigo.dhs.org escribi?: > >>>> Last time I had this problem i solved it stopping website, restarti= ng > >>>> database, vacuumm it, run again website. But I guess this is going t= o > >>>> happen again. > >>>> > >>>> I would like to detect and solve the problem. Any ideas to detect it= ? > >>> Do you have very long transactions? Maybe some client that is connect= ed > >>> all the time that is idle in transaction? > >> There should not be long transactions. I ll keep an eye on Idle transa= ctions > >> > >> I m detecting it using: > >> > >> echo 'SELECT current_query FROM pg_stat_activity;' | > >> /usr/local/pgsql/bin/psql vacadb | grep IDLE | wc -l > > > > If you're using VACUUM FULL, you're doing something wrong. :) > > I do a VACUUM FULL VERBOSE ANALYZE each day. I save all logs so I can > check if vacuum is done properly.(it is) Then, like Jim said, you're doing it wrong. Regular vacuum full is like rebuiling a piece of equipment every night when all it needs is the filter changed. > Run lazy > > vacuum frequently enough (better yet, autovacuum, but cut all of 8.1's > > autovac parameters in half), and make sure your FSM is big enough > > I checked that there is no warnings about FSM in logs. (also in logs > from vacuum). Is it reliable? > > What do u mean for "cut all of 8.1's autovac parameters in half" Maybe > default autovac parameters? Yep. ( I assume) > > (periodic vacuumdb -av | tail is an easy way to check that). > > I ll keep an eye on it. > > > > > Try a REINDEX. VACUUM FULL is especially hard on the indexes, and it's > > easy for them to seriously bloat. > > Reindex is done everyday after VACUUM FULL VERBOSE ANALYZE. I save also > the output averyday and save it into a log, and I can check that it is > done properly. Then you're vacuum full is wasted. A reindex accomplishes the same thing, plus shrinks indexes (vacuum full can bloat indexes). Just run regular vacuums, preferably by autovacuum, and keep an eye on the vacuum analyze you run each night to see if your fsm is big enough. Occasionally vacuum full is absolutely the right answer. Most the time it's not. I'm getting more and more motivated to rewrite the vacuum docs. I think a rewrite from the ground up might be best... I keep seeing people doing vacuum full on this list and I'm thinking it's as much because of the way the docs represent vacuum full as anything. Is that true for you?