Gerenciar a contenção de TOAST OID no Amazon Aurora PostgreSQL

TOAST (The Oversized-Attribute Storage Technique) é um recurso do PostgreSQL projetado para lidar com grandes valores de dados que excedem o tamanho normal de blocos de banco de dados de 8 KB. O PostgreSQL não permite que linhas físicas abranjam vários blocos. O tamanho do bloco atua como um limite superior no tamanho da linha. O TOAST supera essa restrição dividindo grandes valores de campo em partes menores. Ele os armazena separadamente em uma tabela TOAST dedicada vinculada à tabela principal. Para acessar mais informações, consulte a documentação de implementação e mecanismo de armazenamento TOAST do PostgreSQL.

Noções básicas sobre operações TOAST

O TOAST executa a compactação e armazena grandes valores de campo fora da linha. O recurso atribui um OID (Identificador de Objeto) exclusivo a cada bloco de dados superdimensionados armazenado na tabela TOAST. A tabela principal armazena o ID do valor TOAST e o ID da relação na página para fazer referência à linha correspondente na tabela TOAST. Isso permite que o PostgreSQL localize e gerencie com eficiência esses fragmentos do TOAST. No entanto, à medida que a tabela TOAST cresce, o sistema corre o risco de esgotar os OIDs disponíveis, causando a degradação da performance e um possível tempo de inatividade devido ao esgotamento do OID.

Identificadores de objetos no TOAST

Identificador de Objeto (OID) é um identificador exclusivo em todo o sistema usado pelo PostgreSQL para fazer referência a objetos de banco de dados, como tabelas, índices e funções. Esses identificadores desempenham um papel essencial nas operações internas do PostgreSQL, permitindo que o banco de dados localize e gerencie objetos com eficiência.

Para tabelas com conjuntos de dados elegíveis para toasting, o PostgreSQL atribui OIDs para identificar exclusivamente cada fragmento de dado excedente armazenado na tabela TOAST associada. O sistema associa cada fragmento a um chunk_id, o que ajuda o PostgreSQL a organizar e localizar esses fragmentos de forma eficiente na tabela TOAST.

Identificar problemas de performance

O gerenciamento de OID do PostgreSQL depende de um contador global de 32 bits para que ele funcione depois de gerar 4 bilhões de valores exclusivos. Embora o cluster de banco de dados compartilhe esse contador, a alocação de OID envolve duas etapas durante as operações TOAST:

A degradação da performance pode ocorrer quando:

Para acessar mais informações, consulte a documentação de alocação de OID e limites de tamanho de tabelas TOAST do PostgreSQL:

Um contador global gera os OIDs e envolve cada 4 bilhões de valores, para que, periodicamente, o sistema gere novamente um valor já usado. O PostgreSQL detecta isso e tenta novamente com o próximo OID. Um INSERT lento poderá ocorrer se houver uma série muito longa de valores de OID usados sem lacunas na tabela TOAST. Esses desafios se tornam mais pronunciados à medida que o espaço do OID é preenchido, causando inserções e atualizações mais lentas.

Identificar o problema

Isolar o problema

Recomendações

As abordagens a seguir podem ajudar a resolver problemas de contenção do TOAST OID.

Monitoramento

Usar tabelas do sistema

Você pode usar as tabelas do sistema do PostgreSQL para monitorar o crescimento do uso de OIDs.

O bloco anônimo a seguir conta o número de OIDs distintos usados em cada tabela TOAST e exibe as informações da tabela principal:

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
$$;

Exemplo de saída exibindo estatísticas de uso de OIDs pela tabela TOAST:

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

O bloco anônimo a seguir recupera o OID máximo atribuído para cada tabela TOAST não vazia:

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
$$;

Exemplo de saída exibindo o máximo de IDs de blocos para tabelas TOAST:

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

Usar o Performance Insights

Os eventos de espera LWLock:buffer_io e LWLock:OidGenLock aparecem no Insights de Performance durante operações que exigem a atribuição de novos identificadores de objetos (OIDs). As sessões ativas de alta média (AAS) para esses eventos geralmente apontam para contenção durante a atribuição de OIDs e o gerenciamento de recursos. Isso é particularmente comum em ambientes com alta rotatividade de dados, uso extensivo de grandes volumes de dados ou criação frequente de objetos.

LWLock:buffer_io

LWLock:buffer_io é um evento de espera que ocorre quando uma sessão do PostgreSQL aguarda a conclusão das operações de E/S em um buffer compartilhado. Isso geralmente acontece quando o banco de dados lê dados do disco para a memória ou grava páginas modificadas da memória para o disco. O evento de espera BufferIO garante a consistência ao impedir que vários processos acessem ou modifiquem o mesmo buffer enquanto as operações de E/S estão em andamento. Altas ocorrências desse evento de espera podem indicar gargalos no disco ou atividade excessiva de E/S na workload do banco de dados.

Durante as operações TOAST:

O tamanho do índice afeta diretamente o número de páginas de buffer que precisam ser acessadas durante essas operações. Mesmo que o índice não esteja inchado, seu tamanho pode aumentar a E/S do buffer, principalmente em ambientes de alta simultaneidade ou alta rotatividade. Para acessar mais informações, consulte o Guia de solução de problemas com o evento de espera LWLock:BufferIO.

LWLock:OidGenLock

OidGenLock é um evento de espera que ocorre quando uma sessão do PostgreSQL está aguardando para alocar um novo identificador de objeto (OID). Esse bloqueio garante que os OIDs sejam gerados sequencialmente e com segurança, permitindo que somente um processo gere OIDs por vez.

Durante as operações TOAST:

Fatores adicionais que aumentam a contenção de OIDs: