| title | description | ms.service | ms.subservice | ms.topic | ms.author | ms.date |
|---|---|---|---|---|---|---|
Azure Database for PostgreSQL Flexible Server - Autovacuum metrics (Preview) |
Metrics to monitor Auto Vacuum |
postgresql |
flexible-server |
conceptual |
varun.dhawan |
12/01/2022 |
Autovacuum is a daemon or background utility process offered by PostgreSQL to users to issue a regular clean-up of redundant data in the database and server. It does not require the user to manually issue the vacuuming and instead, is defined in the postgresql.conf file. In order to access this file, simply direct yourself to the following directory on your terminal and then open the file in a suitable editor. For more details on autovacuum, refer postgres docs
Typically once you have enabled autovacuuming in Azure Database for Postgres Flexible Server, customers don't need to think about how or when the vacuuming is performed, as the process is automatically handled by the database. However on a busy server with high transactional workload, vacuuming schedules may not be able to keep up with the pace of the changes. In worse sistuations, vacuum processes may not be running at all, that in turn will negatively impact database performance and resource usage. With Autovacuum metrics, our goal is to expose some key autovacuum metrics, that will help our customers to ensure vacuuming is working as expected.
We are exposing 12 new metrics that customers can use to investigate and resolve issues that prevent VACUUMs from running efficiently. Each metric is emitted at a 30 minute frequency, and has up to 93 days of retenion. Customers can configure alerts on the metrics and can also access the new metrics dimensions, to split and filter the metrics data on database name.
- Autovacuum metrics are disabled by default
- To enable these metrics, please turn ON the server parameter
metrics.autovacuum_diagnostics- This parameter is dynamic, hence will not require instance restart.
| Display Name | Metric ID | Unit | Description | Dimension | Default enabled |
|---|---|---|---|---|---|
| Analyze Counter User Tables (Preview) | analyze_count_user_tables | Count | Number of times user only tables have been manually analyzed in this database | DatabaseName | No |
| AutoAnalyze Counter User Tables (Preview) | autoanalyze_count_user_tables | Count | Number of times user only tables have been analyzed by the autovacuum daemon in this database | DatabaseName | No |
| AutoVacuum Counter User Tables (Preview) | autovacuum_count_user_tables | Count | Number of times user only tables have been vacuumed by the autovacuum daemon in this database | DatabaseName | No |
| Estimated Dead Rows User Tables (Preview) | n_dead_tup_user_tables | Count | Estimated number of dead rows for user only tables in this database | DatabaseName | No |
| Estimated Live Rows User Tables (Preview) | n_live_tup_user_tables | Count | Estimated number of live rows for user only tables in this database | DatabaseName | No |
| Estimated Modifications User Tables (Preview) | n_mod_since_analyze_user_tables | Count | Estimated number of rows modified since user only tables were last analyzed | DatabaseName | No |
| User Tables Analyzed (Preview) | tables_analyzed_user_tables | Count | Number of user only tables that have been analyzed in this database | DatabaseName | No |
| User Tables AutoAnalyzed (Preview) | tables_autoanalyzed_user_tables | Count | Number of user only tables that have been analyzed by the autovacuum daemon in this database | DatabaseName | No |
| User Tables AutoVacuumed (Preview) | tables_autovacuumed_user_tables | Count | Number of user only tables that have been vacuumed by the autovacuum daemon in this database | DatabaseName | No |
| User Tables Counter (Preview) | tables_counter_user_tables | Count | Number of user only tables in this database | DatabaseName | No |
| User Tables Vacuumed (Preview) | tables_vacuumed_user_tables | Count | Number of user only tables that have been vacuumed in this database | DatabaseName | No |
| Vacuum Counter User Tables (Preview) | vacuum_count_user_tables | Count | Number of times user only tables have been manually vacuumed in this database (not counting VACUUM FULL) | DatabaseName | No |
Postgres Vacuuming Basics - https://www.postgresql.org/docs/current/routine-vacuuming.html
- Engineering - Sergiu.Ernu@microsoft.com
- Product - Varun.Dhawan@microsoft.com