pg_stat_ioでI/Oの発生元を分析(バックエンドの種類、オブジェクト、コンテキスト)

コマンド

SELECT backend_type,
       object,
       context,
       reads,
       pg_size_pretty(reads * op_bytes) AS read_size,
       writes,
       pg_size_pretty(writes * op_bytes) AS write_size,
       extends,
       hits,
       round(100.0 * hits / NULLIF(hits + reads, 0), 1) AS hit_pct,
       evictions,
       reuses,
       fsyncs,
       round(read_time::numeric, 1) AS read_ms,
       round(write_time::numeric, 1) AS write_ms
FROM pg_stat_io
WHERE coalesce(reads, 0) + coalesce(writes, 0) + coalesce(extends, 0) + coalesce(evictions, 0) > 0
ORDER BY coalesce(reads, 0) + coalesce(writes, 0) DESC;

-- Who writes? Ideally most writes come from the checkpointer and background writer
SELECT backend_type,
       pg_size_pretty(sum(writes * op_bytes)) AS written,
       round(100.0 * sum(writes) / NULLIF(sum(sum(writes)) OVER (), 0), 1) AS pct_of_writes,
       sum(fsyncs) AS fsyncs
FROM pg_stat_io
WHERE object = 'relation' AND writes > 0
GROUP BY backend_type
ORDER BY sum(writes) DESC;

概要

バックエンドの種類(client backend、autovacuum、checkpointer...)、オブジェクト(relation/temp relation)、コンテキスト(normal、vacuum、bulkread、bulkwrite)別に、読み取り、書き込み、拡張、追い出し、fsyncを表示する。書き込みがどのプロセスの種類から来ているかも表示する。

使用する場面

I/Oボトルネック時に、負荷がクエリ、autovacuum、チェックポイントのどれから来ているかを判別するため、およびshared_buffers/bgwriterの設定の検証に。

注意

pg_stat_ioはクラスタ全体の値で、最後のリセット以降の累積値。変化を見るには2つのサンプルの差を取る(リセットするには: SELECT pg_stat_reset_shared('io');)。read_time/write_timeはtrack_io_timing = onの場合のみ設定される。readsにはOSのページキャッシュから提供された読み取りも含まれる。client backendの書き込みや追い出しが多い場合、バックエンドが空きを作るために自らバッファを書き込んでいることを意味する。shared_buffers、bgwriter_lru_maxpages、チェックポイントの設定を見直す。context = 'vacuum'/'bulkread'の行はリングバッファの使用を反映しており、そこでのreusesは正常。