> For the complete documentation index, see [llms.txt](https://hayashier.gitbook.io/article/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://hayashier.gitbook.io/article/rdbms/postgresql-dba-overview.md). # 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: * 28.2. The Statistics Collector * URL: * pg\_stat\_\* , pg\_statio\_\* * 50.11. pg\_class * URL: * 50.46. pg\_statistic * URL: * 50.77. pg\_stats * URL: