reltuples in pg_class is defined as a real, reltuples in pg_autovacuum is defined as an int. the query used to get reltuples returns scientific notation for my larg tables, '4.06927e+06' for the one i mention below. pg_autovacuum happily converts that to a '4' by doing atoi('4.06927e+06'), which is why it's all fubar for my large tables with over a million tuples.
my real quick hack of changing the define in pg_autovacuum.h to cast reltuples to ::int4 makes it work
line: 37
#define TABLE_STATS_QUERY "select a.oid,a.relname,a.relnamespace,a.relpages,a.relisshared,a.reltuples:: int4,b.schemaname,b.n_tup_ins,b.n_tup_upd,b.n_tup_del from pg_class a, pg_stat_all_tables b where a.oid=b.relid and a
.relkind = 'r'"
#define PAGES_QUERY "select oid,reltuples::int4,relpages from pg_class where oid=%i"
however, i think a better fix would be to change the autovacuum to use a double instead of an int. if it's going to stay at int, it should probably be increased to long and the casts changed to ::int8
any suggestions on how best way to fix?
i'll supply a patch once the approach is agreed upon and the problem has been verified.
best regards,
--brian
On May 18, 2004, at 7:37 PM, Brian Hirt wrote:
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"
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
