$shibayu36->blog;

株式会社はてなでエンジニアをしています。プログラミングや読書のことなどについて書いています。

PostgreSQLでSQLチューニングや障害状況調査に使ったクエリ達まとめ

最近PostgreSQLSQLチューニングや、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; に詳しく書きました。

PostgreSQLの現在の状況を把握するクエリ達

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チューニングや障害状況調査に最近使ったクエリ達をまとめてみました。他にもこういうのが便利というのがあったら是非教えてください。

「内部構造から学ぶPostgreSQL」読んだ

最近PostgreSQLに発行するクエリのパフォーマンスチューニングや、PostgreSQLの調査を行うことが多かったので、ちゃんと学ぼうと思って読んだ。

内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 (Software Design plus)

内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 (Software Design plus)

この本はその名の通り、内部処理がどのように行われているかを紹介しながら、PostgreSQLの概念やパフォーマンスチューニングの勘所、統計情報の見方などなどを教えてくれる。特にPostgreSQLを自分で運用している人にはおすすめな本だろう。

アプリケーションエンジニアとして、PostgreSQLの運用自体はしていないけど、アプリケーションからクエリを書いている自分にとっては、以下の場所が非常に面白かった。

  • 6.6 性能を踏まえたインデックス定義
  • 12.2 正常動作の監視
    • pg_stat_activityなど、データベースの統計情報を知る術や、現在の発行しているクエリの様子を知る術を教えてくれる
  • 15章 実行計画の取得/解析
    • EXPLAINの見方を詳しく教えてくれる

特に15章はかなり参考になった。PostgreSQLのEXPLAINは、MySQLのときのEXPLAINとは全く出力が違ったので全く読めてなかったのだけど、これで読めるようになったなーという感じ。ついでに結構PostgreSQL側がいろんなことをやっているのだなということも知ることができた。



さて、以上のような面白いところはあったけれど、逆にもうちょっと基本的な、PostgreSQLで使える機能とか、あとインデックスの使われ方みたいなところは知ることが出来なかったなと思った。この辺学ぶには何を見ると良いのかな〜。

読書ノート

- PostgreSQLのプロセス構成 430
    - マスタサーバ
    - ライタ: 共有バッファの内容をデータファイルに書き出す
    - WALライタ: WALバッファの内容をWALファイルに書き出す
    - チェックポインタ: すべてのダーティページをデータファイルに書き出す
    - 自動バキュームランチャ
    - 自動バキュームワーカ
    - 統計情報コレクタ
    - バックエンドプロセス: クライアントから接続要求に対して起動され、クエリを処理する
- PostgreSQLのメモリ構成 463
    - 共有バッファ: テーブルやインデックスのデータをキャッシュ
    - WALバッファ
    - 空き領域マップ
    - 可視性マップ: テーブルのデータが可視であるか否か
- テーブルファイルは、複数の8192バイトの「ページ」によって構成 530
- インデックスファイルもテーブルファイルと同様に8192バイト 530
- TOASTファイル: テーブル内に長大な行(2KB以上とか)を格納する場合に生成される特殊なファイル 530
- 設定項目が反映されるタイミング 605
    - SET文実行時
    - SIGHUPシグナル受信時
    - 起動時
    - pg_settingsビューのcontext列の値で確認可能
- チェックポイントは、PostgreSQLがクラッシュしたときに、どの箇所からリカバリ処理を行うのかを示すポイントになる 770
- バキュームは、データファイルやインデックス内の不要領域を再利用可能にする 787
- アナライズはクエリ実行に利用する統計情報を収集しpg_statisticを更新する処理 787
- 問い合わせ処理はパーサ、リライタ、プランナ、エクゼキュータという風に実行される 827
- PostgreSQLは追記型アーキテクチャ 962
- 主キーと一意性制約が定義された列には暗黙のB-treeインデックスが作成される 1088
- 外部キーには暗黙的なインデックスが設定されない 1105
- PostgreSQLに対して更新要求があった時、まず更新のログをWALバッファに書き込む 1246
- 複数列インデックスがなくとも、ほとんどの場合検索条件として使われる可能性がある個々の列に対して別々のインデックスを作成すれば十分なケースが多い 1408
- 関数インデックス 1408
- 部分インデックス: 特定の条件を満たす行の値のみインデックス化の対象にできる 1418
- PostgreSQLの稼働統計情報ビュー 1598
    - pg_stat_database, pg_statio_user_tables, pg_statio_user_indexes, pg_stat_activity
- PostgreSQLの正常動作の監視 2506
- Index Only ScanはVisibility Mapを確認する形で実現されている 2756
    - EXPLAINのBUFFERSオプションを付けるとHeap Fetchesの値で分かる
- EXPLAINのBUFFERSオプションを付与すると、共有バッファから読み込んだページ数とディスクから読み込んだページ数を追加で表示する 2860
- EXPLAINの実行計画の読み方 2974~ ☆
- データ結合の実行計画 3015 ☆
    - Nested Loop, Hash Join, Merge Join
- 結合時は外側テーブルと内側テーブルの順序も性能に大きな影響を与える 3025
- Nested Loopは二重ループ。外側テーブルの行数が少なく、内側テーブルにインデックスが貼っていると、他の二つより高速に動く 3026
- ハッシュ結合は、内側テーブルの結合キーでハッシュを作成し、ハッシュと外側テーブルの結合キーを評価する 3026
    - ハッシュを作成するためにまず、内側テーブルをスキャンし、その後外側テーブルをスキャンするという動作になる
    - ハッシュがメモリに収まるサイズの場合有効
- 実行計画の処理コストの見方 3086

PostgreSQLでauto_explainを使ってどのクエリが遅いか把握する

ある機能が重いなどといった理由で、DBのどのクエリが遅いか把握したいことはよくあります。そんな時、PostgreSQLのauto_explainが便利だったので紹介。

auto_explainを使うと、指定した実行時間以上を利用しているクエリに対して、自動で実行計画をログファイルに出力してくれるというもの。詳細はこちら。


最近便利に使えたのは以下の設定。

# 自動でEXPLAIN ANALYZEしてパフォーマンス解析したい時用
session_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = 100 # 100ms以上かかっているクエリを自動でEXPLAINする
auto_explain.log_analyze = on # 出力にはEXPLAIN ANALYZEを使う
# 入れ子状の文に対してもEXPLAINする。
# 例えば外部キー制約でCASCADE DELETEなどを使っていた場合、その実行計画も出力する。
auto_explain.log_nested_statements = on
# あるクエリから発行されるトリガの実行計画も一緒に表示してくれる。
# 上記のlog_nested_statementsで実行時間などは分かるのだが、このオプションを有効にしておくと
# どのクエリからトリガが発行されているかひと目で分かって便利。
auto_explain.log_triggers = on

この辺りの設定をpostgresql.confあたりに追加して、重いページでどういうクエリが発行されているか様子見すると、まずはどの辺が悪そうかがざっくり判断できるのでおすすめです。