Recommandations pour les fonctionnalités MySQL dans Aurora MySQL - Amazon Aurora
Utilisation de la réplication multithread dans Aurora MySQLAppeler des fonctions AWS Lambda à l’aide des fonctions MySQL nativesÉviter les transactions XA avec Amazon Aurora MySQLMaintenir les clés étrangères activées pendant les instructions DMLConfiguration de la fréquence à laquelle le tampon du journal est vidéMinimisation et résolution des blocages d’Aurora MySQL

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.

Recommandations pour les fonctionnalités MySQL dans Aurora MySQL

Les fonctions suivantes sont disponibles dans Aurora MySQL pour la compatibilité MySQL. Cependant, ils présentent des problèmes de performance, de capacité de mise à l’échelle, de stabilité ou de compatibilité dans l’environnement Aurora. Nous vous recommandons donc de suivre certaines directives dans l’utilisation de ces fonctionnalités. Par exemple, nous vous recommandons de ne pas utiliser certaines fonctions pour les déploiements Aurora en production.

Utilisation de la réplication multithread dans Aurora MySQL

Avec la réplication de journaux binaires multithreads, un thread SQL lit les événements du journal de relais et les met en file d’attente pour que les threads de travail SQL s’appliquent. Les threads de travail SQL sont gérés par un thread coordinateur. Si cela est possible, les événements du journal binaire sont appliqués en parallèle.

La réplication multithread est prise en charge dans Aurora MySQL version 3 et dans Aurora MySQL version 2.12.1 ou ultérieure.

Pour les versions d’Aurora MySQL inférieures à 3.04, Aurora utilise la réplication à thread unique par défaut lorsqu’un cluster de bases de données Aurora MySQL est utilisé comme réplica en lecture pour la réplication de journaux binaires.

Les versions antérieures d’Aurora MySQL 2 ont hérité de plusieurs problèmes concernant la réplication multithread à partir de MySQL Community Edition. Pour ces versions, nous vous recommandons de ne pas utiliser la réplication multithread en production.

Si vous utilisez la réplication multithread, nous vous recommandons de la tester pleinement.

Pour plus d’informations sur l’utilisation de la réplication dans Amazon Aurora, consultez Réplication avec Amazon Aurora. Pour plus d’informations sur la réplication multithread dans Aurora MySQL, consultez Réplication des journaux binaires multithread.

Appeler des fonctions AWS Lambda à l’aide des fonctions MySQL natives

Nous vous recommandons d’utiliser les fonctions MySQL natives lambda_sync et lambda_async pour invoquer des fonctions Lambda.

Si vous employez la procédure obsolète mysql.lambda_async, nous vous recommandons d’encapsuler les appels de la procédure mysql.lambda_async dans une procédure stockée. Vous pouvez appeler cette procédure stockée à partir de différentes sources, telles que des déclencheurs ou du code client. Cette approche contribue à éviter les problèmes de discordance d’impédance et permet aux programmeurs de base de données d’appeler plus facilement les fonctions Lambda.

Pour plus d’informations sur l’appel de fonctions Lambda à partir d’Amazon Aurora, consultez Appel d’une fonction Lambda à partir d’un cluster de bases de données Amazon Aurora MySQL.

Éviter les transactions XA avec Amazon Aurora MySQL

Nous vous déconseillons d’utiliser des transactions eXtended Architecture (XA) avec Aurora MySQL, car elles peuvent allonger les temps de récupération si l’architecture XA était à l’état PREPARED. Si vous devez utiliser des transactions XA avec Aurora MySQL, observez les bonnes pratiques suivantes :

  • ne laissez pas de transaction XA ouverte en état PREPARED ;

  • gardez les transactions XA aussi petites que possible.

Pour plus d’informations sur l’utilisation des transactions XA avec MySQL, consultez XA Transactions dans la documentation MySQL.

Maintenir les clés étrangères activées pendant les instructions DML

Nous vous recommandons vivement de ne pas exécuter d’instruction de langage de définition de données (Data Definition Language, DDL) lorsque la variable foreign_key_checks a pour valeur 0 (désactivé).

Si vous avez besoin d’insérer ou de mettre à jour des lignes qui requièrent une violation transitoire des clés étrangères, procédez comme suit :

  1. Définissez foreign_key_checks sur 0.

  2. Apportez vos modifications de langage de manipulation de données (DML).

  3. Assurez-vous que les modifications que vous avez apportées ne vont à l’encontre d’aucune contrainte de clé étrangère.

  4. Affectez à foreign_key_checks la valeur 1 (activé).

De plus, suivez ces autres bonnes pratiques relatives aux contraintes de clé étrangère :

  • Assurez-vous que vos applications clientes n’affectent pas la valeur foreign_key_checks à la variable 0 dans le cadre de la variable init_connect.

  • Si une restauration à partir d’une sauvegarde logique telle que mysqldump échoue ou est incomplète, assurez-vous que la valeur foreign_key_checks soit affectée à 1 avant de commencer toute autre opération dans la même session. Une sauvegarde logique affecte la valeur foreign_key_checks à 0 lorsqu’elle commence.

Configuration de la fréquence à laquelle le tampon du journal est vidé

Dans MySQL Community Edition, pour rendre les transactions durables, le tampon du journal InnoDB doit être vidé vers un stockage durable. Vous utilisez le paramètre innodb_flush_log_at_trx_commit pour configurer la fréquence à laquelle le tampon du journal est vidé vers un disque.

Lorsque vous définissez le paramètre innodb_flush_log_at_trx_commit sur la valeur par défaut de 1, le tampon du journal est vidé à chaque validation de transaction. Ce paramètre permet de maintenir la conformité ACID de la base de données. Nous vous recommandons de conserver le paramètre par défaut sur 1.

Le remplacement d’innodb_flush_log_at_trx_commit par une valeur autre que celle par défaut peut contribuer à réduire la latence du langage de manipulation des données (DML), mais compromet la durabilité des enregistrements du journal. Ce manque de durabilité rend la base de données ACID non conforme. Nous recommandons que vos bases de données soient conformes à ACID pour éviter le risque de perte de données en cas de redémarrage du serveur. Pour plus d’informations sur ce paramètre, consultez innodb_flush_log_at_trx_commit dans la documentation MySQL.

Dans Aurora MySQL, le traitement des fichiers de reprise est déchargé vers la couche de stockage, de sorte qu’aucun vidage des fichiers journaux ne se produit sur l’instance de base de données. Lorsqu’une écriture est émise, les journaux de reprise sont envoyés depuis l’instance de base de données d’écriture directement vers le volume de cluster Aurora. Les seules écritures qui transitent par le réseau sont les enregistrements de journaux de reprise. Aucune page n’est jamais écrite à partir du niveau de la base de données.

Par défaut, chaque thread qui valide une transaction attend la confirmation du volume du cluster Aurora. Cette confirmation indique que cet enregistrement et tous les enregistrements de journaux de reprise précédents sont écrits et ont atteint le quorum. La conservation des enregistrements du journal et l’atteinte du quorum rendent la transaction durable, que ce soit par le biais d’une validation automatique ou d’une validation explicite. Pour plus d’informations sur l’architecture de stockage Aurora, consultez Stockage Amazon Aurora démystifié.

Aurora MySQL ne vide pas les journaux dans les fichiers de données comme le fait MySQL Community Edition. Vous pouvez toutefois utiliser le paramètre innodb_flush_log_at_trx_commit pour assouplir les contraintes de durabilité lors de l’écriture d’enregistrements de journaux de reprise sur le volume de cluster Aurora.

Pour Aurora MySQL version 2 :

  • innodb_flush_log_at_trx_commit = 0 ou 2 : la base de données n’attend pas la confirmation que les enregistrements des journaux de reprise sont écrits sur le volume de cluster Aurora.

  • innodb_flush_log_at_trx_commit = 1 : la base de données attend la confirmation que les enregistrements des journaux de reprise sont écrits sur le volume du cluster Aurora.

Pour Aurora MySQL version 3 :

  • innodb_flush_log_at_trx_commit = 0 : la base de données n’attend pas la confirmation que les enregistrements des journaux de reprise sont écrits sur le volume de cluster Aurora.

  • innodb_flush_log_at_trx_commit = 1 ou 2 : la base de données attend la confirmation que les enregistrements des journaux de reprise sont écrits sur le volume du cluster Aurora.

Par conséquent, pour obtenir le même comportement, autre que celui par défaut, dans Aurora MySQL version 3 que celui que vous obtiendriez avec une valeur définie sur 0 ou 2 dans Aurora MySQL version 2, définissez le paramètre sur 0.

Bien que ces paramètres puissent réduire la latence DML pour le client, ils peuvent également entraîner une perte de données en cas de basculement ou de redémarrage. Par conséquent, nous vous recommandons de conserver la valeur par défaut de 1 pour le paramètre innodb_flush_log_at_trx_commit.

Bien que des pertes de données puissent se produire à la fois dans MySQL Community Edition et Aurora MySQL, le comportement diffère dans chaque base de données en raison de leurs architectures différentes. Ces différences architecturales peuvent entraîner des pertes de données à des degrés divers. Pour vous assurer que votre base de données est conforme à la norme ACID, définissez toujours une valeur 1 pour innodb_flush_log_at_trx_commit.

Note

Dans Aurora MySQL version 3, avant de pouvoir remplacer innodb_flush_log_at_trx_commit par une valeur autre que 1, vous devez d’abord remplacer la valeur d’innodb_trx_commit_allow_data_loss par 1. Ce faisant, vous reconnaissez le risque de perte de données.

Minimisation et résolution des blocages d’Aurora MySQL

Les utilisateurs exécutant des charges de travail qui rencontrent régulièrement des violations de contraintes sur des index secondaires uniques ou des clés étrangères lorsqu’ils modifient simultanément des enregistrements sur la même page de données, peuvent être confrontés à des blocages et à des délais d’attente plus longs. Ces blocages et délais d’attente sont dus à une correction de bogue de MySQL Community Edition.

Ce correctif est inclus dans les versions 5.7.26 et ultérieures de MySQL Community Edition, et a été rétroporté aux versions 2.10.3 et ultérieures d’Aurora MySQL. Le correctif est nécessaire pour mettre en vigueur la sérialisation, en implémentant un verrouillage supplémentaire pour ces types d’opérations en langage de manipulation de données (DML) sur les modifications apportées aux enregistrements d’une table InnoDB. Ce problème a été découvert dans le cadre d’une enquête sur les problèmes de blocage introduits par une précédente correction de bogue de MySQL Community Edition.

Le correctif a modifié la gestion interne de l’annulation partielle d’une mise à jour de tuple (ligne) dans le moteur de stockage InnoDB. Les opérations qui génèrent des violations de contraintes sur des clés étrangères ou des index secondaires uniques entraînent une annulation partielle. Cela inclut, sans toutefois s’y limiter, les instructions INSERT...ON DUPLICATE KEY UPDATE, REPLACE INTO, et INSERT IGNORE simultanées (mises à jour/insertions).

Dans ce contexte, l’annulation partielle ne fait pas référence à l’annulation des transactions au niveau de l’application, mais plutôt à une annulation interne à InnoDB des modifications apportées à un index organisé en cluster, lorsqu’une violation de contrainte est détectée. Par exemple, une valeur de clé dupliquée est détectée lors d’une opération de mise à jour/d’insertion.

Dans une opération d’insertion normale, InnoDB crée de manière atomique des entrées d’index organisés en cluster et secondaires pour chaque index. Si InnoDB détecte une valeur dupliquée sur un index secondaire unique lors d’une opération de mise à jour/d’insertion, l’entrée insérée dans l’index organisé en cluster doit être annulée (annulation partielle) et la mise à jour doit ensuite être appliquée à la ligne dupliquée existante. Au cours de cette étape d’annulation partielle interne, InnoDB doit verrouiller chaque enregistrement considéré dans le cadre de l’opération. Le correctif garantit la sérialisation des transactions en introduisant un verrouillage supplémentaire après l’annulation partielle.

Minimisation des blocages InnoDB

Vous pouvez adopter les approches suivantes pour réduire la fréquence des blocages dans votre instance de base de données. Vous trouverez d’autres exemples dans la documentation MySQL.

  1. Pour réduire les risques de blocages, validez les transactions immédiatement après avoir apporté un ensemble de modifications connexes. Vous pouvez le faire en divisant les transactions volumineuses (mises à jour de plusieurs lignes entre les validations) en transactions plus petites. Si vous insérez des lignes par lots, essayez de réduire la taille des insertions par lots, en particulier lorsque vous utilisez les opérations de mise à jour/d’insertion mentionnées précédemment.

    Pour réduire le nombre d’annulations partielles possibles, vous pouvez essayer l’une des approches suivantes :

    1. Remplacez les opérations d’insertion par lots en insérant une ligne à la fois. Cela peut réduire la durée pendant laquelle les verrous sont bloqués en raison de transactions susceptibles de présenter des conflits.

    2. Au lieu d’utiliser REPLACE INTO, réécrivez l’instruction SQL sous la forme d’une transaction à plusieurs instructions, comme suit :

      BEGIN;
DELETE conflicting rows;
INSERT new rows;
COMMIT;

    3. Au lieu d’utiliser INSERT...ON DUPLICATE KEY UPDATE, réécrivez l’instruction SQL sous la forme d’une transaction à plusieurs instructions, comme suit :

      BEGIN;
SELECT rows that conflict on secondary indexes;
UPDATE conflicting rows;
INSERT new rows;
COMMIT;

  2. Évitez les transactions de longue durée, actives ou inactives, qui pourraient bloquer les verrous. Cela inclut les sessions client MySQL interactives qui peuvent être ouvertes pendant une période prolongée avec une transaction non validée. Lors de l’optimisation de la taille des transactions ou de la taille des lots, l’impact peut varier en fonction d’un certain nombre de facteurs tels que la simultanéité, le nombre de doublons et la structure de la table. Toute modification doit être mise en œuvre et testée en fonction de votre charge de travail.

  3. Dans certains cas, des blocages peuvent survenir lorsque deux transactions tentent d’accéder aux mêmes jeux de données, dans une ou plusieurs tables, dans des ordres différents. Pour éviter cela, vous pouvez modifier les transactions pour accéder aux données dans le même ordre, sérialisant ainsi l’accès. Par exemple, créez une file d’attente de transactions à terminer. Cette approche permet d’éviter les blocages lorsque plusieurs transactions se produisent simultanément.

  4. L’ajout d’index soigneusement sélectionnés à vos tables peut améliorer la sélectivité et réduire le besoin d’accéder aux lignes, ce qui permet de réduire le verrouillage.

  5. En cas de verrouillage des écarts, vous pouvez modifier le niveau d’isolement de la transaction sur READ COMMITTED afin que la session ou la transaction l’empêche. Pour plus d’informations sur les niveaux d’isolement d’InnoDB et leurs comportements, consultez Niveaux d’isolement des transactions dans la documentation MySQL.

Note

Bien que vous puissiez prendre des précautions pour réduire les risques de blocages, les blocages sont un comportement normal des bases de données et peuvent toujours se produire. Les applications doivent disposer de la logique nécessaire pour gérer les blocages lorsqu’ils se présentent. Par exemple, implémentez une logique de nouvelle tentative et de retrait dans l’application. Il est préférable de s’attaquer à la cause racine du problème, mais en cas de blocage, l’application a la possibilité d’attendre et de réessayer.

Surveillance des blocages InnoDB

Des blocages peuvent survenir dans MySQL lorsque des transactions d’application tentent de se verrouiller au niveau des tables et des lignes, ce qui entraîne une attente circulaire. Un blocage occasionnel d’InnoDB n’est pas nécessairement un problème, car le moteur de stockage InnoDB détecte immédiatement la situation et annule automatiquement l’une des transactions. Si vous rencontrez fréquemment des blocages, nous vous recommandons de revoir et de modifier votre application pour atténuer les problèmes de performances et éviter les blocages. Lorsque la détection des blocages est activée (par défaut), InnoDB détecte automatiquement les blocages de transactions et annule une ou plusieurs transactions pour sortir du blocage. InnoDB essaie de sélectionner les petites transactions à annuler, la taille d’une transaction étant déterminée par le nombre de lignes insérées, mises à jour ou supprimées.

  • Instruction SHOW ENGINE : l’instruction SHOW ENGINE INNODB STATUS \G contient des informations sur le blocage le plus récent rencontré sur la base de données depuis le dernier redémarrage.

  • Journal des erreurs MySQL : si vous rencontrez fréquemment des blocages lorsque la sortie de l’instruction SHOW ENGINE est inadéquate, vous pouvez activer le paramètre de cluster de bases de données innodb_print_all_deadlocks.

    Lorsque ce paramètre est activé, les informations relatives à tous les blocages dans les transactions utilisateur d’InnoDB sont enregistrées dans le journal des erreurs Aurora MySQL.

  • Métriques Amazon CloudWatch : nous vous recommandons également de surveiller de manière proactive les blocages à l’aide de la métrique CloudWatch Deadlocks. Pour plus d’informations, consultez Métriques de niveau instance pour Amazon Aurora.

  • Amazon CloudWatch Logs : CloudWatch Logs vous permet d’afficher des métriques, d’analyser des données de journaux et de créer des alarmes en temps réel. Pour plus d’informations, consultez Monitor errors in Amazon Aurora MySQL and Amazon RDS for MySQL using Amazon CloudWatch and send notifications using Amazon SNS (Surveiller les erreurs dans Amazon Aurora MySQL et Amazon RDS for MySQL à l’aide d’Amazon CloudWatch et envoyer des notifications via Amazon SNS).

    Lorsque innodb_print_all_deadlocks est activé sur CloudWatch Logs, vous pouvez configurer des alarmes qui vous avertiront lorsque le nombre de blocages dépasse un seuil donné. Pour définir un seuil, nous vous recommandons d’observer vos tendances et d’utiliser une valeur basée sur votre charge de travail normale.

  • Performances Insights : lorsque vous utilisez Performance Insights, vous pouvez surveiller les métriques innodb_deadlocks et innodb_lock_wait_timeout. Pour obtenir plus d’informations sur ces métriques, consultez Compteurs non natifs pour Aurora MySQL.