Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra.

TOAST (The Oversized-Attribute Storage Technique) est une fonctionnalité de PostgreSQL conçue pour gérer de grandes valeurs de données dépassant la taille de bloc de base de données habituelle de 8 Ko. PostgreSQL n'autorise pas les lignes physiques à s'étendre sur plusieurs blocs. La taille du bloc agit comme une limite supérieure de la taille des lignes. TOAST surmonte cette restriction en divisant les grandes valeurs de champs en plus petits morceaux. Il les stocke séparément dans une table TOAST dédiée liée à la table principale. Pour plus d'informations, consultez le mécanisme de stockage TOAST de PostgreSQL et la documentation d'implémentation.

Comprendre les opérations de TOAST

TOAST effectue la compression et stocke de grandes valeurs de champ hors ligne. TOAST attribue un OID (identifiant d'objet) unique à chaque bloc de données surdimensionné stocké dans la table TOAST. La table principale stocke l'ID de valeur TOAST et l'ID de relation sur la page pour référencer la ligne correspondante dans la table TOAST. Cela permet à PostgreSQL de localiser et de gérer efficacement ces fragments TOAST. Cependant, au fur et à mesure que la table TOAST s'allonge, le système risque d'épuiser le stock disponible OIDs, ce qui entraîne à la fois une dégradation des performances et des temps d'arrêt potentiels dus à l'épuisement de l'OID.

Identifiants d'objets dans TOAST

Un identifiant d'objet (OID) est un identifiant unique à l'échelle du système utilisé par PostgreSQL pour référencer des objets de base de données tels que des tables, des index et des fonctions. Ces identifiants jouent un rôle essentiel dans les opérations internes de PostgreSQL, car ils permettent à la base de données de localiser et de gérer efficacement les objets.

Pour les tables contenant des ensembles de données éligibles au toasting, PostgreSQL OIDs attribue une identification unique à chaque bloc de données surdimensionné stocké dans la table TOAST associée. Le système associe chaque fragment à unchunk_id, ce qui permet à PostgreSQL d'organiser et de localiser efficacement ces fragments dans la table TOAST.

Identifier les défis en matière de performance

La gestion des OID de PostgreSQL repose sur un compteur global de 32 bits, de sorte qu'il fonctionne après avoir généré 4 milliards de valeurs uniques. Bien que le cluster de base de données partage ce compteur, l'allocation d'OID implique deux étapes lors des opérations TOAST :

Une dégradation des performances peut se produire lorsque :

Pour plus d'informations, consultez la documentation relative aux limites de taille des tables TOAST de PostgreSQL et à l'allocation d'OID :

Un compteur global génère OIDs et enveloppe environ 4 milliards de valeurs, de sorte que le système génère à nouveau de temps en temps une valeur déjà utilisée. PostgreSQL le détecte et réessaie avec l'OID suivant. Un INSERT lent peut se produire s'il y a une très longue série de valeurs OID utilisées sans interruption dans la table TOAST. Ces difficultés s'accentuent à mesure que l'espace OID se remplit, ce qui ralentit les insertions et les mises à jour.

Identifier le problème

Isoler le problème

Recommandations

Les approches suivantes peuvent aider à résoudre les problèmes de contention des OID TOAST.

Contrôle

Utilisation des tables système

Vous pouvez utiliser les tables système de PostgreSQL pour surveiller l'augmentation de l'utilisation des OID.

Le bloc anonyme suivant compte le nombre de caractères distincts OIDs utilisés dans chaque table TOAST et affiche les informations de la table parent :

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

Exemple de sortie affichant les statistiques d'utilisation des OID par table 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

Le bloc anonyme suivant récupère l'OID maximal attribué pour chaque table TOAST non vide :

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

Exemple de sortie affichant la partie maximale IDs pour les tables 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

Utilisation de l'Analyse des performances

Les événements LWLock:buffer_io d'attente LWLock:OidGenLock apparaissent dans Performance Insights lors d'opérations nécessitant l'attribution de nouveaux identifiants d'objet ()OIDs. Les sessions actives à moyenne élevée (AAS) associées à ces événements indiquent généralement des conflits lors de l'attribution des OID et de la gestion des ressources. Cela est particulièrement courant dans les environnements caractérisés par une perte de données élevée, une utilisation importante de données ou une création d'objets fréquente.

LWLock:buffer_io

LWLock:buffer_ioest un événement d'attente qui se produit lorsqu'une session PostgreSQL attend la fin I/O des opérations sur une mémoire tampon partagée. Cela se produit généralement lorsque la base de données lit les données du disque dans la mémoire ou écrit des pages modifiées de la mémoire sur le disque. L'événement BufferIO d'attente garantit la cohérence en empêchant plusieurs processus d'accéder à la même mémoire tampon ou de la modifier pendant que les I/O opérations sont en cours. La fréquence élevée de cet événement d'attente peut indiquer un engorgement du disque ou une I/O activité excessive de la charge de travail de la base de données.

Pendant les opérations TOAST :

La taille de l'index influe directement sur le nombre de pages tampon auxquelles il est nécessaire d'accéder au cours de ces opérations. Même si l'indice n'est pas gonflé, sa taille même peut augmenter les E/S de la mémoire tampon, en particulier dans les environnements à forte concurrence ou à taux de désabonnement élevé. Pour plus d'informations, consultez : Guide de résolution des problèmes liés aux événements d'LWLockattente BufferIO.

LWLock:OidGenLock

OidGenLockest un événement d'attente qui se produit lorsqu'une session PostgreSQL attend d'allouer un nouvel identifiant d'objet (OID). Ce verrou garantit qu' OIDs ils sont générés de manière séquentielle et sûre, en ne permettant de générer qu'un seul processus OIDs à la fois.

Pendant les opérations TOAST :

Facteurs supplémentaires qui augmentent la contention des OID :