Thread: auto-vacuum & Negative "anl" Values

Dylan Hansen <dhansen@pixpo.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?

            regards, tom lane

Tom Lane wrote:
> Dylan Hansen <dhansen@pixpo.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

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Tom Lane wrote:
>> 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.

The scenario I was imagining was big delete followed by
VACUUM-without-ANALYZE.  In this situation, it looks to me like
pgstat_recv_vacuum updates n_live_tuples to the new smaller value
and doesn't do anything to last_anl_tuples.  I'm thinking we need

    tabentry->n_live_tuples = msg->m_tuples;
    tabentry->n_dead_tuples = 0;
    if (msg->m_analyze)
    {
        tabentry->last_anl_tuples = msg->m_tuples;
        if (msg->m_autovacuum)
            tabentry->autovac_analyze_timestamp = msg->m_vacuumtime;
        else
            tabentry->analyze_timestamp = msg->m_vacuumtime;
    }
+    else
+    {
+        /* last_anl_tuples must never exceed n_live_tuples */
+        tabentry->last_anl_tuples = Min(tabentry->last_anl_tuples,
+                        msg->m_tuples);
+    }
}

but perhaps I'm confused.

            regards, tom lane

Tom Lane wrote:
> Dylan Hansen <dhansen@pixpo.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?

I think I had something in the contrib version that checked this.  I
always assumed it would be caused by a stats reset which was more common
in earlier PGSQL releases since stats_reset_on_startup (or whatever the
correct spelling of that is) was enabled by default.

Dylan Hansen wrote:
> So can I assume that this is a bug?

Definitively a bug.

> The only resolution I can see right now is to setup a cron job that
> will perform an ANALYZE periodically, as the pg_autovacuum ANALYZE
> threshold is never reached.
>
> Any other suggestions?  Thanks for the input!

I just committed a fix, so the other alternative is get a CVS checkout
from the 8.1 branch and put it up to see if it fixes your problem.  The
relevant patch is below.

Index: src/backend/postmaster/pgstat.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/postmaster/pgstat.c,v
retrieving revision 1.111.2.3
diff -c -p -r1.111.2.3 pgstat.c
*** src/backend/postmaster/pgstat.c    19 May 2006 15:15:38 -0000    1.111.2.3
--- src/backend/postmaster/pgstat.c    27 Jun 2006 03:36:03 -0000
*************** pgstat_recv_vacuum(PgStat_MsgVacuum *msg
*** 2919,2924 ****
--- 2919,2930 ----
      tabentry->n_dead_tuples = 0;
      if (msg->m_analyze)
          tabentry->last_anl_tuples = msg->m_tuples;
+     else
+     {
+         /* last_anl_tuples must never exceed n_live_tuples */
+         tabentry->last_anl_tuplse = Min(tabentry->last_anl_tuples,
+                                         msg->m_tuples);
+     }
  }

  /* ----------
*************** pgstat_recv_tabstat(PgStat_MsgTabstat *m
*** 3055,3061 ****
              tabentry->tuples_updated += tabmsg[i].t_tuples_updated;
              tabentry->tuples_deleted += tabmsg[i].t_tuples_deleted;

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

--- 3061,3068 ----
              tabentry->tuples_updated += tabmsg[i].t_tuples_updated;
              tabentry->tuples_deleted += tabmsg[i].t_tuples_deleted;

!             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;


--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre@commandprompt.com> writes:
> +         /* last_anl_tuples must never exceed n_live_tuples */

If we actually believe the above statement, it seems like your patch
to pgstat_recv_tabstat() opens a new issue: with that patch, it is
possible for pgstat_recv_tabstat() to decrease n_live_tuples, and
therefore a clamp needs to be applied in pgstat_recv_tabstat() too.
No?

The reason I didn't patch it myself is that I'm not quite clear on what
*should* be happening here.  What effect should a large delete have on
the ANALYZE threshold, exactly?  You could argue that a deletion
potentially changes the statistics (by omission), and therefore inserts,
updates, and deletes should equally count +1 towards the analyze
threshold.  I don't think we are implementing that though.  If we want
to do it that way, I suspect last_anl_tuples as currently defined is not
the right comparison point.

            regards, tom lane

Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > +         /* last_anl_tuples must never exceed n_live_tuples */
>
> If we actually believe the above statement, it seems like your patch
> to pgstat_recv_tabstat() opens a new issue: with that patch, it is
> possible for pgstat_recv_tabstat() to decrease n_live_tuples, and
> therefore a clamp needs to be applied in pgstat_recv_tabstat() too.
> No?

Hmm, yeah.

> The reason I didn't patch it myself is that I'm not quite clear on what
> *should* be happening here.  What effect should a large delete have on
> the ANALYZE threshold, exactly?  You could argue that a deletion
> potentially changes the statistics (by omission), and therefore inserts,
> updates, and deletes should equally count +1 towards the analyze
> threshold.  I don't think we are implementing that though.  If we want
> to do it that way, I suspect last_anl_tuples as currently defined is not
> the right comparison point.

Maybe what we should do is revert the pgstat_recv_tabstat() part of the
patch in 8.1, and consider redefining last_anl_tuples in HEAD.  Caffeine
is not high enough yet to propose anything sensible, but I'll think
about it a bit later.

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

Tom Lane wrote:
> The reason I didn't patch it myself is that I'm not quite clear on what
> *should* be happening here.  What effect should a large delete have on
> the ANALYZE threshold, exactly?  You could argue that a deletion
> potentially changes the statistics (by omission), and therefore inserts,
> updates, and deletes should equally count +1 towards the analyze
> threshold.  I don't think we are implementing that though.  If we want
> to do it that way, I suspect last_anl_tuples as currently defined is not
> the right comparison point.

Just as a point of reference, the old contrib pg_autovacuum counts ins +
upd + del against the analyze threshold where as the vacuum threshold
only compares against  upd + del.