Skip site navigation (1) Skip section navigation (2)

Re: Help tuning autovacuum - seeing lots of relationbloat

From: Csaba Nagy <nagy(at)ecircle-ag(dot)com>
To: jody brownell <jody(dot)brownell(at)q1labs(dot)com>
Cc: postgres performance list <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Help tuning autovacuum - seeing lots of relationbloat
Date: 2006-06-21 15:42:21
Message-ID: 1150904541.3309.38.camel@coppola.muc.ecircle.de (view raw or flat)
Thread:
Lists: pgsql-performance
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.



In response to

Responses

pgsql-performance by date

Next:From: Ron St-PierreDate: 2006-06-21 15:57:55
Subject: Tuning New Server (slow function)
Previous:From: Ron St-PierreDate: 2006-06-21 15:37:51
Subject: Tuning New Server (slow function)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group