最近 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;
実行計画を取得する
実行計画を取得する。トランザクションを貼って実行するようにして、影響を少なくする。
begin; explain analyze ...; rollback;
実行計画を取得する
キャッシュヒット率をみる。
select
datname
, blks_hit * 100.0 / (blks_read + blks_hit) AS cache_hit
from
pg_stat_database
where
datname = 'db_name_hogehoge';
実行中のクエリを停止する
以下で停止したいクエリの pid を取得する。
select * from pg_stat_activity;
取得した pid に対して以下を実行する。
SELECT pg_cancel_backend(pid);
上記でも停止しない場合は以下を実行する。
SELECT pg_terminate_backend(pid);
