DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ

肥大化防止：PostgreSQLのパフォーマンスを維持する高度なVACUUM戦略

安全な自動バキューム調整、手動VACUUMのガイダンス、インデックスメンテナンス、トランザクションID監視により、PostgreSQLの肥大化を防止します。

肥大化防止：PostgreSQLのパフォーマンスを維持する高度なVACUUM戦略

PostgreSQLは、強力で多用途なオープンソースのリレーショナルデータベースであり、データの整合性とパフォーマンスを維持するためにいくつかの内部メカニズムに依存しています。その中でも、VACUUM操作は、ストレージ領域を再利用し、デッドタプルによるパフォーマンス低下を防ぐ上で重要な役割を果たします。VACUUMは基本的な用語で語られることが多いですが、高度なバキューム戦略を理解し実装することは、PostgreSQLデータベースの健全性と速度に大きな影響を与える可能性があります。

テーブルの肥大化は、ビジーなデータベースでよく見られる問題で、削除または更新された行がデッドタプルとして残り、すぐに削除されない場合に発生します。これらのデッドタプルはディスク容量を消費し、データベースがより多くのデータをスキャンする必要があるため、クエリの実行を遅くする可能性があります。PostgreSQLの自動バックグラウンドプロセスであるAutovacuumはこれを管理することを目的としていますが、そのデフォルト設定はすべてのワークロードに最適であるとは限りません。有用な作業は、どのテーブルがより積極的なクリーンアップを必要とし、どのテーブルをそのままにしておいてもよいか、そして手動メンテナンスウィンドウがいつ中断に値するかを知ることです。

テーブルの肥大化とその影響を理解する

PostgreSQLは、マルチバージョン同時実行制御（MVCC）システムを使用しています。行が更新されると、行の新しいバージョンが作成され、古いバージョンはデッドとしてマークされます。同様に、行が削除されると、デッドとしてマークされますが、すぐには削除されません。これらのデッドタプルは、VACUUM操作によってクリーンアップされるまでテーブルに残ります。VACUUMが十分な頻度で実行されないか、十分に積極的でない場合、デッドタプルが蓄積され、テーブルの肥大化につながります。

テーブル肥大化の結果は重大です：

  • ディスク使用量の増加：肥大化したテーブルは必要以上にディスク容量を消費し、ストレージの問題やバックアップ時間の増加につながる可能性があります。
  • クエリパフォーマンスの低下：肥大化したテーブルをスキャンするクエリは、デッドタプルを含むより多くのデータを処理する必要があり、実行時間が長くなります。インデックスの肥大化も同様に有害な影響を与える可能性があります。
  • キャッシュ効率の低下：肥大化したテーブルとインデックスはデータベースのキャッシュ内でより多くのスペースを占有し、メモリに保持できるアクティブに使用されるデータの量を減らす可能性があります。
  • Autovacuumのオーバーヘッド：Autovacuumがタプルの更新と削除の速度に追いつくのに苦労すると、それ自体がパフォーマンスのボトルネックになる可能性があります。

Autovacuumのチューニング：最初の防御線

Autovacuumは、大幅な変更が行われたテーブルに対してVACUUMおよびANALYZE操作を自動的に実行するように設計されたバックグラウンドプロセスです。デフォルトで有効になっていますが、その効果は適切な設定に大きく依存します。Autovacuumパラメータをチューニングすることは、過度なシステム負荷をかけずに肥大化を防ぐために重要です。

postgresql.confにある主要なAutovacuum設定パラメータ：

  • autovacuum_vacuum_threshold：テーブルでVACUUMが実行されるまでの、更新または削除されたタプルの最小数。デフォルトは50です。
  • autovacuum_vacuum_scale_factor：VACUUMが実行される前のテーブルサイズの割合。デフォルトは0.2（20%）です。
    • VACUUMは、(デッドタプル数) > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * (ライブタプル数)の場合にトリガーされます。
  • autovacuum_analyze_threshold：ANALYZEが実行されるまでの、挿入、更新、または削除されたタプルの最小数。デフォルトは50です。
  • autovacuum_analyze_scale_factor：ANALYZEが実行される前のテーブルサイズの割合。デフォルトは0.1（10%）です。
    • ANALYZEは、(変更されたタプル数) > autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * (ライブタプル数)の場合にトリガーされます。
  • autovacuum_vacuum_cost_delay：コスト制限を超えた場合にスリープする時間（ミリ秒単位）。デフォルトは20msです。
  • autovacuum_vacuum_cost_limit：バキュームプロセスがスリープする前に蓄積できる最大コスト量。デフォルトは-1（vacuum_cost_limitが設定されている場合はそれを使用し、それ以外の場合は事実上無制限で、理想的ではありません）。
  • autovacuum_max_workers：同時に実行できるバックグラウンドバキュームプロセスの最大数。デフォルトは3です。
  • autovacuum_nap_time：Autovacuumタスクの開始間の最小遅延。デフォルトは1分です。

実用的なAutovacuumチューニングシナリオ：

  1. 高トランザクションレートデータベース：頻繁に更新と削除が行われるテーブルの場合、autovacuum_vacuum_thresholdとautovacuum_vacuum_scale_factorを低く設定して、バキュームをより頻繁にトリガーする必要があるかもしれません。例えば、ビジーなテーブルでは、次のように設定します：

    ALTER TABLE your_table SET (autovacuum_vacuum_threshold = 500, autovacuum_vacuum_scale_factor = 0.05);
ALTER TABLE your_table SET (autovacuum_analyze_threshold = 200, autovacuum_analyze_scale_factor = 0.02);

    これにより、この特定のテーブルでのバキュームがより積極的になります。

  2. 大規模で静的で、たまに更新されるテーブル：主に読み取り専用で、めったに更新されないテーブルの場合、デフォルト設定で問題ないか、scale_factorを増やして不要なバキュームオーバーヘッドを減らすこともできます。

  3. Autovacuumの影響の制御：Autovacuumがリソースを消費しすぎないようにするには、autovacuum_vacuum_cost_delayとautovacuum_vacuum_cost_limitを調整します。適切な値はストレージの速度とワークロードによって異なるため、数値を盲目的にコピーするのではなく、通常のトラフィック中にテストしてください。

    ALTER TABLE your_table SET (
  autovacuum_vacuum_cost_limit = 2000,
  autovacuum_vacuum_cost_delay = 5
);

    session_replication_roleはAutovacuumのチューニング制御ではありません。トリガーとルールの動作に影響を与えるため、肥大化管理のショートカットとして使用すべきではありません。

手動VACUUMのベストプラクティス

Autovacuumは不可欠ですが、手動のVACUUM操作が必要または有益な状況もあります：

  • 大規模なデータロード/削除後：大量の一括操作の後に手動でVACUUMを実行すると、すぐに領域を再利用し、肥大化の蓄積を防ぐことができます。
  • Autovacuumが追いつかない場合：Autovacuumが実行されているにもかかわらず、大幅な肥大化が観察される場合、手動のVACUUMで即座にクリーンアップできます。
  • 極度の肥大化に対するVACUUM FULL：通常のVACUUMでも不十分な深刻な肥大化の場合、VACUUM FULLを使用できます。ただし、VACUUM FULLはテーブル全体を新しいファイルに書き換えるため、ブロッキング操作（排他ロックが必要）であり、大規模なテーブルでは非常に長い時間がかかる可能性があります。これは細心の注意を払って、理想的にはメンテナンスウィンドウ中に使用する必要があります。
  • VACUUM (FREEZE)：このオプションは、将来のすべてのトランザクションによって永続的に可視と見なされるのに十分古い残りのタプルをフリーズするようにVACUUMに強制します。これは、VACUUM警告を防ぎ、トランザクションIDラップアラウンドの問題の可能性を減らすのに役立ちます。

手動VACUUMコマンド：

  • 標準のVACUUM：領域を再利用し、再利用可能にします。TRUNCATEが使用されない限り、ディスク上のファイルサイズを大幅に縮小することはありません。
    VACUUM your_table;
