Uploaded image for project: 'Percona Monitoring and Management'
  1. Percona Monitoring and Management
  2. PMM-5055

Implement PostgreSQL Auto-Vacuum Details Dashboard

    XMLWordPrintable

    Details

    • Needs Doc:
      Yes

      Description

      Problem Statement

      Provide a visual representation of whether your table(s) needs to be Vacuum'ed (defragmented) in order to improve the performance of the database.

      Solution

      Please create a new PostgreSQL Auto-Vacuum Details dashboard showing the following 7 concepts:

      1. Database Age
      2. Tables Age
      3. Vacuum Progress
      4. Never Vacuum'ed Tables
      5. Never Analyzed Tables
      6. Mostly Vacuum'ed Tables (Manual and auto)
      7. Mostly ANALYZE'd Tables (Manual and autoanalyze)

      Actions

      1. Extend Exporter
      2. Build dashboard
      3. Develop Tooltips

      Database Age

      pg_database (Highlight in Red, if database age is more than parameter : autovacuum_freeze_max_age)

      SELECT datname, age(datfrozenxid) FROM pg_database;

      Tables Age

      For each database, run this….
      (Highlight a Table in Red if its age is more than autovacuum_freeze_max_age)

      select n.nspname as schema, c.relname as table, age(c.relfrozenxid) as xid_age, (pg_table_size(c.oid)) as table_size FROM pg_class c, pg_namespace n where n.oid = c.relnamespace and c.relkind = 'r';

      Vacuum Progress

      pg_stat_progress_vacuum

      SELECT
      {{ p.pid,}}
      {{ now() - a.xact_start AS duration,}}
      {{ coalesce(wait_event_type ||'.'|| wait_event, 'f') AS waiting,}}
      {{ CASE}}
      {{ WHEN a.query ~ '^autovacuum.*to prevent wraparound' THEN 'wraparound' }}
      {{ WHEN a.query ~ '^vacuum' THEN 'user'}}
      {{ ELSE 'regular'}}
      {{ END AS mode,}}
      {{ p.datname AS database,}}
      {{ p.relid::regclass AS table,}}
      {{ p.phase,}}
      {{ pg_size_pretty(p.heap_blks_total * current_setting('block_size')::int) AS table_size,}}
      {{ pg_size_pretty(pg_total_relation_size(relid)) AS total_size,}}
      {{ pg_size_pretty(p.heap_blks_scanned * current_setting('block_size')::int) AS scanned,}}
      {{ pg_size_pretty(p.heap_blks_vacuumed * current_setting('block_size')::int) AS vacuumed,}}
      {{ round(100.0 * p.heap_blks_scanned / p.heap_blks_total, 1) AS scanned_pct,}}
      {{ round(100.0 * p.heap_blks_vacuumed / p.heap_blks_total, 1) AS vacuumed_pct,}}
      {{ p.index_vacuum_count,}}
      {{ round(100.0 * p.num_dead_tuples / p.max_dead_tuples,1) AS dead_pct}}
      {{ FROM pg_stat_progress_vacuum p}}
      {{ JOIN pg_stat_activity a using (pid);}}

      Example Output

      [ RECORD 1 ]----+----------------
      {{ pid | 7719}}
      {{ duration | 00:00:00.81071}}
      {{ waiting | f}}
      {{ mode | regular}}
      {{ database | postgres}}
      {{ table | pgbench_accounts}}
      {{ phase | vacuuming heap}}
      {{ table_size | 131 MB}}
      {{ total_size | 153 MB}}
      {{ scanned | 131 MB}}
      {{ vacuumed | 75 MB}}
      {{ scanned_pct | 100.0}}
      {{ vacuumed_pct | 57.3}}
      {{ index_vacuum_count | 1}}
      {{ dead_pct | 0.2}}

      Never Vacuum’ed Tables

      View : pg_stat_user_tables

      select vacuum_count, autovacuum_count, (vacuum_count + autovacuum_count), n_dead_tup from pg_stat_user_tables where (vacuum_count + autovacuum_count) = 0 order by n_dead_tup desc;

      Never Analyzed Tables

      View : pg_stat_user_tables

      select analyze_count, autoanalyze_count, (analyze_count + autoanalyze_count), (n_tup_ins + n_tup_upd + n_tup_del) as total_dmls from pg_stat_user_tables where (analyze_count + autoanalyze_count) = 0 order by (n_tup_ins + n_tup_upd + n_tup_del) desc;

      Mostly VACCUM’ed tables (Manual + Autovacuum)

      View : pg_stat_user_tables

      select vacuum_count, autovacuum_count, (vacuum_count + autovacuum_count) as total_count, n_dead_tup from pg_stat_user_tables order by (vacuum_count + autovacuum_count) desc;

      Mostly ANALYZED’ed Tables (Manual + Autoanalyze)

      View : pg_stat_user_tables

      select analyze_count, autoanalyze_count, (analyze_count + autoanalyze_count) as total_count, (n_tup_ins + n_tup_upd + n_tup_del) as total_dmls from pg_stat_user_tables order by (analyze_count + autoanalyze_count) desc;

        Smart Checklist

          Attachments

            Issue Links

              Activity

                People

                Assignee:
                Unassigned
                Reporter:
                michael.coburn@percona.com Michael Coburn
                Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                  Dates

                  Created:
                  Updated: