Administración de la contención de TOAST OID en Amazon Aurora PostgreSQL

TOAST (la técnica de almacenamiento de atributos sobredimensionados) es una característica de PostgreSQL diseñada para gestionar valores de datos de gran tamaño que superan el tamaño típico de un bloque de base de datos de 8 KB. PostgreSQL no permite que las filas físicas abarquen varios bloques. El tamaño del bloque actúa como límite superior del tamaño de las filas. TOAST supera esta restricción al dividir los valores de campo grandes en fragmentos más pequeños. Los almacena de forma independiente en una tabla TOAST específica vinculada a la tabla principal. Para obtener más información, consulte la documentación de implementación y mecanismo de almacenamiento de PostgreSQL TOAST.

Descripción de las operaciones de TOAST

TOAST realiza la compresión y almacena valores de campo grandes fuera de línea. TOAST asigna un OID (identificador de objeto) único a cada fragmento de datos sobredimensionados almacenados en la tabla TOAST. La tabla principal almacena el ID del valor TOAST y el ID de relación en la página para hacer referencia a la fila correspondiente de la tabla TOAST. Esto permite a PostgreSQL localizar y administrar de forma eficiente estos fragmentos de TOAST. Sin embargo, a medida que crece la tabla de TOAST, el sistema corre el riesgo de agotar los OID disponibles, lo que provoca una degradación del rendimiento y un posible tiempo de inactividad debido al agotamiento de los OID.

Identificadores de objetos en TOAST

Un identificador de objeto (OID) es un identificador único de todo el sistema que PostgreSQL utiliza para hacer referencia a objetos de bases de datos, como tablas, índices y funciones. Estos identificadores desempeñan un papel fundamental en las operaciones internas de PostgreSQL, ya que permiten a la base de datos localizar y administrar los objetos de forma eficiente.

Para las tablas con conjuntos de datos elegibles para acciones de toast, PostgreSQL asigna los OID para identificar de forma única cada fragmento de datos sobredimensionados almacenado en la tabla TOAST asociada. El sistema asocia cada fragmento con un chunk_id, lo que ayuda a PostgreSQL a organizar y ubicar estos fragmentos de manera eficiente dentro de la tabla TOAST.

Identificación de los desafíos de rendimiento

La administración del OID de PostgreSQL se basa en un contador global de 32 bits para poder agrupar después de generar 4 mil millones de valores únicos. Aunque el clúster de bases de datos comparte este contador, la asignación del OID implica dos pasos durante las operaciones TOAST:

La degradación del rendimiento puede producirse cuando:

Para obtener más información, consulte los límites de tamaño de las tablas TOAST de PostgreSQL y la documentación de asignación de OID:

Un contador global genera los OID y los agrupa cada 4000 millones de valores, de modo que, de vez en cuando, el sistema vuelve a generar un valor ya utilizado. PostgreSQL lo detecta y lo vuelve a intentar con el siguiente OID. Se puede producir INSERT lento si hay una serie muy larga de valores OID usados sin espacios en blanco en la tabla TOAST. Estos desafíos se hacen más pronunciados a medida que se va llenando el espacio del OID, lo que hace que las inserciones y actualizaciones sean más lentas.

Identificación del problema

Aislamiento del problema

Recomendaciones

Los siguientes enfoques pueden ayudar a resolver los problemas de contención de TOAST OID.

Monitorización

Uso de tablas de sistemas

Puede utilizar las tablas del sistema de PostgreSQL para supervisar el aumento del uso de los OID.

El siguiente bloque anónimo cuenta el número de OID distintos que se utilizan en cada tabla de TOAST y muestra la información de la tabla 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
$$;

Ejemplo de salida que muestra las estadísticas de uso de los OID por tabla de 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

El siguiente bloque anónimo recupera el OID máximo asignado para cada tabla de TOAST que no esté vacía:

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

Ejemplo de salida que muestra el número máximo de ID de fragmentos para las tablas de 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

Uso de información sobre rendimiento

Los eventos de espera LWLock:buffer_io y LWLock:OidGenLock aparecen en la información de rendimiento durante las operaciones que requieren la asignación de nuevos identificadores de objeto (OID). El promedio alto de sesiones activas (AAS) de estos eventos suele indicar que hay problemas durante la asignación de los OID y la administración de los recursos. Esto es particularmente común en entornos con una alta cantidad de datos, un uso extensivo de datos de gran tamaño o una creación frecuente de objetos.

LWLock:buffer_io

LWLock:buffer_io es un evento de espera que se produce cuando una sesión de PostgreSQL espera a que se completen las operaciones de E/S en un búfer compartido. Esto suele ocurrir cuando la base de datos lee datos del disco a la memoria o escribe páginas modificadas de la memoria al disco. El evento de espera de BufferIO garantiza la coherencia al impedir que varios procesos accedan o modifiquen el mismo búfer mientras las operaciones de E/S están en curso. La alta incidencia de este evento de espera puede indicar cuellos de botella en el disco o una actividad de E/S excesiva en la carga de trabajo de la base de datos.

Durante las operaciones de TOAST:

El tamaño del índice afecta directamente a la cantidad de páginas del búfer a las que se debe acceder durante estas operaciones. Incluso si el índice no está sobrecargado, su gran tamaño puede aumentar la E/S del búfer, especialmente en entornos de alta concurrencia o alta rotación. Para obtener más información, consulte la LWLock:BufferIO wait event troubleshooting guide.

LWLock:OidGenLock

OidGenLock es un evento de espera que se produce cuando una sesión de PostgreSQL está esperando para asignar un nuevo identificador de objetos (OID). Este bloqueo garantiza que los OID se generen de forma secuencial y segura, lo que permite que solo un proceso genere los OID a la vez.

Durante las operaciones de TOAST:

Factores adicionales que aumentan la contención del OID: