サーバーが遅い理由 #5 データベースが遅くなるとき — インデックス・ロック・コネクションプール
シリーズの最終回はデータベースです。データベースの遅さには、他のリソースと区別される特徴が一つあります。昨日まで問題なかったシステムが、コードの変更なしに遅くなるという点です。データは毎日成長し、トラフィックのパターンは少しずつ変わるのに、クエリの実行計画とロックとコネクションプールはその変化に階段状に反応するからです。この記事はその段差三つ、インデックスとロックとコネクションプールを順に押さえます。例は PostgreSQL 基準ですが、同じ概念が MySQL など他のエンジンにも名前を替えて適用できます。
インデックス — データの成長がしきい値を超える日 #
インデックスのない検索はテーブル全体を読むフルスキャンになります。重要なのは、フルスキャンが最初は問題ではないという点です。1 万行のテーブルのフルスキャンはミリ秒で終わり、テーブル全体がキャッシュに収まっている間は数十万行でも持ちこたえます。問題はテーブルがキャッシュ(2 編 のワーキングセットの話がそのまま適用されます)を超える日に始まります。フルスキャンがディスク読み取りに変わり、3 編 で見たストレージの遅延がクエリごとに掛け算されます。「データが溜まって遅くなった」の実体は、多くの場合、線形の劣化ではなくこうしたしきい値の突破です。
遅いクエリを見つけたら、判定は EXPLAIN です。
=# EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_email = 'a@example.com';
Seq Scan on orders (cost=0.00..184230.10 rows=3 width=112)
(actual time=2841.334..2841.336 rows=2 loops=1)
Filter: (customer_email = 'a@example.com'::text)
Rows Removed by Filter: 4183921400 万行を読んで 2 行を得る Seq Scan、典型的なインデックス不在です。インデックスを作れば直りますが、実務でより頻繁に遭遇するのはインデックスがあるのに使われないケースです。代表的なパターンは三つあります。
- カラムを加工した条件:
WHERE lower(email) = ...やWHERE created_at::date = ...のようにカラムに関数を適用すると、元のカラムのインデックスは使えません。関数ベースのインデックスを作るか、条件を範囲に書き換えます。 - 型の不一致: 文字列カラムを数値と比較するような暗黙の型変換も同じ効果を生みます。ORM が生成するクエリでよく見られます。
- 古い統計: オプティマイザは統計から計画を選びます。大量ロードや削除の直後に統計が現実とずれていると、問題のないインデックスを差し置いてフルスキャンを選ぶことがあります。
ANALYZEで統計を更新し、autovacuum がそのテーブルで実際に動いているかを確認します。
アプリケーション側では、クエリ一つの速さと同じくらいクエリの数も問題です。一覧ページ一つに N+1 で数百の速いクエリを投げるパターンは、4 編 の往復の掛け算がデータベースで再現されたものです。ORM 視点の N+1 と EXPLAIN の活用は Django 上級 #3 で扱いました。
ロック — 一つのトランザクションが作る待ちのチェーン #
クエリ自体は速いのに特定のリクエストだけ数秒止まるなら、ロック待ちを見ます。行ロックは同じ行を変更しようとするトランザクションを並ばせますが、この行列はチェーンになります。トランザクション A が行を握ったまま長く続くと B が待ち、B が握っている別の行を C が待つ、という形で待ちが伝播します。症状が「特定の機能だけ断続的に止まる」として現れる理由です。
チェーンの元凶はほぼ例外なく長いトランザクションです。トランザクションを開いたまま外部 API を呼ぶコード、バッチ処理が数百万行を一つのトランザクションで更新するケース、人がコンソールで BEGIN だけ打って昼食に出たケースが、すべてここに該当します。DDL にも注意が必要です。ALTER TABLE はテーブルロックを要求するので、長い SELECT 一つがその前をふさぐと、ALTER の後ろにすべてのクエリが並びます。デプロイ中のマイグレーションがサービス全体を止める典型的な経路です。
いま誰が誰をふさいでいるかは、統計ビューですぐ見えます。
=# SELECT pid, state, wait_event_type, now() - xact_start AS xact_age, query
FROM pg_stat_activity WHERE state <> 'idle' ORDER BY xact_age DESC;
pid | state | wait_event_type | xact_age | query
-------+--------+-----------------+-----------+--------------------------
8123 | idle in transaction | | 00:41:02 | UPDATE orders SET ...
9310 | active | Lock | 00:03:11 | UPDATE orders SET ...41 分も idle in transaction の 8123 が元凶です。pg_blocking_pids() でブロック関係を確定し、急ぐ場合は該当バックエンドを終了させてチェーンを切ります。再発防止はコード側です。トランザクション内での外部呼び出しの禁止、バッチは小さい単位でコミット、idle_in_transaction_session_timeout と lock_timeout の設定が基本です。
コネクションプール — データベースは元気なのにアプリが待つケース #
最後の段差はデータベースの外にあります。アプリケーションのコネクションプールが枯渇すると、リクエストは空きコネクションを待って並びます。このときデータベース側の指標はむしろ暇そうに見えます。1 編 で見た「指標に残らない待ち」のデータベース版です。遅いクエリ一つがコネクションを長く占有するとプールが枯渇し、そこからは速いクエリまでプール待ちで一緒に遅くなって、原因が見えにくくなります。
逆方向の失敗もあります。プールが枯渇するからと最大コネクション数をむやみに上げると、同時実行クエリがコア数をはるかに超えた時点から、データベースはコンテキストスイッチとロック競合でむしろ総スループットが落ちます。数千のコネクションを開いたままそれぞれが遅くなるより、プールをコア数の数倍程度に制限してその手前に列を作るほうが、全体としては速いのです。PostgreSQL なら PgBouncer のような外部プーラーで、アプリケーションのインスタンスが増えてもデータベース側の同時実行数を一定に抑える構成が標準です。
診断の信号を整理するとこうです。アプリケーションの「コネクション取得の待ち時間」メトリクスが跳ねているのにデータベースのアクティブクエリ数や CPU が暇なら、プールサイズかコネクションの占有(遅いクエリ・長いトランザクション)の問題です。データベースのアクティブクエリがコア数を大きく超えて飽和しているなら、プールの縮小とクエリ改善が対処です。
診断手順のまとめ #
症状が「データベースが遅くなった」のとき、手順は次のとおりです。
- 遅いクエリの特定 —
pg_stat_statements(またはスロークエリログ)で、時間を最も消費しているクエリを順位で出します。体感の犯人と実際の犯人が違うことは珍しくありません。 EXPLAIN ANALYZE— フルスキャンか、インデックスを使っているか、推定行数と実際の行数がずれていないか(統計の問題)を見ます。pg_stat_activity—idle in transactionと Lock 待ちを確認します。断続的な停止ならこちらが先です。- プールのメトリクスと突き合わせ — アプリのコネクション待ちと DB のアクティブクエリ数を並べて、プール枯渇か DB 飽和かを切り分けます。
- リソースの確認 — ここまでで見つからなければ、2 編(キャッシュ・ワーキングセット)と 3 編(fsync・ストレージ)の診断をデータベースサーバーに適用します。
まとめ — シリーズを閉じながら #
- データベースの遅さは階段状に来ます。データの成長がキャッシュ・統計・計画のしきい値を超えた瞬間、コードの変更なしに始まります。
- インデックスは存在ではなく使用が問題です。カラムの加工、型変換、古い統計が、問題のないインデックスを無力化します。
- ロック待ちの根本原因はほぼ例外なく長いトランザクションです。
pg_stat_activityのidle in transactionから探します。 - コネクションプールの枯渇は、データベースが暇なのにアプリが遅いという形で現れます。この場合はプールを単純に拡大するより、接続の占有時間を短くするのが基本です。
シリーズ全体を貫く結論は一つです。「スペックは足りているのに遅い」の原因はほぼ常にどこかの待ちであり、待ちは平均使用率のグラフには映りません。CPU の前の行列(1 編)、キャッシュの外のワーキングセット(2 編)、同期書き込みと浅いキュー(3 編)、往復の掛け算(4 編)、そしてロックとプール(今回)がその待ちです。症状から出発して待ちが積もる場所を見つければ、対処はたいてい増設ではなく構造の側にあります。