Re: [HACKERS] More stats about skipped vacuums
This is just a repost as a (true) new thread. At Mon, 30 Oct 2017 20:57:50 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI wrote in <[email protected]> > At Fri, 20 Oct 2017 19:15:16 +0900, Masahiko Sawada > wrote in > > > n_mod_since_analyze | 2 > > > + 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 bac
Re: [HACKERS] More stats about skipped vacuums
At Thu, 26 Oct 2017 15:06:30 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI wrote in <[email protected]> > At Fri, 20 Oct 2017 19:15:16 +0900, Masahiko Sawada > wrote in > > > n_mod_since_analyze | 2 > > > + 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 rela
Re: [HACKERS] More stats about skipped vacuums
Mmm. I've failed to create a brand-new thread.. Thank you for the comment. At Fri, 20 Oct 2017 19:15:16 +0900, Masahiko Sawada wrote in > On Tue, Oct 10, 2017 at 7:26 PM, Kyotaro HORIGUCHI > wrote: > > Hello. > > Once in a while I am asked about table bloat. In most cases the > > cause is long lasting transactions and vacuum canceling in some > > cases. Whatever the case users don't have enough clues to why > > they have bloated tables. > > > > At the top of the annoyances list for users would be that they > > cannot know whether autovacuum decided that a table needs vacuum > > or not. I suppose that it could be shown in pg_stat_*_tables. > > > > n_mod_since_analyze | 2 > > + vacuum_requred | true > > last_vacuum | 2017-10-10 17:21:54.380805+09 > > > > If vacuum_required remains true for a certain time, it means that > > vacuuming stopped halfway or someone is killing it repeatedly. > > That status could be shown in the same view. > > Because the table statistics is updated at end of the vacuum I think > that the autovacuum will process the table at the next cycle if it has > stopped halfway or has killed. So you mean that vacuum_required is for > uses who want to reclaim garbage without wait for autovacuum retry? It could be used for the purpose and for just knowing that a table is left for a long time needing a vacuum and it would be a trigger for users to take measures to deal with the situation. > > n_mod_since_analyze | 2 > > + vacuum_requred | true > > 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 > > > > Where the "Killed by lock conflict" would be one of the followings. > > > > - Completed (oldest xmin = 8023) > > - May not be fully truncated (yielded at 1324 of 6447 expected) > > - Truncation skipped > > - Skipped by lock failure > > - Killed by lock conflict > > > > > > If we want more formal expression, we can show the values in the > > following shape. And adding some more values could be useful. > > > > n_mod_since_analyze | 2 > > + 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. regards, -- Kyotaro Horiguchi NTT Open Source Software Center -- Sent via pgsql-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] More stats about skipped vacuums
On Tue, Oct 10, 2017 at 7:26 PM, Kyotaro HORIGUCHI wrote: > Hello. > Once in a while I am asked about table bloat. In most cases the > cause is long lasting transactions and vacuum canceling in some > cases. Whatever the case users don't have enough clues to why > they have bloated tables. > > At the top of the annoyances list for users would be that they > cannot know whether autovacuum decided that a table needs vacuum > or not. I suppose that it could be shown in pg_stat_*_tables. > > n_mod_since_analyze | 2 > + vacuum_requred | true > last_vacuum | 2017-10-10 17:21:54.380805+09 > > If vacuum_required remains true for a certain time, it means that > vacuuming stopped halfway or someone is killing it repeatedly. > That status could be shown in the same view. Because the table statistics is updated at end of the vacuum I think that the autovacuum will process the table at the next cycle if it has stopped halfway or has killed. So you mean that vacuum_required is for uses who want to reclaim garbage without wait for autovacuum retry? > n_mod_since_analyze | 2 > + vacuum_requred | true > 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 > > Where the "Killed by lock conflict" would be one of the followings. > > - Completed (oldest xmin = 8023) > - May not be fully truncated (yielded at 1324 of 6447 expected) > - Truncation skipped > - Skipped by lock failure > - Killed by lock conflict > > > If we want more formal expression, we can show the values in the > following shape. And adding some more values could be useful. > > n_mod_since_analyze | 2 > + 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. > 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. > Regards, -- Masahiko Sawada NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
