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

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

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

参考

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です