Amazon RDS PostgreSQL または Aurora PostgreSQL を実行している DB インスタンスでクエリがブロックされている原因を特定する方法を教えてください。
Amazon リレーショナルデータベースサービス (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、ストレージ、ネットワーク容量などのリソースにパフォーマンスのボトルネックがあります。パフォーマンスのボトルネックを解決するには、データベースのパフォーマンスを調整してください。たとえば、インデックスを追加したり、クエリを書き換えたり、vacuum コマンドを実行したり、analyze コマンドを実行したりできます。詳細については、「PostgreSQLを使用する際のベストプラクティス」を参照してください。
Performance Insights をオンにしている場合は、待機イベント、ホスト、SQ Lクエリ、またはユーザーごとにグループ化された DB 負荷を表示して、ブロックされたトランザクションを特定できます。詳細については、「Amazon RDS の Performance Insights による DB 負荷のモニタリング」を参照してください。
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. blocking プレフィックスが付いている列を確認します。次の表の例では、ブロックされたトランザクションが 27.0.3.146 ホストで実行され、psql を使用していることがわかります。blocking_user、blocking_user_addr、blocking_client_port を使用して、どのセッションがトランザクションをブロックしているかを特定します。
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;
**重要:**トランザクションを終了する前に、各トランザクションがデータベースとアプリケーションの状態に及ぼす潜在的な影響を評価してください。
4. 以下のクエリを実行して、トランザクションを停止します。
SELECT pg_terminate_backend(PID);
メモ:PID は、前のステップで特定したプロセスの blocking_pid に置き換えてください。
関連情報
ロックの表示に関する PostgreSQL ドキュメント
サーバーシグナル関数に関する PostgreSQL ドキュメント
wait_event の説明に関する PostgreSQL ドキュメント
ロックモニタリングに関する PostgreSQL Wiki
関連するコンテンツ
- 質問済み 6年前lg...
- 質問済み 4ヶ月前lg...
- 承認された回答質問済み 5ヶ月前lg...
- AWS公式更新しました 1年前