PostgreSQL RDS如何识别IOPS消耗最高的查询语句？

一、基于PostgreSQL内置统计视图精准定位 #

PostgreSQL自带的统计扩展和视图可以直接输出IO消耗维度的量化数据，无需依赖经验推测：

• 优先使用pg_stat_statements扩展（RDS PostgreSQL默认开启），该视图可以直接返回每条查询的磁盘读块数，和IOPS消耗直接对应：
  执行以下查询可以直接拿到消耗IO最高的TOP20语句：

  SELECT 
    queryid,
    query,
    calls,
    total_time / 1000 AS total_exec_seconds,
    blks_read * 8 / 1024 AS total_physical_read_mb, -- 每个数据块默认8KB，换算为MB
    blks_hit / (blks_hit + blks_read + 1e-6) AS cache_hit_ratio,
    blks_read / (calls + 1e-6) AS avg_read_blocks_per_exec
  FROM pg_stat_statements
  ORDER BY blks_read DESC
  LIMIT 20;
  

  其中blks_read是语句累计从磁盘读取的块数，直接对应实例的读IOPS消耗，和执行次数、耗时指标结合即可精准定位高开销查询
• 要定位到具体表、索引级别的IO贡献，可以查询pg_statio系列系统视图：
  表级IO开销查询：

  SELECT 
    relname AS table_name,
    heap_blks_read * 8 / 1024 AS table_physical_read_mb,
    idx_blks_read * 8 / 1024 AS index_physical_read_mb,
    toast_blks_read * 8 / 1024 AS toast_physical_read_mb
  FROM pg_statio_user_tables
  ORDER BY (heap_blks_read + idx_blks_read + toast_blks_read) DESC
  LIMIT 10;
  

  索引级IO开销查询：

  SELECT 
    relname AS index_name,
    idx_blks_read * 8 / 1024 AS index_physical_read_mb
  FROM pg_statio_user_indexes
  ORDER BY idx_blks_read DESC
  LIMIT 10;
  

二、RDS专属工具辅助排查 #

• 开启RDS性能洞察（Performance Insights），选择按等待事件分组，io:datafile read类等待事件关联的SQL就是实时消耗IOPS最高的查询，可直接查看对应SQL内容和执行占比
• 通过RDS监控面板拆分读/写IOPS占比：如果是写IOPS占比过高，可以额外结合pg_stat_statements的shared_blks_dirtied字段，以及pg_stat_user_tables的n_tup_ins/n_tup_upd/n_tup_del指标，定位高频写入的语句和表对象

三、突发IO飙升临时排查方案 #

如果是短时间突发IO打满的场景，可以执行以下查询实时查看当前运行语句的IO消耗：

SELECT 
  pid,
  query,
  now() - query_start AS exec_duration,
  (SELECT blks_read FROM pg_stat_statements WHERE pg_stat_statements.queryid = s.queryid) AS query_total_read_blocks
FROM pg_stat_activity s
WHERE state = 'active'
AND now() - query_start > INTERVAL '1s'
ORDER BY exec_duration DESC;

内容的提问来源于stack exchange，提问作者irregular