Amazon RDS PostgreSQL または Aurora PostgreSQL を実行している DB インスタンスで何がクエリをブロックしているかを特定する方法を教えてください。
最終更新日: 2019 年 11 月 7 日
Amazon Relational Database Service (Amazon RDS) PostgreSQL または Amazon Aurora PostgreSQL を実行している DB インスタンスでクエリを実行しようとしましたが、他のクエリが同時に実行されていないにもかかわらず、クエリがブロックされました。クエリがブロックされた理由と、この問題を解決する方法を教えてください。
解決方法
ほとんどの場合、ブロックされたクエリは、コミットされていないトランザクションによって発生します。コミットされていないトランザクションは、新しいクエリをブロックし、スリープ状態となり、ロック待機タイムアウトまたはステートメントタイムアウトを超えたときに最終的に失敗する原因となる可能性があります。この問題を解決するには、まずブロックトランザクションを特定してから、ブロックトランザクションを停止します。
1. pg_stat_activity テーブルに対して次のクエリを実行して、ブロックされたトランザクションの現在の状態を特定します。
SELECT * FROM pg_stat_activity WHERE query iLIKE '%TABLE NAME%' ORDER BY state;
注: TABLE NAME を自分のテーブル名または条件に置き換えます。
[wait_event_type] 列の値が [Lock] の場合、クエリは他のトランザクションまたはクエリによってブロックされます。[wait_event_type] 列に他の値がある場合、CPU、ストレージ、ネットワークキャパシティーなどのリソースにパフォーマンスのボトルネックがあります。パフォーマンスのボトルネックを解決するには、インデックスの追加、クエリの書き換え、バキュームと分析の実行など、データベースのパフォーマンスを調整します。詳細については、「PostgreSQL を使用する際のベストプラクティス」をご参照ください。
DB インスタンスで Performance Insights を有効にした場合、待機イベント、ホスト、SQL クエリ、またはユーザーごとにグループ化された DB 負荷を表示することで、ブロックされたトランザクションを特定することもできます。詳細については、Amazon RDS Performance Insights の使用をご参照ください。
2. [wait_event_type] 列の値が [Lock] の場合、以下を実行してブロックされたトランザクションの原因を特定できます。
SELECT blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocked_activity.client_addr as blocked_client_addr,
blocked_activity.client_hostname as blocked_client_hostname,
blocked_activity.client_port as blocked_client_port,
blocked_activity.application_name as blocked_application_name,
blocked_activity.wait_event_type as blocked_wait_event_type,
blocked_activity.wait_event as blocked_wait_event,
blocked_activity.query AS blocked_statement,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocking_activity.client_addr as blocking_user_addr,
blocking_activity.client_hostname as blocking_client_hostname,
blocking_activity.client_port as blocking_client_port,
blocking_activity.application_name as blocking_application_name,
blocking_activity.wait_event_type as blocking_wait_event_type,
blocking_activity.wait_event as blocking_wait_event,
blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted ORDER BY blocked_activity.pid;
3. ブロックプレフィックスを持つ列を確認します。このクエリによって生成された次のテーブル例では、ブロックされたトランザクションが 27.0.3.146 ホストで実行されており、psql を使用していることが分かります。
blocked_pid | 9069
blocked_user | master
blocked_client_addr | 27.0.3.146
blocked_client_hostname |
blocked_client_port | 50035
blocked_application_name | psql
blocked_wait_event_type | Lock
blocked_wait_event | transactionid
blocked_statement | UPDATE test_tbl SET name = 'Jane Doe' WHERE id = 1;
blocking_pid | 8740
blocking_user | master
blocking_user_addr | 27.0.3.146
blocking_client_hostname |
blocking_client_port | 26259
blocking_application_name | psql
blocking_wait_event_type | Client
blocking_wait_event | ClientRead
current_statement_in_blocking_process | UPDATE tset_tbl SET name = 'John Doe' WHERE id = 1;
ヒント: blocking_user、blocking_user_addr、および blocking_client_port を使用すれば、どのセッションがトランザクションをブロックしているかを識別できます。
重要: トランザクションを終了する前に、各トランザクションがデータベースとアプリケーションの状態に与える潜在的な影響を評価します。
4. 各トランザクションの潜在的な影響を確認したら、次のクエリを実行してトランザクションを停止します。
SELECT pg_terminate_backend(PID);
注: PID をステップ 3 で特定したプロセスの blocking_pid に置き換えます。
関連情報
ロックの表示に関する PostgreSQL ドキュメント
サーバーシグナリング関数に関する PostgreSQL ドキュメント
wait_event の説明に関する PostgreSQL ドキュメント
ロックモニタリングに関する PostgreSQL Wiki