キャパシティプランニングと肥大化監査

予測する対象

ディスクの増加

SELECT pg_size_pretty(pg_database_size(current_database()));
SELECT pg_size_pretty(pg_total_relation_size(t.oid)) AS total,
       relname
FROM pg_class t
WHERE relkind = 'r'
ORDER BY pg_total_relation_size(t.oid) DESC LIMIT 20;

テーブルごとの増加を追跡する

INSERT INTO size_history (ts, tablename, size_bytes)
SELECT NOW(), relname, pg_total_relation_size(oid)
FROM pg_class WHERE relkind = 'r';

IOPSの推定

SELECT relname, seq_tup_read, idx_tup_fetch,
       seq_tup_read + idx_tup_fetch AS total_reads
FROM pg_stat_user_tables
ORDER BY total_reads DESC LIMIT 20;

RAMのサイズ設定

Bloatの監査

SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup, 0), 2) AS dead_ratio,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

インデックスのBloat

CREATE EXTENSION pgstattuple;

SELECT relname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size,
       (pgstatindex(indexrelid::regclass)).leaf_fragmentation
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC LIMIT 20;

未使用のインデックス

SELECT schemaname, relname, indexrelname,
       pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

接続の監査

SELECT datname, usename, application_name, state, COUNT(*)
FROM pg_stat_activity
GROUP BY 1,2,3,4 ORDER BY 5 DESC;

長時間のトランザクション

SELECT pid, state, xact_start, NOW() - xact_start AS xact_age, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_age DESC NULLS LAST LIMIT 20;

WALとアーカイブ

SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0')) AS total_wal_generated;

フェイルオーバーに備える

まとめ

確認問題