Recommandations relatives à mes SQL fonctionnalités dans Aurora My SQL - Amazon Aurora
Utilisation de la réplication multithread dans Aurora My SQLInvoquer AWS Lambda fonctions utilisant les SQL fonctions natives MyÉviter les transactions XA avec Amazon Aurora My SQLGarder les clés étrangères activées pendant DML les relevésConfiguration de la fréquence à laquelle le tampon du journal est vidéMinimisation et résolution des SQL blocages dans Aurora My

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 relatives à mes SQL fonctionnalités dans Aurora My SQL

Les fonctionnalités suivantes sont disponibles dans Aurora My SQL for My SQL compatibility. 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 My SQL

Avec la réplication de journaux binaires multithread, un SQL thread lit les événements du journal de relais et les met en file d'attente pour que les SQL threads de travail s'appliquent. Les SQL threads de travail sont gérés par un thread de coordination. 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 My SQL version 3, ainsi que dans Aurora My SQL version 2.12.1 et versions ultérieures.

Pour les SQL versions d'Aurora My inférieures à 3.04, Aurora utilise la réplication monothread par défaut lorsqu'un cluster Aurora My SQL DB est utilisé comme réplique en lecture pour la réplication de journaux binaires.

Les versions antérieures d'Aurora My SQL version 2 ont hérité de plusieurs problèmes liés à la réplication multithread liés à My SQL 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 de manière approfondie.

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 de journaux binaires multithread

Invoquer AWS Lambda fonctions utilisant les SQL fonctions natives My

Nous vous recommandons d'utiliser les SQL fonctions natives My lambda_sync et lambda_async d'invoquer les 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 Invocation d'une fonction Lambda depuis un cluster Amazon Aurora My DB SQL.

Éviter les transactions XA avec Amazon Aurora My SQL

Nous vous recommandons de ne pas utiliser les transactions eXtended Architecture (XA) avec Aurora MySQL, car elles peuvent entraîner de longs délais de restauration si le XA était dans PREPARED cet état. Si vous devez utiliser des transactions XA avec Aurora MySQL, suivez les meilleures 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 la section Transactions XA dans la SQL documentation My.

Garder les clés étrangères activées pendant DML les relevés

Nous vous recommandons vivement de n'exécuter aucune instruction du langage de définition des données (DDL) lorsque la foreign_key_checks variable est définie sur 0 (off).

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. Modifiez votre langue de manipulation des 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 My SQL Community Edition, pour rendre les transactions durables, la mémoire tampon d'InnoDB doit être vidée vers un espace de 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 garantir la ACIDconformité de la base de données. Nous vous recommandons de conserver le paramètre par défaut sur 1.

Le passage innodb_flush_log_at_trx_commit à 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 ACID conformes afin d'é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 section Ma documentation. SQL

Dans Aurora MySQL, le traitement du redo log est déchargé sur 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 validant 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 la section Stockage Amazon Aurora démystifié.

Aurora My SQL ne vide pas les journaux dans les fichiers de données comme le fait My SQL 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 la SQL version 2 d'Aurora My :

  • innodb_flush_log_at_trx_commit= 0 ou 2 — La base de données n'attend pas la confirmation que les enregistrements de journalisation sont écrits sur le volume du cluster Aurora.

  • innodb_flush_log_at_trx_commit= 1 — La base de données attend la confirmation que les enregistrements du journal de journalisation sont écrits sur le volume du cluster Aurora.

Pour la SQL version 3 d'Aurora My :

  • innodb_flush_log_at_trx_commit= 0 — La base de données n'attend pas la confirmation que les enregistrements du journal de journalisation sont écrits sur le volume du cluster Aurora.

  • innodb_flush_log_at_trx_commit= 1 ou 2 — La base de données attend la confirmation que les enregistrements de journalisation 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 My SQL version 3 que celui que vous obtiendriez avec une valeur définie sur 0 ou 2 dans Aurora My SQL version 2, définissez le paramètre sur 0.

Bien que ces paramètres puissent réduire la DML latence du 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 My SQL Community Edition et Aurora MySQL, le comportement diffère d'une base de données à l'autre 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 ACID conforme, définissez toujours innodb_flush_log_at_trx_commit la valeur 1.

Note

Dans Aurora My SQL version 3, avant de pouvoir passer innodb_flush_log_at_trx_commit à une valeur autre que 1, vous devez d'abord changer la valeur innodb_trx_commit_allow_data_loss de 1. Ce faisant, vous reconnaissez le risque de perte de données.

Minimisation et résolution des SQL blocages dans Aurora My

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 My SQL Community Edition.

Ce correctif est inclus dans les versions 5.7.26 et supérieures de My SQL Community Edition et a été rétroporté dans les SQL versions 2.10.3 et supérieures d'Aurora My. Le correctif est nécessaire pour renforcer la sérialisabilité, en implémentant un verrouillage supplémentaire pour ces types d'opérations du langage de manipulation de données (DML), lors des modifications apportées aux enregistrements d'une table InnoDB. Ce problème a été découvert dans le cadre d'une enquête sur des problèmes de blocage introduits par une précédente correction de bogue de My SQL 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 section Ma SQL documentation.

  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 de l'utiliserREPLACE INTO, réécrivez l'SQLinstruction sous la forme d'une transaction à instructions multiples, comme suit :

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

    3. Au lieu de l'utiliserINSERT...ON DUPLICATE KEY UPDATE, réécrivez l'SQLinstruction sous la forme d'une transaction à instructions multiples, 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 interactives Mes SQL clients 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'isolation de la transaction sur READ COMMITTED afin que la session ou la transaction l'empêche. Pour plus d'informations sur les niveaux d'isolation d'InnoDB et leurs comportements, consultez la section Niveaux d'isolation des transactions dans la section Ma SQL documentation.

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 se produire dans My SQL lorsque des transactions d'application tentent d'être verrouillées au niveau de la table et au niveau 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.

  • Mon journal des SQL erreurs — Si vous rencontrez fréquemment des blocages où la sortie de l'SHOW ENGINEinstruction est inadéquate, vous pouvez activer le paramètre de cluster de base de données innodb_print_all_deadlocks.

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

  • CloudWatch Métriques Amazon — Nous vous recommandons également de surveiller de manière proactive les blocages à l'aide de la CloudWatch métrique. Deadlocks Pour de plus amples informations, veuillez consulter Métriques de niveau instance pour Amazon Aurora.

  • Amazon CloudWatch Logs — Avec CloudWatch Logs, vous pouvez consulter les métriques, analyser les données des journaux et créer des alarmes en temps réel. Pour plus d'informations, consultez Surveiller les erreurs dans Amazon Aurora My SQL et Amazon RDS for My SQL avec Amazon CloudWatch et envoyer des notifications avec Amazon SNS.

    Lorsque l' CloudWatch option Logs innodb_print_all_deadlocks est activée, vous pouvez configurer des alarmes pour vous avertir 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 My SQL.