Bonnes pratiques avec Amazon Aurora MySQL - Amazon Aurora

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.

Bonnes pratiques avec Amazon Aurora MySQL

Cette rubrique contient des informations sur les bonnes pratiques et les options en matière d'utilisation ou de migration de données vers un cluster de bases de données Amazon Aurora MySQL. Les informations contenues dans cette rubrique résument et réitèrent certaines des lignes directrices et procédures que vous pouvez trouver dans Gestion d'un cluster de base de données Amazon Aurora.

Table des matières

Détermination de l'instance de base de données à laquelle vous êtes connecté

Pour déterminer à quelle instance de base de données d'un cluster de bases de données Aurora MySQL une connexion est établie, vérifiez la variable globale innodb_read_only représentée dans l'exemple suivant.

SHOW GLOBAL VARIABLES LIKE 'innodb_read_only';

La variable innodb_read_only est définie sur ON si vous êtes connecté à une instance de base de données de lecteur. Ce paramètre est OFF si vous êtes connecté à une instance DB d'écriture, telle qu'une instance principale dans un cluster provisionné.

Cette approche peut s'avérer utile si vous voulez ajouter de la logique au code de votre application pour équilibrer la charge de travail ou pour garantir qu'une opération d'écriture utilise la connexion appropriée.

Bonnes pratiques pour les performances et la mise à l'échelle de Aurora MySQL

Vous pouvez appliquer les bonnes pratiques suivantes afin d'améliorer les performances et la capacité de mise à l'échelle de vos clusters Aurora MySQL.

Utilisation de classes d'instances T pour le développement et les tests

Les instances Amazon Aurora MySQL qui utilisent les classes d'instance de base de données db.t2, db.t3 ou db.t4g sont particulièrement bien adaptées aux applications qui ne peuvent pas prendre en charge une charge de travail élevée de façon prolongée. Les instances T sont conçues pour offrir des performances de base modérées et la possibilité d'émettre en rafale pour atteindre des performances nettement supérieures si votre charge de travail l'exige. Elles sont prévues pour des charges de travail qui n'utilisent pas souvent ou de manière continue l'intégralité de l'UC, mais qui ont parfois besoin d'émettre en rafale. Nous recommandons d'utiliser les classes d'instance de base de données T uniquement pour les serveurs de développement et de test, ou pour d'autres serveurs non dédiés à la production. Pour plus d'informations sur les classes d'instances T, consultez Instances de performance à capacité extensible.

Si votre cluster Aurora est supérieur à 40 To, n'utilisez pas les classes d'instance T. Lorsque votre base de données contient un volume important de données, la surcharge de mémoire pour la gestion des objets du schéma peut dépasser la capacité d'une instance T.

N'activez pas le schéma de performance MySQL sur des instances T Amazon Aurora MySQL. S'il est activé, l'instance risque de manquer de mémoire.

Astuce

Si votre base de données est parfois inactive mais qu'à d'autres moments, elle gère une charge de travail importante, vous pouvez utiliser Aurora Serverless v2 comme alternative aux instances T. Avec Aurora Serverless v2, vous définissez une plage de capacité et Aurora augmente ou réduit automatiquement votre base de données en fonction de la charge de travail actuelle. Pour plus de détails sur l'utilisation, consultez Utiliser Aurora Serverless v2. Pour connaître les versions du moteur de base de données que vous pouvez utiliser avec Aurora Serverless v2, consultez Exigences et limites pour Aurora Serverless v2.

Lorsque vous utilisez une instance T en tant qu'instance de base de données dans un cluster de base de données Aurora MySQL, nous vous recommandons la procédure suivante :

  • Utilisez la même classe d’instance de base de données pour toutes les instances dans votre cluster de base de données. Par exemple, si vous utilisez db.t2.medium pour votre instance d'écriture, nous vous recommandons d'utiliser db.t2.medium pour vos instances de lecteur également.

  • N'ajustez pas les paramètres de configuration liés à la mémoire, tels que innodb_buffer_pool_size. Aurora utilise un ensemble hautement réglé de valeurs par défaut pour les tampons de mémoire sur les instances T. Ces valeurs par défaut spéciales sont nécessaires pour que Aurora s'exécute sur des instances limitées en mémoire. Si vous modifiez des paramètres liés à la mémoire sur une instance T, vous êtes beaucoup plus susceptible de rencontrer des out-of-memory conditions, même si votre modification vise à augmenter la taille de la mémoire tampon.

  • Surveillez votre solde de crédits UC (CPUCreditBalance) pour vous assurer qu'il est à un niveau viable. Autrement dit, les crédits UC sont accumulés au même rythme qu'ils sont utilisés.

    Lorsque vous avez épuisé les crédits UC pour une instance, vous voyez une baisse immédiate de l'UC disponible et une augmentation de la latence de lecture et d'écriture de l'instance. Cela se traduit par une diminution drastique des performances globales de l'instance.

    Si votre solde de crédits CPU n'est pas à un niveau viable, nous vous conseillons de modifier votre instance de base de données pour utiliser l'une des classes d'instance de base de données R prises en charge (dimensionnement du calcul).

    Pour obtenir plus d'informations sur les métriques de supervision, consultez Affichage des métriques dans la console Amazon RDS.

  • Surveillez le retard de réplica (AuroraReplicaLag) entre l'instance d'enregistreur et les instances de lecteur.

    Si une instance de lecteur manque de crédits CPU avant l'instance d'écriture, le décalage qui en résulte peut entraîner le redémarrage fréquent de l'instance de lecteur. Ceci est commun lorsqu'une application a une lourde charge d'opérations de lecture répartie entre les instances de lecteur, au même moment où l'instance d'écriture a une charge minimale d'opérations d'écriture.

    Si vous notez une augmentation soutenue du décalage de réplica, assurez-vous que votre solde de crédits CPU pour les instances de lecteur de votre cluster de base de données n'est pas épuisé.

    Si votre solde de crédits CPU n'est pas à un niveau viable, nous vous conseillons de modifier votre instance de base de données pour utiliser l'une des classes d'instance de base de données R prises en charge (dimensionnement du calcul).

  • Maintenez le nombre d'insertions par transaction sous 1 million pour les clusters de base de données dont la journalisation binaire est activée.

    Si le groupe de paramètres de cluster de base de données de votre cluster de base de données a une valeur autre queOFF, votre cluster de base de données peut rencontrer des out-of-memory conditions s'il reçoit des transactions contenant plus d'un million de lignes à insérer. binlog_format Vous pouvez surveiller la mesure de mémoire libérable (FreeableMemory) pour déterminer si votre cluster de bases de données est à cours de mémoire disponible. Vous pouvez ensuite vérifier la mesure des opérations d'écriture (VolumeWriteIOPS) pour savoir si une instance de dispositif d'écriture reçoit une lourde charge d'opérations d'écriture. Si tel est le cas, nous vous recommandons de mettre à jour votre application afin de limiter le nombre d'insertions dans une opération à moins de 1 million. Il est également possible de modifier votre instance pour utiliser l'une des classes d'instances de bases de données R prises en charge (dimensionnement du calcul).

Optimisation des requêtes de jointure indexées Aurora MySQL avec lecture anticipée asynchrone des clés

Aurora MySQL peut utiliser la fonctionnalité de lecture anticipée asynchrone des clés (AKP) pour améliorer les performances des requêtes qui joignent des tables par le biais des index. Cette fonction améliore les performances en anticipant les lignes nécessaires à l'exécution des requêtes dans lesquelles une requête JOIN exige l'utilisation de l'algorithme Join d'accès par lots aux clés (Batched Key Access ou BKA) et des fonctions d'optimisation de la lecture multiplage (Multi-Range Read ou MRR). Pour plus d'informations sur BKA et MRR, consultez Block Nested-Loop and Batched Key Access Joins et Multi-Range Read Optimization dans la documentation MySQL.

