pg_autovacuum seems to be a neat freak and cleans way too much

From: Brian Hirt <bhirt(at)mobygames(dot)com>
To: pgsql-general(at)postgresql(dot)org
Cc: Brian Hirt <bhirt(at)berkhirt(dot)com>
Subject: pg_autovacuum seems to be a neat freak and cleans way too much
Date: 2004-05-19 01:37:11
Message-ID: 0C4EE9A9-A935-11D8-BA1E-000D93AD2E74@mobygames.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

I've having a strange issue with pg_autovacuum. I have a table with
about 4 million rows in 20,000 pages. autovacuum likes to vacuum
and/or analyze it every 45 minutes or so, but it probably doesn't have
more that a few hundred rows changed every few hours. when i run
autovacuum with -d3 it says

[2004-05-18 07:04:26 PM] table name:
basement_nightly."public"."search_words4"
[2004-05-18 07:04:26 PM] relid: 396238832; relisshared: 0
[2004-05-18 07:04:26 PM] reltuples: 4; relpages: 20013
[2004-05-18 07:04:26 PM] curr_analyze_count: 0; cur_delete_count:
0
[2004-05-18 07:04:26 PM] ins_at_last_analyze: 0;
del_at_last_vacuum: 0
[2004-05-18 07:04:26 PM] insert_threshold: 504;
delete_threshold 1008

reltuples: 4 seems wrong. I would expect a table with 4m rows and 20k
pages to have more than 4 tuples. I think this is why the insert
threshhold is all messed up -- which is why it gets analyzed way too
frequently.

this happens with other big tables too. the autovacuum is from 7.4.2,
some information is below.

output from vacuum:

basement=# vacuum ANALYZE verbose search_words4;
INFO: vacuuming "public.search_words4"
INFO: index "search_words4_data_id" now contains 4069268 row versions
in 15978 pages
DETAIL: 479 index row versions were removed.
1 index pages have been deleted, 0 are currently reusable.
CPU 0.42s/0.70u sec elapsed 29.48 sec.
INFO: index "search_words4_pkey" now contains 4069268 row versions in
17576 pages
DETAIL: 479 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.77s/0.74u sec elapsed 150.19 sec.
INFO: "search_words4": removed 479 row versions in 6 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "search_words4": found 479 removable, 4069268 nonremovable row
versions in 19950 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 1.30s/1.61u sec elapsed 179.96 sec.
INFO: analyzing "public.search_words4"
INFO: "search_words4": 19950 pages, 3000 rows sampled, 4069800
estimated total rows
VACUUM
basement=#

here's the frequency
[2004-05-18 12:12:54 PM] Performing: ANALYZE "public"."search_words4"
[2004-05-18 01:59:13 PM] Performing: ANALYZE "public"."search_words4"
[2004-05-18 02:05:36 PM] Performing: ANALYZE "public"."search_words4"
[2004-05-18 02:29:25 PM] Performing: VACUUM ANALYZE
"public"."search_words4"
[2004-05-18 02:46:09 PM] Performing: ANALYZE "public"."search_words4"
[2004-05-18 03:39:31 PM] Performing: ANALYZE "public"."search_words4"
[2004-05-18 05:20:45 PM] Performing: ANALYZE "public"."search_words4"
[2004-05-18 06:08:03 PM] Performing: VACUUM ANALYZE
"public"."search_words4"
[2004-05-18 06:18:34 PM] Performing: ANALYZE "public"."search_words4"
[2004-05-18 07:34:27 PM] Performing: ANALYZE "public"."search_words4"
[2004-05-18 07:43:18 PM] Performing: ANALYZE "public"."search_words4"

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Josué Maldonado 2004-05-19 01:46:54 Restricted query
Previous Message Ron St-Pierre 2004-05-18 22:10:36 Function - sequence - cast

Browse pgsql-hackers by date

  From Date Subject
Next Message Lamar Owen 2004-05-19 01:37:52 Re: Call for 7.5 feature completion
Previous Message Claudio Natoli 2004-05-19 01:26:09 Re: Call for 7.5 feature completion