Share via

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

  • Article

S’APPLIQUE À : Azure Database pour PostgreSQL – Serveur flexible

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

Qu’est-ce que le nettoyage automatique ?

La cohérence des données internes dans PostgreSQL est basée sur le mécanisme MVCC (Multi-Version Concurrency Control), qui permet au moteur de base de données de conserver plusieurs versions d’une ligne, et fournit une plus grande concurrence avec un blocage minimal entre les différents processus.

Les bases de données PostgreSQL ont besoin d’être entretenues correctement. Par exemple, lorsqu’une ligne est supprimée, elle n’est pas supprimée physiquement. Au lieu de cela, la ligne est marquée comme étant « morte » (« dead »). De même, pour les mises à jour, la ligne est marquée comme « morte » et une nouvelle version de la ligne est insérée. Ces opérations laissent des enregistrements morts, appelés tuples morts, même après la fin de toutes les transactions susceptibles de voir ces versions. S’ils ne sont pas nettoyés, les tuples morts restent. Ils consomment de l’espace disque et encombrent les tables et les index, ce qui entraîne des performances de requête lentes.

PostgreSQL utilise un processus appelé nettoyage automatique pour éliminer les tuples morts.

É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 :

  • vacuum_cost_page_hit : coût de la lecture d’une page figurant déjà dans des mémoires tampons partagée et ne nécessitant pas de lecture de disque. Par défaut, cette valeur est définie sur 1.
  • vacuum_cost_page_miss : coût d’extraction d’une page ne figurant pas dans les mémoires tampons partagées. Par défaut, cette valeur est définie sur 10.
  • vacuum_cost_page_dirty : coût d’écriture sur une page quand des tuples morts y sont trouvés. La valeur par défaut est définie sur 20.

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

  • autovacuum_vacuum_cost_limit est la quantité de travail que le nettoyage automatique effectue en une seule fois.
  • autovacuum_vacuum_cost_delay nombre de millisecondes pendant lesquelles le nettoyage automatique est en veille une fois qu’il a atteint la limite de coût spécifiée par le paramètre autovacuum_vacuum_cost_limit.

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 le rend égal à la valeur du vacuum_cost_limit standard, qui, par défaut, est 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 :

  • dead_pct: pourcentage de tuples morts par rapport aux tuples vivants.
  • last_autovacuum: date de la dernière exécution de la table.
  • last_autoanalyze: date de la dernière analyse de la 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.

Les équations exactes pour chaque action sont les suivantes :

  • Analyse automatique = autovacuum_analyze_scale_factor * tuples + autovacuum_analyze_threshold
  • Nettoyage automatique = autovacuum_vacuum_scale_factor * tuples + autovacuum_vacuum_threshold

Par exemple, ANALYZE se déclenche après la modification de 60 lignes sur une table qui contient 100 lignes, tandis que VACUUM se déclenche lorsque 70 lignes changent sur la table, à l’aide des équations suivantes :

Autoanalyze = 0.1 * 100 + 50 = 60
Autovacuum = 0.2 * 100 + 50 = 70

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;

Notes

La requête ne tient pas compte du fait que le nettoyage automatique peut être configuré par table à l’aide de la commande DDL « alter table ».

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 :

Paramètre Description
autovacuum_vacuum_scale_factor Valeur par défaut : 0.2, plage : 0.05 - 0.1. Le facteur d’échelle est spécifique à la charge de travail et doit être défini en fonction de la quantité de données contenues dans les tables. Avant de modifier la valeur, examinez la charge de travail et les volumes de chaque table.
autovacuum_vacuum_cost_limit Par défaut : 200. La limite de coût peut être augmentée. L’utilisation du processeur et des E/S sur la base de données doit être surveillée avant et après avoir apporté des modifications.
autovacuum_vacuum_cost_delay Postgres version 11 - Valeur par défaut : 20 ms. Le paramètre peut être réduit à 2-10 ms.
Postgres Versions 12 et ultérieures - Valeur par défaut : 2 ms.

Notes

La valeur autovacuum_vacuum_cost_limit est distribuée proportionnellement entre les workers de nettoyage automatique en cours d’exécution, de sorte que, s’il en existe plusieurs, la somme des limites pour chaque worker ne dépasse pas la valeur du paramètre autovacuum_vacuum_cost_limit

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. 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.

S’il maintenance_work_mem est faible, il peut être augmenté jusqu’à 2 Go sur Azure Database pour PostgreSQL serveur flexible. 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 beaucoup de ressources, vous pouvez effectuer les opérations 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 opérations 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.

Notes

Ce message d’erreur est une supervision de longue date. En règle générale, vous n’avez pas besoin de basculer vers le mode mono-utilisateur. Au lieu de cela, vous pouvez exécuter les commandes VACUUM requises et effectuer le réglage de VACUUM pour qu’il s’exécute rapidement. Bien que vous ne puissiez pas exécuter de langage de manipulation de données (DML), vous pouvez toujours exécuter VACUUM.

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 pu être supprimés par le nettoyage automatique. Les raisons possibles :

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 travailleurs 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 cette table est fréquemment mise à jour, vous pouvez voir des centaines d’opérations de nettoyage automatique par jour. Cela empêche le nettoyage automatique d’assurer la maintenance des autres tables dans lesquelles le pourcentage de modifications n’est pas aussi élevé. 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 antérieures à la version 13, le nettoyage automatique ne s’exécute pas sur les tables ayant une charge de travail d’insertion seule, car s’il n’y a pas de mises à jour ou de suppressions, 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

Pour les versions Postgres antérieures à 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 de serveur flexible Azure Database pour PostgreSQL, il est possible de surveiller les ballonnements au niveau de la base de données ou du schéma individuel, ainsi que d’identifier les bloqueurs potentiels pour le 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 est celui qui traite des bloqueurs du nettoyage automatique et du bouclage. Il aide à identifier les bloqueurs potentiels du nettoyage automatique et à déterminer si les bases de données du serveur sont proches ou non d’une situation de bouclage 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.

Contenu connexe