Subject: Re: Help tuning autovacuum - seeing lots of relationbloat
From: Csaba Nagy <nagy@ecircle-ag.com>
To: jody brownell <jody.brownell@q1labs.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
In-Reply-To: <200606211321.06253.jody.brownell@q1labs.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>
Content-Type: text/plain
Message-Id: <1150907799.3309.53.camel@coppola.muc.ecircle.de>
Mime-Version: 1.0
Date: Wed, 21 Jun 2006 18:36:39 +0200
Content-Transfer-Encoding: 7bit

On Wed, 2006-06-21 at 18:21, jody brownell wrote:
> That is interesting.
> 
> There is one thread keeping a transaction open it appears from ps
> 
> postgres: app app xxx(42644) idle in transaction

That shouldn't be a problem on itself, "idle in transaction" happens all
the time between 2 commands in the same transaction... you only have a
problem if you see the same PID always "idle", that means somebody left
an open transaction and left for lunch.

[snip]
> this was with the "Idle in transaction" though..... 

This probably means you don't have long running transactions currently.
However, if you happen to have just one such long transaction, the dead
space accumulates and normal vacuum will not be able to clean that
anymore. But I guess if you didn't find one now then you should take a
look at Tom's suggestion and bump up debug level to see if autovacuum
picks your table at all...

> Ah HA! Wondering, my autovacuum naptime is 60 seconds, that is also the interval which I wake up and begin persistence.
> Wondering if I am simply locking autovacuum out of the tables b/c they are on a similar timeline.
> 
> I will try a 30 second naptime, if this is it, that should increase the likely hood of falling on the right side of the TX more often.
> 
> make sense?

I don't think that's your problem... vacuum wouldn't be locked out by
any activity which doesn't lock exclusively the table (and I guess
you're not doing that). If your persistence finishes quickly then that's
not the problem.

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.