Re: auto-vacuum & Negative "anl" Values

Tom Lane wrote:
> Dylan Hansen <dhansen(at)pixpo(dot)com> writes:
> > I have been spending some time looking into how auto-vacuum is
> > performing on one of our servers. After putting the PostgreSQL logs
> > in debug I noticed that the threshold for ANALYZE was never being hit
> > for a particular table because the calculated value becomes
> > increasingly negative.
>
> Hmm, it shouldn't ever be negative at all, I would think. The
> calculation in question is
>
> anltuples = tabentry->n_live_tuples + tabentry->n_dead_tuples -
> tabentry->last_anl_tuples;
>
> Apparently somehow last_anl_tuples has managed to get to be bigger than
> n_live_tuples, which maybe could happen after a delete. Should we be
> clamping last_anl_tuples to not exceed n_live_tuples somewhere?
> Alvaro and Matthew, what do you think?

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).

I think there's a bug elsewhere. On pgstat_recv_tabstat, I see

tabentry->n_live_tuples = tabmsg[i].t_tuples_inserted;
tabentry->n_dead_tuples = tabmsg[i].t_tuples_updated +
tabmsg[i].t_tuples_deleted;

But really n_live_tuples should be decreased by t_tuples_deleted as
well, so this should read

tabentry->n_live_tuples = tabmsg[i].t_tuples_inserted -
tabmsg[i].t_tuples_deleted;
tabentry->n_dead_tuples = tabmsg[i].t_tuples_updated +
tabmsg[i].t_tuples_deleted;

On the other hand I don't see how this would explain the problem Dylan
is seeing, because the effect would be the inverse -- anltuples would be
erroneously calculated too high, so it wouldn't become negative.

I think we should catch a negative anltuples value, log a WARNING, and
force an analyze, because it's indicative of a bug.

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

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support