# Amazon RDS for PostgreSQL での TOAST OID 競合の管理 TOAST (オーバーサイズ属性ストレージ技術) は、一般的な 8KB のデータベースブロックサイズを超える大きなデータ値を処理するように設計された PostgreSQL 機能です。PostgreSQL では、物理的な行が複数のブロックにまたがることは許可されません。ブロックサイズは、行サイズの上限として機能します。TOAST は、大きなフィールド値を小さなチャンクに分割することで、この制限を克服します。メインテーブルにリンクされた専用の TOAST テーブルに個別に保存されます。詳細については、「[PostgreSQL TOAST storage mechanism and implementation documentation](https://www.postgresql.org/docs/current/storage-toast.html)」を参照してください。 **Topics** + [ ## TOAST オペレーションについて ](#Appendix.PostgreSQL.CommonDBATasks.TOAST_OID.HowWorks) + [ ## パフォーマンスの課題を特定する ](#Appendix.PostgreSQL.CommonDBATasks.TOAST_OID.PerformanceChallenges) + [ ## 推奨事項 ](#Appendix.PostgreSQL.CommonDBATasks.TOAST_OID.Recommendations) + [ ## モニタリング ](#Appendix.PostgreSQL.CommonDBATasks.TOAST_OID.Monitoring) ## TOAST オペレーションについて TOAST は圧縮を実行し、大きなフィールド値を行外に保存します。TOAST は、TOAST テーブルに保存されているオーバーサイズのデータの各チャンクに一意の OID (オブジェクト識別子) を割り当てます。メインテーブルは TOAST 値 ID とリレーション ID をページに保存し、TOAST テーブルの対応する行を参照します。これにより、PostgreSQL はこれらの TOAST チャンクを効率的に見つけて管理できます。ただし、TOAST テーブルが大きくなると、システムは使用可能な OID を使い果たすリスクがあり、OID の枯渇によりパフォーマンスが低下し、ダウンタイムが発生する可能性があります。 ### TOAST のオブジェクト識別子 オブジェクト識別子 (OID) は、PostgreSQL がテーブル、インデックス、関数などのデータベースオブジェクトを参照するために使用するシステム全体の一意の識別子です。これらの識別子は PostgreSQL の内部オペレーションで重要な役割を果たし、データベースがオブジェクトを効率的に見つけて管理できるようにします。 トーストの対象となるデータセットを持つテーブルの場合、PostgreSQL は OID を割り当てて、関連付けられた TOAST テーブルに保存されているオーバーサイズデータの各チャンクを一意に識別します。システムは各チャンクを `chunk_id` に関連付けます。これにより、PostgreSQL はこれらのチャンクを TOAST テーブル内で効率的に整理して見つけることができます。 ## パフォーマンスの課題を特定する PostgreSQL の OID 管理は、グローバル 32 ビットカウンターに依存しているため、40 億の一意の値を生成した後は最初に戻ります。データベースクラスターがこのカウンターを共有する間、OID 割り当てには TOAST オペレーション中の 2 つのステップが含まれます。 + **割り当てのグローバルカウンター** – グローバルカウンターは、クラスター全体に新しい OID を割り当てます。 + **競合のローカル検索** – TOAST テーブルは、新しい OID がその特定のテーブルで既に使用されている既存の OID と競合しないようにします。 パフォーマンスの低下は、次の場合に発生する可能性があります。 + TOAST テーブルはフラグメント化または密な OID 使用率が高く、OID の割り当てが遅れます。 + データチャーンが高い環境や TOAST を多用する幅の広いテーブルがある環境では、システムは OID を頻繁に割り当てて再利用します。 詳細については、「[PostgreSQL TOAST table size limits and OID allocation documentation](https://wiki.postgresql.org/wiki/TOAST#Total_table_size_limit)」を参照してください。 グローバルカウンターは OID を生成し、40 億個の値ごとに最初に戻るため、システムは時折、既に使用されている値を再生成します。PostgreSQL はそれを検出し、次の OID で再試行します。TOAST テーブル内で、使用済み OID 値が非常に長く連続し、ギャップがない場合、INSERT が遅くなる可能性があります。これらの課題は、OID スペースがいっぱいになるとより顕著になり、挿入と更新が遅くなります。 ### 問題の特定 + 単純な `INSERT` ステートメントは、一貫性のないランダムな方法で、通常よりも大幅に時間がかかります。 + 遅延は、TOAST オペレーションを含む `INSERT` および `UPDATE` ステートメントに対してのみ発生します。 + システムが TOAST テーブルで使用可能な OID を見つけられない場合、次のログエントリが PostgreSQL ログに表示されます。 ``` LOG: still searching for an unused OID in relation "pg_toast_20815" DETAIL: OID candidates have been checked 1000000 times, but no unused OID has been found yet. ``` + Performance Insights は、`LWLock:buffer_io` および `LWLock:OidGenLock` 待機イベントに関連付けられた平均アクティブセッション (AAS) の数が多いことを示します。 次の SQL クエリを実行して、長時間実行される INSERT トランザクションを待機イベントで識別できます。 ``` SELECT datname AS database_name, usename AS database_user, pid, now() - pg_stat_activity.xact_start AS transaction_duration, concat(wait_event_type, ':', wait_event) AS wait_event, substr(query, 1, 30) AS TRANSACTION, state FROM pg_stat_activity WHERE (now() - pg_stat_activity.xact_start) > INTERVAL '60 seconds' AND state IN ('active', 'idle in transaction', 'idle in transaction (aborted)', 'fastpath function call', 'disabled') AND pid <> pg_backend_pid() AND lower(query) LIKE '%insert%' ORDER BY transaction_duration DESC; ``` 待機時間が長い INSERT オペレーションを表示するクエリ結果の例。 ``` database_name | database_user | pid | transaction_duration | wait_event | transaction | state ---------------+-----------------+-------+----------------------+---------------------+--------------------------------+-------- postgres | db_admin_user| 70965 | 00:10:19.484061 | LWLock:buffer_io | INSERT INTO "products" (......... | active postgres | db_admin_user| 69878 | 00:06:14.976037 | LWLock:buffer_io | INSERT INTO "products" (......... | active postgres | db_admin_user| 68937 | 00:05:13.942847 | : | INSERT INTO "products" (......... | active ``` ### 問題の分離 + **小さな挿入をテストする** – `toast_tuple_target` しきい値より小さいレコードを挿入します。TOAST ストレージの前に圧縮が適用されることに注意してください。これがパフォーマンスの問題なく動作する場合、問題は TOAST オペレーションに関連しています。 + **新しいテーブルをテストする** – 同じ構造で新しいテーブルを作成し、`toast_tuple_target` より大きいレコードを挿入します。問題なく動作する場合、問題は元のテーブルの OID 割り当てに限定されます。 ## 推奨事項 以下のアプローチは、TOAST OID 競合の問題を解決するのに役立ちます。 + **データのクリーンアップとアーカイブ** – 廃止されたデータや不要なデータを確認して削除し、将来の使用のために OID を解放するか、データをアーカイブします。次の制限事項を考慮してください。 + 将来のクリーンアップが常に可能とは限らないため、スケーラビリティが制限されます。 + 長時間実行される VACUUM オペレーションで、生成されたデッドタプルを削除できます。 + **新しいテーブルに書き込む** – 将来の挿入用に新しいテーブルを作成し、`UNION ALL` ビューを使用してクエリの古いデータと新しいデータを結合します。このビューは、古いテーブルと新しいテーブルの両方から組み合わされたデータを表示し、クエリが単一のテーブルとしてそれらにアクセスできるようにします。次の制限事項を考慮してください。 + 古いテーブルを更新すると、OID が枯渇する可能性があります。 + **パーティションまたはシャード** – テーブルまたはシャードデータをパーティション化して、スケーラビリティとパフォーマンスを向上させます。次の制限事項を考慮してください。 + クエリロジックとメンテナンスの複雑さが増し、パーティション化されたデータを正しく処理するためにアプリケーションの変更が必要になる可能性があります。 ## モニタリング ### システムテーブルの使用 PostgreSQL のシステムテーブルを使用して、OID 使用量の増加をモニタリングできます。 **警告** TOAST テーブルの OID の数によっては、完了までに時間がかかる場合があります。影響を最小限に抑えるために、営業時間外にモニタリングをスケジュールすることをお勧めします。 次の匿名ブロックは、各 TOAST テーブルで使用される個別の OID の数をカウントし、親テーブル情報を表示します。 ``` DO $$ DECLARE r record; o bigint; parent_table text; parent_schema text; BEGIN SET LOCAL client_min_messages TO notice; FOR r IN SELECT c.oid, c.oid::regclass AS toast_table FROM pg_class c WHERE c.relkind = 't' AND c.relowner != 10 LOOP -- Fetch the number of distinct used OIDs (chunk IDs) from the TOAST table EXECUTE 'SELECT COUNT(DISTINCT chunk_id) FROM ' || r.toast_table INTO o; -- If there are used OIDs, find the associated parent table and its schema IF o <> 0 THEN SELECT n.nspname, c.relname INTO parent_schema, parent_table FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE c.reltoastrelid = r.oid; -- Raise a concise NOTICE message RAISE NOTICE 'Parent schema: % | Parent table: % | Toast table: % | Number of used OIDs: %', parent_schema, parent_table, r.toast_table, TO_CHAR(o, 'FM9,999,999,999,999'); END IF; END LOOP; END $$; ``` TOAST テーブル別に OID 使用状況統計を表示する出力例。 ``` NOTICE: Parent schema: public | Parent table: my_table | Toast table: pg_toast.pg_toast_16559 | Number of used OIDs: 45,623,317 NOTICE: Parent schema: public | Parent table: my_table1 | Toast table: pg_toast.pg_toast_45639925 | Number of used OIDs: 10,000 NOTICE: Parent schema: public | Parent table: my_table2 | Toast table: pg_toast.pg_toast_45649931 | Number of used OIDs: 1,000,000 DO ``` 次の匿名ブロックは、空でない TOAST テーブルごとに割り当てられた最大 OID を取得します。 ``` DO $$ DECLARE r record; o bigint; parent_table text; parent_schema text; BEGIN SET LOCAL client_min_messages TO notice; FOR r IN SELECT c.oid, c.oid::regclass AS toast_table FROM pg_class c WHERE c.relkind = 't' AND c.relowner != 10 LOOP -- Fetch the max(chunk_id) from the TOAST table EXECUTE 'SELECT max(chunk_id) FROM ' || r.toast_table INTO o; -- If there's at least one TOASTed chunk, find the associated parent table and its schema IF o IS NOT NULL THEN SELECT n.nspname, c.relname INTO parent_schema, parent_table FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE c.reltoastrelid = r.oid; -- Raise a concise NOTICE message RAISE NOTICE 'Parent schema: % | Parent table: % | Toast table: % | Max chunk_id: %', parent_schema, parent_table, r.toast_table, TO_CHAR(o, 'FM9,999,999,999,999'); END IF; END LOOP; END $$; ``` TOAST テーブルの最大チャンク ID を表示する出力例。 ``` NOTICE: Parent schema: public | Parent table: my_table | Toast table: pg_toast.pg_toast_16559 | Max chunk_id: 45,639,907 NOTICE: Parent schema: public | Parent table: my_table1 | Toast table: pg_toast.pg_toast_45639925 | Max chunk_id: 45,649,929 NOTICE: Parent schema: public | Parent table: my_table2 | Toast table: pg_toast.pg_toast_45649931 | Max chunk_id: 46,649,935 DO ``` ### Performance Insights の使用 待機イベント `LWLock:buffer_io` と `LWLock:OidGenLock` は、新しいオブジェクト識別子 (OID) の割り当てを必要とするオペレーション中に Performance Insights に表示されます。これらのイベントの高平均アクティブセッション (AAS) は、通常、OID の割り当てとリソース管理中に競合を示します。これは、データチャーンが高い環境、大規模なデータが頻繁に使用される、またはオブジェクトが頻繁に作成される環境で特に一般的です。 #### LWLock:buffer\$1io `LWLock:buffer_io` は、PostgreSQL セッションが共有バッファの I/O オペレーションの完了を待機しているときに発生する待機イベントです。これは通常、データベースがディスクからメモリにデータを読み取り、変更されたページをメモリからディスクに書き込むときに発生します。`BufferIO` 待機イベントは、I/O オペレーションの進行中に複数のプロセスが同じバッファにアクセスまたは変更されないようにすることで、一貫性を確保します。この待機イベントの発生率が高い場合は、データベースワークロードのディスクのボトルネックまたは過剰な I/O アクティビティを示している可能性があります。 TOAST オペレーション中。 + PostgreSQL は大きなオブジェクト OID を割り当て、TOAST テーブルのインデックスをスキャンしてその一意性を確保します。 + 大きな TOAST インデックスでは、OID の一意性を検証するために複数のページにアクセスする必要がある場合があります。これにより、特にバッファプールが必要なすべてのページをキャッシュできない場合に、ディスク I/O が増加します。 インデックスのサイズは、これらのオペレーション中にアクセスする必要があるバッファページの数に直接影響します。インデックスが肥大化していない場合でも、特に高同時実行または高チャーン環境では、膨大なサイズによってバッファ I/O が増加する可能性があります。詳細については、「[LWLock:BufferIO 待機イベントのトラブルシューティングガイド](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/apg-waits.lwlockbufferio.html)」を参照してください。 #### Lwlock:OidGenLock `OidGenLock` は、PostgreSQL セッションが新しいオブジェクト識別子 (OID) の割り当てを待機しているときに発生する待機イベントです。このロックにより、OID が順番に安全に生成され、一度に 1 つのプロセスのみが OID を生成できます。 TOAST オペレーション中。 + **TOAST テーブルのチャンクの OID 割り当て** – PostgreSQL は、大きなデータレコードを管理するときに、TOAST テーブルのチャンクに OID を割り当てます。システムカタログの競合を防ぐために、各 OID は一意である必要があります。 + **高い同時実行性** – OID ジェネレーターへのアクセスはシーケンシャルであるため、複数のセッションが同時に OID を必要とするオブジェクトを作成している場合、`OidGenLock` の競合が発生する可能性があります。これにより、OID 割り当てが完了するまでセッションが待機する可能性が高まります。 + **システムカタログアクセスの依存関係** – OID を割り当てるには、`pg_class` や `pg_type` などの共有システムカタログテーブルを更新する必要があります。これらのテーブルで大量のアクティビティが発生した場合 (DDL オペレーションが頻繁に発生するため)、`OidGenLock` のロック競合が増加する可能性があります。 + **高い OID 割り当ての需要** – 大量のデータレコードを持つ TOAST 負荷の高いワークロードには、一定の OID 割り当てが必要で、競合が増大します。 OID の競合を増やすその他の要因。 + **頻繁なオブジェクトの作成** – 一時テーブルなどのオブジェクトを頻繁に作成および削除するワークロードは、グローバル OID カウンターでの競合を増幅します。 + **グローバルカウンターロック** – グローバル OID カウンターは一意性を確保するために連続してアクセスされ、同時実行性の高い環境で単一の競合ポイントを作成します。