第 22章定常的なデータベース保守作業

目次
22.1. 定常的なバキューム作業
22.1.1. ディスク容量の復旧
22.1.2. プランナ用の統計情報の更新
22.1.3. トランザクションIDの周回エラーの防止
22.1.4. 自動バキュームデーモン
22.2. 定常的なインデックスの再作成
22.3. ログファイルの保守

PostgreSQLサーバの円滑な稼働を維持するために定期的に行わなければならない、定常的な保守作業があります。 ここで説明する作業は繰り返し行うべきものであり、また、cronスクリプトなどの標準的なUnixツールを使用して簡単に自動化することができます。 しかし、適切なスクリプトを設定し、その実行の成功を点検することは、データベース管理者の責任です。

明らかに必要な保守作業の1つに、定期的なデータのバックアップコピーの作成があります。 最近のバックアップがなければ、(ディスクの破損、火災、重要なテーブルの間違った削除などの)破滅の後、復旧することができません。 PostgreSQLで可能なバックアップとリカバリ機構については、第23章にて詳細に説明します。

他の保守作業の主なカテゴリには、定期的なデータベースの"バキューム"があります。 この作業については項22.1で説明します。

この他、ログファイルの管理も定期的に注意しなければなりません。 これについては項22.3で説明します。

PostgreSQLは他のデータベース管理システムに比べ、保守作業は少ないと言えます。 それでもなお、これらの作業に適切に注意することは、システムに対する快適かつ充実した経験を確実に得るのに効果があります。

22.1. 定常的なバキューム作業

PostgreSQLVACUUMコマンドは以下の理由により定期的に実行させる必要があります。

  1. 更新、あるいは削除された行によって占められたディスク領域の復旧。

  2. PostgreSQL問い合わせプランナによって使用されるデータ統計情報の更新。

  3. トランザクションIDの周回による非常に古いデータの損失を防止。

上述の理由それぞれを目的として実行されるVACUUMの頻度や適用範囲は各サイトの必要性によって変わります。 したがって、データベース管理者はこれらの問題を理解し、適切な保守計画を構築しなければなりません。 本節は、高度な問題を説明することに専念していますので、コマンドの構文などの詳細については、VACUUMのリファレンスページを参照してください。

PostgreSQL 7.2から、標準形式のVACUUMは、普通のデータベース操作(選択、挿入、更新、削除、ただし、テーブルスキーマの変更は除きます)と並行して実行できるようになりました。 そのため、定常的なバキューム処理は以前のリリースのような邪魔なものではなく、1日のうちで使用頻度が低い時間にスケジューリングすることは重要なことではなくなりました。

PostgreSQL 8.0から、バックグラウンドで実行されるバキューム処理による性能への影響を軽減させることを調整できるように設定パラメータが追加されました。 項17.4.4を参照してください。

自動的に必要なVACUUM操作を実行する機構がPostgreSQL 8.1で追加されました。 項22.1.4を参照してください。

22.1.1. ディスク容量の復旧

通常のPostgreSQLの操作では、行のUPDATEもしくはDELETEは古い行を即座に削除しません。 この方法は、多版同時性制御(第12章を参照してください)の恩恵を受けるために必要なものです。 あるバージョンの行は他のトランザクションから参照される可能性がある場合は削除されてはなりません。 しかし、結局は、更新される前の行や削除された行を参照するトランザクションはなくなります。 必要なディスク容量が無限大にならないように、これらが占める領域は、新しい行で再利用できるように回収されなければなりません。 これはVACUUMを実行することで行われます。

はっきり言って、頻繁に更新、削除されるテーブルは、滅多に更新されないテーブルよりもより頻繁にバキュームを行う必要があります。 変更頻度がないことがわかっているテーブルを除く、限定したテーブルに対してVACUUMを行う定期的なcron処理を設定することは有益なものになるかもしれません。 これは、巨大な、更新頻度が高いテーブルと巨大な更新頻度が低いテーブルの両方が存在する場合にのみ有益です。 小さなテーブルのバキューム処理のコストは考慮する必要はありません。

VACUUMコマンドには2種類あります。 1つ目の形式は"怠惰なバキューム"や単なるVACUUMと呼ばれるもので、テーブルやインデックス内の不要なデータに対して将来再利用できるように印を付けます。 不要データによって使用されている領域は、その領域がテーブルの最後にある場合、または、排他的テーブルロックが簡単に入手できる場合を除き、回収されません。 ファイルの先頭や中間の不要な領域によるファイルの縮小は行われず、ファイル内の未使用領域はオペレーティングシステムに返却されません。 この種のVACUUMは、普通のデータベース操作と同時に実行することができます。

2つ目の形式はVACUUM FULLコマンドです。 これは、より積極的なアルゴリズムを使用して不要になったバージョンの行が占める領域の回収を行います。 VACUUM FULLで解放された領域は全て即座にオペレーティングシステムに返却されます。 残念ながら、この種のVACUUMコマンドでは、VACUUM FULLが処理中のテーブルごとに明示的なロックが獲得されます。 したがって、VACUUM FULLを頻繁に使用すると、同時に実行されるデータベース問い合わせの性能をかなり下げてしまいます。

標準形式のVACUUMは、ディスク容量を安定状態の使用量のレベルで維持することを目的に最もよく使用されます。 ディスク容量をオペレーティングシステムに返却する必要がある場合は、VACUUM FULLコマンドを使用してください。 しかし、すぐに再度割り当てる必要があるディスク容量を解放することにどんな意味があるでしょうか? 更新頻度の激しいテーブルの保守においては、不定期のVACUUM FULLよりも適切な頻度で標準のVACUUMの方がより良い方法です。

ほとんどのサイトで推奨できる方法は、データベース全体のVACUUMを 1日1回使用頻度が低い時間帯にスケジュールすることです。 必要ならば、更新頻度の激しいテーブルのバキューム処理をより頻繁に行うよう追加してください (非常に高い頻度でデータの更新を行うインストレーションの一部では、分間隔位という頻度で高負荷なテーブルのVACUUMを行うこともあります)。 1つのクラスタで複数のデータベースがある場合、それぞれをバキュームすることを忘れないでください。 vacuumdbプログラムが役に立つかもしれません。

VACUUM FULLは、テーブル内のほとんどの行を削除したことが判明している場合に推奨します。 その安定状態のテーブルサイズをVACUUM FULLのより積極的な方式によって大いに縮小できるからです。 容量の復旧のための定常的なバキューム処理には、VACUUM FULLではなく、普通のVACUUMを使用してください。

テーブルの内容が定期的に完全に削除される場合、DELETEの後にVACUUMを使用するよりも、TRUNCATEを使用する方が良いでしょう。 TRUNCATEはテーブルの全ての内容を即座に削除します。 また、その後に不要となったディスク容量を回収するためにVACUUMVACUUM FULLを行う必要がありません。

22.1.2. プランナ用の統計情報の更新

PostgreSQL問い合わせプランナは、優れた問い合わせ計画を作成するのに、テーブルの内容に関する統計情報に依存しています。 この統計情報はANALYZEによって収集されます。 このコマンドはそのものを呼び出す以外にも、VACUUMのオプション処理としても呼び出すことができます。 合理的な精度の統計情報を持つことは重要です。 さもないと非効率的な計画を選択してしまい、データベース性能を悪化させてしまいます。

領域復旧のためのバキューム処理と同様、頻繁な統計情報の更新は、滅多に更新されないテーブルよりも更新の激しいテーブルにとってより有益です。 しかし、頻繁に更新されるテーブルであっても、データの統計的な分布が大きく変更されなければ、統計情報を更新する必要はありません。 単純な鉄則は、テーブル内の列の最小値、最大値にどのくらいの変化があったかを考えることです。 例えば、行の更新時刻を保持するtimestamp列の場合、最大値は行が追加、更新されるにつれて、単純に増加します。 こういった列は、おそらく、例えば、あるWebサイト上のアクセスされたページのURLを保持する列よりも頻繁に統計情報を更新する必要があるでしょう。 このURL列の更新頻度も高いものかもしれませんが、その値の統計的な分布の変更は相対的に見ておそらく低いものです。

特定のテーブルに対してANALYZEを実行することができます。 また、テーブルの特定の列のみに対してさえも実行することができます。 ですので、アプリケーションの要求に応じて、他よりも頻繁に一部の統計情報を更新できるような柔軟性があります。 しかし、実際は、この機能は有用ではないかもしれません。 PostgreSQL 7.2からANALYZEは、全ての行を読むのではなく、テーブルからランダムに行を抽出して統計処理を行うようになったため、巨大なテーブルに対してもかなり高速に処理するようになりました。 ですので、頻繁にデータベース全体に対して実行する方が、おそらくかなり単純になります。

ティップ: 列単位でのANALYZE実行頻度の調整は非常に実用的とは言えるものではありませんが、ANALYZEで集計される統計情報の詳細レベルの調整を列単位で行うことは価値がある場合があります。 WHERE句でよく使用され、データ分布の規則性がほとんどない列は、他の列よりもより細かいデータの度数分布が必要になるでしょう。 ALTER TABLE SET STATISTICSを参照してください。

ほとんどのサイトで推奨できる方法は、1日1回使用頻度の低い時間帯に、データベース全体に対してANALYZEをスケジュールすることです。 通常は、毎晩のVACUUMと組み合わせることができます。 しかし、テーブルの統計情報の変更が相対的に遅いサイトでは、過剰であるかもしれません。 より低い頻度でANALYZEを実行することで十分です。

22.1.3. トランザクションIDの周回エラーの防止

PostgreSQLのMVCCトランザクションのセマンティックは、トランザクションID(XID)番号の比較が可能であることに依存しています。 現在のトランザクションのXIDよりも新しい挿入時のXIDを持ったバージョンの行は、"未来のもの"であり、現在のトランザクションから可視であってはなりません。 しかし、トランザクションIDのサイズには制限(執筆時点では32ビット)があり、長時間(40億トランザクション)稼働しているクラスタはトランザクションの周回を経験します。 XIDのカウンタが一周して0に戻り、そして、突然に、過去になされたトランザクションが将来のものと見えるように、つまり、その出力が不可視になります。 端的に言うと、破滅的なデータの損失です (実際はデータは保持されていますが、それを入手することができなければ、慰めにならないでしょう)。

PostgreSQL 7.2の前まででは、XIDの周回から保護する唯一の方法は最低でも40億トランザクションごとにinitdbを再度行うことでした。 当然これは、トラフィックが多いサイトを十分に満足させることはありませんでしたので、より良い解決方法が案出されました。 この新しい方法では、サーバを、initdbや再起動などなく、限界なく稼働状態とすることができます。 この保守要求の代価は、データベースの各テーブルは、最低でも10億トランザクションごとにバキュームされなければならないということです。

実際、これは面倒な要求ではありませんが、失敗の結果は(ディスク容量の浪費や性能の低下ではなく)完全なデータの損失となりますので、データベース管理者が、この問題を避けることができるよう少し特別な準備を行いました。 クラスタ内のデータベース毎に、PostgreSQLは、直前のデータベース全体に対するVACUUM実行時刻を保持します。 いずれかのデータベースが10億トランザクションの危険な段階に近づいた時、システムは警告メッセージを出力し始めます。 何も行わなければ、適切な保守作業が手作業で行われるまで通常の操作を停止します。 本節の残りで詳細を説明します。

XIDの新しい比較方法では、2つの特殊なXIDを区別し、1と2の番号を付けます(BootstrapXIDFrozenXID)。 この2つのXIDは常に全ての通常のXIDよりも古いものとみなされます。 通常のXID(2以上の値)はmodulo-231という数式を使用して比較されます。 これは、全ての通常のXIDでは、20億の"より古い"XIDと20億の"より新しい"XIDが存在することを意味します。 言い換えると、通常のXID空間は終わることなく循環されているということです。 そのため、ある特定のXIDであるバージョンの行を作成すると、そのバージョンの行は、以降の20億トランザクションからはどの通常のXIDについて比較しているのかには関係なく、 "過去のもの"と認識されます。 そのバージョンの行が20億トランザクション以上後にも存在していた場合、それは突然に未来のものとして認識されます。 このデータ損失を防ぐために、20億トランザクションより古いとみなされるより、少し前に古いバージョンの行のXIDをFrozenXIDに再割り当てする必要があります。 この特殊なXIDに割り当てられた後は、周回問題に関係なく、全ての通常のトランザクションから"過去のもの"として認識され、また、そのバージョンの行はどれだけ古いものであろうと、削除されるまで好ましい状態となります。 このXIDの再割り当てはVACUUMで扱われます。

VACUUMの通常のポリシーは、過去の10億トランザクションより古い通常のXIDを持つバージョンの行を全てFrozenXIDに再割り当てすることです。 このポリシーは元々の挿入時のXIDをどこからも参照されることがなくなるまで、保存します (実際は、ほとんどのバージョンの行はおそらく"凍結"になることなく、生成、削除されるでしょう)。 このポリシーでは、任意のテーブルのVACUUMの最大の安全な間隔は、正確に10億トランザクションです。 この値以上の間行わなかったとすると、前回は再割り当てするほど古くなかったバージョンの行が20億トランザクション以上の古さとなってしまい、未来のものとして循環され、失われてしまいます (もちろん、その後の20億トランザクション後に再度出現しますが、これは何の助けにもなりません)。

周期的なVACUUMは、これまで説明してきた理由により、とにかく必要とされます。 10億トランザクションの間バキュームされないテーブルがあるとは考えられません。 しかし、管理者がこの制約に合っていることを確実にすることができるように、VACUUMpg_databaseシステムテーブルにトランザクションID統計情報を保存します。 特に、データベースのpg_database行のdatfrozenxidフィールドは、データベース全体に対するVACUUM操作(つまり、特定テーブルの指定のないVACUUM)が完了した時に更新されます。 このフィールドに保存された値は、VACUUMコマンドで使用された、凍結用の切り捨てXIDです。 この切り捨てXIDよりも古い、全ての通常のXIDはそのデータベースのFrozenXIDによって置換されていることが保証されています。 この情報を検査する簡便な方法は、以下の問い合わせを実行することです。

SELECT datname, age(datfrozenxid) FROM pg_database;

age列は切り捨てXIDから現在のトランザクションXIDまでのトランザクション数を測ります。

標準の凍結ポリシーでは、よくバキュームされたデータベースでのage列は10億から始まります。 ageが20億に近い場合、そのデータベースは、周回問題の危険性を回避するために、再度バキュームされなければなりません。 推奨する方式は、十分安全なマージンを確保するために、各データベースを少なくとも5億トランザクションごとにVACUUMを実行することです。 この規則に合わせることを補助するために、各データベース全体に対するVACUUMは、15億トランザクション以上のageを示すpg_databaseのエントリがあった場合に自動的に警告を発します。

play=# VACUUM;
WARNING:  database "mydb" must be vacuumed within 177009986 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "mydb".
VACUUM

VACUUMが発行する警告を無視し続け、周回するまでのトランザクションが1000万より少なくなると、PostgreSQLは、すべてのトランザクションの開始時に上のような警告を発行し始めます。 こうした警告も無視し続け、周回するまでのトランザクションが100万より少なくなると、システムは停止し、新しいトランザクションの実行を拒絶します。

play=# select 2+2;
ERROR:  database is shut down to avoid wraparound data loss in database "mydb"
HINT:  Stop the postmaster and use a standalone backend to VACUUM in "mydb".

この100万トランザクションという安全マージンは、管理者が必要なVACUUMコマンドを手作業で実行することで、データを失うことなくリカバリすることができるようにするために存在します。 しかし、システムがこの安全のための停止モードになると、コマンドを実行しませんので、 実行するためには、postmasterを停止し、スタンドアロンのバックエンドを使用してVACUUMを行う他ありません。 停止モードはスタンドアロンのバックエンドでは強制されません。 スタンドアロンのバックエンドの使用に関する詳細はpostgresマニュアルページを参照してください。

FREEZEオプション付きのVACUUMは、全ての開いているトランザクションによって適切とみなすことができるほど古いバージョンの行を凍結するという、より積極的な凍結ポリシーを使用します。 特に、VACUUM FREEZEがどちらかと言えば待ち状態のデータベースで行われた場合、そのデータベース中の全てのバージョンの行は凍結されます。 したがって、データベースがまったく変更されない限り、トランザクションIDの周回を防ぐことを目的としたバキューム処理を今後行う必要はなくなります。 この技術は、initdbにおいてtemplate0を準備するために使用されています。 また、これは、pg_databaseにてdatallowconn = falseと記録されたユーザ作成のデータベースの準備の時にも使用しなければなりません。 このデータベースに接続することができませんので、VACUUMを実行する方法が存在しないからです。

警告

