Amazon RDS での PostgreSQL Autovacuum の使用 - Amazon Relational Database Service

Amazon RDS での PostgreSQL Autovacuum の使用

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

autovacuum は、すべての新しい Amazon RDS for PostgreSQL DB インスタンスにおいてデフォルトで有効化され、関連する autovacuum 設定パラメータはデフォルトで適切に設定されます。デフォルト値は汎用的であるため、特定のワークロードに合わせてパラメータを調整すると便利です。次のセクションは、autovacuum の必要な調整を行う際に役立ちます。

autovacuum のメモリを割り当てる

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 の循環を防ぐほどは排除率が高くない場合があります。この問題に対処するために、Amazon RDS for PostgreSQL は autovacuum パラメータ値を自動的に適応させる機構を提供しています。autovacuum パラメータのアダプティブチューニングは RDS for PostgreSQL の機能です。トランザクション ID の循環に関する詳しい説明については、PostgreSQL ドキュメントを参照してください。

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

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

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

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

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

これらの autovacuum パラメータのいずれかが、RDS により変更されるたびに、影響を受ける DB インスタンスに対しイベントが生成されます。このイベントは、AWS Management Console (https://console.aws.amazon.com/rds/) および RDS API で表示することができます。CloudWatch のメトリクス MaximumUsedTransactionIDs がしきい値より低い値に戻ると、RDS はメモリ内の autovacuum 関連のパラメータをリセットしてパラメータグループで指定されている値に戻し、この変更に応じた別のイベントを生成します。

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

次のクエリを使用して、データベース内のバキューム処理されていないトランザクションの数を表示できます。データベースの 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 を実行する時間がないことを示している場合があります。この場合は、この問題を早急に解決することをお勧めします。

テーブルがこれらのしきい値を頻繁に超える場合は、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

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

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 が現在実行されているかどうかと実行されている時間の判別

テーブルを手動でバキューム処理する必要がある場合、autovacuum が現在実行されているかどうかを判別する必要があります。実行されている場合、さらに効率的に実行されるようにパラメータを調整するか、VACUUM を手動で実行できるように autovacuum を終了する必要があります。

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

RDS for PostgreSQL バージョン 9.6+ 以降を使用している場合は、次のクエリを使用します。

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; +

使用している Amazon RDS for PostgreSQL のバージョンが 9.6 未満である場合は、以下のクエリを使用します。

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

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

datname | usename | pid | waiting | xact_runtime | query --------+----------+-------+---------+-------------------------+---------------------------------------------------------------------------------------------- mydb | rdsadmin | 16473 | f | 33 days 16:32:11.600656 | autovacuum: VACUUM ANALYZE public.mytable1 (to prevent wraparound) mydb | rdsadmin | 22553 | f | 14 days 09:15:34.073141 | autovacuum: VACUUM ANALYZE public.mytable2 (to prevent wraparound) mydb | rdsadmin | 41909 | f | 3 days 02:43:54.203349 | autovacuum: VACUUM ANALYZE public.mytable3 mydb | rdsadmin | 618 | f | 00:00:00 | SELECT datname, usename, pid, waiting, 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 が十分ではないことを示している場合があります。この場合は、ワーカーの数を指定する必要があります。

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

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

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

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

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

  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 で、このオペレーションに必要なメモリの量を計算します。この例では、このオペレーションに最大 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 で、autovacuum でブロックしていた場合、pg_stat_activity に、バキュームセッションの待機が「T」であることを確認します。この場合、次のようにして autovacuum プロセスを終了する必要があります。

    SELECT pg_terminate_backend('the_pid');
  6. この時点で、セッションが開始されます。このテーブルは作業リストの一番上にあると思われるため、autovacuum が即座に再開される点に注意することが重要です。セッション 2 で vacuum freeze verbose コマンドを開始し、セッション 1 で autovacuum プロセスを終了します。

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

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

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

インデックスが破損しているときに、autovacuum をテーブルに対して実行しようとすると、既に実行中の autovacuum セッションと競合します。「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 で、autovacuum でブロックしていた場合、pg_stat_activity に、バキュームセッションの待機が「T」であることを確認します。この場合、autovacuum プロセスを終了する必要があります。

    select pg_terminate_backend('the_pid');
  5. この時点で、セッションが開始されます。このテーブルは作業リストの一番上にあると思われるため、autovacuum が即座に再開される点に注意することが重要です。セッション 2 で コマンドを開始し、セッション 1 で autovacuum プロセスを終了します。

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 関連のストレージパラメータは、テーブルレベルで設定できます。これは、データベース全体の動作を変更するより適切である場合があります。大きなテーブルでは、極端な設定にする必要が生じる場合がありますが、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 遅延がなくなりますが、システムでのリソース使用量が多くなります。通常は、autovacuum_cost_limit に到達する度に、autovacuum は autovacuum_vacuum_cost_delay によって 一時停止されます。「コストベースのバキューム処理」の詳細については、PostgreSQL のドキュメントを参照してください。

autovacuum のログ記録

デフォルトでは、postgresql.log には autovacuum プロセスに関する情報が含まれていません。rds.force_autovacuum_logging_level パラメータを設定することで、autovacuum ワーカーオペレーションの PostgreSQL エラーログを出力できます。指定できる値は disabled, debug5, debug4, debug3, debug2, debug1, info, notice, warning, error, log, fatal,panic です。デフォルト値は disabled です。指定できる他の値では、ログの情報の量が大幅に増える可能性があるためです。

rds.force_autovacuum_logging_level パラメータの値を warning に設定し、log_autovacuum_min_duration パラメータを 1,000〜5,000 ミリ秒の値に設定することをお勧めします。この値を 5,000 に設定すると、Amazon RDS は、5 秒を超えるすべてのアクティビティをログに書き込みます。また、アプリケーションのロックによって autovacuum で意図的にテーブルがスキップされた場合は、「バキュームのスキップ」メッセージも表示されます。問題をトラブルシューティングしていてさらに詳細が必要な場合、debug1debug3 などの別のログレベルの値を使用できます。これらのデバッグパラメータを使用するのは短期間にしてください。このような設定では、かなり詳細な内容がエラーログファイルに書き込まれるためです。これらのデバッグ設定の詳細については、 PostgreSQL のドキュメントを参照してください。

注記

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