From: jody brownell <jody.brownell@q1labs.com>
To: Csaba Nagy <nagy@ecircle-ag.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Help tuning autovacuum - seeing lots of relationbloat
Date: Wed, 21 Jun 2006 13:39:33 -0300
Message-ID: <200606211339.33262.jody.brownell@q1labs.com> (raw)
In-Reply-To: <1150907799.3309.53.camel@coppola.muc.ecircle.de>
References: <200606211052.43227.jody.brownell@q1labs.com>
<200606211321.06253.jody.brownell@q1labs.com>
<1150907799.3309.53.camel@coppola.muc.ecircle.de>
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.
On Wednesday 21 June 2006 13:36, Csaba Nagy wrote:
> 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: jody.brownell@q1labs.com, nagy@ecircle-ag.com
Subject: Re: Help tuning autovacuum - seeing lots of relationbloat
In-Reply-To: <200606211339.33262.jody.brownell@q1labs.com>
* 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