Pour profiter de la fonction AKP, une requête doit utiliser à la fois BKA et MRR. Une telle requête se produit normalement lorsque la clause JOIN d'une requête utilise un index secondaire, mais nécessite également quelques colonnes pour l'index principal. Par exemple, vous pouvez utiliser AKP lorsque la clause JOIN représente une équijointure sur les valeurs d'index entre une petite table externe et une grande table interne, et que l'index de la grande table est très sélectif. AKP fonctionne en association avec BKA et MRR pour procéder à une recherche d'index secondaire à principal pendant l'évaluation de la clause JOIN. AKP identifie les lignes nécessaires pour exécuter la requête pendant l'évaluation de la clause JOIN. Elle utilise ensuite un thread d'arrière-plan pour charger de manière asynchrone des pages contenant ces lignes en mémoire avant d'exécuter la requête.

La lecture anticipée asynchrone des clés (AKP) est disponible pour Aurora MySQL versions 2.10 et ultérieures et version 3. Pour de plus amples informations sur les versions d'Aurora MySQL, veuillez consulter Mises à jour du moteur de base de données pour Amazon Aurora MySQL.

Activation de la lecture anticipée asynchrone des clés

Vous pouvez activer la fonction AKP en paramétrant aurora_use_key_prefetch, une variable de serveur MySQL, sur on. Par défaut, cette valeur indique on. Néanmoins, l'AKP ne peut pas être activée tant que l'algorithme de jointure BKA n'a pas été activé et que la fonctionnalité MRR basée sur le coût n'a pas été désactivée. Pour cela, vous devez spécifier les valeurs suivantes pour optimizer_switch, une variable du serveur MySQL :

  • Définissez batched_key_access sur on. Cette valeur contrôle l'utilisation de l'algorithme Join BKA. Par défaut, cette valeur indique off.

  • Définissez mrr_cost_based sur off. Cette valeur contrôle l'utilisation de la fonctionnalité MRR basée sur le coût. Par défaut, cette valeur indique on.

Actuellement, vous pouvez uniquement configurer ces valeurs au niveau de la session. L'exemple suivant illustre la configuration de ces valeurs de manière à activer AKP pour la session en cours en exécutant les instructions SET.

mysql> set @@session.aurora_use_key_prefetch=on; mysql> set @@session.optimizer_switch='batched_key_access=on,mrr_cost_based=off';

De la même manière, vous pouvez utiliser des instructions SET pour désactiver la fonction AKP et l'algorithme Join BKA, et réactiver la fonctionnalité MRR basée sur le coût pour la session actuelle, comme indiqué dans l'exemple suivant.

mysql> set @@session.aurora_use_key_prefetch=off; mysql> set @@session.optimizer_switch='batched_key_access=off,mrr_cost_based=on';

Pour plus d'informations sur les commutateurs d'optimiseur batched_key_access et mrr_cost_based, consultez Switchable Optimizations dans la documentation MySQL.

Optimisation des requêtes pour la lecture anticipée asynchrone des clés

Vous pouvez confirmer si une requête doit pouvoir profiter des avantages de la fonction AKP. Pour cela, utilisez l'instruction EXPLAIN afin de profiler la requête avant de l'exécuter. L'instruction EXPLAIN fournit des informations sur le plan d'exécution à utiliser pour une requête déterminée.

Dans la sortie pour l'instruction EXPLAIN, la colonne Extra décrit les informations supplémentaires comprises avec le plan d'exécution. Si la fonction AKP s'applique à une table utilisée dans la requête, cette colonne inclut l'une des valeurs suivantes :

  • Using Key Prefetching

  • Using join buffer (Batched Key Access with Key Prefetching)

L'exemple suivant présente l'utilisation de l'instruction EXPLAIN pour visualiser le plan d'exécution d'une requête qui peut bénéficier d'AKP.

