PostgreSQL 教程: 监控大表状态

一月 28, 2024

摘要:在本教程中,您将学习如何在 PostgreSQL 中监控大表状态。

哪些是大表?

PostgreSQL 提供了一个专用函数pg_relation_size,用于计算特定表或索引使用的实际磁盘空间。我们只需要提供表名。除了主数据文件之外,还有其他文件(称为分支)可以通过指定第二个可选参数来测算。其中包括可见性映射表(VM)、可用空间映射表(FSM)和无日志表的初始化分支。

下面的基本查询可告诉我们哪 10 个表是最大的:

WITH tables AS (
  SELECT current_database() AS dbname, n.nspname AS schema, c.relname,
         pg_relation_size(c.oid) AS size
  FROM pg_class c, pg_namespace n
  WHERE c.relnamespace = n.oid AND c.relkind = 'r'
  ORDER BY size DESC
  LIMIT 10
)
SELECT dbname, schema, relname,
       pg_size_pretty(size) AS relsize
FROM tables
WHERE size > pg_size_bytes('1 GB');

这些表按大小降序显示,最多显示 10 个。在本例中,我们查看的是当前数据库中的所有表。

表上累计的统计信息

一个简单的select * from pg_stat_user_tables,可显示用户表的访问统计信息,当前数据库中的每个表都有一行,以及关键列:

  • 顺序扫描计数(seq_scan)pg_stat_all_tables视图中的seq_scan列,表示对特定表执行过的顺序扫描的总数。大量的顺序扫描可能表明缺少适当的索引,或者没有充分利用索引。
  • 索引扫描计数(idx_scan):在此表上执行过的索引扫描次数。
  • 插入行数(n_tup_ins):已插入的行数。
  • 更新行数(n_tup_upd):已更新的行数(包括 HOT 更新的行)。
  • 删除行数(n_tup_ins):已删除的行数。
  • HOT 更新计数(n_tup_hot_upd):我们可以使用此列数据,对更新频繁的表计算 HOT 更新比率。此视图中的列 n_tup_updn_tup_hot_upd,表示每个表的普通更新和 HOT 更新的总数。那些具有高 HOT 更新率的表属于“表现好”的表;我们应该注意更改计数高但 HOT 更新速率低或为零的表。对 fillfactor 设置的更改是应对这些表的一般方案,它允许在添加其他行和表扩展时保留可用空间。保留空间的存在可确保在页面内更新行,并大大增加发生 HOT 更新的可能性。
  • 碎片行(n_dead_tup):它显示了数据已删除的死行的大致数量,当我们使用 DELETE 或 UPDATE 等更改操作时,这些死行会被我们的数据源重用。

要在 PostgreSQL 中查找旧表,我们可以运行以下查询:

SELECT
    schemaname, relname,
    seq_scan, idx_Scan,
    last_seq_scan, last_idx_scan,
    pg_size_pretty(pg_total_relation_size(relid)) AS table_size
FROM
    pg_stat_user_tables
ORDER BY last_seq_scan ASC, last_idx_scan ASC
LIMIT 10;

此查询显示按 上次顺序扫描时间戳(第 5 列)上次索引扫描时间戳(第 6 列) 排序的所有表的列表。order by 子句也可以替换为 seq_scan 和 idx_scan 列,以获取很少使用的表的列表。

大表上的统计信息

您可以使用以下查询,来显示有关对大表的访问统计信息:

WITH large_tables AS (
  SELECT c.oid AS relid,
         pg_relation_size(c.oid) AS size
  FROM pg_class c
  WHERE c.relkind = 'r'
  ORDER BY size DESC
  LIMIT 10
)
SELECT current_database() AS dbname,
       stat.schemaname, stat.relname,
       pg_size_pretty(large_tables.size) AS relsize,
       stat.seq_scan, stat.seq_tup_read, stat.idx_scan, stat.idx_tup_fetch,
       stat.n_tup_ins, stat.n_tup_upd, stat.n_tup_del, stat.n_tup_hot_upd,
       stat.n_live_tup, stat.n_dead_tup
FROM large_tables
  JOIN pg_stat_user_tables AS stat
    ON large_tables.relid = stat.relid
WHERE large_tables.size > pg_size_bytes('1 GB');

如果您有一个可以监控 SQL 查询返回的值的系统,那么这很适合它。