この文書はPostgreSQL11.7とpgpool4.1.1の話題です。
なんらかの理由でテーブルをロックしたままのPostgreSQLのSQLをキャンセルしてロックを開放したい場合以下の関数を使います。
この関数は成功するとtrueを返すのですが、trueが返ってきたからといって、キャンセルできる(プロセスを終了できる)わけではありません。
以下に例を示します。
ap2=# select l.pid , a.backend_start from (select pid from pg_locks group by pid) l,pg_stat_activity a where l.pid=a.pid order by query_start; pid | backend_start -------+------------------------------- 10673 | 2020-05-06 14:06:57.721961+09 18222 | 2020-05-06 15:52:12.091632+09 28546 | 2020-05-06 16:30:04.77062+09 (3 rows) ap2=# SELECT pg_cancel_backend(10673); pg_cancel_backend ------------------- t (1 row) ap2=# SELECT pg_cancel_backend(18222); pg_cancel_backend ------------------- t (1 row) ap2=# select l.pid , a.backend_start from (select pid from pg_locks group by pid) l,pg_stat_activity a where l.pid=a.pid order by query_start; pid | backend_start -------+------------------------------- 10673 | 2020-05-06 14:06:57.721961+09 18222 | 2020-05-06 15:52:12.091632+09 28546 | 2020-05-06 16:30:04.77062+09 (3 rows)
10673と18222のプロセスを終了したいのですが、終了できません。
pg_terminate_backend(pid int)を使うと終了できるのですが、pgpoolを使っている場合、pg_terminate_backendを使うとpgpoolがPostgreSQLを切り離すので、サービスが中断します。
ErogameScapeにおいて、上記のようにpg_cancel_backendがきかないのは以下のような状況のときのようでした。
Apacheにアクセス
↓
php-fpmがpgpoolにクエリを発行
↓
pgpoolがPostgreSQLにクエリを発行
↓
クエリの実行に時間がかかる
↓
一定時間たってもphp-fpmから応答がないのでApacheがtimeoutとしてユーザーに504を返す
↓
このあとpsコマンドで確認すると
pgpoolはidleもしくはidle in transaction
PostgreSQLはSELECTだったりUPDATEだったりpgpoolが発行したSQLを実行している状態
以下、例を示します。
[root@sakura ap2]# cat /var/log/httpd/access_log | grep update 127.0.0.1 - - [08/May/2020:04:40:01 +0900] "GET /~ap2/ero/toukei_kaiseki/update_userreview_display_log.php HTTP/1.1" 504 247 "-" "Wget/1.19.5 (linux-gnu)" 60027802 127.0.0.1 - - [08/May/2020:04:41:02 +0900] "GET /~ap2/ero/toukei_kaiseki/update_userreview_display_log.php HTTP/1.1" 504 247 "-" "Wget/1.19.5 (linux-gnu)" 60060326 127.0.0.1 - - [08/May/2020:04:42:04 +0900] "GET /~ap2/ero/toukei_kaiseki/update_userreview_display_log.php HTTP/1.1" 200 462 "-" "Wget/1.19.5 (linux-gnu)" 6428
[root@sakura ap2]# cat /var/lib/pgsql/11/log/postgresql-Fri.log 2020-05-08 04:41:10.718 JST [28908] [ap2] LOG: duration: 8271.792 ms execute: UPDATE userreview SET display_unique_count = coalesce( display_unique_count , 0 ) + $1 WHERE uid = $2 AND game = $3 ;
ap2=# select * from (select pid from pg_locks group by pid) l,pg_stat_activity a where l.pid=a.pid order by query_start;
pid | datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port |
backend_start | xact_start | query_start | state_change
| wait_event_type | wait_event | state | backend_xid | backend_xmin |
query | backend_type
-------+----------+---------+-------+----------+---------+------------------+-------------+-----------------+-------------+-
------------------------------+-------------------------------+-------------------------------+-----------------------------
--+-----------------+------------+--------+-------------+--------------+----------------------------------------------------
-------------------------------------------------------------------+----------------
28908 | 10934808 | ap2 | 28908 | 16385 | ap2 | | 127.0.0.1 | | 55878 |
2020-05-08 04:41:01.103872+09 | 2020-05-08 04:41:02.407331+09 | 2020-05-08 04:41:02.446477+09 | 2020-05-08 04:41:02.446478+0
9 | Client | ClientRead | active | 17877103 | | UPDATE userreview SET display_unique_count = coales
ce( display_unique_count , 0 ) + $1 WHERE uid = $2 AND game = $3 ; | client backend
この状態でSELECT pg_cancel_backend(28908);しても、28908を終了させることができません。
28908を使っているpgpoolのプロセスを殺したところ、28908を終了させることができました。
pcp_proc_infoコマンドで28908を使っているpgpoolのプロセスIDを確認します。
[ap2@sakura ~]$ pcp_proc_info -h /var/run/postgresql/ -U pgpool | grep 28908 Password: ap2 ap2 2020-05-08 04:36:00 2020-05-08 04:41:01 3 0 2 28908 1 27546 0
28908と27546のプロセスを確認します。
[ap2@sakura ~]$ ps ax | grep 27546 8121 pts/0 S+ 0:00 grep --color=auto 27546 27546 ? S 0:00 pgpool: ap2 ap2 127.0.0.1(58634) idle in transaction [ap2@sakura ~]$ ps ax | grep 28908 8134 pts/0 S+ 0:00 grep --color=auto 28908 28908 ? Ss 0:00 postgres: ap2 ap2 127.0.0.1(55878) UPDATE
27546をkillします。
※確か、kill 27546では殺せなくて、kill -KILL 27546で殺しました。
これで、28908のプロセスも開放されて、ロックされていたテーブルもロックが解除されました。
※そもそも、なぜpostgresがSELECTやUPDATEのまま止まってしまうのかの原因は未だ分かりません…
