Hi Sami Thank you for your path，it seems some path monitor vacuum status，Can we synthesize their good ideas together。
On Fri, 3 Jan 2025 at 02:24, Sami Imseih <samims...@gmail.com> wrote: > Hi, > > After a recent question regarding tracking vacuum start_time in > pg_stat_all_tables [1], it dawned on me that this view is missing > an important cumulative metric, which is how much time is spent > performing vacuum per table. > > Currently, the only way a user can get this > information is if they enable autovacuum logging or track timing > for manual vacuums. Even then, if a user wants to trend > the time spent vacuuming over time, they must store the > timing data somewhere and perform the calculations. > > Also, unless autovacuum logging is enabled for all a/v > operations, they could have gaps in their analysis. > > Having the total (auto)vacuum elapsed time > along side the existing (auto)vaccum_count > allows a user to track the average time an > operating overtime and to find vacuum tuning > opportunities. > > The same can also be said for (auto)analyze. > > attached a patch ( without doc changes) > that adds 4 new columns: > > total_autovacuum_time > total_vacuum_time > total_autoanalyze_time > total_analyze_time > > Below is an example of output and how it > can be used to derive the average vacuum > operation time. > > postgres=# select > relname, > autovacuum_count, > total_autovacuum_time, > total_autovacuum_time/NULLIF(autovacuum_count,0) average_autovac_time, > vacuum_count, > total_vacuum_time, > total_vacuum_time/NULLIF(vacuum_count,0) average_vac_time > from pg_catalog.pg_stat_all_tables > where relname = 'pgbench_history'; > -[ RECORD 1 ]---------+----------------- > relname | pgbench_history > autovacuum_count | 3 > total_autovacuum_time | 1689 > average_autovac_time | 563 > vacuum_count | 1 > total_vacuum_time | 1 > average_vac_time | 1 > > It should be noted that the timing is only tracked > when the vacuum or analyze operation completes, > as is the case with the other metrics. > > Also, there is another discussion in-flight [2] regarding > tracking vacuum run history in a view, but this serves a > different purpose as this will provide all the metrics > that are other wise exposed in vacuum logging > via sql. This history will also be required to drop > entries using some criteria to keep the cache from > growing infinitely. > > Feedback for the attached patch is appreciated! > > Regards, > > Sami Imseih > Amazon Web Services (AWS) > > [1] > https://www.postgresql.org/message-id/flat/CAGjGUAKQ4UBNdkjunH2qLsdUVG-3F9gCuG0Kb0hToo%2BuMmSteQ%40mail.gmail.com > [2] > https://www.postgresql.org/message-id/flat/b68ab452-c41f-4d04-893f-eaab84f1855b%40vondra.me >