pg_databaseにおいてdatallowconn = falseとされたデータベースは、適切に凍結されているものとみなされます。 こうしたデータベースに対しては、自動警告や周回を保護するための停止は行われません。 したがって、datallowconn = falseとする前にデータベースを正確に凍結させておくことは、ユーザの責任となります。

22.1.4. 自動バキュームデーモン

PostgreSQL 8.1から、autovacuum デーモンという独立した、省略可能なサーバプロセスが存在します。 このデーモンは、VACUUMANALYZE コマンドの実行を自動化することを目的としたものです。 有効にすると、autovacuumデーモンは周期的に実行され、大量のタプルの挿入、更新、削除があったテーブルを検査します。 この検査は、行レベルの統計情報収集機能を使用します。 したがって、stats_start_collectorおよびstats_row_leveltrueに設定されていないと、 autovacuumデーモンを使用することができません。 また、superuser_reserved_connectionsの値を検討する時に、autovacuumプロセス用のスロットを許可することも重要です。

autovacuumデーモンが有効の場合、autovacuumデーモンはautovacuum_naptime秒毎に実行され、どのデータベースを処理するかを決定します。 トランザクションIDが周回に近づいたデータベースはすべて即座に処理されます。 この場合、autovacuumはデータベース全体に対するVACUUMを呼び出し、終了します。 もし対象がテンプレートデータベースであった場合は、VACUUM FREEZEを呼び出し、終了します。 この基準を満たすデータベースが存在しなければ、autovacuumでもっとも過去に処理されたデータベースを選び、必要に応じてVACUUMまたはANALYZEコマンドを個別に呼び出します。

各テーブルに対してどの操作を行うかを決める時に、2つの条件が使用されます。 直前のVACUUMの後に不要となったタプル数が"バキューム閾値"を超えると、テーブルはバキュームされます。 このバキューム閾値は以下のように定義されます。

バキューム閾値 = バキューム基礎閾値 + バキューム規模係数 * タプル数

ここで、バキューム基礎閾値はautovacuum_vacuum_threshold、バキューム規模係数はautovacuum_vacuum_scale_factor、タプル数はpg_class.reltuplesです。 不要となったタプル数は、統計情報コレクタから取り出されます。 これは、各UPDATEDELETE操作で更新される、ほぼ正確な数です。 (負荷が高いと一部の情報が失われる可能性があることから、これはほぼ正確な数でしかありません。) 解析の場合も似たような条件が使用されます。 以下で定義される閾値が、直前のANALYZEの後に挿入、更新、削除されたタプル数と比較されます。

解析閾値 = 解析基礎閾値 + 解析規模係数 * タプル数

デフォルトの閾値と規模係数は、postgresql.confから取られますが、pg_autovacuumシステムカタログに項目を用意することで、テーブル毎に上書きすることができます。 特定のテーブルに対してpg_autovacuum行が存在する場合、そこで指定した設定が適用されます。 さもなければ、全体的な設定が使用されます。 全体的な設定に関する詳細は項17.9を参照してください。

基礎閾値と規模係数の他に、pg_autovacuumで3つのパラメータを各テーブルに対して設定することができます。 1つ目はpg_autovacuum.enabledで、これをfalseに設定することでautovacuumデーモンはこのテーブル全体を対象からはずします。 この場合autovacuumは、トランザクションIDの周回を防ぐためにデータベース全体をバキュームする時のみ、このテーブルを対象とします。 他の2つのパラメータ、バキュームコスト遅延(pg_autovacuum.vac_cost_delay)とバキュームコスト上限(pg_autovacuum.vac_cost_limit)です。 これらは、コストに基づくVacuum遅延機能用のテーブル固有の値を設定するために使用されます。

pg_autovacuum内の値をいずれかの負が設定された場合、あるいは、特定のテーブル用のpg_autovacuumが存在しない場合、postgresql.conf内の対応する値が使用されます。

現時点では、pg_autovacuumカタログに項目を作成するには、手作業でカタログにINSERTするしかありません。 この機能は今後のリリースで改良される予定です。 また、カタログの定義も変更される予定です。

注意

現在、pg_autovacuumシステムカタログの内容は、pg_dumpツールやpg_dumpallツールで作成されたデータベースダンプに保存されません。 ダンプ/リロードの過程でこれらを保持したい場合、このカタログを手作業で確実にダンプしてください。