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

Поиск
Список
Период
Сортировка
От Brian Hirt
Тема pg_autovacuum seems to be a neat freak and cleans way too much
Дата
Msg-id 0C4EE9A9-A935-11D8-BA1E-000D93AD2E74@mobygames.com
обсуждение исходный текст
Ответы Re: pg_autovacuum seems to be a neat freak and cleans way too much  (Brian Hirt <bhirt@mobygames.com>)
Re: pg_autovacuum seems to be a neat freak and cleans way  (Bruce Momjian <pgman@candle.pha.pa.us>)
Список pgsql-general
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"


В списке pgsql-general по дате отправления:

Предыдущее
От: Ron St-Pierre
Дата:
Сообщение: Function - sequence - cast
Следующее
От: Josué Maldonado
Дата:
Сообщение: Restricted query