Présentation de la gestion du plan de SQL requêtes Aurora Postgre - Amazon Aurora
SQLDéclarations soutenuesLimitesTerminologieVersions disponiblesActiver la gestion de plans de requêtesMise à niveau de la gestion de plans de requêtesDésactiver la gestion du plan de SQL requêtes Aurora Postgre

Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra.

Présentation de la gestion du plan de SQL requêtes Aurora Postgre

La gestion du plan de SQL requêtes Aurora Postgre est conçue pour garantir la stabilité du plan, quelles que soient les modifications apportées à la base de données susceptibles d'entraîner une régression du plan de requêtes. La régression du plan de requête se produit lorsque l'optimiseur choisit un plan sous-optimal pour une SQL instruction donnée après des modifications du système ou de la base de données. Les modifications apportées aux statistiques, aux contraintes, aux paramètres d'environnement, aux liaisons de paramètres de requête et aux mises à niveau du moteur de SQL base de données Postgre peuvent toutes entraîner une régression du plan.

Avec la gestion des plans de SQL requêtes Aurora Postgre, vous pouvez contrôler comment et quand les plans d'exécution des requêtes changent. Les avantages de la gestion des plans de SQL requêtes Aurora Postgre sont les suivants.

  • Optimisez la stabilité du plan en forçant l'optimiseur à opérer sa sélection parmi une poignée de plans de qualité connus.

  • Optimisez les plans de manière centralisée, puis distribuez les meilleurs à l'échelle mondiale.

  • Identifiez les index non utilisés et évaluez l'impact de la création ou de la suppression d'un index.

  • Détectez automatiquement un nouveau plan à coût minimal découvert par l'optimiseur.

  • Essayez les nouvelles fonctionnalités de l'optimiseur avec moins de risques, car vous pouvez choisir de n'approuver que les changements de plans qui optimisent les performances.

Vous pouvez utiliser les outils fournis par la gestion des plans de requêtes de manière proactive afin de définir le meilleur plan pour certaines requêtes. Vous pouvez également utiliser la gestion des plans de requêtes pour réagir à l'évolution des circonstances et éviter les régressions du plan. Pour de plus amples informations, veuillez consulter Bonnes pratiques pour la gestion de plans de requêtes Aurora PostgreSQL.

SQLDéclarations soutenues

La gestion du plan de requêtes prend en charge les types d'SQLinstructions suivants.

  • N'importe quelle DELETE déclaration SELECT INSERTUPDATE, quelle que soit sa complexité.

  • Instructions préparées. Pour plus d'informations, consultez PREPAREla SQL documentation Postgre.

  • Instructions dynamiques, y compris celles qui s'exécutent en mode immédiat. Pour plus d'informations, consultez Dynamic SQL et EXECUTEIMMEDIATEla SQL documentation de Postgre.

  • SQLCommandes et instructions intégrées. Pour plus d'informations, consultez la section SQLCommandes intégrées dans la SQL documentation Postgre.

  • Instructions à l'intérieur de fonctions nommées. Pour plus d'informations, consultez CREATEFUNCTIONla SQL documentation Postgre.

  • Déclarations contenant des tables temporaires.

  • Les instructions à l'intérieur des procédures et des blocs DO.

Vous pouvez utiliser la gestion des plans de requêtes avec EXPLAIN en mode manuel pour capturer un plan sans l'exécuter réellement. Pour de plus amples informations, veuillez consulter Analyse du plan choisi par l'optimiseur. Pour en savoir plus sur les modes de gestion des plans de requêtes (manuel, automatique), consultez Capture des plans d'exécution d'Aurora PostgreSQL.

La gestion des plans de SQL requêtes Aurora Postgre prend en charge toutes les fonctionnalités du SQL langage Postgre, notamment les tables partitionnées, l'héritage, la sécurité au niveau des lignes et les expressions de table communes récursives (). CTEs Pour en savoir plus sur ces fonctionnalités du SQL langage Postgre, consultez Partitionnement des tables, politiques de sécurité des lignes et WITHrequêtes (expressions de table communes) et d'autres rubriques de la documentation SQL Postgre.

Pour plus d'informations sur les différentes versions de la fonctionnalité de gestion du plan de SQL requêtes Aurora Postgre, voir les versions de l'extension Aurora Postgre SQL apg_plan_mgmt dans les notes de publication d'Aurora Postgre. SQL

Limites de la gestion des plans de requêtes

La version actuelle de la gestion des plans de SQL requêtes Aurora Postgre présente les limites suivantes.

  • Les plans ne sont pas capturés pour les déclarations qui font référence aux relations du système : les déclarations qui font référence aux relations du système, telles que pg_class, ne sont pas capturées. Ceci est conçu pour empêcher la capture d'un grand nombre de plans générés par le système et utilisés en interne. Cela s'applique également aux tables système à l'intérieur des vues.

  • Une classe d'instance de base de données plus importante peut être nécessaire pour votre cluster de SQL base de données Aurora Postgre. En fonction de la charge de travail, la gestion du plan de requêtes peut nécessiter une classe d'instance de base de données contenant plus de 2vCPUs. Le nombre de max_worker_processes est limité par la taille de la classe d'instances de base de données. Le nombre max_worker_processes fourni par une classe d'CPUinstance de base de données 2 V (db.t3.medium, par exemple) peut ne pas être suffisant pour une charge de travail donnée. Nous vous recommandons de choisir une classe d'instance de base de données avec plus de 2 vCPUs pour votre cluster de SQL base de données Aurora Postgre si vous utilisez la gestion du plan de requêtes.

    Lorsque la classe d'instances de base de données ne peut pas supporter la charge de travail, la gestion du plan de requête affiche un message d'erreur comme suit. 

    WARNING: could not register plan insert background process
HINT: You may need to increase max_worker_processes.

    Dans ce cas, vous devez augmenter la taille de votre cluster de SQL base de données Aurora Postgre pour obtenir une taille de classe d'instance de base de données avec plus de mémoire. Pour de plus amples informations, veuillez consulter Moteurs de base de données pris en charge pour les classes d'instance de base de données.

  • Les plans déjà stockés dans les sessions ne sont pas affectés : la gestion des plans de requêtes permet d'influencer les plans de requêtes sans modifier le code de l'application. Toutefois, lorsqu'un plan générique est déjà stocké dans une session existante et que vous souhaitez modifier son plan de requêtes, vous devez d'abord définir plan_cache_mode sur force_custom_plan dans le groupe de paramètres du cluster de bases de données.

  • queryid dans apg_plan_mgmt.dba_plans et pg_stat_statements peuvent diverger lorsque :

    • Les objets sont supprimés et recréés après leur stockage dans apg_plan_mgmt.dba_plans.

    • La table apg_plan_mgmt.plans est importée depuis un autre cluster.

Pour plus d'informations sur les différentes versions de la fonctionnalité de gestion du plan de SQL requêtes Aurora Postgre, voir les versions de l'extension Aurora Postgre SQL apg_plan_mgmt dans les notes de publication d'Aurora Postgre. SQL

Terminologie de la gestion des plans de requête

Les termes suivants sont utilisés tout au long de cette rubrique.

instruction gérée

Une SQL instruction capturée par l'optimiseur dans le cadre de la gestion du plan de requêtes. Une instruction gérée possède un ou plusieurs plans d'exécution de requêtes stockés dans la vue apg_plan_mgmt.dba_plans.

référence du plan

Ensemble de plans approuvés pour une instruction gérée donnée. C'est-à-dire tous les plans de l'instruction gérée dont la colonne status de la vue dba_plan indique « Approuvé ».

historique du plan

Ensemble de plans capturés pour une instruction gérée donnée. L'historique du plan contient tous les plans capturés pour l'instruction, quel que soit leur statut.

régression du plan de requêtes

Cas où l'optimiseur choisit un plan moins optimal qu'avant une modification donnée de l'environnement de base de données, telle qu'une nouvelle SQL version de Postgre ou des modifications des statistiques.

Versions de gestion du plan SQL de requêtes Aurora Postgre

La gestion du plan de requêtes est prise en charge par toutes les SQL versions d'Aurora Postgre actuellement disponibles. Pour plus d'informations, consultez la liste des SQLmises à jour d'Amazon Aurora Postgre dans les notes de publication d'Aurora SQL Postgre.

La fonctionnalité de gestion du plan de requêtes est ajoutée à votre cluster de SQL base de données Aurora Postgre lorsque vous installez l'apg_plan_mgmtextension. Différentes versions d'Aurora Postgre SQL prennent en charge différentes versions de l'apg_plan_mgmtextension. Nous vous recommandons de mettre à niveau l'extension de gestion du plan de requêtes vers la dernière version correspondant à votre version d'Aurora PostgreSQL.

Note

Pour les notes de publication relatives à chaque version d'apg_plan_mgmtextension, voir les versions de l'extension Aurora Postgre SQL apg_plan_mgmt dans les notes de publication d'Aurora Postgre. SQL

Vous pouvez identifier la version exécutée sur votre cluster en vous connectant à une instance à l'aide de psql et de la métacommande \dx pour répertorier les extensions, comme indiqué ci-dessous.

labdb=> \dx
                       List of installed extensions
     Name      | Version |    Schema     |                            Description
---------------+---------+---------------+-------------------------------------------------------------------
 apg_plan_mgmt | 1.0     | apg_plan_mgmt | Amazon Aurora with PostgreSQL compatibility Query Plan Management
 plpgsql       | 1.0     | pg_catalog    | PL/pgSQL procedural language
(2 rows)

La sortie indique que ce cluster utilise la version 1.0 de l'extension. Seules certaines apg_plan_mgmt versions sont disponibles pour une SQL version donnée d'Aurora Postgre. Dans certains cas, vous devrez peut-être mettre à niveau le cluster de SQL base de données Aurora Postgre vers une nouvelle version mineure ou appliquer un correctif afin de pouvoir passer à la version la plus récente de la gestion des plans de requêtes. La apg_plan_mgmt version 1.0 affichée dans la sortie provient d'un cluster de base de données Aurora Postgre SQL version 10.17, dont aucune version plus récente n'est disponible. apg_plan_mgmt Dans ce cas, le cluster de SQL base de données Aurora Postgre doit être mis à niveau vers une version plus récente de SQL Postgre.

Pour plus d'informations sur la mise à niveau de votre cluster de SQL base de données Aurora Postgre vers une nouvelle version de PostgreSQL, consultez. Mises à jour d'Amazon Aurora Postgre SQL

Pour savoir comment mettre à niveau l'extension apg_plan_mgmt, consultez Mise à niveau de la gestion du plan de SQL requêtes Aurora Postgre.

Activer la gestion du plan de SQL requêtes Aurora Postgre

La configuration de la gestion du plan de requêtes pour votre cluster de SQL base de données Aurora Postgre implique l'installation d'une extension et la modification de plusieurs paramètres de paramètres de cluster de base de données. Vous avez besoin d'rds_superuserautorisations pour installer l'apg_plan_mgmtextension et activer la fonctionnalité pour le cluster de SQL base de données Aurora Postgre.

L'installation de l'extension crée un nouveau rôle, apg_plan_mgmt. Ce rôle permet aux utilisateurs de la base de données de consulter, de gérer et de gérer des plans de requêtes. En tant qu'administrateur doté de privilèges rds_superuser, veillez à attribuer le rôle apg_plan_mgmt aux utilisateurs de la base de données, selon leurs besoins.

Seuls les utilisateurs possédant le rôle rds_superuser peuvent effectuer la procédure suivante. Le rôle rds_superuser est nécessaire pour créer l'extension apg_plan_mgmt et son rôle apg_plan_mgmt. Les utilisateurs doivent recevoir le rôle apg_plan_mgmt pour administrer l'extension apg_plan_mgmt.

Pour activer la gestion des plans de requêtes pour votre cluster de SQL base de données Aurora Postgre

Les étapes suivantes activent la gestion du plan de requêtes pour toutes les SQL instructions soumises au cluster de SQL base de données Aurora Postgre. C'est ce que l'on appelle le mode automatique. Pour en savoir plus sur les différences entre les modes, consultez Capture des plans d'exécution d'Aurora PostgreSQL.

  1. Ouvrez la RDS console Amazon à l'adresse https://console.aws.amazon.com/rds/.

  2. Créez un groupe de paramètres de cluster de base de données personnalisé pour votre cluster de SQL base de données Aurora Postgre. Vous devez modifier certains paramètres pour activer la gestion des plans de requêtes et définir son comportement. Pour de plus amples informations, veuillez consulter Création d'un groupe de paramètres de base de données dans Amazon Aurora.

  3. Ouvrez le groupe de paramètres de cluster de bases de données personnalisé et définissez le paramètre rds.enable_plan_management sur 1, comme illustré dans l'image suivante.

    Image du groupe de paramètres du cluster de base de données.

    Pour de plus amples informations, veuillez consulter Modification des paramètres d'un groupe de paramètres de cluster de base de données dans Amazon Aurora.

  4. Créez un groupe de paramètres de base de données personnalisé que vous pouvez utiliser pour définir les paramètres des plans de requêtes au niveau de l'instance. Pour de plus amples informations, veuillez consulter Création d'un groupe de paramètres de cluster de base de données dans Amazon Aurora.

  5. Modifiez l'instance d'écriture du cluster de SQL base de données Aurora Postgre pour utiliser le groupe de paramètres de base de données personnalisé. Pour de plus amples informations, veuillez consulter Modification d'une instance de base de données dans un cluster de bases de données.

  6. Modifiez le cluster de SQL base de données Aurora Postgre pour utiliser le groupe de paramètres de cluster de base de données personnalisé. Pour de plus amples informations, veuillez consulter Modification du cluster de base de données à l'aide de la consoleCLI, et API.

  7. Réinitialisez votre instance de base de données pour activer les paramètres des groupes de paramètres personnalisés.

  8. Connectez-vous au point de terminaison de l'instance de base de données de votre SQL cluster de base de données Aurora Postgre à l'aide de psql oupgAdmin. L'exemple suivant utilise le compte postgres par défaut pour le rôle rds_superuser.

    psql --host=cluster-instance-1.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=my-db

  9. Créez l'extension apg_plan_mgmt pour votre instance de base de données, comme indiqué ci-dessous.

    labdb=> CREATE EXTENSION apg_plan_mgmt;
CREATE EXTENSION
    Astuce

    Installez l'extension apg_plan_mgmt dans la base de données des modèles pour votre application. La base de données de modèles par défaut est nommée template1. Pour en savoir plus, consultez la section Bases de données modèles dans la SQL documentation Postgre.

  10. Remplacez le paramètre apg_plan_mgmt.capture_plan_baselines par automatic. Ce paramètre oblige l'optimiseur à générer des plans pour chaque SQL instruction planifiée ou exécutée deux fois ou plus.

    Note

    La gestion du plan de requêtes dispose également d'un mode manuel que vous pouvez utiliser pour des SQL instructions spécifiques. Pour en savoir plus, consultez Capture des plans d'exécution d'Aurora PostgreSQL.

  11. Remplacez la valeur du paramètre apg_plan_mgmt.use_plan_baselines par « on ». Ce paramètre force l'optimiseur à choisir un plan pour l'instruction à partir de sa référence de plan. Pour en savoir plus, consultez Utilisation des plans gérés Aurora PostgreSQL.

    Note

    Vous pouvez modifier la valeur de l'un ou l'autre de ces paramètres dynamiques pour la session sans avoir à redémarrer l'instance.

Lorsque la configuration de la gestion de votre plan de requêtes est terminée, veillez à attribuer le rôle apg_plan_mgmt à tous les utilisateurs de bases de données qui ont besoin de consulter, de gérer ou de gérer des plans de requêtes.

Mise à niveau de la gestion du plan de SQL requêtes Aurora Postgre

Nous vous recommandons de mettre à niveau l'extension de gestion du plan de requêtes vers la dernière version correspondant à votre version d'Aurora PostgreSQL.

  1. Connectez-vous à l'instance d'écriture de votre cluster de SQL base de données Aurora Postgre en tant qu'utilisateur disposant de rds_superuser privilèges. Si vous avez conservé le nom par défaut lors de la configuration de votre instance, vous vous connectez comme postgres suit. Cet exemple montre comment l'utiliserpsql, mais vous pouvez également l'utiliser pgAdmin si vous le souhaitez.

    psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password

  2. Exécutez la requête suivante pour mettre à niveau l'extension.

    ALTER EXTENSION apg_plan_mgmt UPDATE TO '2.1';

  3. Utilisez la fonction apg_plan_mgmt.validate_plans pour mettre à jour les hachages de tous les plans. L'optimiseur valide tous les plans approuvés, non approuvés et rejetés afin de s'assurer qu'ils sont toujours viables pour la nouvelle version de l'extension. 

    SELECT apg_plan_mgmt.validate_plans('update_plan_hash');

    Pour en savoir plus sur cette fonction, consultez Validation des plans.

  4. Utilisez cette fonction apg_plan_mgmt.reload pour actualiser tous les plans de la mémoire partagée avec les plans validés depuis la vue dba_plans. 

    SELECT apg_plan_mgmt.reload();

Pour en savoir plus sur toutes les fonctions disponibles pour la gestion des plans de requêtes, consultez Référence fonctionnelle pour la gestion du plan de SQL requêtes Aurora Postgre.

Désactiver la gestion du plan de SQL requêtes Aurora Postgre

Vous pouvez désactiver la gestion des plans de requêtes à tout moment en désactivant apg_plan_mgmt.use_plan_baselines et apg_plan_mgmt.capture_plan_baselines. 

labdb=> SET apg_plan_mgmt.use_plan_baselines = off;

labdb=> SET apg_plan_mgmt.capture_plan_baselines = off;