TT Lab
はじめる
学ぶ 学習パス コース

PostgreSQL障害対応

稼働中のデータベースを診断する

TT Labで続きを見る

目標

3つの症状が同時に出ているデータベースを受け取り、それぞれの原因を数字で切り分けて、最後に診断書1枚にまとめます。直すことではなく診断が、このラボの課題です。

なぜ重要なのか

データベースはたいてい落ちません。起動したまま異常になり、そのとき症状が見える場所と原因がある場所は、ほとんどいつも違います。ブロックされたセッションが指すpidは、そのpid自身もブロックされた被害者であり、遅くなったクエリの原因はそのクエリにはなく、小さくならないテーブルの原因はそのテーブルの中にありません。

そのためこのラボは、コマンドを暗記させません。証拠を取り出して書き出す作業だけを求め、採点ツールはその数字を今生きているデータベースから再度取得して照合します。どう見つけたかは自由で、診断が合っていれば通過します。

環境

このPodはpostgresアカウントで動きます。psqlと入力するだけで、labdbにそのまま接続できます。

export PATH=/usr/lib/postgresql/16/bin:$PATH
export PGHOST=127.0.0.1 PGUSER=lab PGDATABASE=labdb
psql

成果物はすべて/root/inc/の下に置きます。先にmkdir -p /root/incを実行してください。

複数のセッションが必要です。ターミナルが1つしかないため、バックグラウンドで起動します。次の形が、トランザクションを開いたまま遊ぶセッションを作ります。stdinを開いたままにしておくと、psqlが次のコマンドを待ってidle in transactionのまま残ります。

( { printf 'begin;\nupdate orders set status = status where id = 1;\n'; sleep 3600; } \
    | PGAPPNAME=nightly-batch psql -X -q ) >/dev/null 2>&1 </dev/null &

3つのリダイレクトをすべて付けてください。1つでも欠けると、シェルがそのセッションを待ってしまいます。

ステップ

  1. 事故の再現 + アクティビティのスナップショット → /root/inc/01-activity.txt
  2. ロックチェーンの根元 → /root/inc/02-root.txt
  3. 待機が消費するコネクションとlock_timeout → /root/inc/03-waiting.txt
  4. 崩れた実行計画 → /root/inc/04-plan.txt
  5. 統計情報を直したあと → /root/inc/05-stats.txt
  6. デッドタプルとvacuumの答え → /root/inc/06-bloat.txt
  7. ホライズンを握っているセッション → /root/inc/07-horizon.txt
  8. 診断書 → /root/inc/08-report.md

参考

事故を再現し、1枚に写し取る

事故の再現 + アクティビティのスナップショット → /root/inc/01-activity.txt

まず事故を作ります。eventsテーブルにtenant 41を6万件ロードし、ANALYZEは実行せず、ordersを更新してコミットしないセッションを1つと、同じ行を更新するセッションを2つ起動してください。

トランザクションを開いたまま遊ぶセッションは、stdinを開いたままにすると作れます: ( { printf 'begin;\nupdate orders set status = status where id = 1;\n'; sleep 3600; } | PGAPPNAME=nightly-batch psql -X -q ) >/dev/null 2>&1 </dev/null &

そのあと、pg_stat_activityを丸ごと/root/inc/01-activity.txtに保存します。state・wait_event_type・pg_blocking_pidsを必ず含めてください。

チェーンの根元を探す

ロックチェーンの根元 → /root/inc/02-root.txt

ブロックされたセッションが指すpidは、そのpid自身もブロックされた被害者かもしれません。他人をブロックしていて、自分は誰にもブロックされていないpidを探してください。

unnest(pg_blocking_pids(pid))で展開したあと、cardinality(pg_blocking_pids(b)) = 0のものだけを残せば求められます。

/root/inc/02-root.txtにroot_pid=・root_app=・root_state=の3行を書いてください。採点ツールは、この3つの値を今生きているデータベースから再度取得して照合します。

待機が何を消費しているか

待機が消費するコネクションとlock_timeout → /root/inc/03-waiting.txt

ブロックされているセッションの数を数え(cardinality(pg_blocking_pids(pid)) > 0)、show max_connectionsの結果と一緒に書いてください。それらのセッションは遅いのではなく、コネクションを1つずつ握ったまま止まっています。

そのあと、set lock_timeout = '1s';を設定して、同じ行を更新してみてください。無限に待つ代わりに、1秒でエラーとして戻ります。そのエラーの行をそのままファイルに残してください。

ファイル: /root/inc/03-waiting.txt

変わっていないクエリが遅くなった

崩れた実行計画 → /root/inc/04-plan.txt

eventsをcustomersと結合して、tenant 41を集計するクエリを、EXPLAIN (ANALYZE, BUFFERS)で実行してください。数秒かかります。それがこのステップの要点です。

予想と実際の行数は、並列を無効にして別に測るほうが正確です。loopsが1でなければ、表示された値が1回あたりの平均だからです: set max_parallel_workers_per_gather = 0;のあとに、explain (analyze) select count(*) from events where tenant_id = 41を実行します

/root/inc/04-plan.txtにest_rows=・actual_rows=・exec_ms=を書き、計画の全文も一緒に残してください。このステップでは、まだANALYZEを実行しないでください。

直すべきはクエリではなかった

統計情報を直したあと → /root/inc/05-stats.txt

インデックスは作らず、analyze eventsだけを実行してください。そして、ステップ4とまったく同じように、もう一度測ってください。

/root/inc/05-stats.txtにest_rows_after=・exec_ms_after=・join_after=を書き、計画の全文を残してください。採点ツールは、自分で計画を取り直して、オプティマイザーの予想が実際と合っているかを確認するため、統計情報を直さないと、何を書いても通過できません。

更新したのにテーブルが大きくなった

デッドタプルとvacuumの答え → /root/inc/06-bloat.txt

update events set kind = 'view' where tenant_id = 41のように1つのカラムだけを変更し、更新の前後のpg_total_relation_size('events')をバイト単位で測ってください。そのあとvacuum (verbose) eventsを実行すると、vacuumが自分で理由を教えてくれます。

/root/inc/06-bloat.txtにdead_tuples=・removable_cutoff=・size_before=・size_after=を書き、vacuumの出力も一緒に残してください。cutoffを作り話にしてはいけません。採点ツールが、今開いているトランザクションと照合します。

クリーンアップを妨げているもの

ホライズンを握っているセッション → /root/inc/07-horizon.txt

ステップ6のremovable cutoffを握っているセッションを探してください。backend_xminだけを調べても見つかりません。トランザクションを開いたまま遊んでいる書き込みセッションはxminが空で、肝心のホライズンは、そのセッションのbackend_xidが握っています。

where backend_xid is not null order by age(backend_xid) desc limit 1が答えを返します。

/root/inc/07-horizon.txtにholder_pid=・holder_xid=・holder_app=を書いてください。holder_xidはステップ6のcutoffと同じ値のはずで、holder_pidはステップ2で見つけたpidと比べてみてください。

診断書1枚

診断書 → /root/inc/08-report.md

前の7つのステップで取り出した数字をまとめて、/root/inc/08-report.mdに診断書を書きます。증상・원인・조치の節を設け、チェーンの根元のpid、vacuumのcutoffの値、実際の行数を数字のまま入れてください(3つのコードは順に、韓国語で「症状」「原因」「対処」を意味する語です)。

核心は、3つの症状のうち2つは同じ原因で、1つは別物だと切り分けることです。再発防止には、lock_timeoutに必ず言及してください。採点ツールは、診断書に書かれた数字を今生きているデータベースと照合します。