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

there might be another similar bug that was fixed in 7.4.2

i just doubled checked the 7.4.2 tarball, and it does have this problem.

you might want to double check to see if it's fixed in 7.4.3, or i can  
grab cvs and check it if you like.


On May 18, 2004, at 8:06 PM, Bruce Momjian wrote:

>
> I think we already fixed that in 7.4.2.  We also have a few bugs still
> in 7.4.2 and we need to get those fixed soon and release 7.4.3.
>
> ----------------------------------------------------------------------- 
> ----
>
> Brian Hirt wrote:
>> 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"
>>
>>
>> ---------------------------(end of  
>> broadcast)---------------------------
>> TIP 6: Have you searched our list archives?
>>
>>                http://archives.postgresql.org
>>
>
> -- 
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 359-1001
>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square, Pennsylvania  
> 19073