Amazon RDS for PostgreSQL 環境の自動バキュームを理解する

PostgreSQL は、多くのエンタープライズデベロッパーや新興企業に推奨されるオープンソースリレーショナルデータベースになり、主要なビジネスアプリケーションやモバイルアプリケーションを強化しています。アマゾンウェブサービス (AWS) は、完全マネージド型のリレーショナルデータベースサービスとして、Amazon Relational Database Service (Amazon RDS) および Amazon Aurora を提供しています。Amazon RDS for PostgreSQL により、クラウドで PostgreSQL デプロイを簡単にセットアップ、操作、スケーリングできます。コマンドをいくつか使用するだけで、本稼働データベースのインスタンスを AWS で起動して実行することができます。オンラインデータベースを使用すれば、データベース管理者は多くのメンテナンスタスクや管理タスクから解放されます。ただし、VACUUM など、データベースの使用状況に基づいて綿密なモニタリングと変更を必要とするメンテナンスタスクがあります。自動バキュームは、バキュームの操作を自動化するプロセスです。

この記事では、自動バキュームプロセスとその重要性について説明します。また、パフォーマンスを向上させるための自動バキューム設定を調整することと、オフにすることの欠点についても説明します。

PostgreSQL の MVCC

PostgreSQL は多版型同時実行制御 (MVCC) を使用して、データの変更を実行するときに行を複数のバージョンで維持しています。テーブルに対する UPDATE および DELETE 操作中、データベースは古いバージョンの行を保持します。これは、他の実行中のトランザクションがデータのビューに一貫性を持たせることを要求する場合があるためです。PostgreSQL では、データベースを変更するすべてのステートメントが、xid と呼ばれるトランザクション ID を生成します。行のステータスは、xmin と xmax という 2 つの非表示列の xid を用いて追跡します。

1 つの列を持つテーブル test を考えてみましょう。行を挿入するには、次のコードを参照してください。

postgres=# CREATE TABLE mvcc_test(id int);
CREATE TABLE
postgres=# INSERT INTO mvcc_test VALUES(1);
INSERT 0 1
postgres=# SELECT  xmin, xmax, id FROM test;
 xmin | xmax | id
------+------+----
 100 |    0 |  1
(1 row)

xmin は行が挿入された xid を表し、xmax は通常、表示されている行では 0 です。xmax が 0 より大きい場合、これは表示されない期限切れの行を表します。ただし、場合によっては、xmax が 0 より大きい場合でも行が表示されます。これは、トランザクションで何かを更新または削除し、それがロールバックされた場合に発生します。

行を削除しても、その行のバージョンは MVCC を維持しているように見えます。このユースケースでは、削除された行の場合、xmin は行が挿入された INSERT ステートメントの xid であり、xmax は行が削除された DELETE ステートメントの xid になります。

PostgreSQL では、UPDATE は DELETE および INSERT と見なされます。古い行を削除し、新しい行を挿入します。MVCC を満たすために両方の行が維持されます。このユースケースでは、古い行の場合、xmin は行が挿入された xid で、xmax は行が更新された xid です。新しい行の場合、xmin は行が更新された xid で、行が表示されているため xmax は 0 です。

次の図は、MVCC の動作をまとめたものです。

詳細については、PostgreSQL ウェブサイトの「Concurrency Control」を参照してください。

UPDATE および DELETE が原因で期限切れになった行は、dead 行またはデッドタプルと呼ばれます。このようなデッドタプルを持つテーブルは、肥大化したテーブルです。 そのような dead 行をクリーンアップしない限り、それが使用しているスペースを再利用または削除することはできません。このような dead 行を再利用するには、VACUUM コマンドを使用します。

VACUUM は、dead 行バージョンを含む可能性のあるテーブル内のすべてのページ (ヒープ とも呼ばれます) をスキャンします。visibility map と呼ばれるデータ構造は、最後の VACUUM 以降に変更されたページを追跡します。これらのページから dead 行バージョンを削除し、そのスペースを再利用できるようにします。

自動バキュームのご紹介

自動バキュームは、VACUUM および ANALYZE (統計を収集するため) コマンドの実行を自動化するデーモンです。自動バキュームは、データベース内の肥大化したテーブルをチェックし、スペースを解放して再利用できるようにします。

自動バキュームデーモンのワークフロー

自動バキュームデーモンは、自動バキュームランチャーと自動バキュームワーカーという 2 種類のプロセスで設計されています。

自動バキュームランチャーは、自動バキュームパラメータがオンに設定されている場合にポストマスターが開始するデフォルトの実行プロセスです。ポストマスターは、PostgreSQL システムへのリクエストの処理メカニズムとして機能します。すべてのフロントエンドプログラムはスタートアップメッセージをポストマスターに送信し、ポストマスターはメッセージ内の情報を使用してバックエンドプロセスを開始します。自動バキュームランチャープロセスは、テーブルでバキューム操作を実行するための自動バキュームワーカープロセスを開始する適切なタイミングを決定します。

自動バキュームワーカーは、テーブルに対してバキューム操作を実行する実際のワーカープロセスです。ランチャーによってスケジュールされたとおりにデータベースに接続し、カタログテーブルを読み取り、バキューム操作を実行するためのテーブルを選択します。

自動バキュームランチャープロセスは、データベース内のテーブルをモニタリングし続け、テーブルが自動バキュームのしきい値に達した後、バキュームジョブに適したテーブルを選択します。このしきい値は、autovacuum_vacuum_threshold、autovacuum_analyze_threshold、autovacuum_vacuum_scale_factor、autovacuum_analyze_scale_factor などのパラメータに基づいています。

autovacuum_vacuum_threshold および autovacuum_analyze_threshold

これらのパラメータは、自動バキュームと自動アナライザーそれぞれのスケジュールを立てるテーブルのために、テーブル内の更新または削除の最小数を決定します。両方のデフォルトは 50 です。

autovacuum_vacuum_scale_factor および autovacuum_analyze_scale_factor

これらのパラメータは、テーブルが自動バキュームと自動アナライザーそれぞれのスケジュールを立てられるように変更する必要があるテーブルのパーセンテージを決定します。autovacuum_vacuum_scale_factor のデフォルトは 0.2 (20%) で、autovacuum_analyze_scale_factor は 0.1 (10%) です。

テーブルの行数が多すぎなければ、これらの数値は両方ともテーブルに適しています。ただし、多数の行があるテーブルの場合、これらのパラメータのデフォルト値は多数の行の変更を表します。これは、実行時に大変なバキューム作業になります。ただし、大きなテーブルがデータベース上で少数の場合は、設定ファイルではなくテーブルレベルでこれらのパラメータを設定することをお勧めします。

これらのパラメータの詳細については、PostgreSQL ウェブサイトの「Automatic Vacuuming」を参照してください。

しきい値を計算するには、次の式を使用します。

バキュームしきい値 = バキュームベースしきい値 + バキュームスケール係数 * ライブタプルの数。次の値を使用します。

バキュームベースしきい値 – autovacuum_vacuum_threshold
バキュームスケール係数 – autovacuum_vacuum_scale_factor
ライブタプルの数 – pg_stat_all_tables ビューの n_live_tup の値

自動バキュームランチャーは、自動バキュームワーカープロセスを単独で開始することはできません。これはポストマスタープロセスによって行われます。ランチャーは、データベースに関する情報を自動バキューム共有メモリ領域に格納し、共有メモリにフラグを設定し、ポストマスターに信号を送信します。ポストマスターは自動バキュームワーカープロセスを開始します。この新しいワーカープロセスは、共有メモリから情報を読み取り、必要なデータベースに接続して、バキュームジョブを完了します。

ポストマスターがワーカープロセスの開始に失敗した場合、ポストマスターは共有メモリにフラグを設定し、ランチャープロセスにシグナルを送信します。ポストマスターのシグナルを読み取ると、ランチャーはシグナルをポストマスターに送信してワーカープロセスの開始を再試行します (ポストマスターがワーカープロセスを開始できないのは、高い負荷とメモリの負荷、またはすでに実行中のプロセスが多すぎるためです)。

自動バキュームワーカープロセスがバキューム操作を完了すると、ランチャーにシグナルが送信されます。ランチャーがワーカーから信号を受け取ると、共有メモリ内のバキュームされるテーブルのリストが長すぎる場合、ランチャーが起動し、別のワーカーを起動しようとします。これは、そのテーブルのバキュームロックを待機する際に他のワーカーがそブロックされないようにするためです。また、各テーブルをバキュームする直前に pgstats テーブルにデータを再ロードし、別のワーカーがバキュームを終了し、共有メモリに記録されなくなった直後にテーブルをバキュームしないようにします。

PostgreSQL でよくある誤解は、自動バキュームプロセスが I/O の増加を引き起こすということです。したがって、多くの人が自動バキュームプロセスを完全にオフにすることを選択しています。これは、環境の初期段階では効果的なソリューションのように見えるかもしれません。しかし、データベースのサイズが大きくなり始めると、デッドタプルによって占有されるスペースが多く見つかり、急速に遅くなり、データベースのサイズが大きくなります。

自動バキュームの利点

このセクションでは、自動バキュームがオンのときに行う重要な事柄について説明します。また、オフのときに直面する問題についても説明します。

統計の更新

PostgreSQL ANALYZE デーモンは、テーブルの統計を収集して計算します。クエリプランナーはこの統計を用いてクエリプランを実行します。この情報は ANALYZE デーモンによって計算および収集され、この統計を用いてカタログテーブルに格納されます。次に、クエリプランナーはクエリプランを作成してデータを取得します。

同様のシナリオで、自動バキュームがオフに設定されている場合、ANALYZE デーモンは統計を収集および計算しません。クエリプランナーがテーブルに関する情報を持っていないと、コスト効率が良くない不適切なクエリプランを作成することになります。

トランザクションラップアラウンドの防止

前に説明したように、PostgreSQL はトランザクションにトランザクション ID として番号を割り当てます。トランザクション ID は数値であるため、許可する最大値や最小値などの制限が必要です (数を無限に生成することはできません)。

PostgreSQL は、トランザクション ID の明確な数として 4 バイト整数を選択します。つまり、4 バイトで生成できるトランザクション ID の最大数は 2^32 ～ 4294967296 で、40 億のトランザクション ID です。ただし、PostgreSQL は、トランザクション ID を 1 から 2^31 ～ 2147483648 にローテーションすることにより、4 バイト整数で無制限の数のトランザクションを処理できます。つまり、PostgreSQL がトランザクション ID 2147483648 に到達すると、さらに着信するトランザクション用に 1 から 2^31 までのトランザクション ID が割り当てられます。PostgreSQL の用語では、トランザクション ID をローテーションするこの方法は、トランザクション ID ラップアラウンドと呼ばれています。

典型的なトランザクション ID ラップアラウンドのユースケースとして、現在のトランザクション ID を 100 と想定します。トランザクション制限 2^31 に達すると、現在のトランザクション ID は、以前のトランザクション ID のコミットされた情報のみを表示でき、将来のトランザクションは表示できません。101 から 2^31 までのすべてのトランザクション ID は現在のトランザクションの将来の ID であるため、現在のトランザクション ID 100 は、トランザクション ID が 100 を超えるデータベース内の行を表示できず、データベースが利用できなくなります。このトランザクション ID ラップアラウンドの問題を回避するために、トランザクション ID をフリーズできます。

トランザクション ID のフリーズとは、トランザクション ID の値をフリーズされたトランザクション ID (2 の値) に変換することを意味します。前に説明したように、トランザクションラップアラウンドの結果としてトランザクション ID 100 に到達した場合、それ以降トランザクション ID 101 から 2^31 にコミットされたトランザクションは表示されません。これらすべての ID を 2 に変更すると、PostgreSQL はコミットされたすべてのトランザクション ID の情報を表示できます。

FREEZE 操作は、テーブルに対して同様のプロセスを実行します。以前のすべてのトランザクション ID を論理的に 2 に変換します。VACUUM FREEZE の後、以下に示すように、前の ID と同じ xmin が表示されますが、内部的にはフリーズした値を維持しています。

postgres=# select xmin, xmax, * from freeze_test ;
 xmin | xmax | id | name 
------+------+----+------
 1002 |    0 |  1 | test
(1 row)
postgres=# VACUUM FREEZE freeze_test ;
VACUUM
postgres=# select xmin, xmax, * from freeze_test ;
 xmin | xmax | id | name 
------+------+----+------
 1002 |    0 |  1 | test
(1 row)

