1. koudaiii

    Posted

    koudaiii
Changes in title
+インフラエンジニアがいざという時に抑えておきたい postgresql が重くてレスポンス返ってこない時に対処する流れ
Changes in tags
Changes in body
Source | HTML | Preview
@@ -0,0 +1,188 @@
+
+本来はWebサーバー側でクエリを対応することではありますが、
+時々どうしようもないボーンとしている状態の時が突然くる経験はありませんか?
+
+エンジニアが焦って問い合わせが来た時や、突然のアラートでインフラエンジニアも一緒に焦ってしまわないように、どのクエリが問題なのかを特定して対処する方法のまとめです。
+
+## postgresql が重くてレスポンス返ってこない時に対処する流れ
+
+めちゃくちゃ時間がかかるクエリが走っていて、問題が起こっている場合の対処です。
+
+問い合わせを行っているWebサーバー側で対応が出来ると良いのですが、どうしても DB サーバー側で対応の必要がある時があるのでその時の方法です。
+
+### pg_stat_activity で統計情報を取得
+
+現在実行中のクエリは`pg_stat_activity`テーブルが管理しているため確認できます。
+
+- https://www.postgresql.org/docs/9.5/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW
+- https://www.postgresql.jp/document/9.5/html/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW
+
+| 列 | 型 | 説明 |
+|----|----|-----|
+| 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はサーバプロセス毎に、そのプロセスの現在の活動に関連する情報を表示する1行を持ちます。
+- 注意: **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)` です。
+
+クエリの一部だけ表示
+
+```sql
+SELECT pid, query_start, substr(query, 0, 50) FROM pg_stat_activity WHERE state='active' ORDER BY query_start;
+```
+
+```bash:sql_result
+ pid | query_start | substr
+-------+-------------------------------+----------------------------------------------------
+ 26368 | 2016-12-09 01:58:27.238954+00 | SELECT pid, query_start, substr(query, 0, 50) FRO
+```
+
+クエリの全部表示
+
+```sql
+=> SELECT pid, query_start,waiting, substr(query, 0) FROM pg_stat_activity WHERE state='active' ORDER BY query_start;
+```
+
+```bash: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 にあたる処理
+
+```sql
+SELECT pg_cancel_backend(847);
+```
+
+- SIGTERM にあたる処理
+
+```sql
+SELECT pg_terminate_backend(847);
+```
+
+※使うときは自己責任!!!!
+
+### 大量に殺したい時(※使うときは自己責任!!!!)
+
+- 同じようなクエリが複数発生している場合
+
+```sql
+SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE substr(query, 0, 50) = 'sql 文'
+```
+
+```sql
+SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE substr(query, 0, 50) = 'sql 文'
+```
+
+条件にマッチするものを殺すことが出来る。
+
+- inactive を問答無用で一括削除
+
+```sql
+ SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE state != 'active' ORDER BY query_start;
+```
+
+```sql
+ SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state != 'active' ORDER BY query_start;
+```
+
+### 問答無用で動いている query すべて消す(※使うときは自己責任!!!!)
+
+```sql
+ SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state='active' AND pid <> pg_backend_pid();
+```
+
+```sql
+pid <> pg_backend_pid()
+```
+
+これがないと自分自身を消しにいくため対象から外すようにしないといけないです
+
+### `db:migrate`時に以下のクエリを実行しても結果が返ってこない状態の時に対処する流れ
+
+ほとんどは、マイグレーション側をキャンセルして、粗めて行うことがほとんどです。
+しかし、どうしても実行しないとサービスが死んでしまう。。。みたいな場合のときの対処方法です。
+
+```sql
+ALTER TABLE "article" ADD COLUMN "updated_by" integer DEFAULT 0 NOT NULL`
+```
+
+以下のように`state = active`のままで残ってしまっている状態。
+
+```bash:sql_result
+=> SELECT pid, query_start, substr(query, 0, 200) FROM pg_stat_activity WHERE state='active' ORDER BY query_start limit 50;
+ 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, query_start, substr(query, 0, 200) FROM pg_stat_activity WHERE state != 'active' ODER BY query_start LIMIT 30;
+
+ 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` できるようになります。
+
+
+```sql
+SELECT pg_terminate_backend(PID);
+or
+SELECT pg_cancel_backend(PID);
+```