Well based on the autovacuum log that you attached, all of those tables
are insert only (at least during the time period included in the log.
Is that correct? If so, autovacuum will never do a vacuum (unless
required by xid wraparound issues) on those tables. So this doesn't
appear to be an autovacuum problem. I'm not sure about the missing
pg_statistic entries anyone else care to field that one?
Matthew
David Parker wrote:
Thanks. The tables I'm concerned with are named: 'schema', 'usage',
'usageparameter', and 'flow'. It looks like autovacuum is performing
analyzes:
% grep "Performing: " logs/.db.tazz.vacuum.log
[2004-11-17 12:05:58 PM] Performing: ANALYZE
"public"."scriptlibrary_library"
[2004-11-17 12:15:59 PM] Performing: ANALYZE
"public"."scriptlibraryparm"
[2004-11-17 12:15:59 PM] Performing: ANALYZE "public"."usageparameter"
[2004-11-17 12:21:00 PM] Performing: ANALYZE "public"."usageproperty"
[2004-11-17 12:21:00 PM] Performing: ANALYZE "public"."route"
[2004-11-17 12:21:00 PM] Performing: ANALYZE "public"."usageparameter"
[2004-11-17 12:21:00 PM] Performing: ANALYZE
"public"."scriptlibrary_library"
[2004-11-17 12:26:01 PM] Performing: ANALYZE "public"."usage"
[2004-11-17 12:26:01 PM] Performing: ANALYZE "public"."usageparameter"
[2004-11-17 12:31:04 PM] Performing: ANALYZE "public"."usageproperty"
[2004-11-17 12:36:04 PM] Performing: ANALYZE "public"."route"
[2004-11-17 12:36:04 PM] Performing: ANALYZE "public"."service_usage"
[2004-11-17 12:36:04 PM] Performing: ANALYZE "public"."usageparameter"
But when I run the following:
select * from pg_statistic where starelid in
(select oid from pg_class where relname in
('schema','usageparameter','flow','usage'))
it returns no records. Shouldn't it? It doesn't appear to be doing a
vacuum anywhere, which makes sense because none of these tables have
over the default threshold of 1000. Are there statistics which only get
generated by vacuum?
I've attached a gzip of the pg_autovacuum log file, with -d 3.
Thanks again.
- DAP
-----Original Message-----
From: Matthew T. O'Connor [mailto:matthew@xxxxxxxx]
Sent: Wednesday, November 17, 2004 11:41 AM
To: David Parker
Cc: Tom Lane; Jeff; Russell Smith; pgsql-performance@xxxxxxxxxxxxxx
Subject: Re: [PERFORM] query plan question
David Parker wrote:
We're using postgresql 7.4.5. I've only recently put pg_autovacuum in
place as part of our installation, and I'm basically taking the
defaults. I doubt it's a problem with autovacuum itself, but rather
with my configuration of it. I have some reading to do, so
any pointers
to existing autovacuum threads would be greatly appreciated!
Well the first thing to do is increase the verbosity of the
pg_autovacuum logging output. If you use -d2 or higher,
pg_autovacuum will print out a lot of detail on what it thinks
the thresholds are and
why it is or isn't performing vacuums and analyzes. Attach
some of the
log and I'll take a look at it.
