LoginSignup
66
62

More than 5 years have passed since last update.

@koudaiii(Kodai Sakabe)inWantedly, Inc.

インフラエンジニアがいざという時に抑えておきたい postgresql が重くてレスポンス返ってこない時に対処する流れ

Last updated at Posted at 2016-12-09

本来はWebサーバー側でクエリを対応することではありますが、
時々どうしようもないボーンとしている状態の時が突然来た経験はありませんか？

エンジニアから焦って問い合わせが来た時、突然のアラートが飛んできた時など。。。
そんな時にインフラエンジニアも一緒に焦ってしまわないように、どのクエリが問題なのかを特定して対処する方法のまとめました。

postgresql が重くてレスポンス返ってこない時に対処する流れ

めちゃくちゃ時間がかかるクエリが走っていて、問題が起こっている場合の対処です。

問い合わせを行っているWebサーバー側で対応が出来ると良いのですが、どうしても DB サーバー側で対応の必要がある時があるのでその時の方法です。

pg_stat_activity で統計情報を取得

現在実行中のクエリはpg_stat_activityテーブルが管理しているため確認できます。

列 型 説明
datid oid バックエンドが接続するデータベースのOIDです。
datname name バックエンドが接続するデータベースの名前です。
pid integer バックエンドのプロセスIDです。
usesysid oid バックエンドにログインしたユーザの識別子です。
usename name バックエンドに接続したユーザの名前です。
application_name text バックエンドに接続したアプリケーションの名前です。
client_addr inet バックエンドに接続したクライアントのIPアドレスです。 このフィールドがNULLである場合、これはクライアントがサーバマシン上のUnixソケット経由で接続されたか、自動バキュームなど内部処理であることを示します。
client_hostname text client_addrの逆引き検索により報告された、接続クライアントのホスト名です。 IP接続、かつlog_hostnameが有効である場合にのみこのフィールドは非NULLになります。
client_port integer クライアントがバックエンドとの通信に使用するTCPポート、もしUnixソケットを使用する場合は-1です。
backend_start timestamp with time zone プロセスが開始、つまりクライアントがサーバに接続した時刻です。
xact_start timestamp with time zone プロセスの現在のトランザクションが開始した時刻です。 活動中のトランザクションがない場合はNULLです。 現在の問い合わせがトランザクションの先頭である場合、この列はquery_start列と同じです。
query_start timestamp with time zone 現在有効な問い合わせが開始した時刻です。 もしstateがactiveでない場合は直前の問い合わせが開始した時刻です。
state_change timestamp with time zone stateの最終変更時刻です。
waiting boolean バックエンドが現在ロックを待機している場合は真です。
state text 現在のバックエンドの総体的な状態です。 以下のいずれかの値を取ることができます。
active: バックエンドは問い合わせを実行中です。
idle: バックエンドは新しいクライアントからのコマンドを待機しています。
idle in transaction: バックエンドはトランザクションの内部にいますが、現在実行中の問い合わせがありません。
idle in transaction (aborted): この状態はidle in transactionと似ていますが、トランザクション内のある文がエラーになっている点が異なります。
fastpath function call: バックエンドは近道関数を実行中です。
disabled: この状態は、このバックエンドでtrack_activitiesが無効である場合に報告されます。
backend_xid xid もしあれば、このバックエンドの最上位のトランザクション識別子。
backend_xmin xid 現在のバックエンドのxmin。
query text バックエンドの最も最近の問い合わせテキストです。 stateがactiveの場合、現在実行中の問い合わせを意味します。 その他のすべての状態では、実行済みの最後の問い合わせを示します。
  • pg_stat_activityはサーバプロセス毎に、そのプロセスの現在の活動に関連する情報を表示する１行を持ちます。
  • 注意: waitingとstate列は独立 しています。 バックエンドがactive状態である場合、waitingかもしれませんし、そうでないかもしれません。 状態がactiveであり、waitingが真である場合、問い合わせは実行中ですが、システム内の何らかのロックによりブロックされていることを意味します。

pg_stat_activityの中でよく見る項目と便利な関数

  • pid: プロセスのID
  • query_start: SQLクエリを実行開始時間
  • query: 実行しているSQLクエリ
  • state: 実行状態
  • substr(query, 0, 50) :http://www.shift-the-oracle.com/sql/functions/substr.html SUBSTR は取り出す文字列をキャラクタ単位で部分文字列の取り出します。 query 全部観たいときは substr(query, 0) です。

クエリの一部だけ表示

SELECT pid, query_start, substr(query, 0, 50) FROM pg_stat_activity WHERE state='active' ORDER BY query_start;
sql_result
  pid  |          query_start          |                      substr                       
-------+-------------------------------+---------------------------------------------------- 
 26368 | 2016-12-09 01:58:27.238954+00 | SELECT pid, query_start, substr(query, 0, 50) FRO

クエリの全部表示

