Partager via

Réglage autonome

Le réglage autonome est une fonctionnalité de votre instance de serveur flexible Azure Database pour PostgreSQL qui analyse les requêtes suivies de votre charge de travail et fournit des recommandations pour améliorer les performances de ces requêtes.

Il s’agit d’une offre intégrée dans votre instance de serveur flexible Azure Database pour PostgreSQL, qui s’appuie sur les fonctionnalités du magasin de requêtes . Le réglage autonome analyse la charge de travail suivie par le Query Store et génère des recommandations d’index ou de tables afin d’améliorer les performances de la charge de travail analysée. Il peut produire des recommandations pour créer de nouveaux index, éliminer les index dupliqués ou inutilisés, analyser des tables qui n’ont pas de statistiques ou de statistiques obsolètes ou des tables gonflées à vide.

Description générale de l’algorithme de réglage autonome

Quand le paramètre serveur index_tuning.mode a la valeur report, les sessions d’optimisation démarrent automatiquement à la fréquence configurée dans le paramètre serveur index_tuning.analysis_interval, exprimée en minutes.

Dans la première phase, la session de paramétrage recherche la liste des bases de données dans lesquelles elle considère que les recommandations qu’il peut produire peuvent affecter considérablement les performances globales du système. Pour ce faire, elle collecte toutes les requêtes enregistrées par le Magasin des requêtes dont les exécutions ont été capturées dans l’intervalle de recherche sur lequel cette session d’optimisation se concentre. L’intervalle de recherche s’étend aux index_tuning.analysis_interval dernières minutes, à partir de l’heure de début de la session d’optimisation.

Pour toutes les requêtes lancées par l’utilisateur, dont les exécutions sont enregistrées dans le Magasin des requêtes et dont les statistiques d’exécution ne sont pas réinitialisées, un classement est effectué par le système en fonction de leur temps d’exécution total agrégé. Il concentre son attention sur les requêtes les plus importantes, en fonction de leur durée.

Les requêtes suivantes sont exclues de cette liste :

  • Requêtes lancées par le système. (autrement dit, les requêtes exécutées par le rôle azuresu)
  • Requêtes exécutées dans le contexte d’une base de données système (azure_sys, template0, template1 et azure_maintenance).

L’algorithme itère sur les bases de données cibles, à la recherche d’index potentiels susceptibles d’améliorer les performances des charges de travail analysées. Recherche également les index qui peuvent être éliminés, car ils sont des doublons ou ne sont pas utilisés pendant une période configurable. Identifie également les tables qui ne disposent pas de statistiques actuelles ou de tables gonflées.

Recommandations relatives à CREATE INDEX

Pour chaque base de données identifiée comme candidate à analyser, toutes les requêtes SELECT, UPDATE, INSERT et DELETE exécutées pendant l’intervalle de recherche et dans le contexte de cette base de données spécifique sont pris en compte.

L’ensemble de requêtes résultant est classé en fonction de son temps d’exécution total agrégé. Les premières requêtes, définies par index_tuning.max_queries_per_database, sont analysées dans le cadre d’éventuelles recommandations d’index.

Les recommandations potentielles visent à améliorer les performances des types de requêtes suivants :

  • Requêtes avec filtres (c’est-à-dire les requêtes ayant des prédicats dans la clause WHERE),
  • Requêtes qui joignent plusieurs relations, qu’elles suivent la syntaxe dans laquelle les jointures sont exprimées avec la clause JOIN, ou que les prédicats de jointure soient exprimés dans la clause WHERE.
  • Requêtes combinant des filtres et des prédicats de jointure.
  • Requêtes avec regroupement (requêtes ayant une clause GROUP BY).
  • Requêtes combinant des filtres et du regroupement.
  • Requêtes avec tri (requêtes ayant une clause ORDER BY).
  • Requêtes combinant des filtres et du tri.

Note

Le seul type d’index que le système recommande actuellement est B-Tree.

Si une requête fait référence à une colonne d’une table et que cette table n’a pas de statistiques, ne produit aucune recommandation d’index pour améliorer son exécution. Toutefois, il produit une recommandation pour analyser le tableau.

index_tuning.max_indexes_per_table spécifie le nombre d’index qui peuvent être recommandés, à l’exclusion des index pouvant déjà exister sur la table pour toute table unique référencée par un nombre illimité de requêtes durant une session d’optimisation.

index_tuning.max_index_count spécifie le nombre de recommandations d’index produites pour toutes les tables d’une base de données analysée durant une session d’optimisation.

Pour qu’une recommandation d’index soit émise, le moteur d’optimisation doit estimer qu’elle améliore au moins une requête dans la charge de travail analysée par un facteur spécifié avec index_tuning.min_improvement_factor.

De même, toutes les recommandations d’index sont vérifiées pour empêcher qu’elles n’introduisent une régression sur une requête au sein de cette charge de travail, d’un facteur spécifié avec index_tuning.max_regression_factor.

Note

index_tuning.min_improvement_factor et index_tuning.max_regression_factor font tous deux référence au coût des plans de requête, et non à leur durée ou aux ressources qu’ils consomment pendant l’exécution.

Tous les paramètres mentionnés dans les paragraphes précédents, leurs valeurs par défaut et leurs plages valides sont décrits dans Options de configuration.

Le script produit avec la recommandation de création d’un index suit ce modèle :

CREATE INDEX CONCURRENTLY {indexName} ON {schema}.{table}({column_name}[, ...])

Il inclut la clause CONCURRENTLY. Pour plus d’informations sur les effets de cette clause, consultez la documentation officielle de PostgreSQL relative à CREATE INDEX.

Le réglage autonome génère automatiquement les noms des index recommandés, qui se composent généralement des noms des différentes colonnes clés séparées par « _ » (traits de soulignement) et avec un suffixe constant « _idx ». Si la longueur totale du nom dépasse les limites de PostgreSQL, ou si elle entre en conflit avec des relations existantes, le nom est légèrement différent. Le nom peut être tronqué, et un nombre peut être ajouté à la fin de celui-ci.

Calculer l’impact d’une recommandation relative à CREATE INDEX

L’impact de la création d’une recommandation d’index est mesuré sur IndexSize (mégaoctets) et QueryCostImprovement (pourcentage).

IndexSize est une valeur unique qui représente la taille estimée de l’index, compte tenu de la cardinalité actuelle de la table et de la taille des colonnes référencées par l’index recommandé.

QueryCostImprovement se compose d’un tableau de valeurs, où chaque élément représente l’amélioration du coût du plan pour chaque requête dont le coût du plan est censé s’améliorer si cet index existe. Chaque élément montre l’identificateur de la requête (interrogée) et le pourcentage d’amélioration du coût du plan si la recommandation est implémentée (dimensionnelle).

Recommandations DROP INDEX et REINDEX

Pour chaque base de données identifiée comme candidate, elle doit lancer une nouvelle session et une fois la phase de recommandations CREATE INDEX terminée, elle recommande de supprimer ou de réindexer des index existants, en fonction des critères suivants :

  • Supprimer s’il est considéré comme un doublon d’autres index.
  • Supprimer s’il n’est pas utilisé pendant un laps de temps configurable.
  • Réindexer les index marqués comme non valides.

Supprimer les index dupliqués

Recommandations pour la suppression des index dupliqués : identifiez tout d’abord les index qui ont des doublons.

Les doublons sont classés selon les différentes fonctions qui peuvent être attribuées à l’index, et selon leur taille estimée.

Enfin, il est recommandé de supprimer tous les doublons ayant un classement inférieur à celui de son leader de référence, et de décrire la raison pour laquelle chaque doublon a été classé de cette façon.

Pour que deux index soient considérés comme dupliqués, ils doivent :

  • Être créés sur la même table.
  • Être un index du même type.
  • Avoir des colonnes clés correspondantes et, pour les clés d’index multicolonnes, ces colonnes doivent être référencées dans le même ordre.
  • Correspondre à l’arborescence de l’expression de son prédicat. S’applique uniquement aux index partiels.
  • Correspondre à l’arborescence de l’expression de toutes les références de colonnes non simples. S’applique uniquement aux index créés sur des expressions.
  • Correspondre au classement de chaque colonne référencée dans la clé.

Supprimer les index inutilisés

Les recommandations de suppression des index inutilisés identifient les index qui :

  • Ne sont pas utilisés depuis au moins index_tuning.unused_min_period jours.
  • Afficher une quantité minimale (moyenne quotidienne) de index_tuning.unused_dml_per_table DML dans la table où l’index est créé.
  • Afficher une quantité minimale (moyenne quotidienne) de index_tuning.unused_reads_per_table lectures dans la table où l’index est créé.

Réindexer des index non valides

Les recommandations relatives à la réindexation des index existants identifient les index qui sont marqués comme non valides. Pour en savoir plus sur la raison et le moment où les index sont marqués comme étant non valides, consultez REINDEX dans la documentation officielle de PostgreSQL.

Calculer l’impact d’une recommandation relative à DROP INDEX

L’impact d’une recommandation de suppression d’index est mesuré sur deux dimensions : Benefit (pourcentage) et IndexSize (mégaoctets).

L’avantage résultant est celui d’une valeur unique qui peut être ignorée pour le moment.

IndexSize est une valeur unique qui représente la taille estimée de l’index, compte tenu de la cardinalité actuelle de la table et de la taille des colonnes référencées par l’index recommandé.

Recommandations de tables

Pour chaque base de données identifiée comme candidate à analyser, elle lance une session qui vise à produire des recommandations au niveau de la table. Ces recommandations vous invitent à exécuter ANALYZE ou VACUUM sur les tables consultées par les requêtes inspectées, pour lesquelles le moteur de réglage considère que l’exécution de ces commandes pourrait améliorer les performances de votre charge de travail.

Recommandations concernant la table ANALYZE

Recommandations pour l’analyse d’une table identifient ces tables qui :

  • Sont référencés dans une requête et ont une colonne de cette table utilisée dans l’un de ses prédicats (WHERE, JOIN, ORDER BY, GROUP BY) et remplissent également l’une des deux conditions suivantes :
    • N’ont jamais été analysés.
    • Ont été analysés à un moment donné, mais manquent désormais de statistiques (généralement parce que le serveur s’est arrêté avant que les statistiques aient été conservées sur le disque).

Recommandations relatives aux tables VACUUM

Les recommandations pour vider une table identifient les tables qui sont gonflées. Ces recommandations sont produites uniquement lorsque autovacuum_enabled n'est pas défini sur off au niveau du serveur lorsque la charge de travail est analysée.

Configuration du réglage autonome

Le réglage autonome peut être activé, désactivé et configuré via un ensemble de paramètres qui contrôlent son comportement.

Lorsque le réglage autonome est activé, il se réveille avec une fréquence configurée dans le paramètre de serveur (par défaut, 720 minutes ou 12 heures) et commence à analyser la charge de travail enregistrée par le index_tuning.analysis_interval magasin de requêtes pendant cette période.

Notez que si vous changez la valeur de index_tuning.analysis_interval, elle est prise en compte seulement une fois que l’exécution planifiée suivante se termine. Par exemple, si vous activez le réglage autonome un jour à 10h00, car la valeur par défaut est index_tuning.analysis_interval de 720 minutes, la première exécution est planifiée pour démarrer à 10h00 le même jour. Les modifications que vous apportez à la valeur de index_tuning.analysis_interval entre 10h00 et 22h00 n’ont pas d’effet sur cette planification initiale. C’est seulement quand l’exécution planifiée se termine qu’elle lit la valeur actuelle définie pour index_tuning.analysis_interval et planifie l’exécution suivante en fonction de cette valeur.

Les options suivantes sont disponibles pour la configuration des paramètres de paramétrage autonomes :

Paramètre Description Par défaut Plage Unités
index_tuning.analysis_interval Définit la fréquence à laquelle chaque session d’optimisation des index est déclenchée quand index_tuning.mode est défini sur REPORT. 720 60 - 10080 minutes
index_tuning.max_columns_per_index Nombre maximal de colonnes qui peuvent faire partie de la clé d’index pour les index recommandés. 2 1 - 10
index_tuning.max_index_count Nombre maximal d’index recommandés pour chaque base de données lors d’une session d’optimisation. 10 1 - 25
index_tuning.max_indexes_per_table Nombre maximal d’index qui peuvent être recommandés pour chaque table. 10 1 - 25
index_tuning.max_queries_per_database Nombre de requêtes les plus lentes par base de données pour laquelle des index peuvent être recommandés. 25 5 - 100
index_tuning.max_regression_factor Régression acceptable introduite par un index recommandé sur les requêtes analysées lors d’une session d’optimisation. 0.1 0.05 - 0.2 pourcentage
index_tuning.max_total_size_factor Taille totale maximale, en pourcentage de l’espace disque total, que tous les index recommandés pour une base de données particulière peuvent utiliser. 0.1 0 - 1 pourcentage
index_tuning.min_improvement_factor Amélioration des coûts qu’un index recommandé doit fournir à au moins une des requêtes analysées lors d’une session d’optimisation. 0.2 0 - 20 pourcentage
index_tuning.mode Configure l’optimisation des index comme étant désactivée (OFF) ou activée pour émettre seulement des recommandations. Nécessite l’activation du Magasin des requêtes en définissant pg_qs.query_capture_mode sur TOP ou ALL. OFF OFF, REPORT
index_tuning.unused_dml_per_table Nombre minimal d’opérations DML en moyenne par jour affectant la table pour que la suppression de ses index inutilisés soit envisagée. 1000 0 - 9999999
index_tuning.unused_min_period Nombre minimal de jours pendant lesquels, d’après les statistiques système, l’index n’a pas été utilisé pour que sa suppression soit envisagée. 35 30 - 70
index_tuning.unused_reads_per_table Nombre minimal d’opérations de lecture en moyenne par jour affectant la table pour que la suppression de ses index inutilisés soit envisagée. 1000 0 - 9999999

Si vous utilisez les commandes az postgres flexible-server autonomous-tuning show-settings CLI et az postgres flexible-server autonomous-tuning set-settings pour afficher ou modifier l’un des paramètres de réglage autonomes, les valeurs acceptées comme arguments pour le --name paramètre sont celles affichées dans la colonne Paramètre du tableau précédent, mais sans inclure le préfixe index_tuning..

Information produite par le réglage autonome

Utiliser des recommandations de réglage autonome décrit en détail comment obtenir et utiliser les recommandations produites par le réglage autonome.

Limitations et prise en charge

Voici la liste des limitations et de l’étendue de prise en charge pour le réglage autonome.

Suppression automatique des recommandations

Les recommandations sont automatiquement supprimées 35 jours après la dernière fois qu’elles sont produites. Pour que ce mécanisme de suppression automatique fonctionne, le réglage autonome doit être activé.

Dépendance à l’extension hypopg

Pour que le tuning autonome produise des recommandations CREATE INDEX, il utilise l’extension hypopg.

Si l’extension existe déjà lorsqu’une session de paramétrage commence, elle est utilisée sur le schéma dans lequel elle a été créée. Et lorsque la session de paramétrage se termine, l’extension n’est pas supprimée. Une exception à cette règle est que l’extension a été créée dans le pg_catalog schéma. Si c’est le cas, le réglage autonome supprime l’extension.

Si l’extension n’existe pas au premier endroit ou que nous l’avons supprimée, car elle a été créée dans pg_catalog le schéma, le réglage autonome le crée sous un schéma appelé ms_temp_recommendations709253 et, lorsque la session de paramétrage se termine correctement, elle supprime l’extension et supprime le schéma.

Les utilisateurs qui sont membres du rôle azure_pg_admin peuvent supprimer l’extension hypopg à tout moment, même lorsqu’elle est créée par la fonctionnalité de réglage autonome. Toutefois, le supprimer alors qu’une session d’auto-optimisation est en cours peut entraîner l’échec de cette session et empêcher la génération de recommandations.

Niveaux de calcul et références SKU pris en charge

L’auto-optimisation est prise en charge sur tous les niveaux actuellement disponibles : Burstable, Usage général et Optimisé pour la mémoire, et sur toutes les configurations SKU de calcul actuellement prises en charge avec au moins 4 vCores.

Versions de PostgreSQL prises en charge

Le réglage autonome est pris en charge sur les principales versions12 ou ultérieures des instances de serveur flexible Azure Database pour PostgreSQL.

Utilisation de search_path

Le réglage autonome consomme la valeur persistante dans la colonne search_path de query_store.qs_view, de sorte que lorsque chaque requête est analysée, la même valeur que search_path celle définie lorsque la requête exécutée à l’origine est celle à laquelle elle est définie pour analyser les recommandations possibles.

Requêtes paramétrables

Les requêtes paramétrables créées avec PREPARE ou à l’aide du protocole de requête étendu sont analysées afin de produire des recommandations d’index sur celles-ci.

Pour l’analyse des requêtes paramétrables, le réglage autonome nécessite que pg_qs.parameters_capture_mode soit défini capture_first_sample lorsque le magasin de requêtes capture l’exécution de la requête. Elle nécessite également que les paramètres soient correctement capturés par le Magasin des requêtes lorsque la requête est exécutée. En d’autres termes, pour la requête analysée, query_store.qs_view doit avoir sa colonne parameters_capture_status définie sur succeeded.

Mode lecture seule et réplicas en lecture

Étant donné que le réglage autonome s’appuie sur les données que le magasin de requêtes conserve localement dans la azure_sys base de données, ce qui n’est pas supporté dans les réplicas de lecture ou lorsqu’une instance est en mode lecture seule, nous ne le prenons pas en charge sur les réplicas de lecture ou sur les instances en mode lecture seule.

Toutes les recommandations visibles sur un réplica en lecture ont été produites sur le réplica principal après une analyse exclusive de la charge de travail qui s’est exécutée sur le réplica principal.

Effectuer un scale-down du calcul

Si le réglage autonome est activé sur un serveur et que vous effectuez un scale-down du calcul de ce serveur à moins du nombre minimal de vCores requis, la fonctionnalité reste activée. Étant donné que la fonctionnalité n’est pas prise en charge sur les serveurs avec moins de 4 vCores, elle ne s’exécute pas pour analyser la charge de travail et produire des recommandations, même si index_tuning.mode a été réglé(e) sur ON lorsque le calcul a été réduit. Pendant que le serveur ne répond pas à la configuration minimale requise, tous les paramètres du serveur index_tuning.* sont inaccessibles. Chaque fois que vous augmentez votre serveur vers une unité de calcul qui répond aux exigences minimales, index_tuning.mode est configuré avec la valeur qu’il avait avant de le réduire vers une unité de calcul qui ne répondait pas aux exigences.

Haute disponibilité et réplicas en lecture

Si la haute disponibilité ou les réplicas en lecture sont configurés sur votre serveur, tenez compte des implications associées à la production de charges de travail intensives en écriture sur le serveur principal lors de l’implémentation des index recommandés. Soyez particulièrement vigilant quand vous créez des index dont la taille est considérée comme étant importante.

Raisons pour lesquelles le réglage autonome peut ne pas produire de recommandations de création d'index pour certaines requêtes

Voici une liste des types de requêtes pour lesquels le réglage autonome ne génère pas de recommandations CREATE INDEX. Requêtes qui :

  • Une erreur se produit lorsque le moteur d’auto-optimisation tente d’obtenir sa sortie EXPLAIN pendant la phase d’analyse.
  • Tables de référence qui n’ont pas de statistiques sur leur contenu dans le catalogue système pg_statistic. Exécutez ANALYZE sur ces tables afin que le moteur de paramétrage puisse prendre en compte ces requêtes à l’avenir.
  • Tronquer le texte de la requête dans le magasin de requêtes. C’est le cas lorsque la longueur du texte de requête dépasse la valeur configurée dans pg_qs.max_query_text_length.
  • Objets de référence qui ont été supprimés ou renommés avant que l’analyse ne se produise. Ces requêtes peuvent toujours être valides de manière syntactique, mais pas sémantiquement valides.
  • Accédez aux tables temporaires ou aux index sur des tables temporaires.
  • Accédez aux vues ou aux vues matérialisées.
  • Accédez aux tables partitionnée.
  • Sont identifiés comme des instructions utilitaires. Les instructions utilitaires ou les commandes utilitaires sont, en fait, toutes les instructions non considérées comme SELECT, INSERT, UPDATE, DELETE ou MERGE, et certaines commandes contenant l’une de ces commandes.
  • Ne figurent pas parmi les requêtes les plus lentes, dans la limite définie par index_tuning.max_queries_per_database, pour la base de données et la période analysées.
  • Ont été exécutés dans le contexte d’une base de données spécifique, quand aucune de ces requêtes n’a été identifiée comme la plus lente au niveau du serveur.

Contenu connexe

  • Last updated on