Bonnes pratiques pour la gestion de plans de requêtes Aurora PostgreSQL

La gestion du plan de requête vous permet de contrôler comment et quand les plans d'exécution des requêtes changent. En tant qu'administrateur de base de données (DBA), les principaux objectifs de l'utilisation du QPM sont d'éviter les régressions en cas de modification de la base de données et de contrôler si l'optimiseur doit utiliser un nouveau plan. Dans ce qui suit, vous trouverez quelques bonnes pratiques recommandées pour l'utilisation de la gestion du plan de requête. Les approches proactives et réactives de la gestion des plans diffèrent quant à la manière et au moment où les nouveaux plans sont approuvés pour être utilisés.

Table des matières

• Gestion proactive des plans pour empêcher toute régression des performances
  • Assurer la stabilité du plan après une mise à niveau majeure de la version
• Gestion réactive des plans pour détecter et corriger toute régression des performances

Gestion proactive des plans pour empêcher toute régression des performances

Pour éviter toute régression des performances des plans, vous faites évoluer les bases de référence des plans en exécutant une procédure qui compare les performances des plans nouvellement découverts à celles de la base de référence existante des plans approuvés, puis approuve automatiquement l'ensemble de plans le plus rapide comme nouvelle base de référence. De cette façon, la base de référence des plans s'améliore au fil du temps, à mesure que des plans plus rapides sont découverts.

1. Dans un environnement de développement, identifiez les instructions SQL qui ont le plus d'impact sur les performances ou le débit du système. Capturez ensuite les plans pour ces instructions comme décrit dans les sections Capture manuelle de plans pour des instructions SQL spécifiques et Capture automatique de plans.

2. Exportez les plans capturés depuis l'environnement de développement et importez-les dans l'environnement de production. Pour plus d'informations, consultez Exportation et importation de plans.

3. En production, exécutez votre application et imposez l'utilisation des plans gérés approuvés. Pour plus d'informations, consultez Utilisation des plans gérés Aurora PostgreSQL. Tandis que l'application s'exécute, ajoutez également de nouveaux plans à mesure que l'optimiseur les découvre. Pour plus d'informations, consultez Capture automatique de plans.

4. Analysez les plans non approuvés et approuvez ceux qui affichent de bonnes performances. Pour plus d'informations, consultez Évaluation des performances des plans.

5. Tandis que votre application continue de s'exécuter, l'optimiseur commence à utiliser les nouveaux plans selon les besoins.

Assurer la stabilité du plan après une mise à niveau majeure de la version

Chaque version majeure de PostgreSQL comprend des améliorations et des modifications de l'optimiseur de requêtes destinées à améliorer les performances. Toutefois, les plans d'exécution de requêtes générés par l'optimiseur dans les versions antérieures peuvent entraîner des régressions de performances dans les nouvelles versions mises à niveau. Vous pouvez utiliser le gestionnaire de plan de requêtes pour résoudre ces problèmes de performances et garantir la stabilité du plan après une mise à niveau majeure de la version.

L'optimiseur utilise toujours un plan approuvé à coût minimal, même s'il existe plusieurs plans approuvés pour la même instruction. Après une mise à niveau, l'optimiseur peut découvrir de nouveaux plans, mais ils seront enregistrés en tant que plans non approuvés. Ces plans ne sont exécutés que s'ils sont approuvés en utilisant la gestion de plans réactive avec le paramètre unapproved_plan_execution_threshold. Vous pouvez optimiser la stabilité du plan en utilisant la gestion de plans proactive avec le paramètre evolve_plan_baselines. Elle compare les performances des nouveaux plans à celles des anciens, et approuve ou rejette les plans qui sont au moins 10 % plus rapides que le meilleur plan suivant.

Après la mise à niveau, vous pouvez utiliser la fonction evolve_plan_baselines pour comparer les performances du plan avant et après la mise à niveau en utilisant vos liaisons de paramètres de requête. Les étapes suivantes supposent que vous avez utilisé des plans de gestion approuvés dans votre environnement de production, comme indiqué dans Utilisation des plans gérés Aurora PostgreSQL.

1. Avant de procéder à la mise à niveau, lancez votre application avec le gestionnaire de plans de requêtes en cours d'exécution. Tandis que l'application s'exécute, ajoutez de nouveaux plans à mesure que l'optimiseur les découvre. Pour plus d'informations, consultez Capture automatique de plans.

2. Évaluez les performances de chaque plan. Pour plus d'informations, consultez Évaluation des performances des plans.

3. Après la mise à niveau, analysez à nouveau vos plans approuvés à l'aide de la fonction evolve_plan_baselines. Comparez les performances avant et après l'utilisation de vos liaisons de paramètres de requête. Si le nouveau plan est rapide, vous pouvez l'ajouter à vos plans approuvés. S'il est plus rapide qu'un autre plan pour les mêmes liaisons de paramètres, vous pouvez alors marquer le plan le plus lent comme rejeté.

   Pour plus d'informations, consultez Approbation de plans plus performants. Pour obtenir des informations de référence sur cette fonction, consultez apg_plan_mgmt.evolve_plan_baselines.

Pour plus d'informations, consultez Ensuring consistent performance after major version upgrades with Amazon Aurora PostgreSQL-Compatible Edition Query Plan Management.

Note

Lorsque vous effectuez une mise à niveau d'une version majeure à l'aide d'une réplication logique ou de AWS DMS, assurez-vous de répliquer le schéma apg_plan_mgmt pour vous assurer que les plans existants sont copiés dans l'instance mise à niveau. Pour plus d'informations sur la réplication logique, consultez Utilisation de la réplication logique pour effectuer une mise à niveau de version majeure pour Aurora PostgreSQL.

Gestion réactive des plans pour détecter et corriger toute régression des performances

En surveillant votre application pendant son exécution, vous pouvez détecter les plans qui entraînent des régressions de performances. Lorsque vous détectez des régressions, vous devez rejeter ou corriger manuellement les plans problématiques en suivant les étapes suivantes :

1. Tandis que votre application s'exécute, imposez l'utilisation de plans gérés et ajoutez automatiquement les nouveaux plans découverts comme non approuvés. Pour de plus amples informations, veuillez consulter Utilisation des plans gérés Aurora PostgreSQL et Capture automatique de plans.

2. Surveillez l'application en cours d'exécution afin d'identifier toute régression des performances.

3. Lorsque vous détectez une régression d'un plan, définissez le statut de ce plan sur rejected. La prochaine fois que l'optimiseur exécutera l'instruction SQL, il ignorera automatiquement le plan rejeté et utilisera un autre plan approuvé à la place. Pour plus d'informations, consultez Rejet ou désactivation de plans plus lents.

   Dans certains cas, vous préférerez peut-être corriger un plan inapproprié plutôt que de le rejeter, de le désactiver ou de le supprimer. Utilisez l'extension pg_hint_plan pour tenter d'améliorer un plan. Avec pg_hint_plan, vous utilisez des commentaires spéciaux pour indiquer à l'optimiseur de contourner la procédure normale de création d'un plan. Pour plus d'informations, consultez Correction de plans à l'aide de pg_hint_plan.