Partager via

Optimisation des index dans Azure Database pour PostgreSQL – Serveur flexible

  • Article

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

L’optimisation des index est une fonctionnalité d’Azure Database pour PostgreSQL - Serveur flexible, qui améliore automatiquement les performances de votre charge de travail en analysant les requêtes suivies, et en fournissant des recommandations d’index.

Il s’agit d’une offre intégrée à Azure Database pour PostgreSQL – Serveur flexible, qui s’appuie sur la fonctionnalité de monitoring des performances avec le Magasin des requêtes. L’optimisation des index analyse la charge de travail suivie par le Magasin des requêtes, et produit des recommandations d’index pour améliorer les performances de la charge de travail analysée, ou pour supprimer les index dupliqués ou inutilisés.

Description générale de l’algorithme d’optimisation des index

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 d’optimisation recherche la liste des bases de données pour lesquelles elle considère que les recommandations produites peuvent avoir un impact significatif sur 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 qui peuvent être éliminés, car ils sont identifiés en tant que doublons ou non utilisés pendant une période configurable.

Recommandations relatives à CREATE INDEX

Pour chaque base de données identifiée en tant que candidate à l’analyse pour la production de suggestions d’index, toutes les requêtes SELECTIONNER, METTRE À JOUR, INSÉRER et SUPPRIMER exécutées durant l’intervalle de recherche et dans le contexte de cette base de données spécifique sont prises en compte.

Remarque

L’optimisation des index analyse non seulement les instructions SELECT, mais également les instructions DML (UPDATE, INSERT et DELETE).

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.

Remarque

Les seuls types d’index recommandés par le système sont de type arbre B (B-tree).

Si une requête référence une colonne d’une table et que cette table n’a pas de statistiques, elle ignore l’ensemble de la requête et ne génère aucune recommandation d’index pour améliorer son exécution.

L’analyse requise pour collecter des statistiques peut être déclenchée manuellement à l’aide de la commande ANALYZE ou automatiquement par le démon de nettoyage automatique.

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.

Remarque

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.

L’optimisation des index 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és par des « _ » (traits de soulignement) et comportant un suffixe « _idx » constant. 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

Une nouvelle session doit être lancée pour chaque base de données dont la fonctionnalité d’optimisation des index est déterminée. Une fois la phase de recommandations CREATE INDEX achevée, elle recommande la suppression ou la réindexation 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 non valides, consultez REINDEX dans la documentation officielle 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é.

Configuration de l’optimisation des index

L’optimisation des index peut être activée, désactivée, et configurée via un ensemble de paramètres qui contrôlent son comportement, par exemple la fréquence d’exécution d’une session d’optimisation.

Découvrez tous les détails sur la configuration appropriée de la fonctionnalité d’optimisation des index dans le Guide pratique pour activer, désactiver, et configurer l’optimisation des index.

Informations produites par l’optimisation des index

L’article Guide pratique pour lire, interpréter et utiliser les recommandations produites par l’optimisation des index explique en détail comment obtenir et utiliser les recommandations produites par l’optimisation des index.

Limitations et prise en charge

Voici la liste des limitations et l’étendue de la prise en charge pour l’optimisation des index.

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

L’optimisation des index est prise en charge sur tous les niveaux disponibles (Burstable, Usage général et À mémoire optimisée) ainsi que sur toutes les références SKU de calcul prises en charge avec au moins 4 vCores.

Versions de PostgreSQL prises en charge

L’optimisation des index est prise en charge sur les versions majeures 12 ou ultérieures d’Azure Database pour PostgreSQL : serveur flexible.

Utilisation de search_path

L’optimisation des index consomme la valeur conservée dans la colonne search_path de query_store.qs_view, de sorte que lorsque chaque requête est analysée, la même valeur de search_path que celle définie lorsque la requête a été exécutée initialement 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, l’optimisation des index nécessite que pg_qs.parameters_capture_mode soit défini sur capture_first_sample lorsque le Magasin des 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 l’optimisation des index s’appuie sur le Magasin des requêtes, qui n’est pas pris en charge dans les réplicas en lecture seule ou lorsqu’une instance est en mode Lecture seule, nous ne la prenons pas en charge sur les réplicas en lecture seule ou sur les instances qui sont en mode Lecture seule.

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

Effectuer un scale-down du calcul

Si l’optimisation des index est activée sur un serveur et que vous effectuez un scale-down du calcul de ce serveur à un chiffre inférieur au nombre minimal de vCores requis, la fonctionnalité reste activée. La fonctionnalité n’étant pas prise en charge sur les serveurs avec moins de quatre vCores, elle ne s’exécutera pas pour analyser la charge de travail et produire des recommandations, même si index_tuning.mode a été défini sur ON lorsque le calcul a subi le scale-down. 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 effectuez un scale-up de votre serveur vers un calcul qui répond aux exigences minimales, index_tuning.mode est configuré avec la valeur qui était définie avant que vous procédiez au scale-down vers un calcul qui ne répond 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.

Contenu connexe