自動バキュームは、各ページの各行にアクセスし、トランザクション ID をフリーズします。このフリーズオプションを使用して、データベーストランザクション ID の経過時間が autovacuum_freeze_max_age に達したときに発生するラップアラウンドの問題を回避します。トランザクション ID の経過時間は、FREEZE なしで、または FREEZE 操作後に、テーブルまたはデータベースで実行されたトランザクションの数です。データベーストランザクションの経過時間が autovacuum_freeze_max_age に達すると、PostgreSQL は即座に自動バキュームプロセスを起動して、データベース全体でフリーズ操作を実行します。

自動バキュームのモニタリング

自動バキュームが効果的に機能していることを確認するには、定期的に dead 行、ディスク使用量、自動バキュームまたは ANALYZE が最後に実行された時間をモニタリングする必要があります。

デッドタプル

PostgreSQL には pg_stat_user_tables ビューがあり、各テーブル (relname) とテーブル内の dead 行の数 (n_dead_tup) に関する情報を提供します。

各テーブル、特に頻繁に更新されるテーブルの dead 行の数をモニタリングすると、自動バキュームプロセスが定期的にそれらを削除して、ディスク領域を再利用してパフォーマンスを向上できるかどうかを判断できます。次のクエリを使用して、デッドタプルの数と、テーブルで最後の自動バキュームがいつ実行されたかを確認できます。

SELECT
relname AS TableName
,n_live_tup AS LiveTuples
,n_dead_tup AS DeadTuples
,last_autovacuum AS Autovacuum
,last_autoanalyze AS Autoanalyze
FROM pg_stat_user_tables;

テーブルのディスク使用量

各テーブルが使用するディスク容量を追跡することは重要です。これにより、クエリパフォーマンスの経時的な変化を分析できるからです。また、バキュームに関連する問題を検出するのにも役立ちます。たとえば、最近テーブルに多くの新しいデータを追加し、テーブルのディスク使用量が予期せず増加した場合、そのテーブルにバキュームの問題がある可能性があります。

バキュームは古い行を再利用可能としてマークするのに役立ちます。したがって、VACUUM を定期的に実行しないと、新たに追加されたデータは、dead 行によって占められたディスク容量を再利用するのではなく、他のディスク容量を使用します。

最後の自動バキュームと自動アナライザー

pg_stat_user_tables ビューは、テーブルで自動バキュームデーモンが最後に実行された時間に関する情報を提供します。自動バキュームと自動分析を使用して、自動バキュームデーモンが効率的に動作しているかどうかを追跡できます。次のクエリは、テーブルで実行された last_autovacuum および last_autoanalyze の詳細を示します。

SELECT relname, last_autovacuum,last_autoanalyze FROM pg_stat_user_tables;

log_autovacuum_min_duration を有効にする

log_autovacuum_min_duration パラメータは、自動バキュームプロセスが実行したすべてのアクションをログに記録するのに役立ちます。自動バキュームが指定されたミリ秒数を実行するか、しきい値テーブルストレージパラメータを超えると、アクションがログに記録されます。このパラメータを 150 ミリ秒に設定すると、150 ミリ秒以上実行されるすべての自動バキュームプロセスがログに記録されます。さらに、このパラメータが -1 以外の値に設定されている場合、競合するロックのために自動バキュームアクションがスキップされると、メッセージがログに記録されます。また、自動バキュームプロセスの速度が低いことに関する詳細情報を提供することもできます。

Amazon CloudWatch アラームを有効にする

トランザクションラップアラウンドに Amazon CloudWatch アラームを設定できます。詳細については、「Amazon RDS for PostgreSQL のトランザクション ID ラップアラウンドに早期警告システムを実装する」を参照してください。

また、CloudWatch メトリクスを使用してシステムリソース全体の使用状況をモニタリングし、自動バキュームセッションが同時に実行されている場合は、それが許容範囲内であることを確認できます。

自動バキューム関連の一般的な問題

上記の統計が、自動バキュームデーモンがデータベースで効率的に機能していないことを示している場合、いくつかの理由が考えられます。次のセクションでは、その考えられる理由について説明します。

自動バキュームパラメータの調整

自動バキュームによってテーブルのバキュームプロセスが定期的にトリガーされない場合、または効率的に実行されない場合は、自動バキュームパラメータを調整することを検討してください。自動バキュームプロセスは、テーブルで VACUUM コマンドと ANALYZE コマンドを自動的に実行するタイミングを決定する環境設定に左右されます。

次のコードは、調整を検討する必要がある自動バキュームパラメータのリストを示しています。

