Partager via

Optimisation 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 pour Azure Database pour PostgreSQL – Serveur flexible et des guides de résolution des problèmes des fonctionnalités disponibles pour surveiller l’encombrement d’une base de données et les bloqueurs du nettoyage automatique. Il fournit également des informations sur le degré d’urgence ou de saturation où se trouve la base de données.

Qu’est-ce que le nettoyage automatique ?

Le nettoyage automatique est un processus en arrière-plan de 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. Quand il est activé, PostgreSQL décide automatiquement quand exécuter une opération 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 n’est trouvé, le nettoyage automatique ignore la page. Quand le nettoyage automatique trouve des tuples morts, il les supprime. Le coût est basé sur les éléments suivants :

Paramètre Descriptif
vacuum_cost_page_hit Le coût de la lecture d’une page figurant déjà dans des mémoires tampons partagées et ne nécessitant pas de lecture du disque. Par défaut, cette valeur est définie sur 1.
vacuum_cost_page_miss Le coût de l’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 Le coût de l’écriture sur une page quand des tuples morts y sont trouvés. Par défaut, cette valeur est définie sur 20.

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

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

Dans toutes les versions de Postgres actuellement prises en charge, la valeur par défaut pour autovacuum_vacuum_cost_limit est 200 (en fait, elle est définie sur -1, ce qui la rend égale à la valeur de la vacuum_cost_limit standard qui est par défaut 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

Le serveur flexible d'Azure Database pour PostgreSQL fournit les indicateurs suivants pour la surveillance de l'autovacuum.

Vous pouvez utiliser des métriques de nettoyage automatique pour surveiller et ajuster des performances de nettoyage automatique pour Azure Database pour PostgreSQL – Serveur flexible. Chaque métrique est émise à des intervalles de 30 minutes, puis conservée pendant une période de rétention allant jusqu’à 93 jours. Vous pouvez créer des alertes pour des métriques spécifiques, et vous pouvez fractionner, puis filtrer des données de métriques à l’aide de la dimension DatabaseName.

Comment activer les mesures du nettoyage automatique

  • Les métriques de nettoyage automatique sont désactivées par défaut.
  • Pour activer ces métriques, définissez le paramètre serveur metrics.autovacuum_diagnostics sur ON.
  • Ce paramètre est dynamique et ne nécessite donc pas de redémarrage de l’instance.

Liste des mesures de nettoyage automatique

Nom d’affichage ID de la mesure Unité Descriptif Dimension Par défaut permis
Analyser les tables utilisateur du compteur analyze_count_user_tables Nombre Nombre de fois où des tables utilisateur uniquement ont été analysées manuellement dans cette base de données. Nom de la base de données Non
Analyser automatiquement les tables utilisateur du compteur autoanalyze_count_user_tables Nombre Nombre de fois où les tables exclusives aux utilisateurs ont été analysées par le démon autovacuum dans cette base de données. Nom de la base de données Non
Nettoyer automatiquement les tables utilisateur du compteur autovacuum_count_user_tables Nombre Nombre de fois où des tables réservées aux utilisateurs ont été nettoyées par le démon autovacuum dans cette base de données. Nom de la base de données Non
Pourcentage de ballonnements (préversion) bloat_percent Pourcentage Pourcentage de gonflement estimé pour les tables dédiées aux utilisateurs. Nom de la base de données Non
Tables utilisateur estimées des lignes mortes n_dead_tup_user_tables Nombre Nombre estimé de lignes mortes pour les tables utilisateur uniquement dans cette base de données. Nom de la base de données Non
Tables utilisateur estimées des lignes actives n_live_tup_user_tables Nombre Nombre estimé de lignes actives pour les tables utilisateur uniquement dans cette base de données. Nom de la base de données Non
Tables utilisateur estimées des modifications n_mod_since_analyze_user_tables Nombre Nombre estimé de lignes modifiées depuis la dernière analyse des tables utilisateur uniquement. Nom de la base de données Non
Tables utilisateur analysées tables_analyzed_user_tables Nombre Nombre de tables utilisateur uniquement qui ont été analysées dans cette base de données. Nom de la base de données Non
Tables utilisateur analysées automatiquement tables_autoanalyzed_user_tables Nombre Nombre de tables exclusivement utilisateur qui ont été analysées par le processus de nettoyage automatique dans cette base de données. Nom de la base de données Non
Tables utilisateur nettoyées automatiquement tables_autovacuumed_user_tables Nombre Nombre de tables utilisateur uniquement qui ont été nettoyées automatiquement par le démon de nettoyage automatique dans cette base de données. Nom de la base de données Non
Compteur de tables utilisateur tables_counter_user_tables Nombre Nombre de tables utilisateur uniquement dans cette base de données. Nom de la base de données Non
Tables utilisateur nettoyées tables_vacuumed_user_tables Nombre Nombre de tables utilisateur uniquement qui ont été nettoyées dans cette base de données. Nom de la base de données Non
Tables d'utilisateur du compteur de vide vacuum_count_user_tables Nombre Nombre de fois où des tables utilisateur uniquement ont été nettoyées manuellement dans cette base de données (sans compter VACUUM FULL). Nom de la base de données Non

Considérations pour l'utilisation des métriques d'autovacuum

  • Les métriques de nettoyage automatique qui utilisent la dimension DatabaseName ont une limite de 30 bases de données.
  • Sur la référence SKU Burstable, la limite est de 10 bases de données pour les métriques qui utilisent la dimension DatabaseName.
  • La limite de dimension DatabaseName est appliquée à la colonne OID, qui correspond à l’ordre de création de la base de données.

Pour en savoir plus, consultez Métriques Autovacuum.

Utilisez les requêtes suivantes pour surveiller 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é des tables :

Paramètre Descriptif
dead_pct Le pourcentage de tuples morts par rapport aux tuples vivants.
last_autovacuum La date de la dernière exécution de la table.
last_autoanalyze La date de la dernière analyse automatique de la table.

Déclenchement d’un nettoyage automatique

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

Les équations exactes pour chaque action sont les suivantes :

  • Analyse automatique = 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 :

Paramètre Descriptif
autovacuum_analyze_scale_factor Le pourcentage d’insertions/mises à jour/suppressions qui déclenche ANALYZE sur la table.
autovacuum_analyze_threshold Spécifie le nombre minimal de tuples insérés/mis à jour/supprimés pour déclencher ANALYZE sur une table.
autovacuum_vacuum_insert_scale_factor Pourcentage d’insertions qui déclenchent ANALYZE sur la table.
autovacuum_vacuum_insert_threshold Spécifie le nombre minimal de tuples insérés pour déclencher ANALYZE sur une table.
autovacuum_vacuum_scale_factor Le pourcentage de mises à jour/suppressions qui déclenche VACUUM sur la table.

Utilisez la requête suivante pour lister les tables d’une base de données et identifier celles qui sont qualifiées pour le 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;

Remarque

La requête ne tient pas compte du fait que le nettoyage automatique peut être configuré par table en utilisant 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.

Non-suivi du rythme d’un serveur très sollicité

Le processus de nettoyage automatique estime le coût de chaque opération d’E/S, cumule un total pour chaque opération qu’il effectue et s’interrompt une fois que la limite supérieure du coût est 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 du 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 le rythme, les paramètres suivants peuvent être modifiés :

Paramètre Descriptif
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.

Remarque

  • 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.
  • autovacuum_vacuum_scale_factor est un autre paramètre qui peut déclencher un nettoyage sur une table en fonction de l’accumulation de tuples morts. Par défaut : 0.2, plage autorisée : 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 individuelle.

Exécution continue 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 Azure Database pour PostgreSQL – Serveur flexible. En règle générale, 50 Mo sont alloués à maintenance_work_mem pour chaque 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 que 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 judicieux 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 de 3 (sa valeur par défaut) à 4 ou à 5.

Erreurs de mémoire insuffisante

Des valeurs de 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 et 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 à la valeur par défaut, qui est 200.
  • Réduisez le nombre de autovacuum_max_workers si la valeur est supérieure à la valeur par défaut, qui est 3.

Trop de Workers de nettoyage automatique

L’augmentation du nombre de Workers de 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égradation des performances.

Chaque processus Worker de nettoyage automatique obtient seulement (1/autovacuum_max_workers) de la autovacuum_cost_limit totale, de sorte qu’avoir un nombre élevé de Workers entraîne le 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.

Cependant, 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 ne sont pas pris en compte dans l’algorithme d’équilibrage [autovacuum_cost_limit/autovacuum_max_workers].

Protection contre la saturation des ID de transaction (TXID) du nettoyage automatique

Quand une base de données s’exécute dans une protection contre la saturation des 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.

Remarque

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 néanmoins toujours exécuter VACUUM.

Le problème de saturation se produit quand 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 peuvent être les suivantes :

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, conduisant à une dégradation des performances des requêtes et à une situation de saturation. Une des raisons de cette situation peut être que les paramètres du nettoyage automatique ne sont pas correctement définis et qu’il ne suive donc pas le rythme d’un serveur très sollicité.

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. Ce sont des 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é commitées, elles empêchent la suppression des tuples morts.
La requête suivante permet de trouver les instructions préparées non commitées :

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

Utilisez COMMIT PREPARED ou ROLLBACK PREPARED pour commiter ou annuler 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.

Quand la base de données s’exécute dans une protection contre la saturation des 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 augmentant autovacuum_cost_limit à une valeur supérieure à 200. Cependant, les modifications apportées à ces paramètres ne s’appliquent pas aux Workers existants du nettoyage automatique. Redémarrez la base de données ou tuez manuellement les Workers existants pour appliquer les modifications des paramètres.

Exigences spécifiques à des tables

Les paramètres de nettoyage automatique peuvent être définis pour des tables individuelles. C’est particulièrement important pour les tables de petite et de grande taille. Par exemple, pour une petite table qui contient seulement 100 lignes, le nettoyage automatique déclenche l’opération VACUUM quand 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, ce qui empêche la maintenance d’autres tables pour lesquelles le pourcentage de modifications est moins important. Par contre, une table contenant un milliard de lignes doit subir des changements sur 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 un 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 uniquement

Dans les versions de PostgreSQL <= 13, le nettoyage automatique ne s’exécute pas sur les tables ayant une charge de travail d’insertion uniquement, 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 uniquement 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 là où il y a des analyses d’index uniquement, commencent à se dégrader au fil du temps.
  • La base de données peut s’exécuter avec une protection contre la saturation des ID de transaction.
  • Les bits d’indicateur ne sont pas définis.

Les solutions

Versions de Postgres <= 13

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

Pour obtenir des conseils, consultez Considérations spéciales relatives à l’utilisation de pg_cron dans Azure Database pour PostgreSQL – Serveur flexible.

Postgres 13 et versions ultérieures

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

Guides de résolution des problèmes

En utilisant les guides de résolution des problèmes de fonctionnalités disponibles sur le portail d’Azure Database pour PostgreSQL – Serveur flexible, il est possible de surveiller l’encombrement 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. Deux guides de résolution des problèmes sont disponibles : le premier est celui qui porte sur la surveillance automatique du nettoyage automatique, qui vise à surveiller l’encombrement 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 la saturation, ce qui permet d’identifier les bloqueurs potentiels du nettoyage automatique. Il fournit également des informations sur le degré de saturation ou d’urgence des bases de données sur le serveur. Les guides de résolution des problèmes contiennent aussi des 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.

Terminer le processus de nettoyage automatique – rôle pg_signal_autovacuum_worker

Le nettoyage automatique est un processus d’arrière-plan très important, car il contribue à un stockage efficace et au maintien des performances de la base de données. Dans le processus normal de nettoyage automatique, il s’annule lui-même après le deadlock_timeout. Si un utilisateur exécute une instruction DDL sur une table, un utilisateur peut être amené à attendre jusqu’à l’intervalle deadlock_timeout . Autovacuum n'autorise pas l'exécution de lectures/écritures sur la table requise par différentes demandes de connexion, ce qui ajoute à la latence de la transaction.

Nous avons introduit un nouveau rôle pg_signal_autovacuum_worker à partir de PostgreSQL, qui permet aux membres non-superutilisateurs de mettre fin à une tâche de nettoyage automatique en cours. Le nouveau rôle permet aux utilisateurs d’obtenir un accès sécurisé et contrôlé au processus de nettoyage automatique. Les non-super utilisateurs peuvent annuler le processus de nettoyage automatique une fois qu'ils ont reçu le rôle pg_signal_autovacuum_worker en utilisant la commande pg_terminate_backend. Le rôle pg_signal_autovacuum_worker est rétroporté sur Azure Database pour PostgreSQL – Serveur flexible dans les versions 15 et ultérieures de PostgreSQL.

Remarque

Nous vous déconseillons d'interrompre un processus de nettoyage automatique en cours, car terminer le processus de nettoyage automatique peut entraîner un gonflement des tables et des bases de données, ce qui peut entraîner des dégradations de performance. Toutefois, dans les cas où il existe une exigence critique pour l’entreprise impliquant l’exécution planifiée d’une instruction DDL qui coïncide avec le processus de nettoyage automatique, nous pouvons autoriser les non-superutilisateurs à mettre fin au nettoyage automatique de manière contrôlée et sécurisée à l’aide pg_signal_autovacuum_worker role.

Recommandations d’Azure Advisor

Les recommandations d’Azure Advisor sont un moyen proactif de déterminer si un serveur a un ratio d’encombrement élevé ou si le serveur s’approche du scénario de saturation des transactions. Vous pouvez également créer des alertes Azure Advisor pour les recommandations.

Les recommandations sont les suivantes :

  • Taux d’encombrement élevé : un ratio d’encombrement é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 quand le pourcentage d’encombrement 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 quand nous voyons que le serveur franchit le seuil de 1 milliard de transactions.

Contenu connexe