DBのレスポンスが遅い時の調査方法

まず切り分け

「DBが遅い」と言われたら、最初にやるのは本当にDBが遅いのかという確認である。

-- 現在実行中のクエリを確認（MySQL）
SHOW PROCESSLIST;

-- PostgreSQLなら
SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

長時間走っているクエリがあれば、そいつが犯人候補である。なければアプリ側のコネクションプールの枯渇とか、ネットワークレイテンシを疑う。

スロークエリログを見る

本番環境なら、大抵有効化されているはずである。

-- MySQL: スロークエリの設定確認
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

ログの場所は/var/log/mysql/slow.logあたりである。pt-query-digestで集計すると、どのクエリが何回呼ばれて合計何秒食っているか一発でわかる。

pt-query-digest /var/log/mysql/slow.log

EXPLAIN で実行計画を見る

問題のクエリを特定したらEXPLAINで実行計画を見る。

EXPLAIN SELECT * FROM orders
WHERE user_id = 123
AND created_at > '2024-01-01'
ORDER BY created_at DESC
LIMIT 100;

見るべきポイント：

よくある原因と対処

1. インデックスが効いてない

-- インデックスの確認
SHOW INDEX FROM orders;

-- 複合インデックスの順序が重要
-- WHERE user_id = ? AND created_at > ? なら
CREATE INDEX idx_user_created ON orders(user_id, created_at);
-- user_id が先。選択性の高い方を先に。

2. N+1問題

アプリ側のログで同じクエリが大量に流れていたらこれである。ORMのeager loadingで解決する。

# Rails の例
# Bad: N+1
User.all.each { |u| u.posts }

# Good: eager load
User.includes(:posts).each { |u| u.posts }

3. ロック待ち

-- MySQL: ロック状況の確認
SHOW ENGINE INNODB STATUS\G

-- PostgreSQL
SELECT * FROM pg_locks WHERE NOT granted;

長時間トランザクションが開きっぱなしとか、デッドロックとかが原因である。アプリ側でトランザクションの粒度を見直す。

4. 統計情報が古い

古い統計情報を元に、オプティマイザが変な実行計画を選んでいることがある。

-- MySQL
ANALYZE TABLE orders;

-- PostgreSQL
ANALYZE orders;

5. バッファプール/shared_buffers が足りない

-- MySQL: バッファプールのヒット率
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- read_requests に対して reads が多いとディスクI/Oが多い

-- PostgreSQL
SELECT
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as hit_ratio
FROM pg_statio_user_tables;

ヒット率が95%を切っていたら、メモリを増やすことを検討する。

調査の流れまとめ

1. SHOW PROCESSLIST で現在の状況確認
     ↓
2. スロークエリログで犯人特定
     ↓
3. EXPLAIN で実行計画確認
     ↓
4. インデックス追加 or クエリ書き換え or 設定チューニング
     ↓
5. 効果測定

面接で聞かれがちな追加質問

「インデックス張ったのに効かないケースは？」

• カーディナリティが低い（性別とか）
• 関数を使ってる（WHERE YEAR(created_at) = 2024）
• 型の暗黙変換（文字列カラムに数値で検索）
• LIKE の前方一致以外（LIKE '%keyword'）
• OR 条件で片方にインデックスがない

「本番で急に遅くなった原因として何を疑う？」

• データ量の増加で実行計画が変わった
• 統計情報の自動更新タイミング
• ロック競合の増加
• ディスクI/Oの飽和（他のバッチ処理と競合など）
• コネクションプールの枯渇

「読み取りと書き込みどっちが遅い？」

切り分けが重要である。書き込みが遅いならレプリケーション遅延やディスクI/Oを疑う。読み取りだけが遅いならインデックスかキャッシュの問題である。