pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Chris Browne <cbbrowne@acm.org>
To: pgsql-admin@postgresql.org
Subject: Re: Sidetracking pg_autovacuum
Date: Wed, 18 May 2005 16:15:37 -0400
Message-ID: <60hdh0qoie.fsf@dba2.int.libertyrms.com> (raw)
References: <428B8A27.7050402@nexcerpt.com>

jboes@nexcerpt.com (Jeff Boes) writes:
> (Environment: Pg 7.4.1 on Linux; yes, I know it's past time to
> upgrade.)

If you were running 7.2, I'd be pestering you ;-).

We didn't get rid of our last 7.1 system until last December, and I
think the backend is probably still running, albeit with nothing
hooking up to it :-).

> (Originally, I started to post this as a question about how
> pg_class.reltuples could get so far out of whack compared to the
> actual table size. After posting it, I found that (a) I had the
> mailing list address wrong [d'oh!], and (b) I think I solved my
> problem. In essence, by doing a VACUUM in a script after a sizeable
> DELETE, apparently I took the affected table out of the hands of
> pg_autovacuum so that it never, ever did anything with the
> table. Including ANALYZE. Thus, reltuples never got updated.)

Oops.  Change that to a VACUUM ANALYZE (in your script), and things
should work out better.

> I started tracking the COUNT(*) (actual row count) versus the value
> in pg_class.reltuples for a number of our larger tables. Some of the
> tables see a lot of INSERTs and DELETEs over the course of a day; as
> much as 1/12th of the data will be deleted overnight, and new data
> inserted over the course of the day. I have pg_autovacuum running,
> and I also do regular VACUUMs, ANALYZEs, and even some CLUSTERs on
> these tables.
>
> [N.B.: In fact, I started doing a VACUUM explicitly after the big
> nightly DELETE.]

That's not a bad idea, all in all.

> What would cause an otherwise well-behaved table to start doing
> this? Is this just a "dead spot" in the ANALYZE command? (By which I
> mean: ANALYZE randomly sampling rows, but my data is not terribly
> random, so it gets fooled?)

Yes, that doubtless would be it.  There's a new sampling scheme in
version 8 that may do better.

> [And here's the remaining question in my puzzled mind: ANALYZE would
> not change the reltuples value, but VACUUM FULL ANALYZE
> did. Er-wha?]

VACUUM ANALYZE would also change the reltuples value...
-- 
(format nil "~S@~S" "cbbrowne" "acm.org")
http://www.ntlug.org/~cbbrowne/sap.html
Rules of the Evil Overlord #78.  "I will not tell my Legions of Terror
"And he must  be taken alive!" The command will be:  ``And try to take
him alive if it is reasonably practical.''"
<http://www.eviloverlord.com/;



view thread (3+ messages)

Message-ID: <60hdh0qoie.fsf@dba2.int.libertyrms.com>
Permalink:  ../60hdh0qoie.fsf@dba2.int.libertyrms.com/
Also on:    postgresql.org/message-id/60hdh0qoie.fsf@dba2.int.libertyrms.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: cbbrowne@acm.org
  Subject: Re: Sidetracking pg_autovacuum
  In-Reply-To: <60hdh0qoie.fsf@dba2.int.libertyrms.com>
  References: <428B8A27.7050402@nexcerpt.com> <60hdh0qoie.fsf@dba2.int.libertyrms.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