現在実行中のSQLクエリを殺す流れ #PostgreSQL

SQLでめちゃくちゃ時間がかかるクエリが走ってしまっていて問題が起こっている場合にとるべき対処。

本来は、問い合わせを行っているWebサーバー側で対応できれば良いが、DBサーバー側で対応の必要がある時。

1. pg_stat_activityを見る

現在実行中のクエリはpg_stat_activityテーブルが管理している

参考：

pg_stat_activityの中でも大事なのが、以下のフィールド

pid: プロセスのID。殺す時に指定する。
query_start: SQLクエリを実行し始めた時間。
query: 実行しているSQLクエリ。長いことも多々あるので適当に最初の方だけ見ている。
state: 実行状態。activeでないものは何らかの理由で待っているだけなので、殺すべき対象にならないはず。

これらを元に以下のようなクエリで問い合わせる。

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                       
-------+-------------------------------+---------------------------------------------------- 
   847 | 2014-09-09 05:45:21.225616+00 | UPDATE "delayed_jobs" SET locked_at = '2014-01-09'
   850 | 2014-09-09 05:45:17.664716+00 | UPDATE "delayed_jobs" SET locked_at = '2014-09-09'

他にも、datname, usename, application_name, client_hostname, backend_start, state_change, waiting などが必要な状況があるかも知れないけれど、ミニマムではこのクエリがいい気がする。

2. pidから実行中のクエリを殺す

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

SELECT pg_cancel_backend(847);

これは SIGINT にあたる。

これでも死なない場合は、

SELECT pg_terminate_backend(847);

を使えば、 SIGTERM に相当するので死ぬはず。

これを実行した際に起こることには責任は持てないので、使うときは自己責任で。

おまけ：大量に殺したい時

同じようなクエリが複数発生していることもあるので、

SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE substr(query, 0, 50) = 'hogehoge'

といようにすれば条件にマッチするもの全てを殺すことが出来る。