This is just a repost as a (true) new thread. At Mon, 30 Oct 2017 20:57:50 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI <horiguchi.kyot...@lab.ntt.co.jp> wrote in <20171030.205750.246076862.horiguchi.kyot...@lab.ntt.co.jp> > At Fri, 20 Oct 2017 19:15:16 +0900, Masahiko Sawada <sawada.m...@gmail.com> > wrote in <CAD21AoAkaw-u0feAVN_VrKZA5tvzp7jT=mqcqp-svmegkxh...@mail.gmail.com> > > > n_mod_since_analyze | 20000 > > > + vacuum_requred | true > > > + last_vacuum_oldest_xid | 8023 > > > + last_vacuum_left_to_truncate | 5123 > > > + last_vacuum_truncated | 387 > > > last_vacuum | 2017-10-10 17:21:54.380805+09 > > > last_autovacuum | 2017-10-10 17:21:54.380805+09 > > > + last_autovacuum_status | Killed by lock conflict > > > ... > > > autovacuum_count | 128 > > > + incomplete_autovacuum_count | 53 > > > > > > # The last one might be needless.. > > > > I'm not sure that the above informations will help for users or DBA > > but personally I sometimes want to have the number of index scans of > > the last autovacuum in the pg_stat_user_tables view. That value > > indicates how efficiently vacuums performed and would be a signal to > > increase the setting of autovacuum_work_mem for user. > > Btree and all existing index AMs (except brin) seem to visit the > all pages in every index scan so it would be valuable. Instead > the number of visited index pages during a table scan might be > usable. It is more relevant to performance than the number of > scans, on the other hand it is a bit difficult to get something > worth from the number in a moment. I'll show the number of scans > in the first cut. > > > > Where the "Killed by lock conflict" is one of the followings. > > > > > > - Completed > > > - Truncation skipped > > > - Partially truncated > > > - Skipped > > > - Killed by lock conflict > > > > > > This seems enough to find the cause of a table bloat. The same > > > discussion could be applied to analyze but it might be the > > > another issue. > > > > > > There may be a better way to indicate the vacuum soundness. Any > > > opinions and suggestions are welcome. > > > > > > I'm going to make a patch to do the 'formal' one for the time > > > being.
Done with small modifications. In the attached patch pg_stat_all_tables has the following new columns. Documentations is not provided at this stage. ----- n_mod_since_analyze | 0 + vacuum_required | not requried last_vacuum | last_autovacuum | 2017-10-30 18:51:32.060551+09 last_analyze | last_autoanalyze | 2017-10-30 18:48:33.414711+09 vacuum_count | 0 + last_vacuum_truncated | 0 + last_vacuum_untruncated | 0 + last_vacuum_index_scans | 0 + last_vacuum_oldest_xmin | 2134 + last_vacuum_status | agressive vacuum completed + autovacuum_fail_count | 0 autovacuum_count | 5 analyze_count | 0 autoanalyze_count | 1 ----- Where each column shows the following infomation. + vacuum_required | not requried VACUUM requirement status. Takes the following values. - partial Partial (or normal) will be performed by the next autovacuum. The word "partial" is taken from the comment for vacuum_set_xid_limits. - aggressive Aggressive scan will be performed by the next autovacuum. - required Any type of autovacuum will be performed. The type of scan is unknown because the view failed to take the required lock on the table. (AutoVacuumrequirement()) - not required Next autovacuum won't perform scan on this relation. - not required (lock not acquired) Autovacuum should be disabled and the distance to freeze-limit is not known because required lock is not available. - close to freeze-limit xid Shown while autovacuum is disabled. The table is in the manual vacuum window to avoid anti-wraparound autovacuum. + last_vacuum_truncated | 0 The number of truncated pages in the last completed (auto)vacuum. + last_vacuum_untruncated | 0 The number of pages the last completed (auto)vacuum tried to truncate but could not for some reason. + last_vacuum_index_scans | 0 The number of index scans performed in the last completed (auto)vacuum. + last_vacuum_oldest_xmin | 2134 The oldest xmin used in the last completed (auto)vacuum. + last_vacuum_status | agressive vacuum completed The finish status of the last vacuum. Takes the following values. (pg_stat_get_last_vacuum_status()) - completed The last partial (auto)vacuum is completed. - vacuum full completed The last VACUUM FULL is completed. - aggressive vacuum completed The last aggressive (auto)vacuum is completed. - error while $progress The last vacuum stopped by error while $progress. The $progress one of the vacuum progress phases. - canceled while $progress The last vacuum was canceled while $progress This is caused by user cancellation of manual vacuum or killed by another backend who wants to acquire lock on the relation. - skipped - lock unavailable The last autovacuum on the relation was skipped because required lock was not available. - unvacuumable A past autovacuum tried vacuum on the relation but it is not vacuumable for reasons of ownership or accessibility problem. (Such relations are not shown in pg_stat_all_tables..) + autovacuum_fail_count | 0 The number of successive failure of vacuum on the relation. Reset to zero by completed vacuum. ====== In the patch, vacrelstats if pointed from a static variable and cancel reporting is performed in PG_CATCH() section in vacuum(). Every unthrown error like lock acquisition failure is reported by explicit pgstat_report_vacuum() with the corresponding finish code. Vacuum requirement status is calculated in AutoVacuumRequirment() and returned as a string. Access share lock on the target relation is required but it returns only available values if the lock is not available. I decided to return incomplete (coarse grained) result than wait for a lock that isn't known to be relased in a short time for a perfect result. regards, -- Kyotaro Horiguchi NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers