Présentation de la gestion des plans de requêtes d'Aurora PostgreSQL - Amazon Aurora
Instructions SQL prises en chargeLimitesTerminologieVersions disponiblesActiver la gestion de plans de requêtesMise à niveau de la gestion de plans de requêtesDésactivation de la gestion des plans de requêtes Aurora PostgreSQL

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 des plans de requêtes d'Aurora PostgreSQL

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

Avec la gestion des plans de requêtes Aurora PostgreSQL, vous pouvez contrôler quand et comment les plans d'exécution de requêtes changent. Les avantages de la gestion de plans de requêtes Aurora PostgreSQL incluent ce qui suit.

  • 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.

Instructions SQL prises en charge

La gestion des plans de requêtes prend en charge les types d'instructions SQL suivants.

  • Instruction SELECT, INSERT, UPDATE ou DELETE, quelle que soit la complexité.

  • Instructions préparées. Pour en savoir plus, consultez PREPARE dans la documentation PostgreSQL.

  • Instructions dynamiques, y compris celles qui s'exécutent en mode immédiat. Pour plus d'informations, consultez Dynamic SQL et EXECUTE IMMEDIATE dans la documentation PostgreSQL.

  • Commandes et instructions SQL intégrées. Pour en savoir plus, consultez Commandes SQL intégrées dans la documentation PostgreSQL.

  • Instructions à l'intérieur de fonctions nommées. Pour plus d'informations, consultez CREATE FUNCTION dans la documentation PostgreSQL.

  • 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 requêtes Aurora PostgreSQL prend en charge toutes les fonctionnalités du langage PostgreSQL, 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 langage PostgreSQL, consultez Partitionnement de tables, Politiques de sécurité des lignes et Requêtes WITH (expressions de table communes) et d'autres rubriques de la documentation de PostgreSQL.

Pour plus d'informations sur les différentes versions de la fonction de gestion du plan de requête d'Aurora PostgreSQL, consultez Aurora PostgreSQL apg_plan_mgmt extension versions (versions de l'extension apg_plan_mgmt d'Aurora PostgreSQL) dans les notes de mise à jour d'Aurora PostgreSQL.

Limites de la gestion des plans de requêtes

La version actuelle de la gestion du plan de requête d'Aurora PostgreSQL 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 base de données Aurora PostgreSQL : 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 de plus de 2 v. CPUs Le nombre de max_worker_processes est limité par la taille de classe de l'instance de base de données. Le nombre de max_worker_processes fournis par une classe d'instances de base de données à 2 vCPU (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 v CPUs pour votre cluster de base de données Aurora PostgreSQL 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 bases de données Aurora PostgreSQL à 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 fonction de gestion du plan de requête d'Aurora PostgreSQL, consultez Aurora PostgreSQL apg_plan_mgmt extension versions (versions de l'extension apg_plan_mgmt d'Aurora PostgreSQL) dans les notes de mise à jour d'Aurora PostgreSQL.

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 instruction SQL capturée par l'optimiseur dans le cadre de la gestion des plans 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

Le 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 version de PostgreSQL ou des modifications des statistiques.

Versions de la gestion de plans de requêtes Aurora PostgreSQL

La gestion des plans de requêtes est prise en charge par toutes les versions actuellement disponibles d'Aurora PostgreSQL. Pour plus d'informations, consultez la liste des Mises à jour d'Amazon Aurora PostgreSQL dans les Notes de mise à jour d'Aurora PostgreSQL.

La fonctionnalité de gestion des plans de requêtes est ajoutée à votre cluster de bases de données Aurora PostgreSQL lorsque vous installez l'extension apg_plan_mgmt. Différentes versions d'Aurora PostgreSQL prennent en charge différentes versions de l'extension apg_plan_mgmt. Nous vous recommandons de mettre à niveau l'extension de gestion des plans de requêtes vers la dernière version de votre version d'Aurora PostgreSQL.

Note

Pour les notes de mise à jour relatives à chaque version d'extension apg_plan_mgmt, consultez Versions d'extension apg_plan_mgmt d'Aurora PostgreSQL dans les Notes de mise à jour d'Aurora PostgreSQL.

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 versions apg_plan_mgmt sont disponibles pour une version d'Aurora PostgreSQL donnée. Dans certains cas, vous devrez peut-être mettre à niveau le cluster de bases de données Aurora PostgreSQL vers une nouvelle version mineure ou appliquer un correctif afin de pouvoir effectuer la mise à niveau vers la version la plus récente de la gestion des plans de requêtes. La version 1.0 d'apg_plan_mgmt affichée dans la sortie provient d'un cluster de bases de données Aurora PostgreSQL version 10.17, pour lequel aucune version plus récente d'apg_plan_mgmt n' est disponible. Dans ce cas, le cluster de bases de données Aurora PostgreSQL doit être mis à niveau vers une version plus récente de PostgreSQL.

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

Pour savoir comment mettre à niveau l'extension apg_plan_mgmt, consultez Mise à niveau de la gestion des plans de requête d'Aurora PostgreSQL.

Activation de la gestion de plans de requêtes Aurora PostgreSQL

La configuration de la gestion des plans de requêtes pour votre cluster de bases de données Aurora PostgreSQL implique l'installation d'une extension et la modification de plusieurs paramètres de cluster de bases de données. Vous devez disposer d'autorisations rds_superuser pour installer l'extension apg_plan_mgmt et activer la fonction pour le cluster de bases de données Aurora PostgreSQL.

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.

Activer la gestion des plans de requêtes pour votre cluster de bases de données Aurora PostgreSQL

Les étapes suivantes activent la gestion des plans de requêtes pour toutes les instructions SQL qui sont soumises au cluster de bases de données Aurora PostgreSQL. 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 console Amazon RDS à l'adresse https://console.aws.amazon.com/rds/.

  2. Créez un groupe de paramètres de cluster de bases de données personnalisé pour le cluster de bases de données Aurora PostgreSQL. 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 bases de données Aurora PostgreSQL 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 bases de données Aurora PostgreSQL pour utiliser le groupe de paramètres du cluster de bases de données personnalisé. Pour de plus amples informations, veuillez consulter Modification du cluster de bases de données à partir de la console, de l'CLI (CLI) et de l'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 j'instance de base de données du cluster de bases de données Aurora PostgreSQL à l'aide de psql ou pgAdmin. 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 documentation PostgreSQL.

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

    Note

    La gestion des plans de requêtes dispose également d'un mode manuel que vous pouvez utiliser pour des instructions SQL spécifiques. Pour en savoir plus, veuillez consulter la section 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, veuillez consulter la section 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 des plans de requête d'Aurora PostgreSQL

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

  1. Connectez-vous à l'instance d'écriture de votre cluster de bases de données Aurora PostgreSQL en tant qu'utilisateur avec des privilèges rds_superuser. Si vous avez conservé le nom par défaut lors de la configuration de votre instance, vous vous connectez en tant que postgres. Cet exemple montre comment utiliser psql, mais vous pouvez également utiliser pgAdmin si vous préférez.

    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 de la fonction pour la gestion du plan de requête Aurora PostgreSQL.

Désactivation de la gestion des plans de requêtes Aurora PostgreSQL

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;