X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 6A78F9FA65C for ; Wed, 21 Jun 2006 16:38:38 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 40418-06 for ; Wed, 21 Jun 2006 16:38:28 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id E18D59F9CC1 for ; Wed, 21 Jun 2006 16:38:25 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id A56E356450; Wed, 21 Jun 2006 14:38:24 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Wed, 21 Jun 2006 14:38:24 -0500 Date: Wed, 21 Jun 2006 14:38:24 -0500 From: "Jim C. Nasby" To: jody brownell Cc: Csaba Nagy , postgres performance list Subject: Re: Help tuning autovacuum - seeing lots of relationbloat Message-ID: <20060621193823.GI93655@pervasive.com> References: <200606211052.43227.jody.brownell@q1labs.com> <200606211227.06618.jody.brownell@q1labs.com> <1150904541.3309.38.camel@coppola.muc.ecircle.de> <200606211321.06253.jody.brownell@q1labs.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <200606211321.06253.jody.brownell@q1labs.com> X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:060621:jody.brownell@q1labs.com::md2IlG9b60DDotoc:0000000000000 0000000000000000000000000d95 X-Hashcash: 1:20:060621:nagy@ecircle-ag.com::1PqVNnTWoQ14COAL:000000000000000000 0000000000000000000000000wFf X-Hashcash: 1:20:060621:pgsql-performance@postgresql.org::X2ry0qT1TiRKf7z9:00000 0000000000000000000000003ynh X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/416 X-Sequence-Number: 19773 On Wed, Jun 21, 2006 at 01:21:05PM -0300, jody brownell wrote: > Jun 21 13:04:04 vanquish postgres[3311]: [19-1] DEBUG: "target": removed 5645231 row versions in 106508 pages > Jun 21 13:04:04 vanquish postgres[3311]: [19-2] DETAIL: CPU 3.37s/1.23u sec elapsed 40.63 sec. > Jun 21 13:04:04 vanquish postgres[3311]: [20-1] DEBUG: "target": found 5645231 removable, 1296817 nonremovable row versions in 114701 pages > Jun 21 13:04:04 vanquish postgres[3311]: [20-2] DETAIL: 0 dead row versions cannot be removed yet. So the table contained 5.6M dead rows and 1.3M live rows. I think you should forget about having autovacuum keep this table in-check and add manual vacuum commands to your code. Autovac is intended to deal with 99% of use cases; this is pretty clearly in the 1% it can't handle. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461