Comment résoudre les problèmes liés à une instruction SELECT qui s'exécute lentement dans mon cluster de bases de données Aurora compatible avec MySQL ?

Brève description

Votre instruction SELECT peut s'exécuter lentement sur votre cluster de bases de données Aurora compatible avec MySQL pour les motifs suivants :

• Vous utilisez excessivement les ressources de votre système de base de données.
• La base de données est verrouillée.
• L'instruction SELECT effectue des analyses complètes de tables volumineuses. Ou bien, la requête n’utilise pas les index nécessaires.
• Les transactions de longue durée augmentent la longueur de la liste historique (HLL) InnoDB.

Résolution

Remarque : si des erreurs surviennent lorsque vous exécutez des commandes de l'interface de la ligne de commande AWS (AWS CLI), consultez la section Résoudre des erreurs liées à l’AWS CLI. Vérifiez également que vous utilisez bien la version la plus récente de l'AWS CLI.

Utiliser CloudWatch Database Insights pour vérifier pourquoi une instruction SELECT est lente

Activez Performance Insights et utilisez Database Insights pour détecter les requêtes qui entraînent une charge de base de données élevée. Surveillez les dimensions telles que SQL, les utilisateurs et les attentes dans le graphique de charge de la base de données. Surveillez également la section Requêtes SQL lentes de l'onglet Télémétrie de base de données. En outre, vous pouvez analyser l'exécution des requêtes à partir des statistiques SQL de Performance Insights. Par exemple, si le nombre de lignes que vous avez examinées par appel est supérieur à la normale, le plan d'exécution est inefficace.

Utiliser des métriques pour surveiller les ressources de vos systèmes d'instance de base de données

Un processeur élevé, une mémoire insuffisante ou des charges de travail importantes dépassant les capacités de votre classe d'instance de base de données peuvent entraîner un ralentissement de l'exécution de votre instruction SELECT. Pour surveiller les ressources de votre instance de base de données, utilisez les outils suivants :

• Utilisez les métriques Amazon CloudWatch pour Amazon Aurora afin de surveiller l'utilisation de votre processeur.
• Utilisez la surveillance améliorée pour afficher les détails des métriques du système d'exploitation (OS).
• Utilisez l’onglet Données des processus du système d'exploitation dans la télémétrie de base de données pour vérifier si la charge de votre base de données dépasse la valeur maximale du processeur virtuel.

Les recherches du disque peuvent entraîner un ralentissement de l'exécution de l'instruction SELECT. Pour minimiser les E/S du disque, le moteur de base de données met en cache le bloc lu sur le disque. Lorsque la base de données a besoin des mêmes données, elle les extrait de la mémoire plutôt que du disque. Pour déterminer si vous envoyez une requête spécifique depuis le disque ou la mémoire, utilisez les métriques suivantes :

• Vérifiez la métrique ReadIOPS pour afficher le nombre d'opérations d'E/S sur disque. Il est recommandé de maintenir cette valeur aussi basse que possible.
• Vérifiez la métrique BufferCacheHitRatio pour afficher le pourcentage de requêtes traitées par le cache de la mémoire tampon. Il est recommandé de maintenir cette valeur aussi élevée que possible.
• Vérifiez la métrique FreeableMemory pour afficher la mémoire disponible de l'instance de base de données. Il est recommandé de maintenir cette valeur stable. Une mémoire disponible insuffisante peut entraîner une valeur de BufferCacheHitRatio faible et une valeur de ReadIOPS élevée.<br id=hardline_break/> Remarque : si la métrique BufferCacheHitRatio diminue et que l'instruction SELECT est lente, le moteur traite les requêtes des volumes sous-jacents.

Les recherches de stockage local du disque peuvent également entraîner un ralentissement de l'exécution de l'instruction SELECT. Aurora compatible avec MySQL utilise le stockage local pour les tables temporaires manuelles et internes. Pour plus d'informations, consultez la section Nouveau comportement des tables temporaires dans Aurora MySQL version 3. Pour surveiller et résoudre les problèmes liés au stockage local sur disque, consultez les métriques du système d'exploitation Rdstemp de Database Insights. Il est recommandé de maintenir ces valeurs aussi basses que possible.

La saturation du réseau peut entraîner un ralentissement de l'exécution des instructions SELECT. Aurora exécute des opérations d'E/S sur le volume du cluster via le réseau et envoie le résultat de la requête au client via le réseau. Pour surveiller et résoudre la saturation du réseau, vérifiez la métrique NetworkThroughput et StorageNetworkThroughput. Le débit réseau total doit être inférieur à la bande passante du réseau de votre instance de base de données.

Si l'utilisation des ressources dépasse les capacités de votre type d'instance de base de données en raison de votre charge de travail, mettez à niveau la classe d'instance de base de données.

Identifier les blocages et les verrous de blocage

Un blocage se produit lorsqu’au moins deux transactions ne peuvent pas continuer parce qu'elles se bloquent mutuellement. Pour identifier les blocages sur votre base de données, activez le paramètre innodb_print_all_deadlocks dans vos groupes de paramètres. Pour plus d’informations, consultez la page innodb_print_all_deadlocks sur le site Web de MySQL. Puis, surveillez le fichier mysql-error.log depuis la console Amazon RDS, l'interface de la ligne de commande AWS ou l'API.

(Facultatif) Pour identifier les blocages, connectez-vous à un compte administrateur MySQL, puis exécutez la commande suivante :

SHOW ENGINE INNODB STATUS\G;

Remarque : dans la sortie attendue de MySQL Workbench, consultez la section Dernier blocage détecté.

Même en l’absence de blocage, une transaction longue qui conserve les verrous peut bloquer les verrous. Pour identifier les verrous de blocage en cours, consultez la section Pourquoi une requête adressée à mon instance de base de données Amazon RDS for MySQL a-t-elle été bloquée alors qu'aucune autre session n'est active ?

Vérifier si votre requête utilise un index

Lorsqu'une requête ne comporte pas d'index ou qu'elle analyse l'intégralité des tables, elle s'exécute lentement. Les index permettent aux instructions SELECT de s'exécuter plus rapidement. Pour vérifier si votre requête utilise un index, utilisez l'instruction EXPLAIN. Pour plus d’informations, consultez la page Instruction GRANT sur le site Web de MySQL.

Dans la sortie EXPLAIN, vérifiez les noms des tables, la clé et le nombre de lignes que le moteur analyse pendant la requête. Si la sortie n'affiche pas aucune clé en cours d'utilisation, créez un index sur les colonnes utilisées dans la clause WHERE. Si la table utilise l'indexation requise, vérifiez si la table de statistiques est à jour. Utilisez la clause ANALYZE pour mettre à jour les statistiques. Pour plus d'informations, consultez la page Table INFORMATION_SCHEMA STATISTICS sur le site Web de MySQL.

Pour identifier les instructions SELECT lentes, utilisez slow_query_log. Pour enregistrer les requêtes lentes, activez la journalisation lente des requêtes pour votre cluster de base de données.

Vérifier la HLL

InnoDB utilise le contrôle de simultanéité multiversion (MVCC). Le MVCC conserve plusieurs copies du même enregistrement afin de garantir la cohérence de lecture. La LLH correspond au nombre total de journaux d'annulation qui contiennent des modifications dans la liste historique. Lorsqu'une LLH écrit ou lit des données, elle augmente jusqu'à ce que la transaction soit terminée ou annulée. D'autres transactions modifient les tables utilisées par la transaction de longue durée. Si votre charge de travail nécessite plusieurs transactions ouvertes ou de longue durée, il se peut que votre base de données ait une HLL élevée. De plus, si les threads de purge ne peuvent pas suivre les modifications de la base de données, il se peut que la HLL soit élevée. Une HLL élevée entraîne une utilisation accrue des ressources, des performances lentes et incohérentes de l'instruction SELECT et une augmentation du stockage. Dans les cas extrêmes, une HLL élevée peut provoquer une panne de base de données.

Pour surveiller votre HLL, utilisez la métrique RollbackSegmentHistoryListLength de l’instance en écriture. Vous pouvez également exécuter la commande suivante :

SHOW ENGINE INNODB STATUS;

Exemple de sortie :

------------ TRANSACTIONS ------------Trx id counter 26368570695  
Purge done for trx's n:o < 26168770192 undo n:o < 0 state: running but idle History list length 1839

Pour la compatibilité d'Aurora MySQL, la HLL se situe au niveau du cluster. Pour vérifier la HLL au niveau du cluster, connectez-vous à votre instance en écriture et exécutez l'instruction suivante :

SELECT server_id, IF(session_id = 'master_session_id', 'writer', 'reader') AS ROLE, replica_lag_in_msec, oldest_read_view_trx_id, oldest_read_view_lsn from mysql.ro_replica_status;

Remarque : vous pouvez utiliser l'instruction précédente pour identifier le retard du réplica entre les nœuds de lecture et d'écriture. Vérifiez le numéro de séquence de journal (LSN) le plus ancien utilisé par la base de données pour lire depuis le stockage et l'ID de transaction de vue en lecture le plus ancien de l'instance de base de données (Trx ID). Assurez-vous que l'une des instances contient une ancienne vue en lecture.

Pour vous connecter à une instance contenant une ancienne vue en lecture, exécutez l'instruction suivante :

SELECT a.trx_id, a.trx_state, a.trx_started,  TIMESTAMPDIFF(SECOND,a.trx_started, now()) as "Seconds Transaction Has Been Open",    
a.trx_rows_modified, b.USER, b.host, b.db, b.command, b.time, b.state    
from information_schema.innodb_trx a, information_schema.processlist b    
where a.trx_mysql_thread_id=b.id    
order by trx_started;

Remarque : utilisez l'instruction précédente pour identifier la session ou la transaction présentant le trx_id le plus ancien. Pour débloquer l'opération de purge, déterminez si vous pouvez mettre fin à la session.

Pour résoudre une HLL élevée, procédez comme suit :

• Si les écritures DML entraînent une augmentation de la HLL, rétablissez la transaction pour annuler la requête. Ce processus est long en raison du nombre de mises à jour que vous devez restaurer.
• Si une opération READ entraîne une augmentation de la HLL, utilisez mysql.rds_kill_query pour annuler la requête.<br id=hardline_break/> Remarque : contactez l'administrateur de votre base de données pour vérifier si vous pouvez annuler une requête.

Pour éviter une HLL élevée, il est recommandé de valider les données par lots de plus petite taille. En outre, ne redémarrez pas le cluster ou l'instance de base de données. Purgez le HLL lorsqu'il peut accéder aux données de mémoire du pool de mémoires tampons. Si vous redémarrez la base de données, le cache de pages durable est peut-être perdu. Si le cache de pages durable est perdu, les pages de données du volume de cluster doivent être lues pour purger la HLL. Ce processus est plus lent qu'une purge de la mémoire et entraîne des coûts de facturation d'E/S supplémentaires.

Informations connexes

Surveiller les journaux Amazon Aurora MySQL, Amazon RDS for MySQL et MariaDB avec Amazon CloudWatch

Réglage d'Aurora MySQL