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

[Brian Hirt]

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 8: explain analyze is your friend

[Bruce Momjian]

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.

Oh, 7.4.2. I know we have some known bug and are waiting on a patch for
it.

Matthew, we need those fixes for pg_autovacuum soon.

--
Bruce Momjian | http://candle.pha.pa.us
pg***@candle.pha.pa.us | [phone]
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Matthew T. O'Connor]

On Tue, 2004-05-18 at 22:16, Bruce Momjian wrote:

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.
reltuples: 4 is wrong. This is a known bug that has been reported
several times lately. It is already fixed in CVS. If you want you can
grab pg_autovacuum.c and .h from CVS and recompile, that will fix it.
this happens with other big tables too. the autovacuum is from 7.4.2,
some information is below.

Oh, 7.4.2. I know we have some known bug and are waiting on a patch for
it.

Matthew, we need those fixes for pg_autovacuum soon.

As I said above this problem is already fixed, unfortunately it was
fixed just after the 7.4.2 release so it's been sitting in CVS for a
while.

The only outstanding bug I need to fix that I'm aware of is the temp
table issue. Everything else I'm planning on is development work for
7.5, mostly integrating it into the backend directly.

If anyone is aware of some other bug that I'm not thinking about please
let me know.

Thanks,

Matthew
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Vivek Khera]

>>>>> "BH" == Brian Hirt <bh***@mobygames.com> writes:

BH> reltuples in pg_class is defined as a real, reltuples in
BH> pg_autovacuum is defined as an int. the query used to get reltuples
BH> returns scientific notation for my larg tables, '4.06927e+06' for the
BH> one i mention below. pg_autovacuum happily converts that to a '4'
BH> by doing atoi('4.06927e+06'), which is why it's all fubar for my
BH> large tables with over a million tuples.

Wow. What a difference the CVS pg_autovacuum makes in this case. I
was wondering why my large tables were vacuumed *every* iteration
thru, even though I set the churn rate to 3.0. It thought that my 150
million+ row table had 1 row in it! With the latest pg_autovacuum it
gets the numbers right. This one was worse than when we had the
overflow in the time computation causing it to sleep forever between
iterations... :-)

For anyone with large tables using pg_autovaccum, you *have* to update
to the latest version.

Thanks for starting this thread. I really is making a big difference
in my performance no having to run vacuum all the time. I was
beginning to think my table churn was much worse than it really is...

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: kh***@kciLink.com Rockville, MD [phone] x806
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster