読み取り専用のレプリカ側でロック待ちが発生する事象について

PostgreSQLでレプリケーションを行っている際、読み取り専用トランザクションにも関わらず、レプリカ側でロック待ちが発生(still waiting for AccessShareLock)してしまう事象についてです。状況次第では、レプリカ側でデッドロックも発生(deadlock detected)します。

事象
まとめ
原因
1. ACCESS EXCLUSIVEロックについて
調査
1. レプリカ側
2. プライマリ側
  1. PostgreSQLログ
  2. その他
対応
1. 自動バキュームだった場合

事象

レプリカ側で次のようなロック待ちが発生します。

2021-06-20 00:43:39 UTC:xx.xx.xx.xx(55558):postgres@dev:[10485]:LOG: process 10485 still waiting for AccessShareLock on relation 143563 of database 139288 after 1000.096 ms
2021-06-20 00:43:39 UTC:xx.xx.xx.xx(55558):postgres@dev:[10485]:DETAIL: Process holding the lock: 1550. Wait queue: 10485.

状況によっては、デッドロックも発生します。

2021-06-20 00:45:23 UTC:xx.xx.xx.xx(36128):postgres@dev:[22035]:LOG:  process 22035 detected deadlock while waiting for AccessShareLock on relation 143563 of database 139288 after 1000.048 ms at character 61
2021-06-20 00:45:23 UTC:xx.xx.xx.xx(36128):postgres@dev:[22035]:DETAIL:  Process holding the lock: 26223. Wait queue: 13967, 28211, 9113.
2021-06-20 00:45:23 UTC:xx.xx.xx.xx(36128):postgres@dev:[22035]:STATEMENT:  <SQL>
2021-06-20 00:45:23 UTC:xx.xx.xx.xx(36128):postgres@dev:[22035]:ERROR:  deadlock detected at character 61
2021-06-20 00:45:23 UTC:xx.xx.xx.xx(36128):postgres@dev:[22035]:DETAIL:  Process 22035 waits for AccessShareLock on relation 143563 of database 139288; blocked by process 26223.
        Process 26223 waits for AccessExclusiveLock on relation 143563 of database 139288; blocked by process 22035.
        Process 22035: <SQL>
        Process 26223: <backend information not available>
2021-06-20 00:45:23 UTC:xx.xx.xx.xx(36128):postgres@dev:[22035]:HINT:  See server log for query details.
2021-06-20 00:45:23 UTC:xx.xx.xx.xx(36128):postgres@dev:[22035]:STATEMENT:  <SQL>

レプリカ側は読み取り専用トランザクションであるため、本来、このようなロック待ちやデッドロックが発生する操作をユーザ側で行うことはできません。それにも関わらず、レプリカ側でロック関係の問題が発生してしまうという事象です。

まとめ

当該事象についてのまとめです。

ACCESS EXCLUSIVEロックがプライマリ側で取得されている可能性がある
PostgreSQLやアプリケーション側のログから事象発生時に実行されていた操作を特定する
自動バキュームが原因の場合は格納パラメータの変更や更新頻度の削減を検討する
自動バキュームが原因でない場合はアプリケーション設定やレプリカ側でのロックタイムアウト導入を検討する

以下、詳細です。

原因

SELECT文が取得するACCESS SHAREロックと競合し得るACCESS EXCLUSIVEロックを取得する操作がプライマリ側で発生している可能性が高いものと考えられます。

ACCESS EXCLUSIVEロックのみが、SELECT（FOR UPDATE/SHAREなし）文をブロックします。
13.3. 明示的ロック

ACCESS EXCLUSIVEロックは次のような操作で発生し得ます。

DDL(一部除く)
VACUUM FUL
VACUUM(特殊なケース)

ACCESS EXCLUSIVEロックについて

多くのDDLやVACUUM FULLがACCESS EXCLUSIVEロックを取得する件については、一般的にも知られているケースかと存じます。具体的には次のような種類のコマンドです。

DROP TABLE、TRUNCATE、REINDEX、CLUSTER、VACUUM FULL、（CONCURRENTLYなしの）REFRESH MATERIALIZED VIEWコマンドによって獲得されます。 ALTER INDEXとALTER TABLEの多くの形式もこのレベルでロックを獲得します。これはまた、明示的にモードを指定しないLOCK TABLE文のデフォルトのロックモードです。
13.3. 明示的ロック

しかしながら、通常のVACCUMも状況によっては取得するという部分につきましては、意外に感じられる方もいらっしゃるかもしれません。

次のように、VACUUM(VACUUM FULLを除く)はACCESS SHAREロックと競合しないSHARE UPDATE EXCLUSIVEロックを取得し、一般的に競合は発生しないというのが良く知られている挙動であるためです。

VACUUMの標準形式は実運用のデータベースに対する操作と同時に実行させることができます。（SELECT、INSERT、UPDATE、DELETEなどのコマンドは通常通りに動作し続けます。
24.1. 定常的なバキューム作業

また、自動バキュームを利用している場合、ロックが競合した際には相手に処理を譲る(自動VACUUM側が中断される)というのも、良く知られている挙動かと存じます。

自動バキュームワーカは通常は他のコマンドをブロックしません。自動バキュームが保持するSHARE UPDATE EXCLUSIVEロックと衝突するロックを、プロセスが獲得しようとした場合には、ロックの獲得により自動バキュームが中断されます。
24.1. 定常的なバキューム作業

しかしながら、実は「テーブルの末尾に空のページが存在している」状態で、なおかつ、「テーブルの排他ロックを容易に獲得可能」という特殊な場合においては、通常のバキューム(自動バキュームを含む)でも排他ロックが取得されることがあります。これは盲点となり得る部分かと存じます。

標準形式のVACUUMは、テーブルとインデックス内の不要な行を削除し、その領域を将来の再利用が可能であるものとして記録します。しかし、その領域をオペレーティングシステムに返却することはありません。例外として、テーブルの末尾に完全に空のページが存在し、かつそのテーブルの排他ロックが容易に獲得できるような特殊な場合には、その領域を返却します。
24.1. 定常的なバキューム作業

調査

レプリカ側

ロックを取得しているプロセスがバックグラウンドプロセスであることを確認します。ちなみに、Auroraだとaurora wal replay processが対象のプロセスです。
また、ロック待ちが発生している「時間帯」と「テーブル」も確認します。

具体的には、下記のようなロック待ちのログ出力から、ロックを要求しているプロセスを特定します。下記の例で言うところのProcess holding the lock: 1550の1550が、ACCESS EXCLUSIVEを保持しているプロセスIDです。

2021-06-20 00:43:39 UTC:xx.xx.xx.xx(55558):postgres@dev:[10485]:DETAIL: Process holding the lock: 1550. Wait queue: 10485.

プロセスIDが確認できましたら、OSコマンド、RDSやAuroraの場合は拡張メトリクス、あるいは、pg_stat_activity等から該当のプロセスがバックグラウンドプロセスであることを確認します。

プライマリ側

レプリカ側で事象が確認できた「時間帯」「テーブル」の情報をベースに、「プライマリ側でACCESS EXCLUSIVEロックを取得した操作を特定すること」が第一方針となるかと存じます。

PostgreSQLログ

ログに出力されたSQL文や自動バキュームから、当該時間帯にプライマリ側でACCESS EXCLUSIVEロックを取得する操作が実行されていないかを確認します。

log_statementを有効にしている場合、設定レベルに応じてユーザによる操作が記録されているため、当該時間帯のSQLを確認することができます。

また、log_autovacuum_min_durationを指定している場合、この値よりも長い時間を要した自動バキュームについてはログ出力されます。0にすることで全ての自動バキュームが出力されます。

RDSやAuroraの場合、rds.force_autovacuum_logging_levelもdisabled以外の値に設定する必要があります。

なお、上記に挙げたパラメータは動的パラメータです。事象発生時点で有益な情報を取得可能な設定になっていなくとも、事象再現時に備えて再起動なしで設定を反映することができます。

その他

アプリケーション側の実行ログ等から、当該時間帯にプライマリ側でACCESS EXCLUSIVEロックを取得する操作が実行されていないかを確認します。

また、関係者に当該時間帯の操作について確認するのも手段の一つです。

対応

プライマリ側でACCESS EXCLUSIVEロックを取得した操作を特定できたら、次のように対応します。

自動バキュームだった場合

原因がテーブルに対する自動バキュームだった場合、次のような対応が考えられます。

【PostgreSQL 12以降】テーブルの格納パラメータvacuum_truncateをfalseにする
テーブルの格納パラメータautovacuum_enabledをfalseにする
テーブルに対する更新処理量を減らす

【PostgreSQL 12以降】当該テーブルの格納パラメータvacuum_truncateをfalseにする

残念ながらPostgreSQL 12以降にしか対応していませんが、vacuum_truncateをfalseにすることで、VACUUMによりACCESS EXCLUSIVEロックが取得されるページ末尾の空ページ切り捨て操作を無効にできます。

これによりACCESS EXCLUSIVEロックは取得されないようになりますが、OSにページ末尾の空ページ領域を返さなくなるため、基本的にストレージ容量に余裕がある場合の設定となります。

バキュームがテーブル末尾の空ページの切り捨てを試みることを、有効または無効にします。デフォルト値はtrueです。 trueの場合、VACUUMと自動バキュームは切り捨てを行い切り捨てられたページのディスク領域はオペレーティングシステムに返されます。切り捨てにはテーブルにACCESS EXCLUSIVEロックが必要であることに注意してください。
vacuum_truncate

alter table <schema>.<table> set (vacuum_truncate = false, toast.vacuum_truncate = false);

テーブルの格納パラメータautovacuum_enabledをfalseにする

autovacuum_enabledをfaseにすることで、対象のテーブルに対する自動バキュームを無効にすることができます。

ただし、自動バキュームを無効にすることで、テーブルが肥大化し、結果として大幅なパフォーマンス低下に繋がる可能性があるため、定期的に手動でVACUUMを実行されることをお勧め致します。

また、MaximumUsedTransactionIDsについてにも記載がありますが、自動バキュームを無効にしていても、autovacuum_freeze_max_ageに達した場合は強制的にバキュームされます。不要域が多い程、強制バキューム時の負荷が高くなる可能性があるため、注意が必要です。

alter table <schema>.<table> set (autovacuum_enabled = false, toast.autovacuum_enabled = false);

テーブルに対する更新処理量を減らす

当該テーブルに対する更新処理を減らすことで自動バキュームの発生頻度も減らすことができるため、結果として事象発生の頻度も下がります。

例えば、毎回、全件DELETE→INSERTを行っていたり、全件UPDATE→INSERTを行っていたりするような場合、ロジックを変更することで自動バキュームの頻度を下げることができます。

ただし、あくまでも頻度を減らせるだけなので、タイミング次第ではロック待ちの事象は再現します。

自動バキュームでなかった場合

原因が自動バキュームではなく、その他の操作(アプリケーション側の処理等)だった場合、目的の処理を行う際に、ACCESS EXCLUSIVEロックを取得する操作が必要なのかを確認します。

ACCESS EXCLUSIVEロックを伴う操作が必要ない場合(余分にロックをかけていた場合)は、それを改めることでロック待ちの事象を改善することができます。

ACCESS EXCLUSIVEロックを伴う操作が必要な場合は、レプリカ側に接続するクライアントでlock_timeoutを設定することで、延々とロックを待ち続けるような挙動を避けることができます。結果として、全体のスループットが上昇することが予測されます。

テーブル、インデックス、行、またはその他のデータベースオブジェクトに対してロック獲得を試みている最中、指定された時間を超えて待機するいかなる命令も停止されます。時間制限はそれぞれのロック取得の試みに対し個別に適用されます。制限は明示的ロック要求（例えばLOCK TABLE、またはSELECT FOR UPDATE without NOWAITなど）および暗黙的に取得されるロックに適用されます。この値が単位なしで指定された場合は、ミリ秒単位であるとみなします。値ゼロ（デフォルト）はこの機能を無効にします。
lock_timeout