Re: pg_autovacuum seems to be a neat freak and cleans way too much - Mailing list pgsql-hackers

I'm following up on my own email and cross posting to hackers, because
there is a bug that needs fixed.   I spent some more time digging into
this, and I found the cause of the problem.

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"