最近PostgreSQLでSQLチューニングや、DBが詰まった時の状況調査をいろいろやった。その時に便利だったクエリ達をまとめていく。PostgreSQLのバージョンは9.6系です。
SQLチューニングなどに便利だったクエリ達
それ以降に実行するSQLの実行時間を表示する。参考 https://morumoru00.wordpress.com/2011/05/08/postgresql-sql%E5%87%A6%E7%90%86%E6%99%82%E9%96%93%E3%82%92%E8%AA%BF%E3%81%B9%E3%82%8B%EF%BC%88timing/
\timing
実際にクエリを実行して実行計画や実行時間を表示する。クエリが実行されるので破壊的な操作も実行されてしまうことに注意。トランザクション張って最後にROLLBACKしましょう。参考 https://www.postgresql.jp/document/9.6/html/sql-explain.html
BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;
テーブルサイズや行数など、色んな情報込みで眺める。テーブルの肥大化やインデックスの肥大化などの様子を知ることが出来る。参考 https://qiita.com/awakia/items/99c3d114aa16099e825d
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;
auto_explainを使って一定以上遅いクエリを自動でEXPLAIN ANALYZEしてログに表示する。MySQLのスロークエリログみたいなやつ。これは PostgreSQLでauto_explainを使ってどのクエリが遅いか把握する - $shibayu36->blog; に詳しく書きました。
DBの障害時などに使うことができるクエリ達。
現在実行中のクエリ一覧。参考 https://www.postgresql.jp/document/9.6/html/monitoring-stats.html
SELECT * FROM pg_stat_activity where state != 'idle' order by query_start asc;
現在active状態で、1分以上実行されているクエリを探す。
SELECT pid,client_addr, query_start, query FROM pg_stat_activity WHERE state = 'active' AND query_start < ( current_timestamp - interval '1' minute ) AND pid <> pg_backend_pid() ORDER BY query_start;
現在のロック状況を見る。grantedがtならロックを獲得できているし、fならロック待ち状態。参考 https://www.postgresql.jp/document/9.6/html/view-pg-locks.html
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 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 granted = false AND pg_locks.pid <> pg_backend_pid();
masterかslaveか確認。マスタとして動作していた場合はfalseが変えるし、slaveの時はtrue。レプリが切れていたら両方false。参考 http://tyawan080.hatenablog.com/entry/2014/05/12/234226
SELECT pg_is_in_recovery();
そもそもレプリケーションできているか。レプリケーションが出来ていないと空が返る。
SELECT * FROM pg_stat_replication;
特定のクエリが詰まっててDBに影響を及ぼしている場合に、指定したpidのクエリを殺す。
SELECT pg_terminate_backend(<pid>);
条件にマッチするクエリを一括で殺す。殺す範囲がでかいので自己責任で...以下の例は1分以上実行しているSELECT文を殺すもの。
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'active' AND query LIKE 'select%' AND query_start < ( current_timestamp - interval '1' minute ) AND pid <> pg_backend_pid();
その他便利だったクエリ達
SELECTした結果をタブ区切りでファイルに出力する。
$ psql database_name -h ... -A -F $'\t' -c "SQL" > result.tsv
テーブルの特定のカラムの分布図を表示。参考 https://www.postgresql.jp/document/9.6/html/view-pg-stats.html
select most_common_vals, most_common_freqs, histogram_bounds from pg_stats where tablename = 'table' and attname = 'column';
テーブルの特定カラムのNULL値の割合を表示
select null_frac from pg_stats where tablename = 'table' and attname = 'column';
テーブルの特定カラムの統計情報をいろいろ表示。割合やdistinctした時の数など。
select * from pg_stats where tablename = 'table' and attname = 'column';
条件にマッチする設定パラメータを表示。
select name, setting, unit, short_desc from pg_settings where name LIKE '%autovacuum%';
oidをrelation名などに変換。pg_locksとかでどのrelationをロックしてるかとかはoidで出てくるのだけど、そのままだと意味不明なので、::regclassで変換してあげるといい。
select <oid>::regclass;
まとめ
今回はSQLチューニングや障害状況調査に最近使ったクエリ達をまとめてみました。他にもこういうのが便利というのがあったら是非教えてください。
id:a-know