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 D5B389F9CC1 for ; Wed, 21 Jun 2006 13:49:59 -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 19090-07 for ; Wed, 21 Jun 2006 13:49:54 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from q1exch01.Q1LABS.INC (Q1EXCH01.Q1Labs.com [205.174.165.4]) by postgresql.org (Postfix) with ESMTP id 173A99FA65C for ; Wed, 21 Jun 2006 13:49:50 -0300 (ADT) Received: from q1exch02.Q1LABS.INC ([10.100.50.8]) by q1exch01.Q1LABS.INC with Microsoft SMTPSVC(6.0.3790.1830); Wed, 21 Jun 2006 13:49:49 -0300 X-PMWin-Version: 2.5.0e, Antispam-Engine: 2.3.0.1, Antivirus-Engine: 2.34.3 thread-index: AcaVUrVuOfNxGKVBT6O9+wR2bbQMXg== Received: from [10.100.100.123] ([10.100.100.123]) by q1exch02.Q1LABS.INC with Microsoft SMTPSVC(6.0.3790.1830); Wed, 21 Jun 2006 13:49:48 -0300 From: "jody brownell" To: "Csaba Nagy" Subject: Re: Help tuning autovacuum - seeing lots of relationbloat Content-Class: urn:content-classes:message Importance: normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2663 Date: Wed, 21 Jun 2006 13:49:49 -0300 User-Agent: KMail/1.8.3 Cc: "postgres performance list" References: <200606211052.43227.jody.brownell@q1labs.com> <200606211339.33262.jody.brownell@q1labs.com> <1150908279.3309.56.camel@coppola.muc.ecircle.de> In-Reply-To: <1150908279.3309.56.camel@coppola.muc.ecircle.de> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-ID: <200606211349.49305.jody.brownell@q1labs.com> X-OriginalArrivalTime: 21 Jun 2006 16:49:48.0975 (UTC) FILETIME=[B56463F0:01C69552] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/408 X-Sequence-Number: 19765 block and row are always configured on - they are my friend :) thanks On Wednesday 21 June 2006 13:44, Csaba Nagy wrote: > On Wed, 2006-06-21 at 18:39, jody brownell wrote: > > that is exactly what I am seeing, one process, no change, always in idle while the others are constantly > > changing their state. > > > > looks like someone opened a tx then is blocking on a queue lock or something. dang. > > Don't forget to check the statistics collector settings (see below), if > that is not correct then autovacuum is indeed not working correctly... I > should have put that on the beginning of the mail so you won't overlook > it ;-) > > > > > > > Oh, just occured to me... in order to use autovacuum you also need to > > > enable the statistics collector on row level: > > > > > > stats_start_collector = on > > > stats_row_level = on > > > > > > See also: > > > http://www.postgresql.org/docs/8.1/static/maintenance.html#AUTOVACUUM > > > > > > This was not mentioned in the settings in your original post, so I guess > > > you didn't touch that, and I think they are disabled by default. > > > > > > If this is disabled, you should enable it and "pg_ctl reload ....", that > > > should fix the problem. > > > > > > Cheers, > > > Csaba. > > > >