Réglage du nettoyage automatique dans Azure Database pour PostgreSQL – Serveur flexible

S’APPLIQUE À : Azure Database pour PostgreSQL : serveur flexible

Cet article fournit une vue d’ensemble de la fonctionnalité de nettoyage automatique du serveur flexible Azure Database pour PostgreSQL et des guides de résolution des problèmes de fonctionnalités disponibles pour surveiller la surcharge d’une base de données, les bloqueurs du nettoyage automatique. Il fournit également des informations sur la distance entre la base de données et la situation d’urgence ou d’enveloppement.

Qu’est-ce que le nettoyage automatique ?

Le nettoyage automatique est un processus en arrière-plan PostgreSQL qui nettoie automatiquement les tuples morts et met à jour les statistiques. Il permet de maintenir les performances de la base de données en exécutant automatiquement deux tâches de maintenance clés :

• VACUUM (Nettoyage) : libère de l’espace disque en supprimant les tuples morts.
• ANALYZE (Analyse) : collecte des statistiques pour aider l’optimiseur PostgreSQL à choisir les meilleurs chemins d’exécution pour les requêtes.

Pour garantir que le nettoyage automatique fonctionne correctement, le paramètre du serveur de nettoyage automatique doit toujours être défini sur ON. Lorsqu’il est activé, PostgreSQL décide automatiquement quand exécuter VACUUM ou ANALYZE sur une table, ce qui garantit que la base de données reste efficace et optimisée.

Éléments internes du nettoyage automatique

Le nettoyage automatique lit les pages à la recherche de tuples morts et, si aucun fichier n’est trouvé, le nettoyage automatique ignore la page. Lorsque le nettoyage automatique trouve des tuples morts, il les supprime. Le coût est basé sur les éléments suivants :

La quantité de travail de nettoyage automatique dépend de deux paramètres :

Dans toutes les versions actuellement prises en charge de Postgres, la valeur par défaut de autovacuum_vacuum_cost_limit est 200 (en fait, elle est définie sur -1, ce qui la rend égale à la valeur du vacuum_cost_limit standard qui, par défaut, est de 200).

Quant à autovacuum_vacuum_cost_delay, dans Postgres version 11, elle est par défaut de 20 millisecondes, tandis que dans les versions 12 et ultérieures de Postgres, elle est par défaut de 2 millisecondes.

Le nettoyage automatique se réveille 50 fois (50*20 ms=1 000 ms) toutes les secondes. À chaque réveil, le nettoyage automatique lit 200 pages.

Cela signifie qu’en une seconde, le nettoyage automatique peut effectuer :

• ~80 Mo/s [ (200 pages/vacuum_cost_page_hit) * 50 * 8 Ko par page] si toutes les pages contenant des tuples morts sont trouvées dans des mémoires tampons partagées.
• ~8 Mo/s [ (200 pages/vacuum_cost_page_miss) * 50 * 8 Ko par page] si toutes les pages contenant des tuples morts sont lues sur le disque.
• ~4 Mo/s [ (200 pages/vacuum_cost_page_dirty) * 50 * 8 Ko par page], le nettoyage automatique peut écrire jusqu’à 4 Mo/s.

Surveiller le nettoyage automatique

Utilisez les requêtes suivantes pour superviser le nettoyage automatique :

select schemaname,relname,n_dead_tup,n_live_tup,round(n_dead_tup::float/n_live_tup::float*100) dead_pct,autovacuum_count,last_vacuum,last_autovacuum,last_autoanalyze,last_analyze from pg_stat_all_tables where n_live_tup >0;

Les colonnes suivantes permettent de déterminer si le nettoyage automatique est en accord avec l’activité de table :

Quand PostgreSQL déclenche le nettoyage automatique ?

Une action de nettoyage automatique (ANALYZE ou VACUUM) se déclenche lorsque le nombre de tuples morts dépasse un nombre particulier dépendant de deux facteurs : le nombre total de lignes d’une table, ainsi qu’un seuil fixe. ANALYZE, par défaut, se déclenche lorsque 10 % de la table plus 50 lignes changent, tandis que VACUUM se déclenche lorsque 20 % de la table plus 50 lignes changent. Sachant que le seuil de VACUUM est deux fois plus élevé que le seuil d’ANALYZE, ANALYZE est déclenché plus tôt que VACUUM. Pour les versions PG >=13 ; ANALYZE par défaut, se déclenche lors de l’insertion de 20 % de la table plus 1000 lignes.

Les équations exactes pour chaque action sont les suivantes :

• Autoanalyze = autovacuum_analyze_scale_factor * tuples + autovacuum_analyze_threshold ou autovacuum_vacuum_insert_scale_factor * tuples + autovacuum_vacuum_insert_threshold (Pour les versions PG >= 13)
• Nettoyage automatique = autovacuum_vacuum_scale_factor * tuples + autovacuum_vacuum_threshold

Par exemple, si nous avons une table avec 100 lignes. L’équation suivante fournit les informations sur les déclencheurs d’analyse et de nettoyage :

Pour les mises à jour/suppressions : Autoanalyze = 0.1 * 100 + 50 = 60
Autovacuum = 0.2 * 100 + 50 = 70

L’analyse se déclenche après la modification de 60 lignes sur une table et le nettoayge se déclenche lorsque 70 lignes sont modifiées sur une table.

Pour les insertions : Autoanalyze = 0.2 * 100 + 1000 = 1020

L’analyse se déclenche après l’insertion de 1020 lignes sur une table

Voici la description des paramètres utilisés dans l’équation :

Utilisez la requête suivante pour répertorier les tables d’une base de données et identifier celles pouvant être soumises au processus de nettoyage automatique :

 SELECT *
      ,n_dead_tup > av_threshold AS av_needed
      ,CASE
        WHEN reltuples > 0
          THEN round(100.0 * n_dead_tup / (reltuples))
        ELSE 0
        END AS pct_dead
    FROM (
      SELECT N.nspname
        ,C.relname
        ,pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins
        ,pg_stat_get_tuples_updated(C.oid) AS n_tup_upd
        ,pg_stat_get_tuples_deleted(C.oid) AS n_tup_del
        ,pg_stat_get_live_tuples(C.oid) AS n_live_tup
        ,pg_stat_get_dead_tuples(C.oid) AS n_dead_tup
        ,C.reltuples AS reltuples
        ,round(current_setting('autovacuum_vacuum_threshold')::INTEGER + current_setting('autovacuum_vacuum_scale_factor')::NUMERIC * C.reltuples) AS av_threshold
        ,date_trunc('minute', greatest(pg_stat_get_last_vacuum_time(C.oid), pg_stat_get_last_autovacuum_time(C.oid))) AS last_vacuum
        ,date_trunc('minute', greatest(pg_stat_get_last_analyze_time(C.oid), pg_stat_get_last_autoanalyze_time(C.oid))) AS last_analyze
      FROM pg_class C
      LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
      WHERE C.relkind IN (
          'r'
          ,'t'
          )
        AND N.nspname NOT IN (
          'pg_catalog'
          ,'information_schema'
          )
        AND N.nspname !~ '^pg_toast'
      ) AS av
    ORDER BY av_needed DESC ,n_dead_tup DESC;

Problèmes courants liés au nettoyage automatique

Passez en revue la liste suivante des problèmes courants possibles liés au processus de nettoyage automatique.

Valeur ne correspondant pas à un serveur chargé

Le processus de nettoyage automatique estime le coût de chaque opération d’E/S, accumule un total pour chaque opération qu’il effectue et s’interrompt une fois la limite supérieure du coût atteinte. autovacuum_vacuum_cost_delay and autovacuum_vacuum_cost_limit sont les deux paramètres de serveur utilisés lors du processus.

Par défaut, autovacuum_vacuum_cost_limit est défini sur –1, ce qui signifie que la limite de coût de nettoyage automatique est égale à la valeur du paramètre vacuum_cost_limit, qui est défini par défaut sur 200. vacuum_cost_limit correspond au coût du nettoyage manuel.

Si la valeur de autovacuum_vacuum_cost_limit est définie sur -1, le nettoyage automatique utilise le paramètre vacuum_cost_limit, mais si autovacuum_vacuum_cost_limit est définie sur une valeur supérieure à celle de -1, le paramètre autovacuum_vacuum_cost_limit est pris en compte.

Si le nettoyage automatique ne suit pas, les paramètres suivants peuvent être modifiés :

Exécution constante du nettoyage automatique

L’exécution continue du nettoyage automatique peut affecter l’utilisation du processeur et des E/S sur le serveur. Voici quelques-unes des raisons possibles :

maintenance_work_mem

Le démon de nettoyage automatique utilise autovacuum_work_mem, qui est défini par défaut sur -1, ce qui signifie que autovacuum_work_mem aurait la même valeur que le paramètre maintenance_work_mem. Ce document suppose que autovacuum_work_mem est défini sur -1 et que maintenance_work_mem est utilisé par le démon de nettoyage automatique.

Si la valeur de maintenance_work_mem est faible, elle peut être augmentée jusqu’à 2 Go sur un serveur flexible Azure Database pour PostgreSQL. En règle générale, 50 Mo sont alloués à maintenance_work_mem tous les 1 Go de RAM.

Grand nombre de bases de données

Le nettoyage automatique tente de démarrer un worker sur chaque base de données toutes les autovacuum_naptime secondes.

Par exemple, si un serveur comporte 60 bases de données et autovacuum_naptime est défini sur 60 secondes, le Worker de nettoyage automatique démarre toutes les secondes [autovacuum_naptime/Nombre de bases de données].

Il peut être utile d’augmenter autovacuum_naptime s’il existe davantage de bases de données dans un cluster. En même temps, le processus de nettoyage automatique peut être rendu plus agressif en augmentant autovacuum_cost_limit et en diminuant les paramètres autovacuum_cost_delay et en augmentant la valeur de autovacuum_max_workers par défaut de 3 à 4 ou 5.

Erreurs de mémoire insuffisante

Les valeurs maintenance_work_mem trop agressives peuvent occasionner régulièrement des erreurs de mémoire insuffisante dans le système. Il est important de connaître la RAM disponible sur le serveur avant d’apporter toute modification au paramètre maintenance_work_mem.

Le nettoyage automatique provoque trop d’interruptions

Si le nettoyage automatique consomme plus de ressources, vous pouvez effectuer les actions suivantes :

Paramètres de nettoyage automatique

Évaluez les paramètres autovacuum_vacuum_cost_delay, autovacuum_vacuum_cost_limit, autovacuum_max_workers. Une définition incorrecte des paramètres de nettoyage automatique peut aboutir à des scénarios où le nettoyage automatique provoque trop d’interruptions.

Si le nettoyage automatique provoque trop d’interruptions, envisagez les actions suivantes :

• Augmentez autovacuum_vacuum_cost_delay et réduisez autovacuum_vacuum_cost_limit si le paramètre est défini sur une valeur supérieure à celle par défaut de 200.
• Réduisez le nombre de autovacuum_max_workers si la valeur est supérieure à celle par défaut de 3.

Trop de workers de nettoyage automatique

L’augmentation du nombre de travailleurs du nettoyage automatique n’augmente pas nécessairement la vitesse du nettoyage. Il n’est pas recommandé d’avoir un grand nombre de workers de nettoyage automatique.

L’augmentation du nombre de Workers de nettoyage automatique entraîne une plus grande consommation de mémoire et, selon la valeur de maintenance_work_mem, peut entraîner une détérioration des performances.

Chaque processus de worker de nettoyage automatique obtient uniquement (1/autovacuum_max_workers) du total de autovacuum_cost_limit, de sorte que le fait d’avoir un nombre élevé de workers entraîne un fonctionnement plus lent de chacun d’eux.

Si le nombre de workers est augmenté, autovacuum_vacuum_cost_limit doit également être augmenté et/ou autovacuum_vacuum_cost_delay doit être diminué pour accélérer le processus de nettoyage.

Toutefois, si nous avons modifié les paramètres autovacuum_vacuum_cost_delay ou autovacuum_vacuum_cost_limit au niveau des tables, les Workers qui s’exécutent sur ces tables sont exemptés d’être pris en compte dans l’algorithme d’équilibrage [autovacuum_cost_limit/autovacuum_max_workers].

Protection contre le bouclage des ID de transaction (TXID) du nettoyage automatique

Lorsqu’une base de données s’exécute dans une protection contre le bouclage d’ID de transaction, un message d’erreur comme celui-ci peut être observé :

Database isn't accepting commands to avoid wraparound data loss in database 'xx'
Stop the postmaster and vacuum that database in single-user mode.

Le problème de bouclage se produit lorsque la base de données n’est pas nettoyée automatiquement ou si un trop grand nombre de tuples morts n’ont pas été supprimés par le nettoyage automatique. Les raisons possibles de ce problème sont :

Charge de travail élevée

La charge de travail peut entraîner un trop grand nombre de tuples morts dans une brève période, faisant que le nettoyage automatique ait des difficultés à suivre. Les tuples morts dans le système s’accumulent sur une période, entraînant une dégradation des performances de requête et une situation de bouclage. Une des raisons de cette situation peut se trouver dans le fait que les paramètres de nettoyage automatique ne soient pas correctement définis et qu’ils ne soient pas en accord avec un serveur chargé.

Transactions de longue durée

Les transactions de longue durée dans le système empêchent la suppression des tuples morts pendant l’exécution du nettoyage automatique. Ils servent de bloqueurs du processus de nettoyage. La suppression des transactions de longue durée libère des tuples morts pour pouvoir les supprimer lorsque le nettoyage automatique s’exécute.

Les transactions de longue durée peuvent être détectées à l’aide de la requête suivante :

    SELECT pid, age(backend_xid) AS age_in_xids,
    now () - xact_start AS xact_age,
    now () - query_start AS query_age,
    state,
    query
    FROM pg_stat_activity
    WHERE state != 'idle'
    ORDER BY 2 DESC
    LIMIT 10;

Instructions préparées

S’il existe des instructions préparées qui n’ont pas été validées, elles empêchent la suppression des tuples morts.
La requête suivante permet de trouver les instructions préparées non validées :

    SELECT gid, prepared, owner, database, transaction
    FROM pg_prepared_xacts
    ORDER BY age(transaction) DESC;

Utilisez COMMIT PREPARED ou ROLLBACK PREPARED pour valider ou restaurer ces instructions.

Emplacements de réplication non utilisés

Les emplacements de réplication inutilisés empêchent le nettoyage automatique de revendiquer des tuples morts. La requête suivante permet d’identifier les emplacements de réplication non utilisés :

    SELECT slot_name, slot_type, database, xmin
    FROM pg_replication_slots
    ORDER BY age(xmin) DESC;

Utilisez pg_drop_replication_slot() pour supprimer les emplacements de réplication non utilisés.

Lorsque la base de données s’exécute dans une protection contre le bouclage d’ID de transaction, recherchez les éventuels bloqueurs mentionnés précédemment et supprimez-les manuellement pour que le nettoyage automatique continue et se termine. Vous pouvez également augmenter la vitesse du nettoyage automatique en définissant autovacuum_cost_delay sur 0 et en définissant autovacuum_cost_limit sur une valeur supérieure à 200. Cependant, les modifications apportées à ces paramètres ne sont pas appliquées aux Workers existants du nettoyage automatique. Redémarrez la base de données ou neutralisez manuellement les workers existants pour appliquer les modifications de paramètres.

Exigences spécifiques aux tables

Les paramètres de nettoyage automatique peuvent être définis pour des tables individuelles. Il est particulièrement important pour les petites et les grandes tables. Par exemple, pour une petite table qui contient seulement 100 lignes, le nettoyage automatique déclenche l’opération VACUUM lorsque 70 lignes changent (comme calculé précédemment). Si ce tableau est fréquemment mis à jour, vous pouvez voir des centaines d’opérations de nettoyage automatique par jour, ce qui empêche la maintenance d’autres tables avec un pourcentage de modifications moins important. Une table contenant un milliard de lignes doit également changer 200 millions de lignes pour déclencher des opérations de nettoyage automatique. Une définition adéquate des paramètres de nettoyage automatique empêche ce type de scénarios.

Pour définir le paramètre de nettoyage automatique par table, modifiez les paramètres du serveur comme dans les exemples suivants :

    ALTER TABLE <table name> SET (autovacuum_analyze_scale_factor = xx);
    ALTER TABLE <table name> SET (autovacuum_analyze_threshold = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_scale_factor = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_threshold = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_cost_delay = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_cost_limit = xx);

Charges de travail d’insertion seule

Dans les versions de PostgreSQL <= 13, le nettoyage automatique ne s’exécute pas sur les tables ayant une charge de travail d’insertion seule, car il n’y a pas de tuples morts ni d’espace libre à récupérer. En revanche, l’analyse automatique s’exécute pour les charges de travail d’insertion seule, en raison de la présence de nouvelles données. Les inconvénients sont les suivants :

• La carte de visibilité des tables n’est pas mise à jour et, par conséquent, les performances des requêtes, en particulier lorsqu’il existe des analyses d’index seules, commencent à souffrir au fil du temps.
• La base de données peut s’exécuter dans une protection contre le bouclage d’ID de transaction.
• Les bits d’indicateur ne sont pas définis.

Solutions

Versions de Postgres <= 13

À l’aide de l’extension pg_cron, une tâche cron peut être configurée pour planifier une analyse périodique du nettoyage sur la table. La fréquence de la tâche cron dépend de la charge de travail.

Pour obtenir des instructions pas à pas sur l’utilisation de pg_cron, consultez Extensions.

Postgres 13 et versions ultérieures

Le nettoyage automatique s’exécute sur des tables avec une charge de travail d’insertion seule. Deux nouveaux paramètres de serveur autovacuum_vacuum_insert_threshold et autovacuum_vacuum_insert_scale_factor aident à contrôler le moment où le nettoyage automatique peut être déclenché sur les tables à insertion seule.

Guides de résolution des problèmes

À l’aide des guides de résolution des problèmes de fonctionnalités disponibles sur le portail du serveur flexible Azure Database pour PostgreSQL, il est possible de surveiller une surcharge au niveau de la base de données ou d’un schéma individuel et d’identifier les bloqueurs potentiels du processus de nettoyage automatique. Les guides de résolution des problèmes disponibles sont au nombre de deux : le premier est celui qui porte sur la surveillance automatique du nettoyage automatique, qui vise à surveiller le ballonnement au niveau de la base de données ou du schéma individuel. Le deuxième guide de résolution des problèmes concerne les bloqueurs de nettoyage automatique et l’enveloppement, ce qui permet d’identifier les bloqueurs de nettoyage automatique potentiels. Il fournit également des informations sur la distance des bases de données sur le serveur à partir de la situation d’enveloppement ou d’urgence. Les guides de résolution des problèmes font également part de suggestions pour atténuer les problèmes potentiels. Pour savoir comment configurer les guides de résolution des problèmes, consultez Configurer les guides de résolution des problèmes.

Recommandations Azure Advisor

Les recommandations d’Azure Advisor sont un moyen proactif d’identifier si un serveur a un ratio de ballonnement élevé ou si le serveur approche du scénario d’enveloppement des transactions. Vous pouvez également définir des alertes pour les recommandations à l’aide de Créer des alertes Azure Advisor sur de nouvelles recommandations à l’aide du portail Azure

Les recommandations sont les suivantes :

• Taux de ballonnement élevé : un ratio de ballonnement élevé peut affecter les performances du serveur de plusieurs façons. Un problème important est que l’optimiseur du moteur PostgreSQL peut avoir du mal à sélectionner le meilleur plan d’exécution, ce qui entraîne une dégradation des performances des requêtes. Par conséquent, une recommandation est déclenchée lorsque le pourcentage de ballonnement sur un serveur atteint un certain seuil pour éviter ces problèmes de performances.

• Enveloppement de transaction : ce scénario est l’un des problèmes les plus graves qu’un serveur peut rencontrer. Une fois que votre serveur est dans cet état, il peut cesser d’accepter plus de transactions, ce qui met le serveur en mode lecture seule. Par conséquent, une recommandation est déclenchée lorsque nous voyons que le serveur a franchi un seuil de 1 milliard de transactions.

Contenu connexe

• Utilisation élevée du processeur
• Utilisation élevée de la mémoire
• Paramètres de serveur