クエリはそのままなのに計画が変わった
一言でいうと
クエリもインデックスも変わらないのに、ある日突然遅くなったなら、変わったのはオプティマイザーが持っている数字です。そして、テーブルが小さくならないなら、削除するものがないからではなく、削除してよいかがまだわからないからです。
なぜ必要なのか
集計APIが1つ、昨日までは40ミリ秒だったのに、今日は6秒です。デプロイはありませんでした。インデックスもそのままです。データは増えましたが、3倍ほどで、100倍ではありません。
こういうときに最もよくある対応がインデックスをもう1つ作ることですが、たいていそれは答えではありません。実行計画を開いてみると、理由が1行に書かれています。
どう動くのか
EXPLAIN (ANALYZE, BUFFERS)を付けると、計画ノードごとに2組の数字が出ます。前の括弧のrows=は予想、後ろの括弧のrows=は実際です。ラボ環境でそのまま取ったものです。
Aggregate (cost=1988.00..1988.01 rows=1 width=8) (actual time=8.282..8.282 rows=1 loops=1)
Buffers: shared hit=988
-> Seq Scan on events (cost=0.00..1988.00 rows=1 width=0) (actual time=0.925..5.801 rows=60000 loops=1)
Filter: (tenant_id = 41)
Rows Removed by Filter: 20000
予想は1行、実際は60000行。この1行が、その日の事故をすべて説明します。オプティマイザーは、この条件が1行しか返さないと信じていたため、その1行を相手のテーブルのすべての行と突き合わせるネステッドループを選びました。実際には6万行が出たので、そのループが6万回まわりました。
なぜ1と見積もったのか。新しいテナントを大量にロードしてANALYZEを実行しなかったからです。pg_statsにはロード前の分布が残っていて、そこにtenant_id = 41は存在しません。存在しない値を尋ねられると、オプティマイザーは「ほとんどない」と答えます。
同じ環境で測った値です。
통계가 낡은 상태 Nested Loop 실행 3059 ms
analyze events;
통계를 고친 뒤 Hash Join 실행 66 ms
変えたのはクエリでもインデックスでもなく、オプティマイザーが持つ数字だけです。前の数字は、何度か測るうちに1.5秒から6秒の間で揺れました。絶対値ではなく、比率を見るのが正しい読み方です。
読むときの落とし穴が1つあります。計画でloops=が1でなければ、表示されたrowsと時間は1回あたりの平均です。並列計画ではワーカーごとに1回ずつまわるため、rows=100000 loops=2は、実際には20万行という意味です。紛らわしい場面なので、数字を正確に読む必要があるときは、set max_parallel_workers_per_gather = 0で並列を無効にして、もう一度取得するほうがよいです。
インデックスを足せば良くなるのか
同じ事故に対して、events(tenant_id)のインデックスを作って、もう一度測ってみました。以下は20万行のテーブルで測った値です。
인덱스 없음 · 통계 낡음 9774 ms Nested Loop / Seq Scan 예상 1행
인덱스 있음 · 통계 낡음 205 ms Nested Loop / Index Scan 예상 1행
인덱스 없음 · 통계 정상 60 ms Hash Join / Seq Scan 예상 234564행
インデックスは確かに役に立ちます。9.7秒が0.2秒になりました。ところが計画は依然として間違っています。予想は1行のままで、オプティマイザーは相変わらずネステッドループを選んでいます。統計情報を直したほうが、インデックスなしでも3倍以上速いのです。
これが、インデックスを先にいじってはいけない理由です。症状は軽くなりますが原因はそのまま残り、インデックスは書き込みのたびにコストを課します。次に同じテーブルへまた大量ロードが入れば、同じことが繰り返されます。
付け加えると、インデックスがいつも損だという話も事実ではありません。インデックスを置いたまま統計情報を直したところ、オプティマイザーはインデックスを使わないことにして46ミリ秒でしたが、enable_seqscan = offで無理やり使わせたところ、33ミリ秒でむしろ速くなりました。テーブルがすべてshared_buffersの中にあり、探す行が物理的にまとまっていたからです。「インデックスがあれば速い」も「大きな結果にはインデックスが損だ」も、その場で測ってみるまではわかりません。計画の予想と実際をまず合わせておき、そのあとで測るのが順序です。
更新したのにテーブルはなぜ大きくなるのか
PostgreSQLは行を書き換えません。新しいバージョンを書き、古いバージョンに「このトランザクション以降は見えない」という印を残します。そのためUPDATEは事実上挿入であり、DELETEも場所を空けません。残った古いバージョンがデッドタプルです。
1つのカラムだけを変えるupdateを6万行に実行した結果です。
갱신 전 9,945,088 바이트
갱신 후 17,358,848 바이트 n_dead_tup = 60000
1文字も増やしていないのに、テーブルが1.7倍になりました。これを元に戻す作業がVACUUMで、普段はautovacuumが自動で行います。
ホライズン: vacuumが片付けられない理由
ところが、VACUUMを手動で実行しても小さくならない場合があります。vacuumは自分で理由を教えてくれます。
tuples: 0 removed, 131456 remain, 60000 are dead but not yet removable
removable cutoff: 970, which was 10 XIDs old when operation ended
「削除するものがない」のではなく、「まだ削除できない」のです。まだ開いているトランザクションが、その古いバージョンを見る可能性があるからです。その境界がremovable cutoffで、その値は生きている最も古いトランザクションが決めます。
誰が握っているのかを探すとき、よくbackend_xminを調べますが、それだけを見ても見つかりません。
pid | application_name | state | backend_xid | backend_xmin
------+------------------+---------------------+-------------+--------------
941 | nightly-batch | idle in transaction | 932 |
947 | api-order | active | 933 | 932
949 | api-cart | active | 934 | 932
1050 | psql | active | | 932
cutoffを握っているのは941ですが、941のbackend_xminは空です。自分のトランザクションIDであるbackend_xidの932で、ホライズンを握っているからです。逆に、932をbackend_xminに持っている947、949、1050は、941がまだ生きているという事実を自分のスナップショットに反映しているだけです。これらを切断しても、ホライズンは動きません。
探す方法は1つ、backend_xidが最も古いものです。
select pid, application_name, backend_xid, now() - xact_start as age
from pg_stat_activity
where backend_xid is not null
order by age(backend_xid) desc limit 1;
そして941は、前のロックチェーンですでに見たあのセッションです。症状は2つでも、原因は1つです。テーブルが小さくならない原因が、そのテーブルの中にないことがある。このコースが教えようとしているのはそれです。
現場での姿
1つ目は、一括ロードスクリプトの最後の行をANALYZEにすることです。autoanalyzeはいずれ動きますが、いつ動くかは誰にもわからず、その数分が障害の時間になります。ロードした人がその場で1行入れるのが、最も安上がりです。
2つ目は、原因がテーブルの外にある場合があることを覚えておくことです。「このテーブルだけが異常に大きくなる」という報告を受けると、そのテーブルのインデックスやロードのパターンから調べたくなりますが、この事故ではテーブルに何の落ち度もありません。n_dead_tupが大きく、VACUUMがnot yet removableと答えたら、そこからはテーブルではなくトランザクションの一覧を見る必要があります。
3つ目は、n_dead_tupは統計であって実測値ではないと心得ることです。統計コレクターが更新する値なので、実際とずれることがあります。確実に知りたいなら、VACUUM (VERBOSE)の出力を読むほうがよいです。その出力にはcutoffまで一緒に出るので、「なぜ片付けられなかったのか」が一度にわかります。
次のラボですること
ロックチェーン、崩れた実行計画、膨らんだテーブル。3つの症状が同時に出ているデータベースを受け取り、それぞれの証拠を数字で取り出して、最後に診断書1枚にまとめます。採点ツールは、書き出した数字を生きているデータベースから再度取得して照合するので、どう見つけたかは自由で、診断が合っていれば通過します。