【PostgreSQL】メンテナンス系コマンドの種類

Transcript

1. 【PostgreSQL】 メンテナンスコマンド の種類
2. 目次 1. はじめに：なぜメンテナンスが必要か？ 2. 統計情報とは何か？（前知識） 3. 物理順序と論理順序とは何か？（前知識） 4. 各コマンドの役割と特徴 5. 自動 vs 手動　実務で意識するポイント 6. 実務での落とし穴 7. まとめ 8. 参考文献
3. 1. 　はじめに：なぜメンテナンスが必要か？ • 実行計画が遅くなる原因の多くは 「統計情報や物理配置の劣化」 • 自動実行（autovacuum等）だけでは、 タイミングや精度が不十分なことも多い • 適切なタイミングで明示的にメンテナンスすることで、 クエリ性能を維持・改善できる • 放置すると、不要なシーケンシャルスキャンや 誤ったJOIN順序が選ばれることも
4. 2. 　統計情報とは何か？（前知識） • 実行計画(※1)の最適化に使われる、データの概要情報 ◦ 例：件数（カーディナリティ）、値の分布、NULLの割合 など • データベースは統計情報をもとに 　「どのインデックスを使うか」「結合順序はどうするか」 を判断する • 情報が古い・不正確だと、非効率な実行計画が選ばれる (※1)実行計画 DBMSがクエリを最適に実行するための手順書 ・どのインデックスを使用するか ・どのテーブルをどの順番で結合するか ・テーブルスキャンorインデックススキャン 　等
5. 2. 　統計情報とは何か？（前知識） SELECT * FROM 購入明細 WHERE CUSTOMER_ID = ‘C’ パーサ （構文解析） リライタ （書換） プランナ （オプティマイザ） エグゼ キュータ 実行結果 きゃ 実行 計画 統計 情報 適切な実行計画がない場合 統計情報を使用し 実行計画を作成 テーブル 実行計画に従い データアクセス Oracle 実行計画取得 PostgreSQL 実行計画作成 ※例外有 クエリ実行の流れ
6. 3. 　物理順序と論理順序とは何か？（前知識） • 物理順序 ◦ テーブルにデータが実際に格納されている順序（※ヒープ構造） ◦ データの並び順に意味はなく、 INSERTやUPDATEにより順序は崩れやすい ◦ 統計情報は各カラムごとに収集され、実行計画の判断材料となる • 論理順序 ◦ インデックスが管理する、検索用の並び順 ◦ インデックスは、特定のカラムやカラムの組み合わせごとに作成され、 ◦ 値の昇順／降順などの論理的な並び順でアクセスできるように構造化されている • 物理順序と論理順序は必ずしも一致しない ◦ 追加、更新、削除で差異が生じることがある • 順序が一致していると、 I/O効率が上がり、処理が高速化される ・※ヒープ構造：テーブルの実データが格納されている場所 （行の並び順に意味はなく、順序は自動で整わない）
7. 4. 　各コマンドの役割と特徴（標準機能） コマンド CPU使用率 ロック 実行時間 備考 ANALYZE 低 ACCESS SHARE 読み取り専用 数秒 統計情報更新 VACUUM 中 SHARE UPDATE EXCLUSIVE 更新共有排他ロック 数秒〜分 デッドタプル除去。 膨張対策 VACUUM FULL 高 ACCESS EXCLUSIVE 排他ロック 数分〜時間 ディスク圧縮。 時間がかかるため使用注意 CLUSTER 高 ACCESS EXCLUSIVE 排他ロック 数分〜時間 物理順序の並び替え。 直後にANALYZE推奨 REINDEX 中〜高 ACCESS EXCLUSIVE(テーブル全体) →検索× 追加更新削除× ACCESS EXCLUSIVE(インデックス単位 ) →検索⚪ 追加更新削除× 分 インデックス断片化対策
8. 4. 　各コマンドの役割と特徴（補助的手段） コマンド CPU使用率 ロック 実行時間 備考 pg_repack 中 ほぼなし 数分〜時間 拡張モジュール 。VACUUM FULL代替。 インストールや事前準備が必要。 autovacuum 可変 - バックグラウンド リトライや起動時間の考慮が必要。
9. 5. 　自動 vs 手動　実務で意識すること 🔁 自動 × 手動の組み合わせ • 自動は「広く浅く」、手動は「狭く深く」 • autovacuum の限界を補う目的で手動実行を併用 ◦ 例：月末処理や大量削除後に VACUUM FULL や pg_repack • 定期スクリプトに ANALYZE や REINDEX を組み込むケースも多い
10. 5. 　自動 vs 手動　実務で意識すること 🔄 リトライ処理の考慮 • autovacuum にリトライ機能は存在しない ◦ テーブルのロックを取得できない場合はスキップされる • 一度失敗すると再実行の保証がない ◦ 例：長時間ロックや競合 → 統計情報が永遠に更新されない ◦ 対策：バッチやスクリプトで再試行処理を実装するのが安全
11. 5. 　自動 vs 手動　実務で意識すること 考慮項目 内容 ロック確認 実行前に pg_locks でロック状況を確認し衝突を回避 時間帯の配慮 業務時間外や夜間にメンテ処理をまとめて実行 ログ監視 autovacuum の失敗や遅延をログや pg_stat_user_tables で検知 閾値調整 scale_factor, threshold を調整し、頻繁な更新に対応 過剰な自動化の回避 全自動に頼りすぎず、手動メンテの余地を残すことで異常に気付きや すくなる
12. 6. 　⚠ 実務での落とし穴 • VACUUM だけではディスク使用量は減らない ◦ → VACUUM FULL や pg_repack が必要 • CLUSTER 実行後に ANALYZE を忘れると ◦ → 統計情報が古く、実行計画が最適化されない • autovacuum が実行されていると思い込む ◦ → 実際はトランザクション競合や設定不足で動いていないことも • インデックスの断片化に気づかず放置 ◦ → 検索性能が劣化、REINDEX の検討が必要 • メンテナンス処理が業務時間中に走り、ロックで障害発生 ◦ → 時間帯や影響範囲を考慮して実行すべき
13. 7. 　まとめ • パフォーマンス劣化の多くは「統計情報の不整合」と「データ断片化」が原因 ◦ → ANALYZE や VACUUM による更新・整理が基本 • コマンドごとのロック特性を理解して使い分ける ◦ → VACUUM FULL や CLUSTER は強力なロックに注意 • autovacuum に過信は禁物 ◦ → ロック取得に失敗するとスキップされ、その場での再試行は行われない • 自動と手動を併用し、運用に適したメンテナンスを計画的に実行 ◦ → 月末処理後・大量削除後などは手動実行が有効
14. 8. 　参考文献 • ANALYZE ◦ https://www.postgresql.org/docs/current/sql-analyze.html • VACUUM、VACUUM FULL ◦ https://www.postgresql.org/docs/current/sql-vacuum.html • VACUUMのロック方法確認箇所 ◦ PostgreSQLのソースコード（ vacuum.c）にて、VACUUMが取得するロックは以下 rel = table_open(relid, ShareUpdateExclusiveLock); → SHARE UPDATE EXCLUSIVEロック が使用されている • CLUSTER ◦ https://www.postgresql.org/docs/current/sql-cluster.html • REINDEX ◦ https://www.postgresql.org/docs/current/sql-reindex.html • Pg_repack ◦ https://github.com/reorg/pg_repack#overview