最近 PostgreSQL で動いているシステムに携わるようになって、しばらく MySQL ばかり触っていたので、PostgreSQL の障害調査の時とかに四苦八苦するので、使えそうな SQL とかコマンドの類をメモとして書いておく。
現在実行中のクエリを確認する
pg_stat_activity
ビューに、PostgreSQLの各サーバープロセスのに関する情報を持っているので、これを参照する。
select * from pg_stat_activity where state != 'idle' order by query_start asc;
また、例えば実行に 30 秒以上かかっているクエリを参照する場合は以下のような感じになる。
select pid , client_addr , query_start , query from pg_stat_activity where state = 'active' and query_start < ( current_timestamp - interval '30' second ) and pid <> pg_backend_pid() order by query_start;
ロックの状態を確認する。ロック状態にあるものは pg_locks ビューにロックの情報が格納されているので、ここに格納されている pid と pg_stat_activity ビューにある pid を元に内部結合して、ロックされているプロセスの情報を参照する。
select locktype , pg_locks.pid , mode , relation::regclass , usename , application_name , client_addr , query_start , state_change , granted , state , query from pg_locks inner join pg_stat_activity on pg_locks.pid = pg_stat_activity.pid where pg_locks.pid <> pg_backend_pid();
各テーブルのサイズ等の情報を確認する
https://lets.postgresql.jp/documents/technical/statistics/2
各テーブルの使用率を取得する。
select pgn.nspname , relname , pg_size_pretty(relpages::bigint * 8 * 1024) AS size , CASE WHEN relkind = 't' THEN ( select pgd.relname from pg_class pgd where pgd.reltoastrelid = pg.oid ) WHEN nspname = 'pg_toast' AND relkind = 'i' THEN ( select pgt.relname from pg_class pgt where SUBSTRING(pgt.relname FROM 10) = REPLACE(SUBSTRING(pg.relname FROM 10), '_index', '')) ELSE ( select pgc.relname from pg_class pgc where pg.reltoastrelid = pgc.oid ) END::varchar AS refrelname , CASE WHEN nspname = 'pg_toast' AND relkind = 'i' THEN ( select pgts.relname from pg_class pgts where pgts.reltoastrelid = ( select pgt.oid from pg_class pgt where SUBSTRING(pgt.relname FROM 10) = REPLACE(SUBSTRING(pg.relname FROM 10), '_index', '') ) ) END AS relidxrefrelname , relfilenode , relkind , reltuples::bigint , relpages from pg_class pg , pg_namespace pgn where pg.relnamespace = pgn.oid and pgn.nspname NOT IN ('information_schema', 'pg_catalog') order by relpages desc;
VACUUM の情報を確認する
select relname , n_live_tup , n_dead_tup , round(n_dead_tup * 100 / nullif(n_live_tup + n_dead_tup, 0), 2) as dead_ratio , last_autovacuum from pg_stat_user_tables;
実行中のクエリの停止
以下で停止したいクエリの pid を取得する。
select * from pg_stat_activity;
取得した pid に対して以下を実行する。
SELECT pg_cancel_backend(pid);
上記でも停止しない場合は以下を実行する。
SELECT pg_terminate_backend(pid);