pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Robin Iddon <robin@edesix.com>
To: pgsql-admin@postgresql.org
Subject: Re: 8.0.3 pg_autovacuum doesn't clear out stats table?
Date: Thu, 23 Feb 2006 18:31:54 +0000
Message-ID: <43FDFF9A.90603@edesix.com> (raw)
In-Reply-To: <43FDF046.2050609@zeut.net>
References: <43FD7312.5080701@edesix.com>
	<43FDF046.2050609@zeut.net>

Hi Matt,

Thanks for the reply.


Matthew T. O'Connor wrote:

> What do you mean by stats table entries?  Are you saying there is 
> bloat in a specific table, or just the whole database?
>
The table in question is pg_statistic.  It's pages as reported by 
pg_class.relpages grow without bound until I perform a vacuum full.

I do a "vacuum full" and it drops in size to next to nothing.

I have a standby database server configured in the same way, with the 
same data set, but almost completely idle; I did the vacuum full on it 
~1-2 months back when I started to look into this issue; since then it 
has accumulated 215K relpages (which is, I think, around 1.7G!)  in the 
pg_statistic table.

Chasing through pg_database.oid and pg_class.relfilenode I compute that 
pg_statistic should be stored in $PGDATA/base/17230/16408.  Here is what 
du reports for those files:

1049600 17230/16408
1049600 17230/16408.1
1049600 17230/16408.2
15912   17230/16408.3

Which is a total of just over 3G (this implies to me that the relpages 
count in the pg_class table is out of date).

Looking at another (tiny) database that is definitely only used for 
testing and I know hasn't been used in months it's pg_statistic has also 
grown to 1.1G on disk.

> Second, I think you need to make autovacuum more aggressive.  I think 
> most people find that the vacuum scaling factor should be more like 
> 0.3 or so.  With the settings you have a 1000 tuple table would have 
> to see 3000 invalidated tuples before it does a vacuum that is 
> probably much more slack space than you want.
>
Valuable advice, but as it happens we delete next to nothing and I think 
the disk space in use by the database is reasonable for the data stored.

> Third: Are you seeing in the autovacuum log that autovacuum is 
> actually trying to vacuum tables?  Turn up the debugging to -d2 that 
> should give you some more info as to why autovac is (or is not) doing 
> what it's doing.
>
I will turn it on and take a look.  I am guessing it will tell me that 
for whatever reason it isn't actually doing anything to the pg_catalog 
tables ...

> Fourth: there will always be some slack space.  Under the best of 
> circumstances, a database running with autovac will be somewhat bigger 
> than the database right after a VACUUM FULL, probably not 2.5G, but some.
>
Understood - I am happy for there to be 50% slack - I just don't 
understand why pg_statistic grows without bound and I am concerned that 
the size of the stats table might be causing me some performance issues 
(this is fear rather than fact).

Thanks again,

Robin




view thread (13+ messages)  latest in thread

Message-ID: <43FDFF9A.90603@edesix.com>
Permalink:  ../43FDFF9A.90603@edesix.com/
Also on:    postgresql.org/message-id/43FDFF9A.90603@edesix.com

 · 

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: robin@edesix.com
  Subject: Re: 8.0.3 pg_autovacuum doesn't clear out stats table?
  In-Reply-To: <43FDFF9A.90603@edesix.com>
  References: <43FD7312.5080701@edesix.com> <43FDF046.2050609@zeut.net> <43FDFF9A.90603@edesix.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