BUG #5722: vacuum full does not update last_vacuum statistics

"Jochen Erwied" <jochen@pgsql.erwied.eu> writes:

VACUUM FULL does not update statistics so display of pg_stat_user_tables is
wrong. A normal VACUUM updates the relevant information.

Hmm. This is a definitional issue: what do we really mean by last_vacuum?
I'm inclined to think that the current behavior is reasonable. VACUUM
FULL is (still) not intended as a routine maintenance operation, and
the point of that column is to track routine maintenance operations.

regards, tom lane

Monday, October 25, 2010, 4:12:39 PM you wrote:

"Jochen Erwied" <jochen@pgsql.erwied.eu> writes:

VACUUM FULL does not update statistics so display of pg_stat_user_tables is
wrong. A normal VACUUM updates the relevant information.

Hmm. This is a definitional issue: what do we really mean by last_vacuum?
I'm inclined to think that the current behavior is reasonable. VACUUM
FULL is (still) not intended as a routine maintenance operation, and
the point of that column is to track routine maintenance operations.

Well, when reading

http://www.postgresql.org/docs/current/static/monitoring-stats.html

then last_vacuum contains the last time of a user-initiated vacuum. There's
no distinction made what kind of vacuum was made. And IMHO even if VACUUM
FULL isn't meant for routine vacuuming, the state should be changed.

Of course the easiest way to fix this bug (or better flaw) is to change the
documentation :-)

--
Jochen Erwied | home: jochen@erwied.eu +49-208-38800-18, FAX: -19
Sauerbruchstr. 17 | work: joe@mbs-software.de +49-2151-7294-24, FAX: -50
D-45470 Muelheim | mobile: jochen.erwied@vodafone.de +49-173-5404164

Jochen Erwied <jochen@pgsql.erwied.eu> writes:

Monday, October 25, 2010, 4:12:39 PM you wrote:

"Jochen Erwied" <jochen@pgsql.erwied.eu> writes:

VACUUM FULL does not update statistics so display of pg_stat_user_tables is
wrong. A normal VACUUM updates the relevant information.

Hmm. This is a definitional issue: what do we really mean by last_vacuum?
I'm inclined to think that the current behavior is reasonable. VACUUM
FULL is (still) not intended as a routine maintenance operation, and
the point of that column is to track routine maintenance operations.

Well, when reading
http://www.postgresql.org/docs/current/static/monitoring-stats.html
then last_vacuum contains the last time of a user-initiated vacuum. There's
no distinction made what kind of vacuum was made. And IMHO even if VACUUM
FULL isn't meant for routine vacuuming, the state should be changed.

Perhaps. The new implementation of VACUUM FULL is really more like a
CLUSTER, or one of the rewriting variants of ALTER TABLE. Should all
of those operations result in an update of last_vacuum? From an
implementation standpoint it's difficult to say that only some of them
should, because all of them result in a table that has no immediate
need for vacuuming. The only argument I can see for having only VACUUM
FULL update the timestamp is that it's called VACUUM and the others
aren't. Which is an argument, but not a terribly impressive one IMO.

Of course the easiest way to fix this bug (or better flaw) is to change the
documentation :-)

Yeah, that part of the docs will require editing no matter what we do.
I'm just trying to get some clarity on what the most reasonable behavior
is.

regards, tom lane

On Mon, 2010-10-25 at 16:48 +0200, Jochen Erwied wrote:

Well, when reading

http://www.postgresql.org/docs/current/static/monitoring-stats.html

then last_vacuum contains the last time of a user-initiated vacuum.
There's no distinction made what kind of vacuum was made. And IMHO
even if VACUUM FULL isn't meant for routine vacuuming, the state
should be changed.

+1, but I'm not sure whether this might be an appropriate change for 9.0
or not.
-- 
Devrim GÜNDÜZ
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
PostgreSQL RPM Repository: http://yum.pgrpms.org
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz

On Mon, Oct 25, 2010 at 8:03 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Perhaps.  The new implementation of VACUUM FULL is really more like a
CLUSTER, or one of the rewriting variants of ALTER TABLE.  Should all
of those operations result in an update of last_vacuum?  From an
implementation standpoint it's difficult to say that only some of them
should, because all of them result in a table that has no immediate
need for vacuuming.  The only argument I can see for having only VACUUM
FULL update the timestamp is that it's called VACUUM and the others
aren't.  Which is an argument, but not a terribly impressive one IMO.

Perhaps we should have another field last_table_rewrite or something?

--
greg

On Mon, 2010-10-25 at 10:46 -0700, Greg Stark wrote:

Perhaps we should have another field last_table_rewrite or something?

Seems like overkill. And we don't want to make it sound like table
rewrites are expected to be a normal part of maintenance (perhaps that's
just a terminology issue, however).

Regards,
Jeff Davis

On Mon, Oct 25, 2010 at 11:03:07AM -0400, Tom Lane wrote:

Perhaps. The new implementation of VACUUM FULL is really more like a
CLUSTER, or one of the rewriting variants of ALTER TABLE. Should all
of those operations result in an update of last_vacuum? From an
implementation standpoint it's difficult to say that only some of them
should, because all of them result in a table that has no immediate
need for vacuuming. The only argument I can see for having only VACUUM
FULL update the timestamp is that it's called VACUUM and the others
aren't. Which is an argument, but not a terribly impressive one IMO.

I agree it's an unimpressive argument; perhaps it's worth considering that
last_vacuum doesn't really indicate how much a particular table needs
vacuuming, either. Without the update/delete statistics telling you how much
updating and deleting has happened since the last vacuum, there's really no
way of guessing how vacuum-needy something might be based only on available
statistics. last_vacuum is just a nice way of verifying that [auto]vacuum
happens on the table sometimes, and influencing an administrator's WAGs about
what needs vacuuming.

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com

Tom Lane wrote:

Jochen Erwied <jochen@pgsql.erwied.eu> writes:

Monday, October 25, 2010, 4:12:39 PM you wrote:

"Jochen Erwied" <jochen@pgsql.erwied.eu> writes:

VACUUM FULL does not update statistics so display of pg_stat_user_tables is
wrong. A normal VACUUM updates the relevant information.

Hmm. This is a definitional issue: what do we really mean by last_vacuum?
I'm inclined to think that the current behavior is reasonable. VACUUM
FULL is (still) not intended as a routine maintenance operation, and
the point of that column is to track routine maintenance operations.

Well, when reading
http://www.postgresql.org/docs/current/static/monitoring-stats.html
then last_vacuum contains the last time of a user-initiated vacuum. There's
no distinction made what kind of vacuum was made. And IMHO even if VACUUM
FULL isn't meant for routine vacuuming, the state should be changed.

Perhaps. The new implementation of VACUUM FULL is really more like a
CLUSTER, or one of the rewriting variants of ALTER TABLE. Should all
of those operations result in an update of last_vacuum? From an
implementation standpoint it's difficult to say that only some of them
should, because all of them result in a table that has no immediate
need for vacuuming. The only argument I can see for having only VACUUM
FULL update the timestamp is that it's called VACUUM and the others
aren't. Which is an argument, but not a terribly impressive one IMO.

Of course the easiest way to fix this bug (or better flaw) is to change the
documentation :-)

Yeah, that part of the docs will require editing no matter what we do.
I'm just trying to get some clarity on what the most reasonable behavior
is.

I have updated the documentation to say that vacuum statistics and
counts are for non-FULL vacuums; applied patch attached.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +