Amazon RDS for PostgreSQL での PostgreSQL 自動バキュームの使用 - Amazon Relational Database Service

Amazon RDS for PostgreSQL での PostgreSQL 自動バキュームの使用

autovacuum 機能を使用して、PostgreSQL DB インスタンスの状態を維持することを強くお勧めします。autovacuum は、VACUUM コマンドと ANALYZE コマンドのスタートを自動化します。自動バキュームが、多数のタプルが挿入、更新、または削除されたテーブルを確認します。確認後、自動バキュームは PostgreSQL データベースから古いデータやタプルを削除することで、ストレージを再利用します。

デフォルトの PostgreSQL DB パラメータグループのいずれかを使用して作成した Amazon RDS for PostgreSQL DB インスタンスでは、デフォルトで自動バキュームがオンになっています。デフォルトのパラメータグループには、default.postgres10default.postgres11 などが含まれます。すべてのデフォルトの PostgreSQL DB パラメータグループには 1 に設定されたパラメータ rds.adaptive_autovacuum があるので、この機能がアクティブになります。autovacuum 機能に関連するその他の設定パラメータもデフォルトで設定されます。これらのデフォルト値は汎用的であるため、特定のワークロードに対して、autovacuum 機能に関連付けられているパラメータの一部をチューニングすることには利点があります。

次に、自動バキュームの詳細と、RDS for PostgreSQL DB インスタンスでそのパラメータの一部をチューニングする方法について説明します。概要については、「PostgreSQL を使用するためのベストプラクティス」を参照してください。

autovacuum のメモリを割り当てる

自動バキュームのパフォーマンスに影響を与える最も重要なパラメータの 1 つは、maintenance_work_mem パラメータです。このパラメータでは、データベーステーブルをスキャンしたり、バキューム処理するすべての行 ID を保持したりするために、autovacuum に割り当てるメモリの量を指定します。maintenance_work_mem パラメータの設定値が低すぎると、バキューム処理が完了するまでにテーブルの複数回のスキャンが必要になる場合があります。このような複数のスキャンは、パフォーマンスに悪影響を及ぼすことがあります。

計算を行うときは、次の 2 つの点を念頭に置いて maintenance_work_mem パラメータ値を決定します。

  • このパラメータのデフォルト単位はキロバイト (KB) です。

  • maintenance_work_mem パラメータは、autovacuum_max_workers パラメータと連動して機能します。小さいテーブルが多数ある場合、autovacuum_max_workers の割り当てを増やして maintenance_work_mem の割り当てを減らします。大きなテーブル (100 GB 以上など) がある場合は、メモリの割り当てを増やしてワーカープロセス数を減らします。最も大きいテーブルを正常に処理するには、十分なメモリを割り当てる必要があります。各 autovacuum_max_workers は、割り当てたメモリを使用できます。したがって、ワーカープロセスとメモリの組み合わせが、割り当てるメモリの合計と等しいことを確認してください。

通常、大きいホストの場合は、maintenance_work_mem パラメータを 1〜2 ギガバイト (1,048,576~2,097,152 KB) の値に設定します。非常に大きいホストの場合は、このパラメータを 2〜4 ギガバイト (2,097,152~4,194,304 KB) の値に設定します。このパラメータに設定する値は、ワークロードに応じて異なります。Amazon RDS では、このパラメータのデフォルト値が、次のようにしてキロバイト単位で計算されるように更新されています。

GREATEST({DBInstanceClassMemory/63963136*1024},65536).

トランザクション ID の循環の可能性を減らす

autovacuum に関連するパラメータグループの設定は、トランザクション ID の循環を防ぐほどは排除率が高くない場合があります。この問題に対処するために、RDS for PostgreSQL には autovacuum パラメータ値を自動的に適応させるメカニズムが用意されています。autovacuum パラメータのアダプティブチューニングは RDS for PostgreSQL の機能です。トランザクション ID の循環に関する詳しい説明については、PostgreSQL ドキュメントを参照してください。

自動バキュームパラメータのアダプティブチューニングは、動的パラメータ rds.adaptive_autovacuum が ON に設定されている RDS for PostgreSQL インスタンスでは、デフォルトでオンになります。この設定をオンにしておくことを強くお勧めします。ただし、autovacuum パラメータのアダプティブチューニングをオフにする場合は、rds.adaptive_autovacuum パラメータを 0 または OFF に設定します。

トランザクション ID の循環は、Amazon RDS で autovacuum パラメータをチューニングした後でも発生する場合があります。トランザクション ID の循環に対して Amazon CloudWatch アラームを実装することをお勧めします。詳細については、AWS データベースブログの記事「Implement an early warning system for transaction ID wraparound in RDS for PostgreSQL」(RDS for PostgreSQL でトランザクション ID の循環に早期警告システムを実装する) を参照してください。

自動バキュームパラメータのアダプティブチューニングをオンにすると、CloudWatch メトリクス MaximumUsedTransactionIDsautovacuum_freeze_max_age パラメータの値または 500,000,000 のいずれか大きいほうに達したときに、Amazon RDS で自動バキュームパラメータの調整が開始されます。

テーブルでトランザクション ID の循環の傾向が続く場合、Amazon RDS では自動バキュームパラメータの調整が続行されます。続行される調整ごとに、循環を避けるために autovacuum に割り当てられる専用のリソースが増えます。Amazon RDS は、以下の autovacuum 関連のパラメータを更新します。

これらのパラメータが RDS で変更されるのは、新しい値で autovacuum による排除率が高くなる場合に限られます。パラメータは、DB インスタンスのメモリで変更されます。パラメータグループの値は変更されません。現在のメモリ内の設定を確認するには、PostgreSQL の SHOW SQL コマンドを使用します。

これらの自動バキュームパラメータのいずれかが Amazon RDS で変更されると、影響を受ける DB インスタンスでイベントが生成されます。このイベントは、AWS Management Console や Amazon RDS API を介して表示できます。CloudWatch メトリクス MaximumUsedTransactionIDs がしきい値より低い値に戻ると、Amazon RDS はメモリ内の自動バキューム関連のパラメータをリセットして、パラメータグループで指定されている値に戻します。次に、この変更に対応する別のイベントが生成されます。

データベース内のテーブルにバキューム処理が必要かどうかの判別

次のクエリを使用して、データベース内のバキューム処理されていないトランザクションの数を表示できます。データベースの datfrozenxid 行の pg_database 列は、そのデータベースに表示されている正常なトランザクション ID の下限です。この列は、データベース内のテーブルあたりの relfrozenxid 値の最小数です。

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY age(datfrozenxid) desc limit 20;

例えば、前述のクエリの実行結果は以下のようになります。

datname | age mydb | 1771757888 template0 | 1721757888 template1 | 1721757888 rdsadmin | 1694008527 postgres | 1693881061 (5 rows)

データベースのトランザクション ID 数が 20 億に達すると、トランザクション ID (XID) の循環が発生し、データベースは読み取り専用になります。このクエリを使用してメトリクスを生成し、1 日に数回実行できます。デフォルトでは、autovacuum は保持するトランザクション数が 200,000,000 以下になるように設定されます ()。autovacuum_freeze_max_age

サンプルモニタリング戦略は次のようになります。

  • autovacuum_freeze_max_age の値を 2 億トランザクションに設定します。

  • テーブルのバキューム処理されていないトランザクション数が 5 億に達すると、重要度が低いアラームがトリガーされます。これは無効な値ではありませんが、autovacuum が遅れていることを示している場合があります。

  • テーブルのトランザクション数が 10 億に達した場合は、対処を要するアラームとして扱う必要があります。一般的に、パフォーマンス上の理由から、トランザクション数は autovacuum_freeze_max_age に近い値にしてください。以下の推奨事項を使用して調査することをお勧めします。

  • テーブルのバキューム処理されていないトランザクション数が 15 億に達すると、重要度が高いアラームがトリガーされます。データベースでトランザクション ID をどれだけ速く使用するかによりますが、このアラームは、システムに autovacuum を実行する時間がないことを示している場合があります。この場合は、この問題を早急に解決することをお勧めします。

テーブルのサイズがこれらのしきい値を頻繁に超える場合は、自動バキュームパラメータをさらに変更します。デフォルトでは、手動で VACUUM (コストベースの遅延が無効) を使用するほうが、デフォルトの autovacuum を使用するより排除率が高くなりますが、システム全体に与える負担が増えます。

次の構成を推奨します。

  • この場合、最も古いトランザクションの経過時間を認識できるように、モニタリングメカニズムをオンにしてください。

    トランザクション ID の循還について警告するプロセスを作成する方法については、AWS のデータベースブログの記事「Amazon RDS for PostgreSQL でトランザクション ID の循環に早期警告システムを実装する」を参照してください。

  • 処理の多いテーブルでは、autovacuum の使用に加えて、メンテナンスウィンドウ中に手動でバキュームフリーズを定期的に実行してください。手動バキュームフリーズの実行については、「 手動バキュームフリーズの実行」を参照してください。

現在 autovacuum の対象となっているテーブルの判別

多くの場合、1 つ以上のテーブルにバキューム処理が必要です。relfrozenxid の値が autovacuum_freeze_max_age のトランザクション数を超えているテーブルは、常に autovacuum の処理対象となります。それ以外の場合、前回の VACUUM 以降「古い」とされたタプルの数が「バキュームしきい値」を超えると、テーブルがバキューム処理されます。

autovacuum しきい値は、次のように定義されます。

Vacuum-threshold = vacuum-base-threshold + vacuum-scale-factor * number-of-tuples

データベースに接続しているときに、次のクエリを実行し、自動バキュームがバキューム処理の対象と見なしているテーブルのリストを表示します。

WITH vbt AS (SELECT setting AS autovacuum_vacuum_threshold FROM pg_settings WHERE name = 'autovacuum_vacuum_threshold'), vsf AS (SELECT setting AS autovacuum_vacuum_scale_factor FROM pg_settings WHERE name = 'autovacuum_vacuum_scale_factor'), fma AS (SELECT setting AS autovacuum_freeze_max_age FROM pg_settings WHERE name = 'autovacuum_freeze_max_age'), sto AS (select opt_oid, split_part(setting, '=', 1) as param, split_part(setting, '=', 2) as value from (select oid opt_oid, unnest(reloptions) setting from pg_class) opt) SELECT '"'||ns.nspname||'"."'||c.relname||'"' as relation, pg_size_pretty(pg_table_size(c.oid)) as table_size, age(relfrozenxid) as xid_age, coalesce(cfma.value::float, autovacuum_freeze_max_age::float) autovacuum_freeze_max_age, (coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) + coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * c.reltuples) AS autovacuum_vacuum_tuples, n_dead_tup as dead_tuples FROM pg_class c join pg_namespace ns on ns.oid = c.relnamespace join pg_stat_all_tables stat on stat.relid = c.oid join vbt on (1=1) join vsf on (1=1) join fma on (1=1) left join sto cvbt on cvbt.param = 'autovacuum_vacuum_threshold' and c.oid = cvbt.opt_oid left join sto cvsf on cvsf.param = 'autovacuum_vacuum_scale_factor' and c.oid = cvsf.opt_oid left join sto cfma on cfma.param = 'autovacuum_freeze_max_age' and c.oid = cfma.opt_oid WHERE c.relkind = 'r' and nspname <> 'pg_catalog' AND (age(relfrozenxid) >= coalesce(cfma.value::float, autovacuum_freeze_max_age::float) OR coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) + coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * c.reltuples <= n_dead_tup -- or 1 = 1) ORDER BY age(relfrozenxid) DESC LIMIT 50;

Autovacuum が現在実行されているかどうかと実行されている時間の判別

テーブルを手動でバキューム処理する必要がある場合、必ず自動バキュームが現在実行されているかどうか判別してください。実行されている場合、さらに効率的に実行されるようにパラメータを調整するか、自動バキュームを一時的にオフに切り替えて VACUUM を手動で実行できるようにする必要がある場合があります。

次のクエリを使用して、autovacuum が実行中か、どのくらいの時間実行中か、また別のセッションの待機中かを判別します。

SELECT datname, usename, pid, state, wait_event, current_timestamp - xact_start AS xact_runtime, query FROM pg_stat_activity WHERE upper(query) LIKE '%VACUUM%' ORDER BY xact_start;

クエリが実行されると、次のような出力が表示されます。

datname | usename | pid | state | wait_event | xact_runtime | query --------+----------+-------+--------+------------+-------------------------+-------------------------------------------------------------------------------------------------------- mydb | rdsadmin | 16473 | active | | 33 days 16:32:11.600656 | autovacuum: VACUUM ANALYZE public.mytable1 (to prevent wraparound) mydb | rdsadmin | 22553 | active | | 14 days 09:15:34.073141 | autovacuum: VACUUM ANALYZE public.mytable2 (to prevent wraparound) mydb | rdsadmin | 41909 | active | | 3 days 02:43:54.203349 | autovacuum: VACUUM ANALYZE public.mytable3 mydb | rdsadmin | 618 | active | | 00:00:00 | SELECT datname, usename, pid, state, wait_event, current_timestamp - xact_start AS xact_runtime, query+ | | | | | | FROM pg_stat_activity + | | | | | | WHERE query like '%VACUUM%' + | | | | | | ORDER BY xact_start; +

いくつかの問題が原因で autovacuum セッションの実行が長期間 (複数日) に渡る場合があります。最もよくある問題は、maintenance_work_mem パラメータ値で設定されたテーブルのサイズまたは更新速度が小さすぎることです。

次の計算式を使用して、maintenance_work_mem パラメータ値を設定することをお勧めします。

GREATEST({DBInstanceClassMemory/63963136*1024},65536)

実行時間が短い autovacuum セッションは、以下の問題を示している可能性もあります。

  • ワークロード用の autovacuum_max_workers が十分ではないことを示している場合があります。この場合は、ワーカーの数を指定する必要があります。

  • インデックスの破損を示している場合があります (自動バキュームがクラッシュし、同じリレーションで再起動されますが進行はありません)。この場合は、手動 vacuum freeze verbose table を実行して正確な原因を確認します。

手動バキュームフリーズの実行

バキュームプロセスが既に実行されているテーブルで、手動バキュームを実行できます。これは、トランザクション数が 20 億に近づいている (または、モニタリングしているしきい値を上回った) テーブルに気付いた場合に役立ちます。

次の手順はガイドラインであり、プロセスにはいくつかのバリエーションがあります。例えば、テスト時に、maintenance_work_mem パラメータの設定値が小さすぎて、テーブルに早急な対処が必要であることに気づいたとします。ただし、今はインスタンスをバウンスしたくない場合があります。前のセクションのクエリを使用することで、問題のあるテーブルを判別し、長時間実行されている autovacuum セッションを確認できます。maintenance_work_mem パラメータ設定の変更が必要であることがわかっていても、すぐに対処して問題のテーブルにバキューム処理を実行する必要があります。このような場合、次の手順で対応します。

バキュームフリーズを手動で実行するには

  1. バキュームを実行するテーブルを含むデータベースへのセッションを 2 つ開きます。2 番目のセッションで、接続が中断された場合にセッションを維持する「screen」または他のユーティリティを使用します。

  2. セッション 1 で、テーブルで実行されている自動バキュームセッションのプロセス ID (PID) を取得します。

    次のクエリを実行し、autovacuum セッションの PID を取得します。

    SELECT datname, usename, pid, current_timestamp - xact_start AS xact_runtime, query FROM pg_stat_activity WHERE upper(query) LIKE '%VACUUM%' ORDER BY xact_start;
  3. セッション 2 で、このオペレーションに必要なメモリの量を計算します。この例では、このオペレーションに最大 2GB のメモリを使用できると決めたため、現在のセッションの maintenance_work_mem を 2GB に設定します。

    SET maintenance_work_mem='2 GB'; SET
  4. セッション 2 で、テーブルに対して vacuum freeze verbose コマンドを発行します。現在のところ PostgreSQL には進行状況レポートがないため、verbose 設定はアクティビティを確認するのに役立ちます。

    \timing on Timing is on. vacuum freeze verbose pgbench_branches;
    INFO: vacuuming "public.pgbench_branches" INFO: index "pgbench_branches_pkey" now contains 50 row versions in 2 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "pgbench_branches_test_index" now contains 50 row versions in 2 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pgbench_branches": found 0 removable, 50 nonremovable row versions in 43 out of 43 pages DETAIL: 0 dead row versions cannot be removed yet. There were 9347 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM Time: 2.765 ms
  5. セッション 1 で、自動バキュームがバキュームセッションをブロックしていた場合、pg_stat_activity で、バキュームセッションの [waiting] (待機) が「T」であることを確認できます。この場合、次のようにして自動バキュームプロセスを終了する必要があります。

    SELECT pg_terminate_backend('the_pid');

    この時点で、セッションがスタートされます。このテーブルは作業リストの一番上にあると思われるため、autovacuum が即座に再開される点に注意することが重要です。

  6. セッション 2 で vacuum freeze verbose コマンドを開始し、セッション 1 で自動バキュームプロセスを終了します。

autovacuum の実行中にテーブルのインデックスを再作成する

インデックスが破損した場合、autovacuum はテーブルの処理を続けますが失敗します。この状況で手動バキュームを試みると、次のようなエラーメッセージが表示されます。

postgres=> vacuum freeze pgbench_branches; ERROR: index "pgbench_branches_test_index" contains unexpected zero page at block 30521 HINT: Please REINDEX it.

インデックスが破損しているときに、自動バキュームをテーブルで実行しようとすると、既に実行中の自動バキュームセッションと競合します。「REINDEX」コマンドを発行する場合は、テーブルに対する排他ロックを取り除きます。書き込みオペレーションがブロックされ、この特定のインデックスを使用する読み込みオペレーションもブロックされます。

autovacuum がテーブルに対して実行されているときにテーブルのインデックスを再作成するには

  1. バキュームを実行するテーブルを含むデータベースへのセッションを 2 つ開きます。2 番目のセッションで、接続が中断された場合にセッションを維持する「screen」または他のユーティリティを使用します。

  2. セッション 1 で、テーブルを実行している autovacuum セッションの PID を取得します。

    次のクエリを実行し、autovacuum セッションの PID を取得します。

    SELECT datname, usename, pid, current_timestamp - xact_start AS xact_runtime, query FROM pg_stat_activity WHERE upper(query) like '%VACUUM%' ORDER BY xact_start;
  3. セッション 2 で、reindex コマンドを発行します。

    \timing on Timing is on. reindex index pgbench_branches_test_index; REINDEX Time: 9.966 ms
  4. セッション 1 で、自動バキュームがプロセスをブロックしていた場合、pg_stat_activity で、バキュームセッションの [waiting] (待機) が「T」であることを確認できます。この場合、自動バキュームプロセスを終了します。

    SELECT pg_terminate_backend('the_pid');

    この時点で、セッションがスタートされます。このテーブルは作業リストの一番上にあると思われるため、autovacuum が即座に再開される点に注意することが重要です。

  5. セッション 2 で コマンドを開始し、セッション 1 で自動バキュームプロセスを終了します。

autovacuum に影響を与えるその他のパラメータ

次のクエリは、autovacuum とその動作に直接影響を与えるパラメータのいくつかについて値を表示します。autovacuum パラメータの詳細については、PostgreSQL のドキュメントを参照してください。

SELECT name, setting, unit, short_desc FROM pg_settings WHERE name IN ( 'autovacuum_max_workers', 'autovacuum_analyze_scale_factor', 'autovacuum_naptime', 'autovacuum_analyze_threshold', 'autovacuum_analyze_scale_factor', 'autovacuum_vacuum_threshold', 'autovacuum_vacuum_scale_factor', 'autovacuum_vacuum_threshold', 'autovacuum_vacuum_cost_delay', 'autovacuum_vacuum_cost_limit', 'vacuum_cost_limit', 'autovacuum_freeze_max_age', 'maintenance_work_mem', 'vacuum_freeze_min_age');

これらはすべて autovacuum に影響を与えますが、最も重要なものは以下のとおりです。

テーブルレベルの autovacuum パラメータを設定する

自動バキューム関連のストレージパラメータをテーブルレベルで設定できます。これは、データベース全体の動作を変更するより適切である場合があります。大きなテーブルでは、極端な設定にする必要が生じる場合がありますが、autovacuum がすべてのテーブルに対してそのように動作するわけではありません。

次のクエリは、現在テーブルレベルのオプションが設定されているテーブルを表示します。

SELECT relname, reloptions FROM pg_class WHERE reloptions IS NOT null;

これが役立つ可能性がある例として、残りのテーブルよりかなり大きいテーブルがあります。1 個の 300 GB のテーブルと、他の 30 個の 1 GB 未満のテーブルがあるとします。この場合、システム全体の動作を変更しないで、大きなテーブルのいくつかの特定のパラメータを設定できます。

ALTER TABLE mytable set (autovacuum_vacuum_cost_delay=0);

これを行うと、このテーブルでコストベースの自動バキューム遅延がなくなりますが、システムでのリソース使用量が多くなります。通常、自動バキュームは autovacuum_cost_limit に達するたびに autovacuum_vacuum_cost_delay で一時停止します。詳細については、「PostgreSQL ドキュメント」の「cost-based vacuuming」(コストベースのバキューム処理) を参照してください。

自動バキュームおよびバキュームアクティビティのログ記録

自動バキュームアクティビティに関する情報は、rds.force_autovacuum_logging_levelパラメータで指定したレベルに基づいて postgresql.log に送信されます。このパラメータで指定できる値、およびその値がデフォルトで設定されている PostgreSQL バージョンは次のとおりです。

  • disabled (PostgreSQL 10、PostgreSQL 9.6)

  • debug5, debug4, debug3, debug2, debug1

  • info (PostgreSQL 12、PostgreSQL 11)

  • notice

  • warning (PostgreSQL 14、PostgreSQL 13)

  • error、ログ、fatalpanic

rds.force_autovacuum_logging_level では log_autovacuum_min_duration パラメータが使用されます。log_autovacuum_min_duration パラメータの値はしきい値 (ミリ秒単位) です。このしきい値を超過すると、自動バキュームアクションがログに記録されます。-1 に設定するとログに何も記録されませんが、0 に設定するとすべてのアクションが記録されます。rds.force_autovacuum_logging_level と同様に、log_autovacuum_min_duration のデフォルト値はバージョンによって次のように異なります。

  • 10000 ms – PostgreSQL 14、PostgreSQL 13、PostgreSQL 12、および PostgreSQL 11

  • (empty) – PostgreSQL 10 と PostgreSQL 9.6 の場合、デフォルト値はありません

rds.force_autovacuum_logging_levelWARNING に設定することをお勧めします。log_autovacuum_min_duration についても 1,000~5,000 の値に設定することをお勧めします。5,000 に設定すると、5,000 ミリ秒を超える長さのアクティビティがログに記録されます。-1 以外の設定では、ロックの競合または同時に削除されたリレーションが原因で自動バキュームアクションがスキップされた場合にも、メッセージがログに記録されます。詳細については、「PostgreSQL のドキュメント」の「Automatic Vacuuming」(自動バキューム処理) を参照してください。

問題のトラブルシューティングを行うために、rds.force_autovacuum_logging_level パラメータを debug1 から debug5 までのデバッグレベルの 1 つに変更し、最も詳しい情報を取得します。デバッグ設定は、短期間かつトラブルシューティングの目的でのみ使用することをお勧めします。詳細については、「PostgreSQL のドキュメント」の「When to log」(ログ記録のタイミング) を参照してください。

注記

PostgreSQL では、rds_superuser アカウントが pg_stat_activity 内の autovacuum セッションを表示できます。例えば、コマンドの実行をブロックしている autovacuum セッション、あるいは手動で発行される vacuum コマンドよりも実行スピードが遅い autovacuum セッションを特定して終了することもできます。