pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Sidetracking pg_autovacuum
3+ messages / 3 participants
[nested] [flat]

* Sidetracking pg_autovacuum
@ 2005-05-18 18:32 Jeff Boes <jboes@nexcerpt.com>
  2005-05-18 19:40 ` Re: Sidetracking pg_autovacuum Tom Lane <tgl@sss.pgh.pa.us>
  2005-05-18 20:15 ` Re: Sidetracking pg_autovacuum Chris Browne <cbbrowne@acm.org>
  0 siblings, 2 replies; 3+ messages in thread

From: Jeff Boes @ 2005-05-18 18:32 UTC (permalink / raw)
  To: pgsql-admin

(Environment: Pg 7.4.1 on Linux; yes, I know it's past time to upgrade.)

(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.)

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.]

One table in particular started to act "funny", which got me looking at
the innards of it, and I found that on our production system (which has
significantly higher throughput), the ratio of (pg_class.reltuples /
count(*)) would climb to 2.0 or higher; that seemed to indicate that
pg_autovacuum wasn't doing its job, at least not when I expected it to,
so I started doing a VACUUM after the DELETEs overnight, and that made
*that* problem go away.

However, now the test system (with the lower throughput) is behaving
oddly. The ratio has *fallen* to about 0.16, meaning there are *six
times as many* actual rows in the table compared to what the optimizer
thinks. I did a hand-analyze on the table, and the situation didn't
change. Right now, row count is about 182,000 and reltuples is under 29,000.

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?)

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


-- 
Jeff Boes                                         Vox 269-226-9550 x24
Director of Software Development                  Fax 269-349-9076

Exfacto!         Exceptional Online Content     http://www.exfacto.com
Nexcerpt         ...Extend Your Expertise...    http://www.nexcerpt.com





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Sidetracking pg_autovacuum
  2005-05-18 18:32 Sidetracking pg_autovacuum Jeff Boes <jboes@nexcerpt.com>
@ 2005-05-18 19:40 ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 3+ messages in thread

From: Tom Lane @ 2005-05-18 19:40 UTC (permalink / raw)
  To: Jeff Boes <jboes@nexcerpt.com>; +Cc: pgsql-admin

Jeff Boes <jboes@nexcerpt.com> writes:
> 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?)

7.4's ANALYZE is indeed easily fooled by nonuniform tuple density.
8.0 uses a different sampling algorithm that we hope will 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?]

Any variant of VACUUM records the exact number of tuples that it found
in its full table scan in reltuples.  It's only a standalone ANALYZE
that has to approximate.

The case where you saw reltuples > count(*) probably came from the fact
that VACUUM counts physical tuples --- that is, the difference reflects
dead-but-not-yet-removable tuples.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Sidetracking pg_autovacuum
  2005-05-18 18:32 Sidetracking pg_autovacuum Jeff Boes <jboes@nexcerpt.com>
@ 2005-05-18 20:15 ` Chris Browne <cbbrowne@acm.org>
  1 sibling, 0 replies; 3+ messages in thread

From: Chris Browne @ 2005-05-18 20:15 UTC (permalink / raw)
  To: pgsql-admin

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/;



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2005-05-18 20:15 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-05-18 18:32 Sidetracking pg_autovacuum Jeff Boes <jboes@nexcerpt.com>
2005-05-18 19:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-05-18 20:15 ` Chris Browne <cbbrowne@acm.org>

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