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

Brève description

Il existe plusieurs raisons pour lesquelles les requêtes SELECT peuvent s'exécuter lentement sur votre cluster de bases de données Aurora compatible avec MySQL :

• Vos ressources système Amazon Relational Database Service (Amazon RDS) sont surutilisées. Cela peut se produire en raison d'un processeur é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 (HLL) InnoDB a considérablement augmenté en raison des transactions de longue durée.

Solution

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

Il est recommandé de constamment surveiller l'utilisation de votre processeur et de libérer de la mémoire sur votre cluster Amazon Aurora. Bien que les pics occasionnels du processeur soient normaux, un processeur constamment élevé pendant de longues périodes peut ralentir le fonctionnement de vos requêtes SELECT. Servez-vous des outils suivants pour déterminer comment et où vous utilisez votre CPU :

1.    Les métriques Amazon CloudWatch sont le moyen le plus simple de surveiller l'utilisation de votre processeur. Pour plus d'informations sur les métriques disponibles pour Aurora, consultez Métriques CloudWatch pour Aurora.

2.    La surveillance améliorée donne un aperçu détaillé des métriques au niveau du système d'exploitation à une granularité inférieure. La répartition détaillée illustre la façon dont les processus utilisent votre processeur.

3.    Performance Insights détermine précisément votre charge de base de données. Activez Performance Insights pour votre instance de base de données, puis vérifiez si votre charge dépasse le processeur virtuel maximal. 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 maximaux.

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, ce dernier sera récupéré depuis mémoire plutôt que depuis le disque.

Utilisez ces métriques 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é. Il est recommandé de s'assurer que cette valeur est aussi faible que possible.
• BufferCacheHitRatio : cette métrique est le pourcentage de demandes que le cache de tampon sert. Il est recommandé de s'assurer que cette valeur est la plus élevée 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 permettant d'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. Pour la version compatible avec MySQL 5.6, utilisez le schéma des performances 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, surveillez mysql-error.log à partir de la console, la CLI ou l'API RDS.

Vous pouvez également vous connecter à MySQL avec un compte administrateur et exécuter cette commande pour identifier les blocages à partir de la sortie de commande de la section Latest Detected Deadlock (Dernier blocage détecté) :

mysql> SHOW ENGINE INNODB STATUS\G;

Vérifier si votre requête utilise un index

Si une requête ne possède pas d'index ou effectue des analyses de tables complètes, alors elle s'exécute plus lentement. 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. Toutefois, 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ées.

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

Remarque : les transactions à longue durée ne sont pas la seule cause de pics HLL. Même si les threads de purge ne parviennent pas à suivre les modifications sur la base de données, la HLL peut rester élevée.

Si vous ne surveillez pas la taille de la HLL, les performances diminuent au fil du temps. L'augmentation de la taille de la HLL peut également entraîner une consommation de ressources plus élevée, des performances plus lentes et incohérentes des instructions SELECT 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. Elle 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 au statut 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 RollbackSegmentHistoryListLength 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';

Si Performance Insights est activé pour vos instances Aurora MySQL, vous pouvez vérifier le RollbackSegmentHistoryListLength. Accédez au rédacteur Performance Insight et procédez comme suit :

1.    Sélectionnez Manage metrics (Gérer les métriques), puis sélectionnez Database metrics (Métriques de base de données).

2.    Sélectionnez la métrique trx_rseg_history_len, puis sélectionnez Update graph (Mettre à jour le graphe).

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 administrateur de base de données (DBA) pour vérifier si vous pouvez mettre fin à la requête à l'aide de la procédure stockée.

Il est recommandé d'éviter la croissance en surveillant la HLL à l'aide de ces méthodes, et d'éviter les transactions ouvertes ou de longue durée sur la base de données. Il est également recommandé de valider les données en lots plus petits.

Important : ne redémarrez pas le cluster ou l'instance de bases de données. Il est plus efficace de purger la HLL lorsqu'elle peut accéder aux données en mémoire dans le groupe de mémoires tampons. Si vous redémarrez la base de données, le cache de pages survivable risque d'être perdu. Dans ce cas, les pages de données du volume de cluster doivent être lues afin de purger la HLL. Ce processus est plus lent que lorsqu'il est réalisé en 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