mysql> explain select sql_no_cache -> ps_partkey, -> sum(ps_supplycost * ps_availqty) as value -> from -> partsupp, -> supplier, -> nation -> where -> ps_suppkey = s_suppkey -> and s_nationkey = n_nationkey -> and n_name = 'ETHIOPIA' -> group by -> ps_partkey having -> sum(ps_supplycost * ps_availqty) > ( -> select -> sum(ps_supplycost * ps_availqty) * 0.0000003333 -> from -> partsupp, -> supplier, -> nation -> where -> ps_suppkey = s_suppkey -> and s_nationkey = n_nationkey -> and n_name = 'ETHIOPIA' -> ) -> order by -> value desc; +----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+ | 1 | PRIMARY | nation | ALL | PRIMARY | NULL | NULL | NULL | 25 | 100.00 | Using where; Using temporary; Using filesort | | 1 | PRIMARY | supplier | ref | PRIMARY,i_s_nationkey | i_s_nationkey | 5 | dbt3_scale_10.nation.n_nationkey | 2057 | 100.00 | Using index | | 1 | PRIMARY | partsupp | ref | i_ps_suppkey | i_ps_suppkey | 4 | dbt3_scale_10.supplier.s_suppkey | 42 | 100.00 | Using join buffer (Batched Key Access with Key Prefetching) | | 2 | SUBQUERY | nation | ALL | PRIMARY | NULL | NULL | NULL | 25 | 100.00 | Using where | | 2 | SUBQUERY | supplier | ref | PRIMARY,i_s_nationkey | i_s_nationkey | 5 | dbt3_scale_10.nation.n_nationkey | 2057 | 100.00 | Using index | | 2 | SUBQUERY | partsupp | ref | i_ps_suppkey | i_ps_suppkey | 4 | dbt3_scale_10.supplier.s_suppkey | 42 | 100.00 | Using join buffer (Batched Key Access with Key Prefetching) | +----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+ 6 rows in set, 1 warning (0.00 sec)

Pour plus d'informations sur le format de sortie EXPLAIN, consultez Format de sortie EXPLAIN étendu (langue française non garantie) dans la documentation MySQL.

Optimisation des requêtes de jointure MySQL Aurora volumineuses avec des jointures de hachage

La jointure par hachage peut améliorer les performances de requêtes lorsque vous devez joindre une grande quantité de données au moyen d'une équijointure. Vous pouvez activer les jointures par hachage pour Aurora MySQL.

Une colonne de jointure par hachage peut être une expression complexe. Dans une colonne de jointure par hachage, vous pouvez effectuer des comparaisons dans les types de données des manières suivantes :

  • Vous pouvez comparer n'importe quoi dans la catégorie des types de données numériques précises, tels que int, bigint, numeric et bit.

  • Vous pouvez comparer n'importe quoi dans la catégorie des types de données numériques approximatives, tels que float et double.

  • Vous pouvez comparer des éléments dans des types de chaînes si ces types de chaînes ont le même jeu de caractères et le même classement.

  • Vous pouvez comparer des éléments avec des types de données de date et d'horodatage si les types sont identiques.

Note

Vous ne pouvez pas comparer les types de données de différentes catégories.

Les restrictions suivantes s'appliquent aux jointures par hachage pour Aurora MySQL :

  • Les jointures externes gauche-droite ne sont pas prises en charge pour Aurora MySQL version 2, mais elles sont prises en charge pour la version 3.

  • Les semi-jointures telles que les sous-requêtes ne sont pas prises en charge, sauf si les sous-requêtes sont matérialisées en premier.

  • Les mises à jour et les suppressions sur plusieurs tables ne sont pas prises en charge.

    Note

    Les mises à jour et les suppressions à table unique sont prises en charge.

  • Les colonnes de types de données spatiales et BLOB ne peuvent pas constituer de colonnes de jointure dans une jointure par hachage.

Activation des jointures par hachage

Pour activer les jointures par hachage :

  • Aurora MySQL version 2 – Définissez le paramètre de base de données ou le paramètre de cluster de base de données aurora_disable_hash_join sur 0. La désactivation de aurora_disable_hash_join définit optimizer_switch sur la valeur hash_join=on.

  • Aurora MySQL version 3 – Définissez le paramètre du serveur MySQL optimizer_switch sur block_nested_loop=on.

Les jointures par hachage sont activées par défaut dans Aurora MySQL version 3 et désactivées par défaut dans Aurora MySQL version 2. L'exemple suivant montre comment activer les jointures par hachage pour Aurora MySQL version 3. Vous pouvez commencer par publier l'instruction select @@optimizer_switch pour voir les autres paramètres présents dans la chaîne de paramètre SET. La mise à jour d'un paramètre du paramètre optimizer_switch n'efface ni ne modifie les autres paramètres.

