pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Christian Kratzer <ck-lists@cksoft.de>
To: Benjamin Krajmalnik <kraj@illumen.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Unexplained growth of tables
Date: Sun, 23 Jul 2006 14:50:37 +0200 (CEST)
Message-ID: <20060723143850.P64773@vesihiisi.cksoft.de> (raw)
In-Reply-To: <BF337097BDD9D849A2F4B818DDB2798702949D@stash.stackdump.local>
References: <BF337097BDD9D849A2F4B818DDB2798702949D@stash.stackdump.local>

Hi,

On Sun, 23 Jul 2006, Benjamin Krajmalnik wrote:
<snipp/>
> The actual number of physical rows in this table is 3404.  Row width is
> 361.  Table size = 639 MB.
> What can be causing this growth?  Not vacuuming often enough? I hav
> pg_autovacuum running every 60 seconds.  These tables have 10-15
> insert/update statements per second.
>
> Any assistance or guidance will be deeply appreciated.  I am pulling
> hairs on this one.

something might be stopping vacuum from reclaiming the dead rows.

We had a similar situation with a DBD::Pg based perl script that was 
monitoring the database for new jobs to perform.    The script started
once, connceted to the db, prepared its statemens and then just slept 
waiting for notifies.

We now have the  script regularly exit and reopen a new db connection 
which magically allows vacuum to reclaim rows.

Could be related to prepared staments and locks on system tables
or something of that sort.

I would recommend comparing the output of vacuum full with and
without your application connected.

Greetings
Christian

-- 
Christian Kratzer                       ck@cksoft.de
CK Software GmbH                        http://www.cksoft.de/
Phone: +49 7452 889 135                 Fax: +49 7452 889 136



view thread (7+ messages)  latest in thread

Message-ID: <20060723143850.P64773@vesihiisi.cksoft.de>
Permalink:  ../20060723143850.P64773@vesihiisi.cksoft.de/
Also on:    postgresql.org/message-id/20060723143850.P64773@vesihiisi.cksoft.de

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: ck-lists@cksoft.de, kraj@illumen.com
  Subject: Re: Unexplained growth of tables
  In-Reply-To: <20060723143850.P64773@vesihiisi.cksoft.de>
  References: <BF337097BDD9D849A2F4B818DDB2798702949D@stash.stackdump.local> <20060723143850.P64773@vesihiisi.cksoft.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