PostgreSQLが重い時や接続数が多い時の対応

PostgreSQLが重いとか負荷が高いとかメモリがやばい時などに役立つかもしれないSQLです。

動作環境
現在の接続状況
クエリのキャンセル
セッション切断
最大接続数の確認
まとめ

動作環境

Linux系(CentOS7)
PostgreSQL 11.8

現在の接続状況

stateの値を確認する

active：問い合わせを実行中です。
idle：新しいクライアントからのコマンドを待機しています。

postgres=# SELECT datid,datname,pid,usename,query_start,state FROM pg_stat_activity ORDER BY pid;
  datid   |  datname  |  pid  | usename  |          query_start          | state
----------+-----------+-------+----------+-------------------------------+--------
 33867487 | test01_db | 27083 | test01   | 2022-09-08 07:00:19.890734+00 | idle
 33867487 | test01_db | 29316 | test01   | 2022-09-08 07:00:32.035811+00 | idle
 33867487 | test01_db | 29388 | test01   | 2022-09-08 06:46:42.518732+00 | idle
 33867487 | test01_db | 29930 | test01   | 2022-09-08 04:41:15.242637+00 | idle
          |           | 30243 |          |                               |
          |           | 30244 |          |                               |
          |           | 30245 |          |                               |
          |           | 30246 |          |                               |
          |           | 30248 | postgres |                               |
 33867487 | test01_db | 30360 | test01   | 2022-09-08 06:54:09.898685+00 | idle
 33867487 | test01_db | 30693 | test01   | 2022-09-08 06:10:19.44827+00  | idle
 33867487 | test01_db | 30999 | test01   | 2022-09-08 07:00:21.051092+00 | idle
    12723 | postgres  | 31602 | postgres | 2022-09-08 07:00:32.270094+00 | active

クエリのキャンセル

pidはキャンセルしたいプロセスのpid

postgres=# SELECT pg_cancel_backend(pid);
 pg_cancel_backend
-------------------
 t

セッション切断

pidは切断したいプロセスのpid

postgres=# SELECT pg_terminate_backend(pid);
 pg_terminate_backend
----------------------
 t

最大接続数の確認

postgres=# SHOW max_connections;
 max_connections
-----------------
 100

まとめ

処理が重いクエリを実行してしまった時とかに役立つと思います。

最大接続数を調整するときも、常時どれくらい接続があるのか確認もできます。

DBをチューニングするより、CPUを高性能にしたり、SSDに変えたり、メモリを増やしたりハード面の性能アップのほうが効果あるかもしれません。