Utiliser le conseiller d'index

Cette page décrit le conseiller d'index Cloud SQL pour PostgreSQL et explique comment afficher et appliquer ses recommandations d'index.

Cloud SQL pour PostgreSQL propose un conseiller d'index entièrement géré qui suit régulièrement les requêtes traitées par votre base de données. Le conseiller d'index analyse régulièrement ces requêtes pour recommander de nouveaux index susceptibles d'en améliorer les performances. L'outil Index Advisor vous permet de détecter et de résoudre les problèmes de performances des systèmes et des requêtes.

Comment fonctionne le conseiller d'index ?

Le conseiller d'index vous aide à améliorer le traitement des requêtes en :

• recommandant un ensemble d'index à l'aide de commandes SQL permettant de créer des index.
• fournissant des données pour vous aider à évaluer les index recommandés, par exemple la taille de stockage estimée et l'impact des index sur une requête.

Les recommandations d'index sont chiffrées au repos.

Limites

Le conseiller d'index Cloud SQL pour PostgreSQL présente les limites suivantes:

• Le conseiller d'index ne fournit que des recommandations CREATE INDEX.
• Le conseiller d'index n'est pas compatible avec les instances présentant les configurations suivantes :
• Instances Cloud SQL Enterprise
• Instances répliquées avec accès en lecture

Avant de commencer

Pour obtenir des recommandations de l'outil de conseil sur les index, vous devez utiliser l'édition Cloud SQL Enterprise Plus et activer les insights sur les requêtes pour l'édition Cloud SQL Enterprise Plus pour votre instance Cloud SQL.

Rôles et autorisations requis

Pour obtenir les autorisations nécessaires pour obtenir des recommandations de l'outil de conseil sur les index, demandez à votre administrateur de vous accorder le rôle IAM Lecteur Cloud SQL (roles/cloudsql.viewer) sur le projet qui héberge l'instance Cloud SQL. Pour en savoir plus sur l'attribution de rôles, consultez la page Gérer l'accès aux projets, aux dossiers et aux organisations.

Ce rôle prédéfini contient les autorisations requises pour obtenir des recommandations de l'outil Index Advisor. Pour connaître les autorisations exactes requises, développez la section Autorisations requises :

Vous pouvez également obtenir ces autorisations avec des rôles personnalisés ou d'autres rôles prédéfinis.

Activer les recommandations du conseiller d'index

Pour activer les recommandations du conseiller d'index :

1. Dans la console Google Cloud , accédez à la page Instances Cloud SQL.

   Accéder à la page Instances Cloud SQL

   .
2. Pour ouvrir la page Présentation d'une instance, cliquez sur son nom. .
3. Dans la tuile Configuration, cliquez sur Modifier la configuration.
4. Dans la section Personnaliser votre instance, développez Insights sur les requêtes.
5. Assurez-vous que l'option Activer Insights sur les requêtes est activée.
6. Si ce n'est pas déjà fait, sélectionnez Activer les fonctionnalités Enterprise Plus.
7. Sélectionnez Activer le conseiller d'index.
8. Cliquez sur Enregistrer.

Désactiver les recommandations du conseiller d'index

Pour désactiver les recommandations du conseiller d'index, procédez comme suit :

1. Dans la console Google Cloud , accédez à la page Instances Cloud SQL.

   Accéder à la page Instances Cloud SQL

2. Pour ouvrir la page Présentation d'une instance, cliquez sur son nom.
3. Dans la tuile Configuration, cliquez sur Modifier la configuration.
4. Dans la section Personnaliser votre instance, développez Insights sur les requêtes.
5. Décochez la case Activer le conseiller d'index.
6. Cliquez sur Enregistrer.

Afficher les recommandations du conseiller d'index

Cloud SQL exécute automatiquement et régulièrement l'analyse du conseiller d'index. Pour afficher les recommandations du conseiller d'index, utilisez le tableau de bord "Insights sur les requêtes". Vous pouvez également afficher et interroger les recommandations du conseiller d'index sous forme de tableau, ou demander une analyse et un rapport à la demande à tout moment.

Afficher et filtrer les recommandations dans le tableau de bord "Insights sur les requêtes"

1. Dans la console Google Cloud , accédez à la page Instances Cloud SQL.

   Accéder à la page Instances Cloud SQL

2. Pour ouvrir la page Présentation d'une instance, cliquez sur son nom.
3. Cliquez sur Insights sur les requêtes.
4. Les recommandations du conseiller d'index sont affichées dans la colonne Recommandation de la section Requêtes et tags les plus fréquents.
5. (Facultatif) Pour n'afficher que les requêtes avec des recommandations CREATE INDEX, ajoutez un filtre pour le champ Recommandation : Créer des index.

Afficher les recommandations pour une requête

Pour afficher les recommandations d'index pour une requête spécifique, procédez comme suit :

1. Dans la console Google Cloud , accédez à la page Instances Cloud SQL.

   Accéder à la page Instances Cloud SQL

2. Pour ouvrir la page Présentation d'une instance, cliquez sur son nom.
3. Cliquez sur Insights sur les requêtes.
4. Dans la section Requêtes et tags les plus fréquents, cliquez sur Requêtes.
5. Pour obtenir les détails d'une recommandation sur une requête, effectuez l'une des opérations suivantes :
   • Cliquez sur une requête pour en savoir plus sur les recommandations de la requête sélectionnée, y compris les informations suivantes :
     • Impact sur les performances (élevé, moyen et faible): estimation de la vitesse de requête après la création de tous les index recommandés.
     • Recommandations: permet de créer des recommandations d'index.
     • Tables concernées : nombre de tables qui seront concernées lorsque des index seront créés.
     • Espace de stockage supplémentaire estimé nécessaire: taille d'espace de stockage estimée nécessaire pour créer tous les index recommandés.
     • Nombre de requêtes concernées : nombre total de requêtes de la charge de travail concernées par les recommandations d'index. Un index peut bénéficier de plusieurs requêtes.
   • Cliquez sur Créer des index pour une requête spécifique afin d'obtenir des recommandations détaillées sur la création d'index afin d'améliorer les performances des requêtes.

Afficher les recommandations sous forme de vue Tableau de base de données

• google_db_advisor_recommended_indexes : répertorie les nouveaux index recommandés pour chaque base de données. Il comprend également des estimations de l'espace de stockage requis pour chaque index et du nombre de requêtes que chaque index peut affecter.

• google_db_advisor_workload_report : liste chaque requête pour laquelle le conseiller recommande un ou plusieurs nouveaux index. Chaque ligne récapitule les recommandations pour la requête concernée.

Par exemple, pour afficher les résultats de l'analyse des recommandations d'index la plus récente, au format tableau, exécutez la requête suivante :

SELECT * FROM google_db_advisor_recommended_indexes;

Si l'analyse la plus récente du conseiller d'index ne trouve aucune recommandation, cette requête renvoie un tableau sans aucune ligne.

Étant donné que tous ces rapports existent en tant que vues de base de données ordinaires, vous pouvez écrire des requêtes qui filtrent ou présentent ces informations. Par exemple, pour afficher un rapport qui associe les index recommandés à la requête complète associée, associez les vues google_db_advisor_workload_report et google_db_advisor_workload_statements sur leurs colonnes query_id respectives:

SELECT DISTINCT recommended_indexes, query
FROM google_db_advisor_workload_report r, google_db_advisor_workload_statements s
WHERE r.query_id = s.query_id;

Demander manuellement une analyse d'index

Plutôt que d'attendre la prochaine analyse planifiée du conseiller d'index, vous pouvez demander à Cloud SQL pour PostgreSQL d'exécuter immédiatement une analyse et d'afficher son rapport. Pour Cloud SQL pour PostgreSQL, vous devez attendre au moins 15 minutes après avoir activé le conseiller d'index pour exécuter une analyse manuelle. Pour ce faire, exécutez la fonction SQL suivante :

SELECT * FROM google_db_advisor_recommend_indexes();

Une fois l'analyse terminée, Cloud SQL pour PostgreSQL affiche un rapport sous forme de table contenant la description et les besoins de stockage estimés de tous les index recommandés. Si l'analyse ne trouve aucun nouvel index à recommander, la vue ne contient aucune ligne.

Notez que le rôle utilisateur qui exécute cette commande peut affecter les recommandations affichées. Cloud SQL pour PostgreSQL limite son affichage aux recommandations d'index en fonction des requêtes émises par l'utilisateur actuel de la base de données.

Créer des index recommandés

Vous pouvez créer des index recommandés à partir du tableau de bord "Insights sur les requêtes" ou d'une vue Tableau de base de données.

Créer un index recommandé à l'aide du tableau de bord "Insights sur les requêtes"

Pour créer un index recommandé à l'aide du tableau de bord "Insights sur les requêtes", procédez comme suit :

1. Dans la console Google Cloud , accédez à la page Instances Cloud SQL.

   Accéder à la page Instances Cloud SQL

2. Pour ouvrir la page Présentation d'une instance, cliquez sur son nom.
3. Cliquez sur Insights sur les requêtes.
4. Dans le tableau Dimensions principales par charge de la base de données, cliquez sur Requêtes.
5. Cliquez sur Créer des index pour une requête spécifique.
6. Cliquez sur Copier toutes les commandes d'index. Les commandes CREATE INDEX sont copiées dans le presse-papiers.
7. Connectez-vous à l'instance principale sur la ligne de commande.

8. Pour créer les index recommandés, exécutez les commandes qui ont été copiées dans votre presse-papiers, par exemple :

   CREATE INDEX ON "public"."demo_order" ("customer_id");

Créer un index recommandé à l'aide d'une vue Tableau de base de données

La colonne index de la vue google_db_advisor_recommended_indexes contient, dans chaque ligne, une instruction LDD CREATE INDEX PostgreSQL complète permettant de générer l'index recommandé dans cette ligne.

Pour appliquer la recommandation de cette ligne, exécutez l'instruction LDD exactement telle qu'elle est présentée. Cela inclut de le copier dans votre presse-papiers et de le coller dans une requête psql.

Par exemple, considérons ce résultat de l'exécution manuelle d'une analyse à l'aide de la requête décrite dans la section précédente :

                    index                   | estimated_storage_size_in_mb
--------------------------------------------+------------------------------
 CREATE INDEX ON "School"."Students"("age") |                            3
(1 row)

Ce rapport contient une seule recommandation : ajouter un index à une seule colonne sur la colonne age dans la table Students du schéma School. Pour appliquer ce conseil, saisissez une requête LDD comme indiqué dans le rapport :

CREATE INDEX ON "School"."Students"("age");

Afficher les requêtes concernées

1. Dans la console Google Cloud , accédez à la page Instances Cloud SQL.

   Accéder à la page Instances Cloud SQL

2. Pour ouvrir la page Présentation d'une instance, cliquez sur son nom.
3. Cliquez sur Insights sur les requêtes.
4. Dans le tableau Dimensions principales par charge de la base de données, cliquez sur Requêtes.
5. Cliquez sur Créer des index pour une requête spécifique.
6. Cliquez sur Afficher les requêtes concernées.
7. Cliquez sur une requête pour en savoir plus sur celle-ci.

Afficher les requêtes suivies du conseiller d'index

La vue google_db_advisor_workload_statements contient la liste de toutes les requêtes suivies par le conseiller d'index, ainsi que des métadonnées importantes pour chacune d'elles, telles que les métriques suivantes :

• Nombre de fois où l'instance a exécuté chaque requête
• Temps total passé par l'instance à traiter ces requêtes
• ID de l'utilisateur de la base de données qui exécute ces requêtes

Effacer les requêtes suivies du conseiller d'index

Vous pouvez réinitialiser le comportement du conseiller d'index sur une instance en effaçant les requêtes qu'il suit. Pour ce faire, exécutez la fonction SQL suivante :

SELECT google_db_advisor_reset();

Cloud SQL pour PostgreSQL vide immédiatement la collection de requêtes suivies du conseiller d'index.

Étapes suivantes

• Utiliser les insights sur les requêtes