SELECT pid, query_start,waiting, substr(query, 0) FROM pg_stat_activity WHERE state='active' ORDER BY query_start;
sql_result
  pid  |          query_start          | waiting |                                                       substr                                                       
-------+-------------------------------+---------+--------------------------------------------------------------------------------------------------------------------
 10516 | 2016-12-09 03:27:47.215649+00 | f       | SELECT pid, query_start,waiting, substr(query, 0) FROM pg_stat_activity WHERE state='active' ORDER BY query_start;
(1 row)

datname, usename, application_name, client_hostname, backend_start, state_change, waiting は適宜追加すると良い。

実行中のクエリを殺す

殺したいクエリのpidさえ分かれば、以下のクエリでプロセスを殺すことが出来ます

  • SIGINT にあたる処理
SELECT pg_cancel_backend(847);
  • SIGTERM にあたる処理
SELECT pg_terminate_backend(847);

※使うときは自己責任!!!!

大量に殺したい時(※使うときは自己責任!!!!)

  • 同じようなクエリが複数発生している場合に、条件にマッチするものを殺す。
SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE substr(query, 0, 50) = 'sql 文'

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE substr(query, 0, 50) = 'sql 文'
  • inactive 状態を一括削除
 SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE state != 'active' ORDER BY query_start;

 SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state != 'active' ORDER BY query_start;
  • すべての query を消す
 SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state='active' AND pid <> pg_backend_pid();

pid <> pg_backend_pid()

これがないと自分自身を消しにいくため対象から外すようにしないといけないです

db:migrate時に以下のクエリを実行しても結果が返ってこない状態の時に対処する流れ

ほとんどは、マイグレーション側をキャンセルして、改めて行うことがほとんどです。
しかし、どうしても実行しないとサービスが死んでしまう。。。みたいな場合のときの対処方法です。

ALTER TABLE "article" ADD COLUMN "updated_by" integer DEFAULT 0 NOT NULL`

以下のようにstate = activeのままで残ってしまっている状態。

SELECT pid, query_start, substr(query, 0, 200) FROM pg_stat_activity WHERE state='active' ORDER BY query_start limit 50;
sql_result
  pid  |          query_start          |                                                          substr
-------+-------------------------------+--------------------------------------------------------------------------------------------------------------------------
 27679 | 2015-11-13 03:49:06.057732+00 | ALTER TABLE "article" ADD COLUMN "updated_by" integer DEFAULT 0 NOT NULL
 22383 | 2015-11-13 03:49:12.738348+00 | SELECT pid, query_start, substr(query, 0, 200) FROM pg_stat_activity WHERE state='active' ORDER BY query_start limit 50;
(2 rows)
  • stateを active 以外も確認する
SELECT pid, state, query_start, substr(query, 0, 200) FROM pg_stat_activity WHERE state != 'active' ORDER BY query_start LIMIT 30;
sql_result
  pid  |        state        |          query_start          |                                               substr                                                
-------+---------------------+-------------------------------+----------------------------- ------------------------------------------------------------------------
 10575 | idle in transaction | 2015-11-11 03:33:40.675587+00 | SELECT  "jobs".* FROM "jobs"  WHERE "jobs"."deleted_at" IS NULL AND "jobs"."user_id" = $1 AND "jobs
 30356 | idle                | 2015-11-12 13:43:26.010795+00 |             SELECT attr.attname                                                                    +
       |                     |                               |             FROM pg_attribute attr                                                                 +
       |                     |                               |             INNER JOIN pg_constr
 17047 | idle                | 2015-11-12 14:10:04.15198+00  | SELECT  "projects".* FROM "projects"  WHERE "projects"."deleted_at" IS NULL AND "projects"."id" = $
  4591 | idle                | 2015-11-13 01:18:02.595226+00 | SELECT pg_cancel_backend(915);
 20262 | idle                | 2015-11-13 02:00:19.977307+00 | SELECT  "profiles".* FROM "profiles"  WHERE "profiles"."user_id" = $1 LIMIT 1
 30027 | idle                | 2015-11-13 02:00:22.280172+00 | SELECT "users".* FROM "users"  WHERE "users"."id" IN (123)
 20287 | idle                | 2015-11-13 03:00:19.565167+00 | SELECT "users".* FROM "users"  WHERE "users"."id" IN (234, 345)
 20263 | idle                | 2015-11-13 03:00:23.19501+00  | SELECT "users".* FROM "users"  WHERE "users"."id" IN (456, 567)
 21243 | idle                | 2015-11-13 03:13:14.598247+00 | ALTER TABLE "article" ADD COLUMN "updated_by" integer DEFAULT 0 NOT NUL;
 22383 | idle                | 2015-11-13 03:50:32.823274+00 | SELECT pid, query_start, substr(query, 0, 200) FROM pg_stat_activity WHERE state='active' ORDER BY

idleおよびidle in transactionなクエリを殺していくとボトルネックになっているクエリが解消されるため alter table できるようになります。

SELECT pg_terminate_backend(PID);

or

SELECT pg_cancel_backend(PID);
66
62
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
66
62