VACUUM VERBOSE your_table; -- より多くの出力を提供
  • VACUUM ANALYZE：VACUUMを実行し、次にテーブル統計を更新します。これはクエリプランナーにとって重要です。
    VACUUM ANALYZE your_table;
  • VACUUM FULL：テーブルを書き換え、未使用の領域をすべて再利用し、ファイルを縮小します。排他ロックが必要です。
    VACUUM FULL your_table;
  • VACUUM (FREEZE)：古いタプルのフリーズを強制します。
    VACUUM (FREEZE) your_table;
  • VACUUM (TRUNCATE)：PostgreSQL 13+で利用可能なこのオプションは、TRUNCATEと同様にテーブルファイルの末尾から領域を再利用できますが、操作全体に対して排他ロックは必要ありません。ただし、最後に短い排他ロックが必要です。
    VACUUM (TRUNCATE) your_table;

高度な戦略と考慮事項

基本的なAutovacuumのチューニングと手動のVACUUMコマンドに加えて、いくつかの高度なテクニックでバキュームをさらに最適化できます：

  1. 肥大化の監視：テーブルの肥大化を定期的に監視します。SQLクエリを使用して肥大化を推定したり、監視ツールを利用したりできます。

    -- 肥大化を推定するクエリ（pgstattuple拡張機能が必要）
-- CREATE EXTENSION pgstattuple;
SELECT
    schemaname,
    relname,
    pg_size_pretty(pg_total_relation_size(oid)) AS total_size,
    pg_size_pretty(pg_table_size(oid)) AS table_size,
    pg_size_pretty(pg_total_relation_size(oid) - pg_table_size(oid)) AS index_size,
    CASE WHEN dead_tuples > 0 THEN round(100.0 * dead_tuples / (live_tuples + dead_tuples), 2) ELSE 0 END AS percent_bloat
FROM (
    SELECT
        schemaname,
        relname,
        n_live_tup AS live_tuples,
        n_dead_tup AS dead_tuples,
        c.oid
    FROM pg_stat_user_tables s JOIN pg_class c ON s.relid = c.oid
) AS stats
WHERE live_tuples + dead_tuples > 0
ORDER BY percent_bloat DESC;

-- 拡張機能なしで肥大化を推定する代替クエリ
SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    CASE WHEN n_live_tup > 0 THEN round(100.0 * n_dead_tup / (n_live_tup + n_dead_tup), 2) ELSE 0 END AS percent_bloat
FROM pg_stat_user_tables
ORDER BY percent_bloat DESC;

  2. インデックスメンテナンス：インデックスも肥大化する可能性があります。必要に応じてREINDEXを使用して再構築します。通常のREINDEXは通常の作業をブロックする可能性があります。REINDEX CONCURRENTLYは中断を減らしますが、時間がかかり、計画が必要です。

    REINDEX INDEX CONCURRENTLY your_index_name;

  3. トランザクションIDラップアラウンドの防止：PostgreSQLはトランザクションIDを再利用します。IDが最大値に達すると、ラップアラウンドします。データ破損を防ぐために、PostgreSQLは古いタプルをフリーズします。VACUUM（特にFREEZEを使用するもの）は重要な役割を果たします。Autovacuumのfreeze_max_ageパラメータは、他のしきい値が満たされていなくても、Autovacuumが強制的に実行される前にトランザクションIDがどの程度古くなるかを決定します。

    -- トランザクションIDの経過時間を監視
SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY age(datfrozenxid) DESC LIMIT 10;

    非常に大きな経過時間が表示される場合、バキュームが追いついていない可能性があることを示しています。

  4. パーティショニング戦略：非常に大きなテーブルの場合は、パーティショニングを検討してください。小さなパーティションのバキュームは、巨大な単一テーブルのバキュームよりもはるかに高速で、リソース消費も少なくなります。

  5. コネクションプーリング：直接的なバキューム戦略ではありませんが、効率的なコネクションプーリング（PgBouncerの使用など）は、データベース接続の確立にかかるオーバーヘッドを削減でき、間接的に全体的なデータベースパフォーマンスに利益をもたらし、Autovacuumなどのバックグラウンドメンテナンスタスクをよりスムーズに実行できるようにします。

  6. 長時間トランザクションの制御：単一の古いトランザクションがクリーンアップを妨げる可能性があります。長時間開かれているセッション、特にidle in transactionセッションを確認してください。これらは古い行バージョンを可視に保ち、肥大化を強制的に成長させる可能性があるためです。

    SELECT pid, state, now() - xact_start AS transaction_age, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

