Réglage autonome dans le Serveur flexible Azure Database pour PostgreSQL

Le réglage autonome est une fonctionnalité de Azure Database pour PostgreSQL serveur flexible qui analyse les requêtes enregistrées à partir 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 Azure Database pour PostgreSQL serveur flexible qui s'appuie sur les fonctionnalités du magasin de requêtes. L’optimisation automatique analyse la charge de travail suivie par Query Store et génère des recommandations d’index ou de tables pour 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.

• Identifiez les index qui sont bénéfiques à créer, car ils pourraient améliorer considérablement les requêtes analysées pendant une session de paramétrage autonome.
• Identifiez les index qui sont exactement des doublons et peuvent être éliminés.
• Identifiez les index non utilisés au cours d’une période configurable, et qui peuvent être de bons candidats à la suppression.
• Identifiez les index marqués comme non valides qui doivent être réindexés pour les transformer en index valides.
• Identifiez les tables qui manquent de statistiques actuelles qui doivent être analysées.
• Identifiez les tables qui sont gonflées et qui doivent être nettoyées.

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

Lorsque vous configurez le paramètre index_tuning.mode sur report, le système lance automatiquement des sessions de réglage à la fréquence que vous définissez dans le paramètre 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 où les recommandations 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. Il recherche également les index que vous pouvez éliminer, car ils sont des doublons ou ne sont pas utilisés pendant une période configurable. Il identifie également les tables qui manquent de statistiques actuelles ou de tables gonflées.

Recommandations relatives à CREATE INDEX

Pour chaque base de données identifiée comme candidate à analyser, le processus considère 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.

Le processus classe l’ensemble de requêtes résultant en fonction de leur temps d’exécution total agrégé et analyse les principales index_tuning.max_queries_per_database recommandations d’index possibles.

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

• Requêtes avec des filtres (c’est-à-dire des requêtes avec 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.

Si une requête fait référence à une colonne d’une table et que cette table n’a pas de statistiques, le processus 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, le processus vérifie toutes les recommandations d’index pour s’assurer qu’elles n’introduisent pas de régression sur une requête unique dans cette charge de travail d’un facteur spécifié avec index_tuning.max_regression_factor.

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

Le script produit avec la recommandation de créer 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 PostgreSQL pour 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 candidat, le processus lance une nouvelle session. 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

Les recommandations relatives à la suppression d’index en double commencent par identifier les index qui ont des doublons.

Les doublons sont classés en fonction de différentes fonctions que vous pouvez attribuer à l’index et en fonction de leurs tailles estimées.

Le processus recommande enfin de supprimer tous les doublons avec un classement inférieur à son leader de référence et décrit pourquoi chaque doublon a été classé comme il était.

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.
• Mettez en correspondance leurs colonnes clés et, pour les clés d’index sur plusieurs colonnes, respectez l’ordre dans lequel elles sont référencées.
• Correspondre à l’arborescence de l’expression de son prédicat. Cette condition s’applique uniquement aux index partiels.
• Correspondre à l’arborescence de l’expression de toutes les références de colonnes non simples. Cette condition s’applique uniquement aux index créés sur les expressions.
• Correspondre au classement de chaque colonne référencée dans la clé.

Supprimer les index inutilisés

Les recommandations relatives à la suppression d’index inutilisés identifient ces 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 pour réindexer les index existants identifient les index marqués comme non valides. Pour en savoir plus sur la raison et le moment où les index sont marqués comme non valides, consultez la documentation officielle REINDEX dans 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 est une valeur unique que vous pouvez ignorer 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, le processus 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 auxquelles les requêtes inspectées accèdent. Le moteur de réglage considère que l’exécution de ces commandes peut 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, , JOINORDER BY, GROUP BY), et répondent également à l’une des deux conditions suivantes :
  • Ne sont jamais 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. Le processus produit ces recommandations uniquement si 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

Vous pouvez activer, désactiver et configurer le réglage autonome via un ensemble de paramètres qui contrôlent son comportement.

Lorsque vous activez le réglage autonome, il se réveille à une fréquence configurée dans le paramètre (qui prend 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.

Si vous modifiez la valeur pour index_tuning.analysis_interval, la nouvelle valeur prend effet uniquement une fois l’exécution planifiée suivante terminée. 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 10 h 00 et 22 h 00 n’affectent pas cette programmation initiale. Uniquement lorsque l’exécution planifiée se termine, elle lit la valeur actuelle définie pour index_tuning.analysis_interval et planifie l’exécution suivante en fonction de cette valeur.

Utilisez les options suivantes pour configurer les paramètres de paramétrage autonomes :

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

La liste suivante décrit les limitations et l’étendue de prise en charge pour le réglage autonome.

Suppression automatique des recommandations

Le système supprime automatiquement les recommandations 35 jours après la dernière fois qu’il les a produites. Pour que ce mécanisme de suppression automatique fonctionne, vous devez activer le réglage autonome.

Dépendance à l’extension hypopg

Pour produire des CREATE INDEX recommandations, le réglage automatique utilise l’extension hypopg.

Si l’extension existe lorsqu’une session de paramétrage commence, le processus l’utilise sur le schéma où il a été créé. Une fois la session de paramétrage terminée, le processus ne supprime pas l’extension. 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 si le processus le supprime, car il a été créé dans pg_catalog le schéma, le réglage autonome le crée sous un schéma appelé ms_temp_recommendations709253. Une fois la session de paramétrage terminée, le processus supprime l’extension et supprime le schéma.

Les utilisateurs membres du azure_pg_admin rôle peuvent supprimer l’extension hypopg à tout moment, même lorsque la fonctionnalité de paramétrage autonome l’a créée. 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

Azure Database pour PostgreSQL : Serveur flexible prend en charge le réglage autonome sur tous les niveaux actuellement disponibles : Burstable, Usage général et Optimisé pour la mémoire. Il prend également en charge le réglage automatique sur tout SKU de calcul actuellement pris en charge disposant d’au moins 4 vCores.

Versions de PostgreSQL prises en charge

Le serveur flexible Azure Database pour PostgreSQL prend en charge le réglage autonome pour les versions majeures12 ou ultérieures.

Utilisation de search_path

Le réglage autonome utilise la valeur dans la search_path colonne de query_store.qs_view. Lorsqu’elle analyse chaque requête, elle utilise la même search_path valeur que celle définie lors de l’exécution initiale de la requête 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 et analysées pour produire des recommandations d’index.

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. Il nécessite également que le magasin de requêtes capture correctement les paramètres lors de l’exécution de la requête. En d’autres termes, pour la requête analysée, la parameters_capture_status colonne de query_store.qs_view doit être définie sur succeeded.

Mode lecture seule et réplicas en lecture

Étant donné que l’optimisation autonome s’appuie sur les données que le Query Store conserve localement dans la base de données azure_sys, et que les réplicas en lecture seule ou le mode lecture seule d’un serveur ne sont pas pris en charge, la fonctionnalité n’est pas prise en charge sur les réplicas en lecture seule ni sur les serveurs en mode lecture seule.

Toutes les recommandations affichées sur un réplica en lecture ont été produites sur le réplica principal après une analyse portant exclusivement sur la charge de travail exécutée sur le réplica principal.

Effectuer un scale-down du calcul

Si vous activez le réglage autonome sur un serveur, puis 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 elle a été définie ON lorsque vous avez réduit le calcul. Même si le serveur ne répond pas aux exigences minimales, tous les index_tuning.* paramètres 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 vous configurez la haute disponibilité ou des réplicas en lecture sur votre serveur, tenez compte des conséquences liées à la génération de charges de travail intensives en écriture sur le serveur principal lorsque vous appliquez les 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

Le réglage autonome ne génère CREATE INDEX pas de recommandations pour les types de requêtes suivants :

• Requêtes qui rencontrent une erreur lorsque le moteur de réglage autonome tente d’obtenir leur sortie EXPLAIN pendant la phase d’analyse.
• Requêtes qui référencent des tables sans statistiques sur leur contenu dans le pg_statistic catalogue système. Exécutez ANALYZE sur ces tables afin que le moteur de paramétrage puisse envisager ces requêtes à l’avenir.
• Requêtes avec texte de requête tronqué dans Query Store. Cette troncation se produit lorsque la longueur du texte de requête dépasse la valeur configurée dans pg_qs.max_query_text_length.
• Requêtes qui référencent des objets que vous avez supprimés ou renommés avant que l’analyse ne se produise. Ces requêtes peuvent toujours être valides de manière syntactique, mais elles ne sont pas sémantiquement valides.
• Requêtes qui accèdent à des tables temporaires ou à des index sur des tables temporaires.
• Requêtes qui accèdent aux vues ou aux vues matérialisées.
• Requêtes qui accèdent à des tables partitionnées.
• Requêtes identifiées en tant qu’instructions utilitaires. Les instructions utilitaires ou les commandes utilitaires sont, essentiellement, toute instruction qui n’est pas considérée comme SELECT, INSERT, UPDATE, DELETE ou MERGE, ainsi que certaines commandes contenant l’une de ces instructions.
• Requêtes qui ne figurent pas parmi les index_tuning.max_queries_per_database les plus lentes, pour la base de données et la période analysées.
• Requêtes qui s’exécutent dans le contexte d’une base de données spécifique, quand aucune de ces requêtes n’est identifiée comme la plus lente au niveau du serveur.

Contenu connexe

• Magasin de requêtes.
• Configurer le réglage autonome.
• Utilisez des recommandations de réglage autonomes.