2018-04-09

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

tech book

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

内部構造から学ぶ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

2018-04-03

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

tech

ある機能が重いなどといった理由で、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あたりに追加して、重いページでどういうクエリが発行されているか様子見すると、まずはどの辺が悪そうかがざっくり判断できるのでおすすめです。