mysql> SET optimizer_switch='block_nested_loop=on';
Note

Pour Aurora MySQL Version 3, la prise en charge de la jointure par hachage est disponible dans toutes les versions mineures et est activée par défaut.

Pour Aurora MySQL version 2, la prise en charge des jointures par hachage est disponible dans toutes les versions mineures. Dans Aurora MySQL version 2, la fonction de jointure par hachage est toujours contrôlée par la valeur aurora_disable_hash_join.

Avec ce paramètre, l'optimiseur choisit d'utiliser la jointure par hachage sur la base du coût, des caractéristiques de requête et de la disponibilité des ressources. Si l'estimation de coût est incorrecte, vous pouvez forcer l'optimiseur à choisir une jointure par hachage. Il suffit pour cela de paramétrer hash_join_cost_based, une variable de serveur MySQL, sur off. L'exemple suivant montre comment forcer l'optimiseur à choisir une jointure par hachage.

mysql> SET optimizer_switch='hash_join_cost_based=off';
Note

Ce paramètre remplace les décisions de l'optimiseur basé sur les coûts. Bien que ce paramètre puisse être utile à des fins de test et de développement, nous vous recommandons de ne pas l'utiliser en production.

Optimisation des requêtes pour les jointures par hachage

Pour savoir si une requête peut tirer parti d'une jointure par hachage, utilisez l'instruction EXPLAIN pour profiler la requête en premier. L'instruction EXPLAIN fournit des informations sur le plan d'exécution à utiliser pour une requête déterminée.

Dans la sortie pour l'instruction EXPLAIN, la colonne Extra décrit les informations supplémentaires comprises avec le plan d'exécution. Si une jointure par hachage s'applique aux tables utilisées dans la requête, cette colonne inclut des valeurs similaires aux suivantes :

  • Using where; Using join buffer (Hash Join Outer table table1_name)

  • Using where; Using join buffer (Hash Join Inner table table2_name)

L'exemple suivant présente l'utilisation d'EXPLAIN pour visualiser le plan d'exécution d'une requête de jointure par hachage.

mysql> explain SELECT sql_no_cache * FROM hj_small, hj_big, hj_big2 -> WHERE hj_small.col1 = hj_big.col1 and hj_big.col1=hj_big2.col1 ORDER BY 1; +----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+ | 1 | SIMPLE | hj_small | ALL | NULL | NULL | NULL | NULL | 6 | Using temporary; Using filesort | | 1 | SIMPLE | hj_big | ALL | NULL | NULL | NULL | NULL | 10 | Using where; Using join buffer (Hash Join Outer table hj_big) | | 1 | SIMPLE | hj_big2 | ALL | NULL | NULL | NULL | NULL | 15 | Using where; Using join buffer (Hash Join Inner table hj_big2) | +----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+ 3 rows in set (0.04 sec)

Dans la sortie, Hash Join Inner table représente la table utilisée pour construire la table de hachage, et Hash Join Outer table représente la table employée pour sonder la table de hachage.

Pour plus d'informations sur le format de sortie étendu d'EXPLAIN, consultez Extended EXPLAIN Output Format dans la documentation du produit MySQL.

Dans les versions 2.08 et supérieures d'Aurora MySQL, vous pouvez utiliser des indicateurs SQL pour déterminer si une requête utilise ou non la jointure de hachage, et quelles tables utiliser pour les côtés construction et sonde de la jointure. Pour plus de détails, consultez Indicateurs Aurora MySQL.

Utilisation d'Amazon Aurora pour dimensionner les lectures de votre base de données MySQL

Vous pouvez utiliser Amazon Aurora avec votre instance de base de données MySQL pour tirer parti des capacités de mise à l'échelle en lecture d'Amazon Aurora et développer la charge de travail en lecture de votre instance de base de données MySQL. Pour utiliser Aurora afin de mettre à l'échelle en lecture votre instance de base de données MySQL, créez un cluster de bases de données Aurora MySQL et faites-en un réplica en lecture de votre instance de base de données MySQL. Ensuite, connectez-vous au cluster Aurora MySQL pour traiter les requêtes en lecture. La base de données source peut être une instance de base de données RDS pour MySQL ou une base de données MySQL s'exécutant en dehors de Amazon RDS. Pour de plus amples informations, veuillez consulter Utilisation d'Amazon Aurora pour dimensionner les lectures de votre base de données MySQL.

Optimisation des opérations d'horodatage

Lorsque la valeur de la variable système time_zone est définie sur SYSTEM, chaque appel de fonction MySQL qui nécessite un calcul de fuseau horaire effectue un appel à la bibliothèque système. Lorsque vous exécutez des instructions SQL qui renvoient ou modifient de telles valeurs TIMESTAMP avec un taux de simultanéité élevé, vous pouvez constater une augmentation de la latence, de la contention des verrou et de l'utilisation du processeur. Pour plus d'informations, consultez time_zone dans la documentation MySQL.

Pour éviter ce comportement, nous vous recommandons de modifier la valeur du paramètre time_zone de cluster de base de données sur UTC. Pour de plus amples informations, veuillez consulter Modification de paramètres dans un groupe de paramètres de cluster de base de données.

Le paramètre time_zone est dynamique (il ne nécessite pas de redémarrage du serveur de base de données), mais la nouvelle valeur est utilisée uniquement pour les nouvelles connexions. Pour vous assurer que toutes les connexions sont mises à jour pour utiliser la nouvelle valeur time_zone, nous vous recommandons de recycler les connexions de votre application après avoir mis à jour le paramètre du cluster de bases de données.

Bonnes pratiques pour Aurora MySQL haute disponibilité

Vous pouvez appliquer les bonnes pratiques suivantes afin d'améliorer la disponibilité de vos clusters Aurora MySQL.

Utilisation d'Amazon Aurora pour la reprise après sinistre avec vos bases de données MySQL

Vous pouvez utiliser Amazon Aurora avec votre instance de base de données MySQL pour créer une sauvegarde hors site pour la reprise après sinistre. Pour utiliser Aurora pour la reprise après sinistre de votre instance de base de données MySQL, créez un cluster de bases de données Amazon Aurora et faites-en un réplica en lecture de votre instance de base de données MySQL. Cela s'applique à une instance de base de données RDS for MySQL ou à une base de données MySQL s'exécutant en dehors de Amazon RDS.

Important

Lorsque vous configurez la réplication entre une instance de base de données MySQL et un cluster de bases de données Amazon Aurora MySQL, vous devez surveiller la réplication pour vous assurer qu'elle reste saine et la réparer si nécessaire.

Pour obtenir des instructions sur la façon de créer un cluster de bases de données Amazon Aurora MySQL et d'en faire un réplica en lecture de votre instance de base de données MySQL, suivez la procédure décrite dans Utilisation d'Amazon Aurora pour dimensionner les lectures de votre base de données MySQL.

Pour obtenir plus d'informations sur les modèles de reprise après sinistre, consultez la section How to choose the best disaster recovery option for your Amazon Aurora MySQL cluster (Comment choisir la meilleure option de reprise après sinistre pour votre cluster Amazon Aurora MySQL).

Migration depuis MySQL vers Amazon Aurora MySQL avec une interruption réduite

Lors de l'importation de données depuis une base de données MySQL prenant en charge une application active vers un cluster de bases de données Amazon Aurora MySQL, vous pouvez souhaiter réduire la durée d'interruption du service de vos données pendant la migration. Pour ce faire, vous pouvez utiliser la procédure documentée dans la section Importation de données vers une instance de base de données MySQL ou MariaDB avec un temps réduit du Guide de l'utilisateur Amazon Relational Database Service. Cette procédure peut s'avérer tout spécialement utile si vous travaillez avec une base de données très volumineuse. Elle vous permet de réduire le coût de l'importation en diminuant la quantité de données transmises à AWS via le réseau.

La procédure répertorie les étapes à suivre pour transférer une copie des données de votre base de données vers une instance Amazon EC2 et les importer vers une nouvelle instance de base de données RDS pour MySQL. Comme Amazon Aurora est compatible avec MySQL, vous pouvez utiliser à la place un cluster de bases de données Amazon Aurora pour l'instance de base de données Amazon RDS MySQL cible.

Contournement des performances lentes, du redémarrage automatique et du basculement pour les instances de base de données Aurora MySQL

Si vous exécutez une charge de travail importante ou des charges de travail qui dépassent les ressources allouées à votre instance de base de données, vous pouvez épuiser les ressources sur lesquelles vous exécutez votre application et votre base de données Aurora. Pour obtenir des statistiques sur votre instance de base de données, telles que l'utilisation du processeur, l'utilisation de la mémoire et le nombre de connexions de base de données utilisées, vous pouvez vous référer aux métriques fournies par Amazon CloudWatch, Performance Insights et Enhanced Monitoring. Pour plus d'informations sur la surveillance de votre instance de base de données, consultez Surveillance des métriques d'un cluster de bases de données Amazon Aurora.

Si votre charge de travail épuise les ressources que vous utilisez, votre instance de base de données peut ralentir, redémarrer ou même basculer vers une autre instance de base de données. Pour éviter cela, surveillez l'utilisation de vos ressources, examinez la charge de travail exécutée sur votre instance de base de données et effectuez des optimisations si nécessaire. Si les optimisations n'améliorent pas les métriques de l'instance et n'atténuent pas l'épuisement des ressources, envisagez d'augmenter votre instance de base de données avant d'atteindre ses limites. Pour plus d'informations sur les classes d'instance de base de données disponibles et leurs spécifications, consultez Classes d'instances de base de données Aurora.

Recommandations pour 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 multithreads dans Aurora MySQL version 3

Par défaut, Aurora utilise la réplication à thread unique lorsqu'un cluster de base de données Aurora MySQL est utilisé comme réplica en lecture pour la réplication de journaux binaires.

Bien qu'Aurora MySQL n'interdise pas la réplication multithread, cette fonctionnalité n'est prise en charge que dans Aurora MySQL version 3.

Aurora MySQL version 2 a hérité de plusieurs problèmes concernant la réplication multithread de MySQL. Pour cette version, nous vous recommandons de ne pas utiliser la réplication multithread en production.

Si vous utilisez la réplication multithreads, nous vous recommandons de tester pleinement toute utilisation.

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 multithreads dans Aurora MySQL version 3, consultez Réplication de journaux binaires multithreads (Aurora MySQL 3 et versions ultérieures).

Invocation de AWS Lambda fonctions à l'aide de 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.

Changer innodb_flush_log_at_trx_commit pour une valeur autre que celle par défaut, 0 ou 2, 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, dans Aurora MySQL, le paramètre innodb_flush_log_at_trx_commit est défini sur 1. Chaque thread qui commet 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 MySQL ne vide pas les journaux dans les fichiers de données comme le fait MySQL Community Edition. Toutefois, vous pouvez utiliser ce innodb_flush_log_at_trx_commit paramètre pour assouplir les contraintes de durabilité lorsque vous écrivez des enregistrements de journalisation sur le volume du cluster Aurora :

  • Dans Aurora MySQL version 2, lorsque vous définissez une valeur innodb_flush_log_at_trx_commit autre que celle par défaut (0 ou 2), 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.

  • Dans Aurora MySQL version 3, lorsque vous définissez la innodb_flush_log_at_trx_commit valeur 0, la base de données n'attend pas la confirmation que les enregistrements du journal de rétablissement sont écrits sur le volume du cluster Aurora. Par conséquent, pour obtenir le même comportement qu'avec des valeurs autres que celles par défaut dans Aurora MySQL version 2, définissez le paramètre sur 0 dans Aurora MySQL version 3.

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 innodb_flush_log_at_trx_commit passer à 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 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'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 Transaction isolation levels (Niveaux d'isolation 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.

  • 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 MySQL et Amazon RDS for MySQL avec CloudWatch Amazon et envoyer des notifications via 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 MySQL.