Amazon Aurora PostgreSQL에서 TOAST OID 경합 관리

TOAST(Oversized-Attribute Storage Technique)는 일반적인 8KB 데이터베이스 블록 크기를 초과하는 큰 데이터 값을 처리하도록 설계된 PostgreSQL 기능입니다. PostgreSQL은 물리적 행이 여러 블록에 걸쳐 있는 것을 허용하지 않습니다. 블록 크기는 행 크기에 대한 상한 역할을 합니다. TOAST는 큰 필드 값을 더 작은 청크로 분할하여 이러한 제한을 극복합니다. 기본 테이블에 연결된 전용 TOAST 테이블에 별도로 저장합니다. 자세한 내용은 PostgreSQL TOAST 스토리지 메커니즘 및 구현 설명서를 참조하세요.

TOAST 작업 이해

TOAST는 압축을 수행하고 큰 필드 값을 일렬로 저장합니다. TOAST는 TOAST 테이블에 저장된 각 크기 초과 데이터 청크에 고유한 OID(객체 식별자)를 할당합니다. 기본 테이블은 TOAST 테이블의 해당 행을 참조하기 위해 TOAST 값 ID와 관계 ID를 페이지에 저장합니다. 이를 통해 PostgreSQL은 이러한 TOAST 청크를 효율적으로 찾고 관리할 수 있습니다. 그러나 TOAST 테이블이 증가함에 따라, 시스템에서 사용 가능한 OID가 소진되어 성능 저하와 OID 고갈로 인한 잠재적 가동 중지 시간이 발생할 위험이 있습니다.

TOAST의 객체 식별자

객체 식별자(OID)는 PostgreSQL에서 테이블, 인덱스 및 함수와 같은 데이터베이스 객체를 참조하는 데 사용하는 시스템 전체의 고유 식별자입니다. 이러한 식별자는 PostgreSQL의 내부 작업에서 중요한 역할을 하므로 데이터베이스가 객체를 효율적으로 찾고 관리할 수 있습니다.

토스팅에 적합한 데이터 세트가 있는 테이블의 경우 PostgreSQL은 OID를 할당하여 연결된 TOAST 테이블에 저장된 크기 초과 데이터의 각 청크를 고유하게 식별합니다. 시스템은 각 청크를 chunk_id와 연결하므로 PostgreSQL이 TOAST 테이블 내에서 이러한 청크를 효율적으로 구성하고 찾을 수 있습니다.

성능 문제 식별

PostgreSQL의 OID 관리는 40억 개의 고유 값을 생성한 후 래핑되도록 글로벌 32비트 카운터를 사용합니다. 데이터베이스 클러스터가 이 카운터를 공유하는 동안 OID 할당에는 TOAST 작업 중 두 단계가 포함됩니다.

성능 저하는 다음과 같은 경우에 발생할 수 있습니다.

자세한 내용은 PostgreSQL TOAST 테이블 크기 제한 및 OID 할당 설명서를 참조하세요.

글로벌 카운터는 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

성능 개선 도우미 사용

대기 이벤트 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가 순차적으로 안전하게 생성되므로 한 번에 하나의 프로세스만 OID를 생성할 수 있습니다.

TOAST 작업 중에 다음을 수행합니다.

OID 경합을 높이는 추가 요인: