PostgreSQL DBA 入門
PostgreSQL DBA 入門
よく使うビュー
運用管理
容量監視
ディスク容量測定
データベース領域
pg_database_size('db') : データベース全体の容量
pg_relation_size('table') : テーブルやインデックスそれぞれの容量
pg_total_relation_size('table') : インデックスのサイズを含むテーブルのサイズ
WAL領域/アーカイブWAL領域
df / du コマンド
メンテナンス
VACUUM/自動バキューム
pg_stat_user_tables
SELECT last_vacuum, last_autovacuum, n_dead_tup FROM pg_stat_user_tables; - last_vacuum, last_autovacuum でVACUUM, 自動VACUUMがいつ実行されたか、n_dead_tupで不要なタプルが何行削除されたか
pg_stat_user_tables
last_analyze, last_autoanalyze でいつANALYZE/自動ANALYZE(自動VACUUMによるANALYZE)を実行したか
n_dead_tupで除去された不要な行数
pg_stats
テーブルごとに収集された統計情報。ANALYZEが実行されていないと No rows となる。
REINDEX
pg_class
relpages, reltuples でページ数、行数の確認。行数に対してページ数が多くないかといった観点で確認
パフォーマンス
アクセス統計情報 : 標準統計情報ビュー
pg_stat_activity
track_activitiesパラメータが有効だと実行中のSQL内容も確認。長時間経過のSQLやロック中のSQLを確認
pid, query_start, query, state 等を一緒に確認。ロングトランザクションを確認される場合、xact_start も合わせて確認
pg_stat_database
blk_hitとblks_read等でキャッシュヒット率を確認
データベースあたり1行の形式でデータベース全体の情報表示
同一トランザクション中では同じ結果。pg_stat_clear_snapshot関数で最新情報に更新
テーブル上の最古のXIDから現在のXIDのトランザクション数確認 - SELECT datname, age(datfrozenxid) FROM pg_database; - SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r';
pg_stat_bgwriter
checkpoints_reqが大きい → checkpoint_segments増加の検討
buffers_backendがbuffers_allocに対して大きい → shared_buffersの値が不足している可能性
pg_stat_all_tables
テーブルあたり1行の形式でテーブルへのアクセス統計情報を記録
seq_tup_read / seq_scan でテーブルスキャン1回分の読み取り行数 → 予想より大きい場合、インデックスが想定どおりに利用されていない可能性
n_dead_tupでバキューム対象の行数
pg_relation_size関数等と合わせて利用すると大半のバキューム対象量を把握可能
n_tup_hot_upd / n_tup_upd でHOT更新の比率を確認 → 比率が予想より小さい場合、費用なインデックスの存在やロングトランザクションの影響の調査の必要性
pg_statio_all_tables
テーブルあたり1行の形式で、ブロック単位のI/Oに関する統計情報
_blks_readが_blks_hitより大幅に低い → 共有バッファが有効動作している - *_blks_readはOSキャッシュから読み取られた場合もカウントされるので、ディスクアクセス量をそのまま表示しているわけではない
pg_stat_all_indexes
インデックス毎のアクセスに関する統計情報 → 使用されていないインデックスの特定可能
idx_tup_read列は該当インデックスが利用された際に取得したエントリ数
idx_tup_fetchはBitmpIndexScanとして利用された場合加算されない
pg_stat_all_indexesとpg_statio_all_indexesの共通列が多いため、結合が容易 - SELECT * FROM pg_stat_user_indexes NATURAL JOIN pg_statio_user_indexes LIMIT 1;
pg_statio_all_indexes
インデックス毎のI/Oに関する統計情報
テーブル/カラム統計情報
pg_class
relpagesとreltuplesでそれぞれテーブルのページ数と行数
pg_statistic
列単位で統計情報
実データの一部が格納され一般ユーザーでは参照できないため、一般ユーザー向けにpg_statsを用意
pg_stats
実行計画が想定どおりに選択されない場合の原因調査
null_fracでNULLの割合、avg_widthでバイト単位の平均サイズ、n_distinctで個別値の数、割合
most_common_valsで頻出値、most_common_freqsで頻出値の割合が配列
histogram_boundsでヒストグラム境界値、correlationで相関率(ディスク上の物理的な行の並び順と論理的な並び順)
ANALYZE実行直後にもかかわらず、EXPLAIN ANALYZE実行時の想定行数と実際の行数の乖離等 - データの分布に極端な偏りがある場合 - → 該当列のstatistics値をデフォルトの100より大きい値にしてプランナがより正確なデータ分布を把握できるようにする等の対策
ロックのチューニング
pg_locks
SQL文との対応はpg_statc_activityと結合
行ロック取得しているトランザクションの表示にはpgrowlogsモジュール併用の必要性
スロークエリの検出
pg_stat_statements
実行された全てのSQL文の実行時の統計情報
EXPLAIN 確認点 例
子ノードから順に「下から上へ」問題箇所を探す
初期コストと総コストの差が大きい箇所を探す
親ノードの初期コストから子ノードの総コストを引いた値が親ノードの実質的な初期コスト
→ 親ノードか子ノードが重いのかの切り分け
EXPLAIN ANALYZEの場合
cost と actual timeの相対差大きい箇所
実際の取得行数と予測行数の差が大きい箇所
コストが大きい場合
Seq Scan かつ cost が大きいノード
インデックスの追加を検討 ※ クエリ中に該当列名が必ずしも含まれるわけではない
検索条件列
結合条件列
ソート条件列
インデックス作成済みなのに利用されない場合
検索条件に問題がないかの確認
LIKE検索等で中間一致が使われていないか
検索条件を変更できないか
全文検索機能が利用できないか - 関数が使われていないか
関数インデックスを作り直すことができないか
実際の取得行数と予測行数の差が大きい場合
統計情報が更新されていない
自動ANALYZEの実行を待つ
手動ANALYZE
統計情報を更新してもrowsの値に問題
STASTICSの値の増加
default_statistics_targetではなくALTER文でテーブルの列ごとの設定を変更
目安は該当列中の頻出値の個数以上
参考
LPI-Japan OSS-DB Gold 認定教材 PostgreSQL 高度技術者育成テキスト
URL: https://www.amazon.co.jp/gp/product/B00P4WD4QG/ref=as_li_qf_sp_asin_tl?ie=UTF8&camp=247&creative=1211&creativeASIN=B00P4WD4QG&linkCode=as2&tag=lpijapan01-22
28.2. The Statistics Collector
URL: https://www.postgresql.org/docs/9.6/monitoring-stats.html
pg_stat_* , pg_statio_*
50.11. pg_class
URL: https://www.postgresql.org/docs/9.6/catalog-pg-class.html
50.46. pg_statistic
URL: https://www.postgresql.org/docs/9.6/catalog-pg-statistic.html
50.77. pg_stats
URL: https://www.postgresql.org/docs/9.6/view-pg-stats.html
Last updated