Re: auto-vacuum & Negative "anl" Values

Hi Tom, Alvaro. Thanks so much for your replies.

On 22-Jun-06, at 12:56 PM, Alvaro Herrera wrote:

> Hmm ... I'd think that the number of dead tuples plus live tuples
> should
> never be smaller than the number of tuples seen at last analyze.
> Unless
> some stats messages are lost (say, stop the server, start with stats
> disabled, do a big DELETE, stop, restart normally).

We had to restart the server to upgrade to 8.1.4 when it was
released, but since then we have not restarted our server. We
haven't changed our stats gathering recently, although we've made
some changes to auto-vacuum to do it more frequently (outlined in my
first e-mail). Unfortunately, I do not have any data to supply
previous to the upgrade as we have just recently changed our logging
to be more verbose.

> I'm interested in what Dylan can tell about the activity over this
> table? What does pg_stat_user_tables say about it?

Here's the pg_stat_user_tables entry for this table:

-[ RECORD 1 ]-+----------
relid | #####
schemaname | public
relname | tablename
seq_scan | 345
seq_tup_read | 46699390
idx_scan | 33731562
idx_tup_fetch | 171166937
n_tup_ins | 3359
n_tup_upd | 8205786
n_tup_del | 0

As you can see, the number of deleted tuples is 0, so I don't believe
that it could be an issue with DELETEs on the table because there
aren't any!

It has to be something that occurs when the VACCUM threshold is hit,
because it resets both the VACUUM and ANALYZE counters, and it decreases

Yesterday we tried running the ANALYZE command and it did reset the
counter to a non-negative value. However, the next time that the
VACUUM threshold was reached, the ANALYZE counter was decreased to a
negative number.

Thanks for looking into this, please let me know if there's any
information I can provide!
--
Dylan Hansen
Enterprise Systems Developer