PostgreSQL のトラブル時に使うクエリなどのメモ書き

最近 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();

各テーブルのサイズ等の情報を確認する

各テーブルのサイズを取得する。

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 の情報を確認する

n_live_tup(有効な行数)とn_dead_tup(不要な行数)、それらの比率や最後に AutVacuum が実行されたタイミングを確認できる。

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);

dshimizu/blog

アルファ版