# 診断テーブルとインデックスの肥大化 PostgreSQL のマルチバージョン同時実行制御 (MVCC) を使用すると、データの整合性を維持できます。PostgreSQL MVCC は、トランザクションがコミットまたはロールバックされるまで、更新または削除された行 (*タプルとも呼ばれます*) の内部コピーを保存することによって機能します。この保存された内部コピーはユーザーからは見えません。ただし、これらの非表示のコピーが VACUUM または AUTOVACUUM ユーティリティによって定期的にクリーンアップされない場合、テーブルが肥大化する可能性があります。テーブルの肥大化をチェックしないと、ストレージコストの増加や処理速度の低下につながる可能性があります。 多くの場合、Aurora の VACUUM または AUTOVACUUM のデフォルト設定は、不要なテーブルの肥大化を処理するのに十分なものです。ただし、次のような状況がアプリケーションで発生している場合は、肥大化がないかどうかを確認することをお勧めします。 + VACUUM プロセスの間で、比較的短時間で多数のトランザクションを処理している。 + パフォーマンスが低下し、ストレージが不足している。 はじめに、dead タプルでどの程度のスペースを使用しているか、テーブルとインデックスの肥大化をクリーンアップすることでどれだけ回復が見込めるか、最大限正確な情報を収集してください。そのためには、`pgstattuple` 拡張機能を使用して、Aurora クラスターの統計情報を収集します。詳細については、「[pgstattuple](https://www.postgresql.org/docs/current/pgstattuple.html)」を参照してください。`pgstattuple` 拡張機能を使用する権限は、`pg_stat_scan_tables` ロールとデータベースのスーパーユーザーに限定されます。 Aurora で `pgstattuple` 拡張機能を作成するには、クライアントセッション (psql や pgAdmin など) をクラスターに接続し、次のコマンドを使用します。 ``` CREATE EXTENSION pgstattuple; ``` プロファイルする各データベースで、拡張機能を作成します。拡張機能を作成したら、コマンドラインインターフェイス (CLI) を使用して、使用できないスペースをどの程度再利用できるかを測定します。統計を収集する前に、AUTOVACUUM を 0 に設定してクラスターパラメータグループを変更します。0 に設定すると、アプリケーションによって残されたデットタプルを Aurora が自動的にクリーンアップすることを防止しますが、結果の精度に影響を与える可能性があります。次のコマンドを入力して、単純なテーブルを作成します。 ``` postgres=> CREATE TABLE lab AS SELECT generate_series (0,100000); SELECT 100001 ``` 次の例では、DB クラスターの AUTOVACUUM をオンにしてクエリを実行しています。`dead_tuple_count` は 0 で、これは AUTOVACUUM が PostgreSQL データベースから古いデータやタプルを削除したことを示します。 `pgstattuple` を使用してテーブルに関する情報を収集するには、クエリにテーブルの名前またはオブジェクト識別子 (OID) を指定します。 ``` postgres=> SELECT * FROM pgstattuple('lab'); ``` ``` table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent -----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+-------------- 3629056 | 100001 | 2800028 | 77.16 | 0 | 0 | 0 | 16616 | 0.46 (1 row) ``` 次のクエリでは、AUTOVACUUM をオフにして、テーブルから 25,000 行を削除するコマンドを入力しています。その結果、`dead_tuple_count` は 25000 に増加しました。 ``` postgres=> DELETE FROM lab WHERE generate_series < 25000; DELETE 25000 ``` ``` SELECT * FROM pgstattuple('lab'); ``` ``` table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent -----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+-------------- 3629056 | 75001 | 2100028 | 57.87 | 25000 | 700000 | 19.29 | 16616 | 0.46 (1 row) ``` これらのデットタプルを再利用するには、VACUUM プロセスを開始します。 ## アプリケーションを中断せずに肥大化をモニタリングする Aurora クラスターの設定は、ほとんどのワークロードでベストプラクティスが実現できるように最適化されています。ただし、アプリケーションや使用パターンに合わせてクラスターを最適化する場合もあります。この場合、ビジー状態のアプリケーションを中断することなく `pgstattuple` 拡張機能を使用できます。そのためには、次のステップを実行します。 1. Aurora インスタンスのクローンを作成します。 1. パラメータファイルを変更して、クローンの AUTOVACUUM をオフにします。 1. サンプルワークロードまたは pgbench (PostgreSQL でベンチマークテストを実行するためのプログラム) を使用してクローンをテストしながら `pgstattuple` クエリを実行します。詳細については、「[pgbench](https://www.postgresql.org/docs/current/pgbench.html)」を参照してください。 アプリケーションを実行して結果を確認したら、復元したコピーに pg\$1repack または VACUUM FULL を使用して違いを比較します。dead\$1tuple\$1count、dead\$1tuple\$1len、dead\$1tuple\$1percent のいずれかが大幅に減少している場合は、本稼働クラスターのバキュームスケジュールを調整して肥大化を最小限に抑えています。 ## テンポラリテーブルでの肥大化の回避 アプリケーションでテンポラリテーブルを作成する場合、そのテンポラリテーブルが不要になった場合はアプリケーションから削除してください。自動バキュームプロセスでは、テンポラリテーブルを検索しません。テンポラリテーブルをそのままにしておくと、データベースがすぐに肥大化してしまう可能性があります。さらに、この肥大化はシステムテーブルにまで及ぶ可能性があります。システムテーブルは、pg\$1attribute や pg\$1depend などの PostgreSQL オブジェクトや属性を追跡する内部テーブルです。 テンポラリテーブルが不要になったら、TRUNCATE ステートメントを使用してテーブルを空にして、スペースを空けることができます。次に、pg\$1attribute テーブルと pg\$1depend テーブルのバキューム処理を手動で実行します。これらのテーブルをバキューム処理することで、テンポラリテーブルを継続的に作成、切り捨て、削除しても、タプルが増えることでシステムが肥大化することがなくなります。 次の構文を追加して、コンテンツがコミットされた際に新しい行を削除することで、テンポラリテーブルを作成する際にこの問題を回避できます。 ``` CREATE TEMP TABLE IF NOT EXISTS table_name(table_description) ON COMMIT DELETE ROWS; ``` `ON COMMIT DELETE ROWS` 句は、トランザクションがコミットされたときにテンポラリテーブルを切り捨てます。 ## インデックスの肥大化の回避 テーブルのインデックス付きフィールドを変更した場合、インデックスを更新すると、そのインデックスに 1 つまたは複数のデットタプルが生成されます。デフォルトでは、自動バキューム処理によってインデックスの肥大化をクリーンアップしますが、このクリーンアップにはかなりの時間とリソースが必要です。テーブルの作成時にインデックスクリーンアップ設定を指定するには、vacuum\$1index\$1cleanup 句を含めてください。デフォルトでは、テーブルの作成時にこの句は AUTO に設定されます。つまり、サーバーは、テーブルのバキューム処理時にインデックスのクリーンアップが必要かどうかを判断します。句を ON に設定すると、特定のテーブルのインデックスクリーンアップを有効になります。OFF に設定すると、そのテーブルのインデックスクリーンアップを無効にできます。インデックスのクリーンアップをオフにすると時間を節約できる可能性がありますが、インデックスが肥大化する可能性があることに注意してください。 コマンドラインでテーブルのバキューム処理を行うと、インデックスのクリーンアップを手動で制御できます。テーブルをバキュームしてデッドタプルをインデックスから削除するには、INDEX\$1CLEANUP 句を ON の値とテーブル名で記述します。 ``` acctg=> VACUUM (INDEX_CLEANUP ON) receivables; INFO: aggressively vacuuming "public.receivables" VACUUM ``` インデックスを消去せずにテーブルをバキューム処理するには、OFF の値を指定します。 ``` acctg=> VACUUM (INDEX_CLEANUP OFF) receivables; INFO: aggressively vacuuming "public.receivables" VACUUM ```