Re: POC: track vacuum/analyze cumulative time per relation

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 <samimseih(at)gmail(dot)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
>