実践的なバキュームチューニングワークフロー

サーバー全体ではなく、問題のあるテーブルから始めてください。900 GBの注文テーブルが肥大化していて、20 MBのルックアップテーブルがクリーンな場合、グローバルな変更はノイズを発生させるだけで、実際の問題を解決しない可能性があります。最初にpg_stat_user_tablesを確認します：

SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  last_autovacuum,
  last_autoanalyze,
  vacuum_count,
  autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

次に、それをワークロードと比較します。ステータスを常に更新するキューライクなテーブルは、低いautovacuum_vacuum_scale_factorを必要とする場合があります。これは、巨大なテーブルの20%がデッドになるのを待つのは遅すぎるからです。月次のアーカイブパーティションは、積極的な設定をまったく必要としない場合があります。テーブルごとの設定により、これらのケースを異なる方法で処理できます。

更新の多いテーブルでは、一般的なパターンは次のとおりです：

ALTER TABLE job_events SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_analyze_threshold = 1000
);

これらの数値は出発点であり、普遍的な真実ではありません。Autovacuumの実行間でデッドタプルが増加しなくなったか、クエリレイテンシが改善されたか、Autovacuumがピーク時に許容できないI/Oを生成していないかを監視します。

肥大化がすでに深刻な場合、通常のVACUUMは悪化を防ぐかもしれませんが、リレーションファイルを縮小することはありません。これは多くのチームを驚かせます。通常のVACUUMはテーブル内で領域を再利用可能にしますが、通常、ほとんどの領域をオペレーティングシステムに返しません。大規模なテーブルを物理的に縮小するには、VACUUM FULL、テーブルの再構築、パーティションのローテーション、または許可されている場合はpg_repackなどのツールといった、破壊的なオプションから選択することになります。各オプションには、ロック、ディスク容量、および運用上のトレードオフがあります。

最も痛みの少ない修正を選択する

テーブルが中程度に肥大化しているが、まだ安定した書き込みを受けている場合は、Autovacuumのチューニングと古いトランザクションのクリーンアップから始めてください。PostgreSQLが営業時間中に大規模なテーブルを書き換える代わりに、自然に領域を再利用するようにしたいのです。

テーブルが一度だけパージされ、現在ははるかに小さい場合、通常のVACUUMにより、空き領域が将来の挿入と更新で再利用可能になります。その領域をオペレーティングシステムに戻す必要がある場合は、書き換えオプションを計画してください。VACUUM FULLはシンプルですがブロッキングします。pg_repackは中断が少なくて済みますが、追加の拡張機能であり、置き換え構造を構築するために十分な空きディスク容量が必要です。パーティションテーブルは別のオプションを提供します：1つの巨大なテーブルから数百万行を削除する代わりに、古いパーティションを削除またはデタッチします。

インデックスが問題である場合、習慣ですべてのインデックスを再構築しないでください。どのインデックスが大きく、未使用で、重複しているかを確認してください。pg_stat_user_indexesはインデックススキャンカウントを示し、スキーマレビューにより、(user_id)と(user_id, created_at)のように、1つだけが必要な重複インデックスが明らかになる場合があります。真に未使用のインデックスを削除すると、書き込みパフォーマンスが向上し、将来のバキューム作業が削減されます。

SELECT
  schemaname,
  relname,
  indexrelname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

再起動後や統計リセット後の「未使用」インデックスには注意してください。カウンターがリセットされるためです。何かを削除する前に、十分な履歴を確認してください。

優れたバキューム戦略は、機能しているときは退屈です。Autovacuumが十分な頻度で実行され、デッドタプルが蓄積されず、手動メンテナンスは既知のイベントのために予約され、古いトランザクションは無害なアイドルセッションではなく、本番環境の問題として扱われます。目標は、可能な限りバキュームすることではありません。目標は、アプリケーションが必要とするI/Oを奪うことなく、クリーンアップをチャーンより先に進めておくことです。