16個のロックがあれば十分なはず

16個のロックがあれば誰にでも十分なはず

［画像］

PostgreSQLに関する、長年運用しているユーザーでさえ驚くような事実があります。それは、テーブルに対するあらゆるクエリが、たとえそのクエリがどれも使用しないインデックスであっても、そのテーブル上のすべてのインデックスに対してロックを取得するということです。

これは通常、無害です。ロックはAccessShareLockという最も弱いロックであり、ほとんどのものと競合せず、取得コストも低いです。しかし、「安い」は「無料」ではなく、インデックス数とクエリレートの特定の組み合わせでは、この挙動が、単一行の主キー検索のような無害なクエリでさえCPUを食い潰すロック競合問題に変わってしまうことがあります。

プランナーは見たものすべてをロックする

PostgreSQLがクエリを計画する際、クエリが使用する可能性のあるすべてのリレーション（テーブル、およびその上のすべてのインデックス）に対してAccessShareLockを取得します。これは、プランナーがインデックスが有用かどうかを判断するために各インデックスを開く必要があるためです。最終的にそのうちの1つだけを使用することになったとしても関係ありません。検討するにはロックが必要です。

したがって、主キーと20個のセカンダリインデックスを持つテーブルでは、クエリごとに22個のリレーションロックが必要になります。プランナーが検討したが破棄したテーブル、主キーインデックス、およびその他の20個のインデックスです。これは実行ごとに発生します。

高速パスとその脱落

ロックの取得は通常、共有ロックテーブルのエントリを意味します。このテーブルは共有メモリに存在し、軽量ロック（16個のパーティションに分割され、それぞれに独自のLWLockを持つ）によって保護されています。マルチコアマシンでの高いクエリレートでは、これらの16個のLWLock自体が競合の原因となります。

PostgreSQLには、9.2で追加されたこのための最適化があります：高速パスロッキング。各バックエンドは、共有ロックテーブルに一切触れることなく、弱いリレーションロック（AccessShareLock、RowShareLock、RowExclusiveLock）を記録できる小さなプライベート配列を取得します。PostgreSQL 17まで、その配列にはちょうど16個のスロットがありました。

16個です。インデックスの数を数えてみてください。

クエリが16個を超えるリレーションロックを必要とする場合、オーバーフローはLWLockなどを含む共有ロックテーブルを通過します。これを実行するバックエンドが1つであれば問題ありません。毎秒数千回これを実行する数百のバックエンドは、pg_stat_activity（PostgreSQL 13より前はlock_managerと表記）にLWLock:LockManagerの待機が壁のように表示され、CPUがピン止めされ、クエリ自体は非常に単純なままでスループットが低下する原因となります。RDSやAuroraで実行している場合、これはPerformance Insightsが不穏な茶色で表示するのが好きなLWLock:LockManager待機イベントです。

この障害モードの厄介な特性は、まさに間違ったテーブルに集中することです。インデックスが多すぎるテーブルはコアテーブルであり、あらゆるクエリがアクセスするため、あらゆるクエリがその代償を支払い、競合は総クエリレートとスケールします。パーティション化されたテーブルは、プランナーが各パーティションとそのインデックスをロックする必要があるため、さらに早くこの状態になります。

発生の観察

これは簡単に実証できます。主キーと20個の単一列インデックスを持つusersテーブルを作成し（本番環境でこれより悪い例を見たことがありますし、あなたも見たことがあるはずです）、想像できる限り最も退屈なクエリを実行します。

1BEGIN;
2SELECT * FROM users WHERE id = 42;
3
4SELECT fastpath, count(*) 
5  FROM pg_locks
6 WHERE pid = pg_backend_pid()
7   AND locktype = 'relation'
8   AND relation <> 'pg_locks'::regclass
9 GROUP BY 1;
10
11 fastpath | count
12----------+-------
13 f        |     6
14 t        |    16

これはPostgreSQL 16です。単一行の主キー検索が22個のリレーションロックを取得し、16個の高速パススロットすべてを埋め、6個のロックを共有ロックテーブルに押し込んでいます。実行ごとに、永遠に。

プリペアドステートメント、偶然の修正

ここからが明らかではない部分です：プリペアドステートメントはこの問題を消し去ります。そして、ロック規則に関する理由からではありません。

プリペアドステートメントは、最初の5回の実行のためにカスタムプランで計画されます。6回目の実行で、PostgreSQLはジェネリックプランを構築し、キャッシュします（コスト比較がうまくいけば、主キー検索の場合はうまくいきます）。それ以降は再利用されます。キャッシュされたプランの再利用はプランナーを完全にスキップし、キャッシュされたプランの実行はプランナーが検討したであろうすべてのリレーションではなく、実際にプランに含まれるリレーションのみをロックします。

