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.kyotaro@lab.ntt.co.jp> wrote in
<20171030.205750.246076862.horiguchi.kyotaro@lab.ntt.co.jp>
> At Fri, 20 Oct 2017 19:15:16 +0900, Masahiko Sawada <sawada.mshk@gmail.com> wrote in
<CAD21AoAkaw-u0feAVN_VrKZA5tvzp7jT=mQCQP-SvMegKXHHaw@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+09last_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
commentfor 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
therequired 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-wraparoundautovacuum.
+ 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
somereason.
+ 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
progressphases.
- 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
oraccessibility 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 (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers