Zenn
Open6

PostgreSQLのメンテナンス機能に関するメモ

PostgreSQL
SQL
ヒガシヒガシ

このスクラップを書く目的

  • DBの性能劣化に起因する障害を防止すること、障害を早期に解消すること

背景

  • 業務において検索処理がタイムアウトして画面を表示できない障害を経験した。
  • 原因はPostgreSQLの統計情報が適切に収集されておらず、不適切な実行計画が作成されたことだった。
  • 筆者にはPostgreSQLのメンテナンスに関する知識が無く、トラブルシューティングに貢献することができなかった。
ヒガシヒガシ

前提知識：PostgreSQLに依存しないDB一般の話

SQL実行の仕組み

  1. パーサ：構文をチェックする
  2. リライタ：一定のルールに沿ってSQLを修正する
  3. プランナ／オプティマイザ：統計情報に基づいて実行計画を作成する
    • 統計情報：SQLの最適な実行計画を決めるためのテーブルやインデックスに関する情報。いわゆる「データについてのデータ」
      • 例：行数・サイズ・データの重複度合いや分布状況
    • 実行計画：統計情報を元に選択されたSQLの実行方法
      • 例：検索方法・結合方法・結合順序
  4. エグゼキュータ：実行計画に従ってSQLを実行する

SQLはどんなデータを取得するか・データをどんな状態にするかを宣言するが、どのような経路でデータにアクセスするかには関与しない。オプティマイザが統計情報に沿って最短(と思われる)経路を選択し、実行計画を作成する。

DBの運用設計においては、DBMSが最適な実行計画を選択できるように統計情報を適切に収集する必要がある。

統計情報収集のポイント

1. 統計情報収集のタイミング

  • 原則として 「データが大きく更新された後になるべく早く」 収集する(ここでいう更新=INSERT／UPDATE／DELETE)
  • できればシステムへのアクセスが少ない時間帯に行う



大規模なデータ更新後に統計情報を収集しない場合、実際のテーブルの状態と統計情報が乖離する(この状態を「統計情報が古い」と表現することがある)。統計情報が古いと適切な実行計画が作成されず、DBの性能が劣化する場合がある。逆に、データの更新料が少なければ統計情報を収集する必要はない。


また、統計情報の収集はメモリ消費量がそれなりに多いため、システムへのアクセスが多い時間帯に実施すると逆に性能を劣化させる可能性がある。したがって、できればシステムへのアクセスが少ない時間帯(多くの場合は夜間)に実施することが望ましい。

2. 統計情報収集の対象

  • 大きくデータが更新されるテーブルやインデックス

1.から必然的に導かれる事なので、これ以上言及しない。

ヒガシヒガシ

前提知識：PostgreSQL固有の話

  • PostgreSQLは追記型アーキテクチャを採用している。追記型アーキテクチャとは、データ更新時に元データを更新するのではなく、更新後のデータを追加する設計である。
  • 追記型アーキテクチャにより、複数のトランザクションがそれぞれ異なる時点のデータを参照できる。
  • 更新前のデータを参照するトランザクションが無くなると不要領域として扱われる。不要領域が大量に残ると性能劣化に繋がる。
  • 性能劣化を防ぐために定期的にVACUUMを実行して不要領域を削除する必要がある。
ヒガシヒガシ

PostgreSQLの設定

PostgreSQLはデフォルト設定である程度はDBをメンテナンスしてくれる。
設定値はいずれもPostgreSQL16.xにおける値である。

項目名 概要 デフォルト値
autovacuum 自動バキュームの実行有無 on
autovacuum_naptime 自動バキューム要否のチェック間隔
単位が無い場合は秒		 1min
autovacuum_vacuum_threshold 自動バキューム実行の閾値
指定行数以上の更新・削除時に起動		 50
autovacuum_vacuum_insert_threshold 自動バキューム実行の閾値
指定行数以上の挿入時に起動		 1000
autovacuum_vacuum_scale_factor 自動バキューム実行の閾値
指定割合以上の行の更新・削除時に起動		 0.2
autovacuum_vacuum_insert_scale_factor 自動バキューム実行の閾値
指定割合以上の行の挿入時に起動		 0.2
autovacuum_analyze_threshold 自動実行の閾値
指定行数以上の更新・削除時に起動		 50
autovacuum_analyze_scale_factor 自動アナライズ実行の閾値
指定割合以上の行の更新・削除時に起動		 0.1

デフォルト設定では自動バキュームは以下のような挙動をとる。

  • 1分毎に各テーブルの自動バキューム要否をチェックする
  • 以下いずれかの条件を満たすなら、自動バキュームを実行する
    • 更新・削除行数が50 + 0.2 * (テーブル行数)を超えている
    • 挿入行数が1000 + 0.2 * (テーブル行数)を超えている
  • 以下の条件を満たすなら、自動アナライズを実行する
    • 更新・削除行数が50 + 0.1 * (テーブル行数)を超えている

つまり、特に意識せずともある程度は定期的に不要領域の削除や統計情報の収集が実行されるようになっている。

備考

  • postgres.confファイルで設定を管理する
  • ALTER TABLEでテーブル毎にVACUUM, ANALYZEの挙動を個別設定することも可能