1PREPARE u(bigint) AS SELECT * FROM users WHERE id = $1;
2-- カスタムプランフェーズを通過するために6回実行してから：
3
4BEGIN;
5EXECUTE u(42);
6
7SELECT relation::regclass AS relation, fastpath
8  FROM pg_locks
9 WHERE pid = pg_backend_pid()
10   AND locktype = 'relation'
11   AND relation <> 'pg_locks'::regclass;
12
13  relation  | fastpath
14------------+----------
15 users_pkey | t
16 users      | t

22個のロックが2つになり、どちらも快適に高速パス上にあり、共有ロックマネージャーはこのクエリから二度と連絡を受けません。ボーナスとして、クエリを毎実行ごとに再計画するコストも削減されました。これは、インデックスが過剰なテーブルではそれ自体で小さなCPU使用量ではありません。

2つの注意点があります。第一に、ジェネリックプランにはよく知られた障害モードがあります：パラメータの分布が偏っていると、特定の値に対して壊滅的に間違ったプランが得られることがあります。plan_cache_modeは、これが問題になった場合に存在しますが、正直な答えは、ホットなクエリをテストすることです。

第二に、そしてより厄介なことですが、プリペアドステートメントとコネクションプーラーは複雑な関係を持っています。PgBouncerのトランザクションプーリングモードは、1.21以降、max_prepared_statementsを介してプロトコルレベルのプリペアドステートメントのみをサポートしています。古いバージョンを使用している場合は、アップグレードしてください。RDS Proxyはさらに悪いです：プリペアドステートメントはクライアントセッションをバックエンド接続にピン止めし、RDS Proxyが存在する理由そのものである多重化を無効にします。アーキテクチャがRDS Proxyに依存している場合、この修正は事実上テーブルから外され、次のセクションに特別な関心を持って読むべきです。

PostgreSQL 18はマジックナンバーを削除する

PostgreSQL 18では、Tomas Vondraの作業のおかげで、高速パス配列はもはや16スロットに固定されなくなりました。サーバー起動時にmax_locks_per_transactionから16スロット単位でサイズが決定されるため、デフォルトの64は各バックエンドに64個の高速パススロットを提供します。同じテーブル、同じクエリ、プリペアドステートメントなし：

1SHOW max_locks_per_transaction;
2 max_locks_per_transaction
3---------------------------
4 64
5
6BEGIN;
7SELECT * FROM users WHERE id = 42;
8
9SELECT fastpath, count(*) 
10  FROM pg_locks
11 WHERE pid = pg_backend_pid()
12   AND locktype = 'relation'
13   AND relation <> 'pg_locks'::regclass
14 GROUP BY 1;
15
16 fastpath | count
17----------+-------
18 t        |    22

アウトオブザボックスで22個のロックすべてが高速パス上にあります。（18でmax_locks_per_transaction = 16を設定すると、歴史的な災害を再現するのが好きなら、古い16/6の分割に戻ります。）

これ専用のノブはないことに注意してください。高速パス容量はmax_locks_per_transactionに乗って移動します。これは主に共有ロックテーブルのサイジングが主な仕事です。スキーマがパーティション化されているか、インデックスが多い場合は、クエリあたりの最悪ケースのリレーション数を超えて引き上げてください。高速パス配列自体はほとんどコストがかかりません。共有ロックテーブルのエントリはより大きいですが、この問題が存在するマシンであれば、メモリは十分にあります。

または、聞いてほしいのですが：インデックスを減らす

上記のどちらの修正も症状を治療しています。病気は21個のインデックスを持つテーブルです。

過剰なインデックスの標準的なコストはすでに知っているでしょう：すべてのINSERTと非HOT UPDATEはすべてのインデックスを維持し、VACUUMはすべてのインデックスを処理する必要があり、それぞれがストレージとバッファキャッシュを占有します。これにこの問題を加えてください：PostgreSQL 17まで、ホットテーブル上の15番目のインデックスを超えるすべてのインデックスは、そのテーブルに対するすべてのクエリをロック高速パスから押し出し、そのペナルティは、それを実行したクエリだけでなく、システム全体での共有メモリの競合で支払われます。

pg_stat_all_indexesのidx_scanを見てください。代表的な期間でそれがゼロであり、インデックスが制約を強制していない場合、それを削除してください。ロックマネージャーは、静かに、16個のロックずつ、感謝するでしょう。