パフォーマンスチューニングのための調査に便利な SQL

PostgreSQL で、スロークエリの調査や、重いクエリを実行してしまって結果が返って来なくなってしまったときなどに活用できる SQL をご紹介します。

（その他便利な SQL などあったら教えていただきたいです。）

統計情報コレクタ

PostgreSQLの統計情報コレクタはサーバの活動状況に関する情報を収集し、報告するサブシステムです。

PostgreSQLは他のサーバプロセスによって現在実行されている正確なコマンドなど現在システム内で起きていること、またシステム内にどんな他の接続が存在するかということについての動的情報を正確に報告する機能を持ちます。

pg_stat_activity

サーバプロセスあたり １ 行の形式で、状態や現在の問い合わせ等のプロセスの現在の活動状況に関連した情報を表示させる。

-- 実行中のクエリに関する情報を表示させる
SELECT
  pid
  ,application_name
  ,wait_event_type
  ,wait_event
  ,now() - state_change AS wait_dur
  ,now() - query_start AS query_dur
  ,regexp_replace(regexp_replace(query, '^[[:space:]]+', ''), '[[:space:]]+', ' ', 'g') as query
FROM
  pg_stat_activity
WHERE
  pid <> pg_backend_pid()
  AND state = 'active'
ORDER BY
  query_start ASC
;

上記の SQL を実行した結果:

  pid  | application_name | wait_event_type | wait_event |    wait_dur     |    query_dur    |        query         
-------+------------------+-----------------+------------+-----------------+-----------------+----------------------
 27464 | Postico 1.5.19   | Timeout         | PgSleep    | 00:00:03.730648 | 00:00:03.730654 | select pg_sleep(60);
(1 row)

※ 裏で以下の SQL を実行している

select pg_sleep(60);

pg_stat_user_functions

関数ごとに 1 行の形式で、関数の実行に関する統計情報を表示させる。

-- ストアドファンクションの実行時間を表示させる
SELECT
  funcid
  ,funcname
  ,calls
  ,round((self_time / calls / 1000)::numeric, 1) AS self_time_sec
  ,round((total_time / calls / 1000)::numeric, 1) AS total_time_sec
FROM
  pg_stat_user_functions
ORDER BY
  total_time_sec DESC
;

システムカタログ

システムカタログとは、リレーショナルデータベース管理システムがテーブルや列の情報などのスキーマメタデータと内部的な情報を格納する場所です

pg_locks

ロック対象となる進行中のオブジェクト、要求されたロックモード、および関連するプロセスごとに 1 行の形式で、ロックに関する情報を表示させる。

SELECT
  pid
  ,granted
  ,locktype
  ,relation::regclass
  ,mode
FROM
  pg_locks
WHERE
  pid <> pg_backend_pid()
ORDER BY
  pid ASC
;

上記の SQL を実行した結果:

  pid  | granted |  locktype  | relation |      mode       
-------+---------+------------+----------+-----------------
 27464 | t       | relation   | sample   | AccessShareLock
 27464 | t       | virtualxid |          | ExclusiveLock
(2 rows)

※ 裏で以下の SQL を実行している

begin;
select * from sample;
-- commit しないで放置

時間のかかっているクエリ、ロックをとっているプロセスを kill したい場合:

pg_stat_activity や pg_locks から取得した pid を使って、サーバシグナル送信関数で取り消すことができる。

-- バックエンドの現在の問い合わせを取り消す（SIGINT）
SELECT pg_cancel_backend([pid]);

-- バックエンドを終了する（SIGTERM）
SELECT pg_terminate_backend([pid]);