Résolution des problèmes de performance des requêtes pour les bases de données Aurora MySQL - Amazon Aurora
Comprendre le temps consacré aux requêtesRévision 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 affectent la manière dont les plans de requêtes sont évalués, d'optimisations commutables, d'indices d'optimiseur et d'index, ainsi que du 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. Le réglage des requêtes nécessite d'identifier les goulots d'étranglement et d'optimiser le chemin d'exécution.

  • Trouvez le plan d'exécution de la requête et vérifiez si celle-ci utilise les index appropriés. Vous pouvez optimiser votre requête en utilisant EXPLAIN et en consultant les détails de chaque plan.

  • Aurora MySQL version 3 (compatible avec MySQL 8.0 Community Edition) utilise une EXPLAIN ANALYZE instruction. L'EXPLAIN ANALYZEinstruction est un outil de profilage qui indique où MySQL consacre du temps à votre requête et pourquoi. 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. EXPLAIN ANALYZE 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'ANALYZEinstruction. L'optimiseur de requêtes peut parfois choisir de mauvais plans d'exécution en raison de statistiques obsolètes. Cela peut nuire aux performances d'une requête en raison d'estimations de cardinalité inexactes à la fois des tables et des index. La last_update colonne de la table innodb_table_stats indique la dernière mise à jour des statistiques de votre schéma, ce qui est un bon indicateur de « obsolescence ».

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

Comprendre le temps consacré aux requêtes

Les méthodes suivantes 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ù vous passez le plus de temps. Selon les états généraux des threads de la documentation MySQL, la lecture et le traitement des lignes d'une SELECT instruction constituent souvent l'état le plus long de la durée de vie d'une requête donnée. Vous pouvez utiliser cette EXPLAIN instruction pour comprendre comment MySQL exécute cette requête.

  2. Consultez le journal des requêtes lentes pour évaluer rows_examined et rows_sent 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 résultats suivants 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 saisir des informations périodiquement lorsque des requêtes sont exécutées ou configurer une cron tâche 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. Ceci est différent duinformation_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 on Aurora My SQL My SQL.

Suivi de l'optimiseur de requêtes

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

Exécutez un suivi de l'optimiseur 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";

Révision des paramètres de l'optimiseur de requêtes

La version 3 d'Aurora MySQL (compatible avec MySQL 8.0 Community Edition) comporte de nombreuses modifications liées à l'optimiseur par rapport à la version 2 d'Aurora MySQL (compatible avec MySQL 5.7 Community Edition). Si vous avez des valeurs personnalisées pour leoptimizer_switch, nous vous recommandons de vérifier les différences entre les valeurs par défaut et de définir optimizer_switch les valeurs 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 de la communauté de 20 pour le paramètre innodb_stats_persistent_sample_pages.

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

SELECT @@optimizer_switch\G;

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

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 les sections Optimisations commutables (MySQL 5.7) et Optimisations commutables (MySQL 8.0) dans la documentation MySQL.