I/O事件持续出现且较多,出现I/O瓶颈,需进行具体查询分析。

遇到I/O瓶颈时,可通过以下三种方式来进行分析观察:
  • 观察 polar_stat_activity_rt中哪类进程I/O比较高。
    SELECT
        backend_type,
        SUM(local_read_iops   local_write_iops) iops,
        SUM(local_read_iothroughput   local_write_iothroughput) throughput,
        SUM(local_read_latency local_write_latency) latency 
    FROM polar_stat_activity_rt 
    GROUP BY
        backend_type 
    ORDER BY
        iops 
    DESC; \watch 1
    • 如果是client backend进程I/O高,则可进一步通过polar_stat_activity查看是哪些SQL和等待事件造成I/O高。
      SELECT 
          query, COUNT(*) AS wait_count
      FROM polar_stat_activity 
      WHERE state='active' AND backend_type='client backend' AND wait_event_type = 'IO'
      GROUP BY query
      ORDER BY wait_count DESC;\watch 1
    • 如果是checkpoint进程I/O高,说明写入数据量太大。
    • 如果是bgwriter进程I/O高,说明存储写入太慢。
    • 如果是vacuum进程,可以进一步通过观察pg_stat_progress_vacuum查看清理表的进度。
      SELECT * FROM pg_stat_progress_vacuum;
  • 可以通过观察polar_stat_io_info视图,按照文件类型为维度,记录I/O监控信息,具体内容请参见polar_stat_io_info
  • 可以通过观察polar_stat_io_latency视图来分析问题,该视图按照文件操作类型为维度,记录各类文件I/O操作的延时信息。具体内容请参见polar_stat_io_latency