select category, name,setting,unit,source,min_val,max_val from pg_settings where category = 'autovacuum' ;
 
  category |                name                 | setting   | unit |       source       | min_val |  max_val   | boot_val 
------------+-------------------------------------+-----------+------+--------------------+---------+------------+-----------
Autovacuum | autovacuum                          | on        |      | default            |         |            | on
Autovacuum | autovacuum_analyze_scale_factor     | 0.05      |      | configuration file | 0       | 100        | 0.1
Autovacuum | autovacuum_analyze_threshold        | 50        |      | default            | 0       | 2147483647 | 50
Autovacuum | autovacuum_freeze_max_age           | 200000000 |      | default            | 100000  | 2000000000 | 200000000
Autovacuum | autovacuum_max_workers              | 3         |      | default            | 1       | 262143     | 3
Autovacuum | autovacuum_multixact_freeze_max_age | 400000000 |      | default            | 10000   | 2000000000 | 400000000
Autovacuum | autovacuum_naptime                  | 30        | s    | configuration file | 1       | 2147483    | 60
Autovacuum | autovacuum_vacuum_cost_delay        | 20        | ms   | default            | -1      | 100        | 20
Autovacuum | autovacuum_vacuum_cost_limit        | -1        |      | default            | -1      | 10000      | -1
Autovacuum | autovacuum_vacuum_scale_factor      | 0.1       |      | configuration file | 0       | 100        | 0.2
Autovacuum | autovacuum_vacuum_threshold         | 50        |      | default            | 0       | 2147483647 | 50

上記の出力の Settings 列は、現在設定されている値を示しています。boot_val 列は、PostgreSQL によって設定された自動バキュームパラメータのデフォルト値を示しています。これは、デフォルトパラメータを変更しない場合に使用します。これらの自動バキュームパラメータを調整すると、自動バキュームプロセスがテーブルで頻繁かつ効率的に機能するようになります。

自動バキュームの調整の詳細については、「Amazon RDS for PostgreSQL での自動バキュームチューニングのケーススタディ」を参照してください。

ロックの競合による自動バキュームのスキップ

すでに自動バキューム設定を最適化していて、自動バキュームデーモンがいくつかのテーブルでバキュームプロセスをスキップしているのを確認した場合は、テーブルの EXCLUSIVE ロックが原因である可能性があります。

テーブルでバキュームを実行するには、自動バキュームプロセスが SHARE UPDATE EXCLUSIVE ロックを取得する必要があります。これは、2 つのトランザクションが SHARE UPDATE EXCLUSIVE ロックを同時に保持できないため、他のロックと競合します。これは、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE などの他のロックモードでも同じです。

SHARE UPDATE EXCLUSIVE ロックは、SELECT、UPDATE、INSERT、または DELETE をブロックしません。次のロックのあるトランザクションのみをブロックします。

SHARE UPDATE EXCLUSIVE – VACUUM (FULL なし)、ANALYZE、CREATE INDEX CONCURRENTLY、REINDEX CONCURRENTLY、CREATE STATISTICS、および特定の ALTER INDEX および ALTER TABLE バリアントで取得します。
SHARE – CREATE INDEX で取得します (CONCURRENTLY なし)。
SHARE ROW EXCLUSIVE – CREATE TRIGGER および一部の形式の ALTER TABLE で取得します。
EXCLUSIVE – REFRESH MATERIALIZED VIEW CONCURRENTLY で取得します。
ACCESS EXCLUSIVE – DROP TABLE、TRUNCATE、REINDEX、CLUSTER、VACUUM FULL、および REFRESH MATERIALIZED VIEW (CONCURRENTLY なし) コマンドで取得します。多くの形式の ALTER INDEX および ALTER TABLE も、このレベルでロックを取得します。

したがって、いずれかのトランザクションがこれらのロックの 1 つをテーブルに保持するリクエストを伴い、自動バキュームデーモンがすでにそれらのテーブルの 1 つでバキュームジョブを実行している場合、他のトランザクションがロックを取得できるようにバキュームジョブを即座にキャンセルします。同様に、トランザクションがテーブルの ACCESS EXCLUSIVE ロックをすでに保持している場合、自動バキュームはそれらのテーブルのバキューム処理をスキップします。自動バキュームプロセスは、スキップされたテーブルを保持し、次の反復でバキュームジョブを実行します。

長時間実行されているトランザクションが原因で発生する自動バキュームアクションのスキップ

PostgreSQL は MVCC の概念に基づいているため、1 つ以上のトランザクションが古いバージョンのデータにアクセスしている場合、自動バキュームプロセスはデッドタプルをクリーンアップしません。データが削除または更新される前に取得したデータのスナップショットでトランザクションが機能している場合、自動バキュームはデッドタプルをスキップし、そのデッドタプルは次の反復でバキュームされます。これは通常、データベースで長時間実行されているトランザクションで発生します。データベースで長時間実行されているトランザクションを見つけるには、次のコードを入力します。

SELECT now()-query_start as Running_Since, pid, datname, usename, application_name, client_addr , left(query,60) FROM pg_stat_activity WHERE state in ('active','idle in transaction') AND (now() - query_start) > interval '5 minutes';

idle in transaction セッションをモニタリングの一部として含めることをお勧めします。これにより、自動バキュームが dead 行をスキップする可能性があるためです。

自動バキュームのベストプラクティス

このセクションでは、自動バキュームを実行するためのベストプラクティスについて説明します。

自動バキューム用のメモリの割り当て

maintenance_work_mem パラメータは、自動バキュームのパフォーマンスに影響を与える重要なパラメータです。自動バキュームプロセスがデータベース内のテーブルをスキャンし、バキュームを必要とする行 ID を保持するために使用するメモリの量を決定します。

パラメータを「低」に設定すると、バキュームプロセスがテーブルを複数回スキャンしてバキュームジョブを完了し、データベースのパフォーマンスに悪影響を及ぼします。

小さなテーブルが多数ある場合は、autovacuum_max_workers の割り当てを増やし、maintenance_work_mem の割り当てを減らします。大きなテーブル (100 GB を超える) がある場合は、より多くのメモリを割り当て、ワーカープロセスを減らします。最大のテーブルで上手くいくようにするには、十分なメモリを割り当てる必要があります。各 autovacuum_max_workers は、割り当てたメモリを使用できます。したがって、ワーカープロセスとメモリの組み合わせが、割り当てる必要があるメモリの合計と等しくなるようにする必要があります。

大規模なホストの場合、maintenance_work_mem を 1 ～ 2 GB (1,048,576 ～ 2,097,152 KB) の値に設定します。大規模なホストの場合、パラメータを 2 ～ 4 GB (2,097,152 ～ 4,194,304 KB) の値に設定します。このパラメータに設定する値は、データベースのワークロードによって異なります。

autovacuum_work_mem または maintenance_work_mem パラメータを設定すると、各自動バキュームワーカープロセスが使用すべき最大メモリサイズが設定されます。デフォルトでは、autovacuum_work_mem は -1 に設定されています。これは、自動バキュームワーカープロセスのメモリ割り当てに maintenance_work_mem 設定を使用する必要があることを示しています。

Amazon RDS は、このパラメータのデフォルトを次のように計算された KB に更新しました:

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

詳細については、「自動バキューム用のメモリの割り当て」と「Amazon RDS for PostgreSQL の自動バキュームチューニングのケーススタディ」を参照してください。

トランザクション ID ラップアラウンドの可能性を減らす

一部のユースケースでは、自動バキューム設定を調整しても、トランザクション ID ラップアラウンドを防ぐほど強力ではありません。この問題に対処するために、Amazon RDS には自動バキュームパラメータ値を自動的に調整するメカニズムがあります。

アダプティブ自動バキュームパラメータの調整を有効にすると、CloudWatch メトリクス MaximumUsedTransactionIDs が 750,000,000 に達するか、パラメータ autovacuum_freeze_max_age のいずれか大きい方に達すると、Amazon RDS が自動バキュームパラメータの調整を開始します。

Amazon RDS は、テーブルがトランザクション ID ラップアラウンドに向かう傾向がある場合、自動バキュームのパラメータを調整し続けます。調整ごとに、ラップアラウンドを回避するために、自動バキュームにより多くのリソースが割り当てられます。Amazon RDS は、次の自動バキューム関連パラメータを更新します。

autovacuum_vacuum_cost_delay – 自動バキュームプロセスが制限を超えたときにスリープする指定された時間 (ミリ秒)。デフォルト値は 20 ミリ秒です。
autovacuum_vacuum_cost_limit – 自動バキュームプロセスをスリープさせる累積コスト。デフォルト値は 200 です。
autovacuum_work_mem – 各自動バキュームワーカープロセスが使用するメモリの最大量。デフォルトは -1 で、maintenance_work_mem の値を使用する必要があることを示しています。
autovacuum_naptime – 任意のデータベースでの自動バキューム実行間の最小遅延を指定します。各ラウンドで、デーモンはデータベースを調べ、そのデータベース内のテーブルに対して必要に応じて VACUUM および ANALYZE コマンドを発行します。遅延は秒単位で測定され、デフォルトは 1 分です。このパラメータは、postgresql.conf ファイルまたはサーバーのコマンドラインでのみ設定できます。

Amazon RDS は、既存の値が十分に積極的でない場合にのみ、これらのパラメータを変更します。これらのパラメータは DB インスタンスのメモリで変更され、パラメータグループでは変更されません。

Amazon RDS がこれらの自動バキュームパラメータのいずれかを変更するたびに、影響を受ける DB インスタンスのイベントを生成します。これは、Amazon RDS API を介して AWS マネジメントコンソールで確認できます。MaximumUsedTransactionIDs CloudWatch メトリクスがしきい値を下回ると、Amazon RDS はメモリ内の自動バキューム関連パラメータをパラメータグループで指定された値にリセットします。

テーブルレベルで自動バキュームを設定する

グローバル自動バキューム設定に基づいて成長している PostgreSQL 環境では、大きなテーブルが効果的にバキュームされず、小さなテーブルが頻繁にバキュームされるのを目にするかもしれません。これらのシナリオを回避するために、次の手順に従ってテーブルレベルで自動バキュームパラメータを設定できます。

環境内の大きなテーブルを一覧表示します。
変更が多数発生しているテーブルを一覧表示します。
'n_dead_tup の数が多いテーブルを確認します。
テーブルが最後に自動分析され、自動バキュームされたタイミングを確認します。
テーブルレベルで自動バキュームと自動分析パラメータを変更します。

まとめ

デッドタプルはスペースを占有し続け、膨らむ可能性があるため、PostgreSQL データベースのパフォーマンスを低下させる可能性があります。VACUUM を使用して無効なタプルを削除し、将来の挿入と更新のためにスペースを再利用できます。テーブル統計を更新するテーブルで ANALYZE を使用して、オプティマイザーが SQL ステートメントの最適な実行プランを選択できるようにすることもできます。自動バキュームは、肥大化の解消、テーブルディスクの使用量の削減、テーブル統計の定期的な更新に役立ちます。これで、クエリプランナーがコスト効率よく実行できるようになります。

自動バキュームは大量の I/O を発生させるため、データベースの速度を低下させるのではという誤解が一般的にあります。けれども、多少の I/O が生成される場合でも、適切に調整してベストプラクティスに従うと、Amazon RDS for PostgreSQL 環境にとって非常にメリットがあります。自動バキュームをオフにすると、パフォーマンスの問題とトランザクションのラップアラウンド問題が発生し、データベースが機能しなくなるため、オフにしないでください。

著者について

Anuraag Deekonda は、AWS ProServe India のアソシエイトコンサルタントです。彼は移行スペシャリストとして、オンプレミスから AWS クラウドにワークロードを移行するお客様と緊密に連携しています。彼は、顧客が移行の過程でアプリケーションとデータベースを再ホストできるようにしています。彼は、顧客が非常にスケーラブルで、可用性が高く、安全なソリューションを構築できるように手助けしています。彼は PostgreSQL、MySQL、およびその他のオープンソースデータベースに情熱を傾けています。

Baji Shaik は、AWS ProServe (GCC India) のコンサルタント です。彼のバックグラウンドは、SQL/NoSQL データベーステクノロジーの幅広い専門知識と経験に及びます。彼はデータベース移行エキスパートで、データベースをオンプレミスから Amazon RDS および Aurora PostgreSQL に移行するための難しいビジネス要件に対処するデータベースソリューションを数多く開発し、成功を収めてきました。彼は著名な作家で、PostgreSQL に関する本を何冊も執筆しています。彼の最近の作品には、「PostgreSQL Configuration」や「PostgreSQL Development Essentials」などがあります。さらに、彼はいくつかのカンファレンスとワークショップセッションを開催しました。

Amazon Web Services ブログ