根据20个最大的表显示last_autovacuum/anaylze等

Question

我正在尝试监控我们的postgresql数据库，并确定20个最大的表，然后看看最后一次真空和分析发生在什么时候。

我有一个查询，它显示了最大的20个模式名/relname，这很好，这就是我正在寻找的：

SELECT schema_name, relname, pg_size_pretty(table_size) AS size, table_size 
FROM ( SELECT pg_catalog.pg_namespace.nspname  AS schema_name,relname, 
pg_relation_size(pg_catalog.pg_class.oid) AS table_size FROM 
pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON relnamespace = 
pg_catalog.pg_namespace.oid ) t WHERE schema_name NOT LIKE 'pg_%' ORDER BY 
table_size DESC LIMIT 20;

我还有这个查询，它显示了我想要查看的模式名称和relname的所有分析：

select relname, schemaname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_user_tables;

但我真的很难将它们组合到一个查询中，该查询将显示这些分析何时只针对这20个表。

一旦完成，我希望在datadog中的某种图形视图中查看结果，所以如果有人知道如何将此查询作为datadog posgres查询运行，也将是令人惊叹的。

Answer 1

如果有人需要答案，我就是这么做的。它显示了在过去两周内未清理的最大表，但限制了20个结果的列表。

如果您愿意，您可以更改限制20或将其删除为较短/较长列表。还可以更改analyze的pg.last_autovacuum或pg.stat表中您想要检查的任何其他内容，还可以将2周更改为您想要的任何时间段。

SELECT t.nspname || '.' || t.relname AS "relation", size, count(DISTINCT t.relname)
from (SELECT nspname, relname, pg_size_pretty(pg_relation_size(C.oid)) AS "size"
      FROM pg_class C
         LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
      WHERE nspname NOT IN ('pg_catalog', 'information_schema')
      ORDER BY pg_relation_size(C.oid) DESC
      LIMIT 20) t
       left join pg_stat_user_tables pg on pg.relname = t.relname
where pg.last_autovacuum < now() - interval '2 week'
group by relation, size;

Answer 2

在postgres.yaml下的datadog中，我添加了以下内容：

custom_metrics:
- # Last Auto vacuum tables
  query: SELECT t.nspname || '.' || t.relname AS "relation", size, %s from (SELECT nspname , relname , pg_size_pretty(pg_relation_size(C.oid)) AS "size" FROM pg_class C LEFT JOIN pg_namespace N ON ( N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') ORDER BY pg_relation_size(C.oid) DESC LIMIT 20) t left join pg_stat_user_tables pg on pg.relname = t.relname where pg.last_autovacuum < now() - interval '2 week' group by relation, size;
  metrics:
      count(DISTINCT t.relname) last_autovacuum: [postgresql.last_autovacuum, GAUGE]
  relation: false
  descriptors:
      - [relation, relation]
      - [size, size]

然后，我将其添加为仪表板中的顶级列表。您还可以将其设置为警报，并汇总指标中的计数，并决定您希望开始清理的限制，尽管我们只是建议定期运行它，而不仅仅是当它太大时，这就是为什么我们仅将其用作仪表板中的列表。只为了得到眼睛。