Pourquoi ma requête SELECT s'exécute-t-elle lentement sur mon cluster de bases de données Amazon Aurora MySQL ?

Date de la dernière mise à jour : 10/12/2020 

J' ai un cluster de bases de données Amazon Aurora pour MySQL et je voudrais utiliser la requête SELECT pour sélectionner des données de ma base de données. Lorsque j'exécute la requête SELECT sur mon cluster de bases de données, la requête s'exécute lentement. Comment puis-je identifier la cause des requêtes SELECT lentes et les corriger ?

Brève description

Il existe un certain nombre de raisons pour lesquelles les requêtes SELECT peuvent s'exécuter lentement sur votre cluster de bases de données Amazon Aurora pour MySQL.

  • Vos ressources système Amazon Relational Database Service (Amazon RDS) sont surutilisées. Cela peut se produire en raison d'un CPU élevé, d'une mémoire faible ou d'une charge de travail qui dépasse ce que votre type d'instance de base de données peut gérer.
  • La base de données se verrouille et les événements d'attente qui en résultent provoquent des performances médiocres pour les requêtes SELECT .
  • La requête SELECT effectue des analyses de table complète sur des tables volumineuses, ou la requête ne possède pas les index nécessaires.
  • La longueur de votre liste d'historique InnoDB (HLL) a considérablement augmenté en raison des transactions de longue durée.

Résolution

Surveillez vos ressources système Amazon RDS à l'aide de métriques

Vous devez toujours surveiller l'utilisation de votre CPU et libérer de la mémoire sur votre cluster Amazon Aurora. Bien que les pics occasionnels de CPU soient normaux, un CPU constamment élevé pendant de longues périodes peut faire fonctionner vos requêtes SELECT lentement. Utilisez les outils suivants pour déterminer comment et où vous utilisez votre CPU :

  1. Les métriquesAmazon CloudWatch sont le moyen le plus simple de surveiller l'utilisation de votre CPU. Pour plus d'informations sur les métriques disponibles pour Amazon Aurora, consultez Surveillance des métriques de cluster de bases de données Amazon Aurora.
  2. Lasurveillance améliorée donne un aperçu détaillé des métriques au niveau du système d'exploitation à une granularité inférieure. Il vous donne une ventilation détaillée de la façon dont les processus utilisent votre CPU.
  3. Performance insights déterminent précisément votre charge de base de données. Activez Performance insights de votre instance de base de données, puis vérifiez si votre charge dépasse le vCPU max. Vous pouvez également surveiller vos requêtes de charge et vos SQL par des temps d'attente, et identifier les utilisateurs à l'origine des temps d'attente maximums.

Les requêtesSELECT peuvent également s'exécuter lentement en raison des recherches sur disque. Pour minimiser les E/S du disque, le moteur de base de données tente de mettre en cache le bloc lu à partir du disque. Cela signifie que la prochaine fois que la base de données aura besoin du même bloc de données, elle peut récupérer de la mémoire plutôt que d'aller sur le disque.

Utilisez les métriques ci-dessous pour vérifier si vous servez une requête particulière à partir du disque ou de la mémoire :

  • VolumeReadsIOPS : Cette métrique est le nombre d'opérations de lecture [disque] au niveau du volume facturé et doit être aussi faible que possible.

  • BufferCacheHitRatio : Cette métrique est le pourcentage de demandes que le cache de tampon sert et doit être aussi élevé que possible. Si BufferCacheHitRatio chute et que votre instruction SELECT est lente, vous traitez la requête à partir des volumes sous-jacents.

Une autre ressource importante pour identifier les instructions SELECT lentes est le journal des requêtes lentes. Activez la journalisation des requêtes lentes pour votre cluster de bases de données pour enregistrer ces requêtes et prendre des mesures plus tard. Si vous utilisez la version compatible MySQL 5.6, utilisez le schéma de performance MySQL pour surveiller en permanence les performances des requêtes.

Identifier les blocages et les événements d'attente

Amazon RDS verrouille les données dans votre base de données afin qu'une seule session utilisateur puisse écrire ou mettre à jour une ligne à tout moment. Toute autre transaction nécessitant cette ligne est maintenue en attente. Dans un verrou partagé, les transactions d'écriture/mise à jour sont maintenues en attente pendant que les transactions en lecture lisent les données. Si une requête attend d'accéder à une ligne verrouillée par une autre requête, cela peut provoquer un blocage.

Pour identifier les blocages de votre base de données, activez le paramètre innodb_print_all_deadlocks dans vos groupes de paramètres.

Ensuite, exécutez cette commande dans la section Dernier blocage pour identifier les blocages :

SHOW ENGINE INNODB STATUS\G;

Remarque : Vous ne pouvez pas exécuter cette commande sur les lecteurs Aurora car les lecteurs sont configurés avec le paramètre MySQL innodb_read_only défini sur ON, par défaut. Ce paramètre n'est pas modifiable pour une instance de base de données car Aurora MySQL gère l'état en lecture seule et en lecture/écriture des instances de bases de données en fonction du type de cluster.

Vous pouvez également interroger la table INFORMATION_SCHEMA INNODB_TRX. Cela vous donne des détails précis sur chaque transaction en cours d'exécution dans INNODB, y compris les attentes.

Vérifiez si votre requête utilise un index

Si une requête n'a pas d'index ou effectue des analyses complètes de table, elle sera plus lente en général. Les index aident à accélérer les requêtes SELECT.

Pour vérifier si votre requête utilise un index, utilisez la requête EXPLAIN. Il s'agit d'un outil utile pour résoudre les requêtes lentes. Dans la sortie de EXPLAIN, vérifiez les noms des tables, la clé utilisée et le nombre de lignes analysées pendant la requête. Si la sortie n'affiche aucune clé utilisée, créez un index sur les colonnes utilisées dans la clause WHERE.

Si la table possède l'indexation requise, vérifiez si les statistiques de la table sont à jour. S'assurer que les statistiques sont exactes signifie que l'optimiseur de requête utilise les index les plus sélectifs avec la cardinalité correcte. Cela améliore les performances des requêtes.

Vérifier la longueur de la liste d'historique (HLL)

InnoDB utilise un concept appelé MVCC (Multi-version-Concurrency Control). MVCC conserve plusieurs copies du même enregistrement pour préserver la cohérence de lecture. Cela signifie qu'une fois que vous avez validé une transaction, InnoDB purge les anciennes copies. Mais lorsqu'une transaction n'est pas validée pendant une longue période, en raison de la croissance du nombre de segments d'annulation, la longueur de la liste d'historique (HLL) augmente. La longueur de la liste d'historique InnoDB représente le nombre de modifications non vidé.

Si votre charge de travail exige beaucoup de transactions longues ou ouvertes, vous pouvez vous attendre à voir une HLL élevée sur la base de données.

Note : Les transactions à long terme ne sont pas la seule cause de pics HLL. Même si les threads de purge ne parviennent pas à suivre les changements sur la base de données, HLL peut rester élevé.

Si vous ne surveillez pas la taille de la HLL, cela entraînera une régression des performances au fil du temps, ainsi qu'une augmentation de la consommation de ressources, des performances d'instruction SELECT plus lentes et incohérentes et une augmentation du stockage. Dans les cas extrêmes, cela peut entraîner une panne de base de données.

Pour vérifier la longueur de la liste d'historique, exécutez la commande suivante :

SHOW ENGINE INNODB STATUS;

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 Aurora MySQL, en raison de la nature des volumes du stockage partagé, la longueur de la liste d'historique est au niveau du cluster et non au niveau de l'instance individuelle. Connectez-vous à votre instance de base de données en écriture et exécutez la requête 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;

Cette requête vous aide à comprendre le décalage du réplica entre les nœuds en lecture et le nœud en écriture. Il détaille également le plus ancien LSN utilisé par l'instance de base de données pour lire à partir du stockage, et le plus ancien ID TRX de la vue en lecture de l'instance de base de données. Utilisez ces informations pour vérifier si l'une des instances en lecture détient une ancienne vue en lecture (par rapport à l'état du moteur InnoDB sur l'instance en écriture).

Remarque : à partir de Aurora MySQL 1.19 et 2.06, vous pouvez surveiller la HLL en utilisant la métrique RollbackSegmentHistoryListoryLength dans CloudWatch. Ou, dans les anciennes versions, utilisez trx_rseg_history_len pour vérifier la HLL à l'aide de la commande suivante :

select NAME AS RollbackSegmentHistoryListLength, 
COUNT from INFORMATION_SCHEMA.INNODB_METRICS where NAME = 'trx_rseg_history_len';

Utilisez les méthodes suivantes pour résoudre les problèmes liés à la croissance de la HLL :

  • Si DML (écritures) provoque la croissance de la HLL : l'annulation ou la fin de cette instruction implique une annulation de la transaction interrompue. Cela prend beaucoup de temps car toutes les mises à jour effectuées jusqu'à ce moment sont annulées.

  • Si un READ provoque la croissance de la HLL : terminez la requête en utilisant mysql.rds_kill_query.

  • Selon la durée d'exécution de la requête, travaillez avec votre DBA pour voir si vous pouvez mettre fin à la requête à l'aide de la procédure stockée.

La meilleure façon d'éviter une croissance est de surveiller la HLL en utilisant les méthodes ci-dessus et d'éviter les transactions longues ou ouvertes sur la base de données. En outre, il est recommandé de valider les données en lots plus petits.

Important : ne redémarrez pas le cluster de bases de données ou l'instance. La purge de la HLL est plus efficace si elle peut accéder aux données du pool de tampons. Si vous redémarrez la base de données, toutes les données seront lues à partir du disque afin de les nettoyer.


Cet article vous a-t-il été utile ?


Besoin d'aide pour une question technique ou de facturation ?