From: Csaba Nagy <nagy@ecircle-ag.com>
To: jody brownell <jody.brownell@q1labs.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Help tuning autovacuum - seeing lots of relationbloat
Date: Wed, 21 Jun 2006 18:36:39 +0200
Message-ID: <1150907799.3309.53.camel@coppola.muc.ecircle.de> (raw)
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>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: nagy@ecircle-ag.com, jody.brownell@q1labs.com
Subject: Re: Help tuning autovacuum - seeing lots of relationbloat
In-Reply-To: <1150907799.3309.53.camel@coppola.muc.ecircle.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox