Résolution des problèmes de performance des requêtes pour les bases de données Aurora MySQL - Amazon Aurora
Comprendre le temps passé par les requêtesExamen des paramètres de l’optimiseur de requêtes

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.

Résolution des problèmes de performance des requêtes pour les bases de données Aurora MySQL

MySQL permet de contrôler l’optimiseur de requêtes par le biais de variables système qui influent sur le mode d’évaluation des plans de requêtes, les optimisations remplaçables, les indices d’optimiseur et d’index, ainsi que le modèle de coût de l’optimiseur. Ces points de données peuvent être utiles non seulement pour comparer différents environnements MySQL, mais également pour comparer les plans d’exécution de requêtes précédents avec les plans d’exécution actuels, et pour comprendre l’exécution globale d’une requête MySQL à tout moment.

Les performances des requêtes dépendent de nombreux facteurs, notamment le plan d’exécution, le schéma et la taille de la table, les statistiques, les ressources, les index et la configuration des paramètres. L’ajustement des requêtes nécessite d’identifier les goulots d’étranglement et d’optimiser le chemin d’exécution.

  • Identifiez le plan d’exécution de la requête et vérifiez si cette dernière utilise les index appropriés. Vous pouvez optimiser votre requête en utilisant EXPLAIN et en examinant les détails de chaque plan.

  • Aurora MySQL version 3 (compatible avec MySQL 8.0 Community Edition) utilise une instruction EXPLAIN ANALYZE. L’instruction EXPLAIN ANALYZE est un outil de profilage qui indique où MySQL consacre du temps à votre requête, et pourquoi. Avec EXPLAIN ANALYZE, Aurora MySQL planifie, prépare et exécute la requête tout en comptant les lignes et en mesurant le temps passé à différents moments du plan d’exécution. Lorsque la requête est terminée, EXPLAIN ANALYZE imprime le plan et ses mesures au lieu du résultat de la requête.

  • Mettez à jour les statistiques de votre schéma à l’aide de l’instruction ANALYZE. L’optimiseur de requêtes peut parfois choisir des plans d’exécution inappropriés en raison de statistiques obsolètes. Cela peut nuire aux performances d’une requête en raison d’estimations de cardinalité inexactes à la fois pour les tables et les index. La colonne last_update de la table innodb_table_stats indique la dernière fois que les statistiques de votre schéma ont été mises à jour, ce qui est un bon indicateur d’« obsolescence ».

  • D’autres problèmes peuvent survenir, tels que le biais de distribution des données, lesquels ne seront pas pris en compte pour la cardinalité des tables. Pour plus d’informations, consultez Estimation de la complexité d’ANALYZE TABLE pour les tables InnoDB et Statistiques d’histogramme dans MySQL dans la documentation MySQL.

Comprendre le temps passé par les requêtes

Les éléments suivants permettent de déterminer le temps passé par les requêtes :

Profilage

Par défaut, le profilage est désactivé. Activez le profilage, puis exécutez la requête lente et vérifiez son profil.

SET profiling = 1;
Run your query.
SHOW PROFILE;

  1. Identifiez l’étape où la requête passe le plus de temps. Selon États généraux des threads dans la documentation MySQL, la lecture et le traitement des lignes d’une instruction SELECT est souvent l’état le plus long au cours de la durée de vie d’une requête donnée. Vous pouvez utiliser cette instruction EXPLAIN pour comprendre comment MySQL exécute cette requête.

  2. Consultez le journal des requêtes lentes pour évaluer rows_examined et rows_sent afin de vous assurer que la charge de travail est similaire dans chaque environnement. Pour plus d’informations, consultez Journalisation pour les bases de données Aurora MySQL.

  3. Exécutez la commande suivante pour les tables faisant partie de la requête identifiée :

    SHOW TABLE STATUS\G;

  4. Capturez les sorties suivantes avant et après l’exécution de la requête sur chaque environnement :

    SHOW GLOBAL STATUS;

  5. Exécutez les commandes suivantes sur chaque environnement pour voir si une autre requête/session influence les performances de cet exemple de requête.

    SHOW FULL PROCESSLIST;

SHOW ENGINE INNODB STATUS\G;

    Parfois, lorsque les ressources du serveur sont occupées, cela a un impact sur toutes les autres opérations du serveur, y compris les requêtes. Vous pouvez également capturer des informations périodiquement lorsque des requêtes sont exécutées, ou configurer une tâche cron pour capturer des informations à des intervalles utiles.

Schéma de performance

Le schéma de performance fournit des informations utiles sur les performances d’exécution du serveur, tout en ayant un impact minimal sur ces performances. Il diffère de l’information_schema, qui fournit des informations de schéma sur l’instance de base de données. Pour plus d’informations, consultez Présentation du schéma de performance pour Performance Insights sur Aurora MySQL.

Traçabilité de l’optimiseur de requête

Pour comprendre pourquoi un plan de requête particulier a été choisi pour être exécuté, vous pouvez configurer optimizer_trace pour accéder à l’optimiseur de requêtes MySQL.

Exécutez une opération optimizer_trace pour afficher des informations détaillées sur tous les chemins disponibles pour l’optimiseur et sur son choix.

SET SESSION OPTIMIZER_TRACE="enabled=on"; 
SET optimizer_trace_offset=-5, optimizer_trace_limit=5;

-- Run your query.
SELECT * FROM table WHERE x = 1 AND y = 'A';

-- After the query completes:
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET SESSION OPTIMIZER_TRACE="enabled=off";

Examen des paramètres de l’optimiseur de requêtes

Aurora MySQL version 3 (compatible avec MySQL 8.0 Community Edition) inclut de nombreuses modifications liées à l’optimiseur par rapport à Aurora MySQL version 2 (compatible avec MySQL 5.7 Community Edition). Si vous avez des valeurs personnalisées pour optimizer_switch, nous vous recommandons de vérifier les différences entre les valeurs par défaut et de définir les valeurs optimizer_switch les mieux adaptées à votre charge de travail. Nous vous recommandons également de tester les options disponibles pour Aurora MySQL version 3 afin d’examiner les performances de vos requêtes.

Note

Aurora MySQL version 3 utilise la valeur par défaut 20 pour le paramètre innodb_stats_persistent_sample_pages.

Vous pouvez utiliser la commande suivante pour afficher les valeurs optimizer_switch :

SELECT @@optimizer_switch\G;

Le tableau suivant affiche les valeurs optimizer_switch par défaut pour Aurora MySQL versions 2 et 3.

Paramètre Aurora MySQL version 2 Aurora MySQL version 3
batched_key_access off off
block_nested_loop on on
condition_fanout_filter on on
derived_condition_pushdown – on
derived_merge on on
duplicateweedout on on
engine_condition_pushdown on on
firstmatch on on
hash_join off on
hash_join_cost_based on –
hypergraph_optimizer – off
index_condition_pushdown on on
index_merge on on
index_merge_intersection on on
index_merge_sort_union on on
index_merge_union on on
loosescan on on
materialization on on
mrr on on
mrr_cost_based on on
prefer_ordering_index on on
semijoin on on
skip_scan – on
subquery_materialization_cost_based on on
subquery_to_derived – off
use_index_extensions on on
use_invisible_indexes – off

Pour plus d’informations, consultez Optimisations remplaçables (MySQL 5.7) et Optimisations remplaçables (MySQL 8.0) dans la documentation MySQL.