Amazon Aurora PostgreSQL での TOAST OID 競合の管理

TOAST (オーバーサイズ属性ストレージ技術) は、一般的な 8KB のデータベースブロックサイズを超える大きなデータ値を処理するように設計された PostgreSQL 機能です。PostgreSQL では、物理的な行が複数のブロックにまたがることは許可されません。ブロックサイズは、行サイズの上限として機能します。TOAST は、大きなフィールド値を小さなチャンクに分割することで、この制限を克服します。メインテーブルにリンクされた専用の TOAST テーブルに個別に保存されます。詳細については、「PostgreSQL TOAST storage mechanism and implementation documentation」を参照してください。

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 つのステップが含まれます。

パフォーマンスの低下は、次の場合に発生する可能性があります。

詳細については、「PostgreSQL TOAST table size limits and OID allocation documentation」を参照してください。

グローバルカウンターは OID を生成し、40 億個の値ごとに最初に戻るため、システムは時折、既に使用されている値を再生成します。PostgreSQL はそれを検出し、次の OID で再試行します。TOAST テーブル内で、使用済み OID 値が非常に長く連続し、ギャップがない場合、INSERT が遅くなる可能性があります。これらの課題は、OID スペースがいっぱいになるとより顕著になり、挿入と更新が遅くなります。

問題の特定

問題の分離

ベストプラクティス

以下のアプローチは、TOAST OID 競合の問題を解決するのに役立ちます。

モニタリング

システムテーブルの使用

PostgreSQL のシステムテーブルを使用して、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_io

LWLock:buffer_io は、PostgreSQL セッションが共有バッファの I/O オペレーションの完了を待機しているときに発生する待機イベントです。これは通常、データベースがディスクからメモリにデータを読み取り、変更されたページをメモリからディスクに書き込むときに発生します。BufferIO 待機イベントは、I/O オペレーションの進行中に複数のプロセスが同じバッファにアクセスまたは変更されないようにすることで、一貫性を確保します。この待機イベントの発生率が高い場合は、データベースワークロードのディスクのボトルネックまたは過剰な I/O アクティビティを示している可能性があります。

TOAST オペレーション中。

インデックスのサイズは、これらのオペレーション中にアクセスする必要があるバッファページの数に直接影響します。インデックスが肥大化していない場合でも、特に高同時実行または高チャーン環境では、膨大なサイズによってバッファ I/O が増加する可能性があります。詳細については、「LWLock:BufferIO 待機イベントのトラブルシューティングガイド」を参照してください。

Lwlock:OidGenLock

OidGenLock は、PostgreSQL セッションが新しいオブジェクト識別子 (OID) の割り当てを待機しているときに発生する待機イベントです。このロックにより、OID が順番に安全に生成され、一度に 1 つのプロセスのみが OID を生成できます。

TOAST オペレーション中。

OID の競合を増やすその他の要因。