Обсуждение: after 9.2.4 patch vacuumdb -avz not analyzing all tables

Mike Broers <mbroers@gmail.com> writes:
> After patching to 9.2.4 I am noticing some mysterious behavior in my
> nightly vacuumdb cron job.

> I have been running vacuumdb -avz nightly for a while now, and have a
> script that tells me the next day if all the tables in pg_stat_user_tables
> have been vacuumed and analyzed in the last 24 hours.

> Since the patch some tables do not seem to be getting analyzed.  This did
> not happen after I patched from earlier versions of 9.2 like 9.2.1 to
> 9.2.2, 9.2.3.

> I pipe the output of the vacuumdb command to a log file, and there dont
> appear to be any errors, its just not analyzing all the tables as it did
> before.  Some tables are still being analyzed ok.  Its vacuuming all but a
> few tables that I know are being blocked by idle in transaction sessions.

[ pokes around ... ]  You certain 9.2.3 didn't do this too?  This
appears to be an intentional behavior of the 9.2.3 patch that made it
cancel truncation when there were conflicting lock requests:

    /*
     * Report results to the stats collector, too. An early terminated
     * lazy_truncate_heap attempt suppresses the message and also cancels the
     * execution of ANALYZE, if that was ordered.
     */
    if (!vacrelstats->lock_waiter_detected)
        pgstat_report_vacuum(RelationGetRelid(onerel),
                             onerel->rd_rel->relisshared,
                             new_rel_tuples);
    else
        vacstmt->options &= ~VACOPT_ANALYZE;

However I've got to say that both of those side-effects of
exclusive-lock abandonment seem absolutely brain dead now that I see
them.  Why would we not bother to tell the stats collector what we've
done?  Why would we think we should not do ANALYZE when we were told to?

Would someone care to step forward and defend this behavior?  Because
it's not going to be there very long otherwise.

            regards, tom lane

[ pokes around ... ]  You certain 9.2.3 didn't do this too?  This
appears to be an intentional behavior of the 9.2.3 patch that made it
cancel truncation when there were conflicting lock requests:

    /*
     * Report results to the stats collector, too. An early terminated
     * lazy_truncate_heap attempt suppresses the message and also cancels the
     * execution of ANALYZE, if that was ordered.
     */
    if (!vacrelstats->lock_waiter_detected)
        pgstat_report_vacuum(RelationGetRelid(onerel),
                             onerel->rd_rel->relisshared,
                             new_rel_tuples);
    else
        vacstmt->options &= ~VACOPT_ANALYZE;

However I've got to say that both of those side-effects of
exclusive-lock abandonment seem absolutely brain dead now that I see
them.  Why would we not bother to tell the stats collector what we've
done?  Why would we think we should not do ANALYZE when we were told to?

Jeff Janes <jeff.janes@gmail.com> writes:
> I believe the rationale was so that an autovacuum would still look like it
> was needed, and get fired again the next naptime, so that it could continue
> with the truncation attempts.  (Rather than waiting for 20% turnover in the
> table before trying again).  I'm not convinced by this argument.  If the
> DBA is desperate to get the space back, they can go do vacuum full.

Well, that's why I think the lock abandonment shouldn't apply to manual
plain vacuum.  You shouldn't need to do a vacuum full for that; that'd
be a huge increase in the cost, not to mention that it'd transiently
require twice the disk space, hardly a good thing if you're short.

            regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> wrote:

> However I've got to say that both of those side-effects of
> exclusive-lock abandonment seem absolutely brain dead now that I
> see them.  Why would we not bother to tell the stats collector
> what we've done?  Why would we think we should not do ANALYZE
> when we were told to?
>
> Would someone care to step forward and defend this behavior?
> Because it's not going to be there very long otherwise.

I'm pretty sure that nobody involved noticed the impact on VACUUM
ANALYZE command; all discussion was around autovacuum impact; and
Jan argued that this was leaving things in a status quo for that,
so I conceded the point and left it for a follow-on patch if
someone felt the behavior needed to change.  Sorry for the miss.

http://www.postgresql.org/message-id/50BB700E.8060404@Yahoo.com

As far as I'm concerned all effects on the explicit command were
unintended and should be reverted.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Scott Marlowe <scott.marlowe@gmail.com> wrote:

> Does this behavior only affect the 9.2 branch? Or was it ported
> to 9.1 or 9.0 or 8.4 as well?

After leaving it on master for a while to see if anyone reported
problems in development, I back-patched as far as 9.0 in time for
the 9.2.3 (and related) patches.  Prior to that the code was too
different for it to be the same patch, and (perhaps not entirely
coincidentally) I had not seen the problems before 9.0.  From 9.0
on I have seen multiple sites (all using queuing from Slony or a
JMS implementation) with recurring problems when the queue
temporarily got large, shrank again, and then wrapped around to the
beginning of the table's file space.  In some cases performance was
so impaired that when such an event was triggered they would shut
down their application until a manual VACUUM could be run.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company