Обсуждение: BUG #5722: vacuum full does not update last_vacuum statistics
The following bug has been logged online: Bug reference: 5722 Logged by: Jochen Erwied Email address: jochen@pgsql.erwied.eu PostgreSQL version: 9.0.1 Operating system: x86_64-pc-linux-gnu Description: vacuum full does not update last_vacuum statistics Details: VACUUM FULL does not update statistics so display of pg_stat_user_tables is wrong. A normal VACUUM updates the relevant information. Example on a live database: smtpscan=# select * from pg_stat_all_tables where relname='servers_part_226'; -[ RECORD 1 ]----+------------------------------ relid | 30559 schemaname | public relname | servers_part_226 seq_scan | 38 seq_tup_read | 38 idx_scan | 0 idx_tup_fetch | 0 n_tup_ins | 1 n_tup_upd | 0 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 1 n_dead_tup | 0 last_vacuum | last_autovacuum | last_analyze | 2010-10-25 14:17:20.013568+02 last_autoanalyze | smtpscan=# vacuum full servers_part_226; VACUUM smtpscan=# select * from pg_stat_all_tables where relname='servers_part_226'; -[ RECORD 1 ]----+------------------------------ relid | 30559 schemaname | public relname | servers_part_226 seq_scan | 42 seq_tup_read | 42 idx_scan | 0 idx_tup_fetch | 0 n_tup_ins | 1 n_tup_upd | 0 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 1 n_dead_tup | 0 last_vacuum | last_autovacuum | last_analyze | 2010-10-25 14:17:20.013568+02 last_autoanalyze | smtpscan=# vacuum servers_part_226; VACUUM smtpscan=# select * from pg_stat_all_tables where relname='servers_part_226'; -[ RECORD 1 ]----+------------------------------ relid | 30559 schemaname | public relname | servers_part_226 seq_scan | 42 seq_tup_read | 42 idx_scan | 0 idx_tup_fetch | 0 n_tup_ins | 1 n_tup_upd | 0 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 1 n_dead_tup | 0 last_vacuum | 2010-10-25 14:41:18.67515+02 last_autovacuum | last_analyze | 2010-10-25 14:17:20.013568+02 last_autoanalyze |
"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
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
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=20 http://www.postgresql.org/docs/current/static/monitoring-stats.html=20 then last_vacuum contains the last time of a user-initiated vacuum. There's= =20 no distinction made what kind of vacuum was made. And IMHO even if VACUUM= =20 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 :-) --=20 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
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. =A0The new implementation of VACUUM FULL is really more like a > CLUSTER, or one of the rewriting variants of ALTER TABLE. =A0Should all > of those operations result in an update of last_vacuum? =A0From 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. =A0The only argument I can see for having only VACUUM > FULL update the timestamp is that it's called VACUUM and the others > aren't. =A0Which is an argument, but not a terribly impressive one IMO. Perhaps we should have another field last_table_rewrite or something? --=20 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 abo= ut what needs vacuuming.=20 -- 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. + diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 2dc1bfc..aaa613e 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -325,11 +325,11 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re scans, numbers of row insertions, updates, and deletions, number of row updates that were HOT (i.e., no separate index update), numbers of live and dead rows, - the last time the table was vacuumed manually, + the last time the table was non-<option>FULL</> vacuumed manually, the last time it was vacuumed by the autovacuum daemon, the last time it was analyzed manually, the last time it was analyzed by the autovacuum daemon, - number of times it has been vacuumed manually, + number of times it has been non-<option>FULL</> vacuumed manually, number of times it has been vacuumed by the autovacuum daemon, number of times it has been analyzed manually, and the number of times it has been analyzed by the autovacuum daemon. @@ -781,7 +781,7 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re <entry><literal><function>pg_stat_get_last_vacuum_time</function>(<type>oid</type>)</literal></entry> <entry><type>timestamptz</type></entry> <entry> - Time of the last vacuum initiated by the user on this table + Time of the last non-<option>FULL</option> vacuum initiated by the user on this table </entry> </row> @@ -814,7 +814,7 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re <entry><literal><function>pg_stat_get_vacuum_count</function>(<type>oid</type>)</literal></entry> <entry><type>bigint</type></entry> <entry> - The number of times this table has been vacuumed manually + The number of times this table has been non-<option>FULL</> vacuumed manually </entry> </row>