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 17:42:21 +0200
Message-ID: <1150904541.3309.38.camel@coppola.muc.ecircle.de> (raw)
In-Reply-To: <200606211227.06618.jody.brownell@q1labs.com>
References: <200606211052.43227.jody.brownell@q1labs.com>
<1150898912.3309.23.camel@coppola.muc.ecircle.de>
<200606211227.06618.jody.brownell@q1labs.com>
On Wed, 2006-06-21 at 17:27, jody brownell wrote:
> Our application is broken down quite well. We have two main writing processes
> writing to two separate sets of tables. No crossing over, nothign to prohibit the
> vacuuming in the nature which you describe.
It really doesn't matter what table are you touching, as it doesn't
matter if you read or write either, what matters is how long ago was the
last "begin" without "commit" or "rollback". VACUUM will not touch
tuples which were deleted after the oldest not yet finished transaction
started, regardless if that transaction touched the vacuumed table or
not in any way...
> My longest transaction on the tables in question are typically quite short until
> of course they begin to bloat.
Well, your application might be completely well behaved and still your
DBA (or your favorite DB access tool for that matter) can leave open
transactions in an interactive session. It never hurts to check if you
actually have "idle in transaction" sessions. It happened a few times to
us, some of those were bad coding on ad-hoc tools written by us, others
were badly behaved DB access tools opening a transaction immediately
after connect and after each successful command, effectively leaving an
open transaction when leaving it open while having lunch...
So it might very well be that some interactive or ad hoc tools you're
using to manage the DB are your 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: <1150904541.3309.38.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