Bonnes pratiques pour les SQL performances et le dimensionnement d'Aurora My - 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 pour les SQL performances et le dimensionnement d'Aurora My

Vous pouvez appliquer les meilleures pratiques suivantes pour améliorer les performances et l'évolutivité de vos SQL clusters Aurora My.

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

Les SQL instances Amazon Aurora My qui utilisent les classes d'instance db.t2db.t3, ou db.t4g DB sont particulièrement adaptées aux applications qui ne supportent pas une charge de travail élevée pendant une période 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. Ils sont destinés aux charges de travail qui n'utilisent pas CPU souvent ou régulièrement la totalité de leur capacité, mais qui doivent parfois être surchargées. Nous recommandons d'utiliser uniquement les classes d'instance de base de données T 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 My SQL Performance sur les instances Amazon Aurora My SQL T. S'il est activé, l'instance risque de manquer de mémoire.

Astuce

Si votre base de données est parfois inactive mais qu'elle a parfois 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és et Aurora redimensionne automatiquement votre base de données à la hausse ou à la baisse en fonction de la charge de travail actuelle. Pour plus de détails sur l'utilisation, consultez Utiliser Aurora Serverless v2. Pour les versions du moteur de base de données que vous pouvez utiliser avec Aurora Serverless v2, voir Exigences et limites pour Aurora Serverless v2.

Lorsque vous utilisez une instance T comme instance de base de données dans un cluster Aurora My SQL DB, nous vous recommandons ce qui suit :

  • 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 CPU créditeur (CPUCreditBalance) pour vous assurer qu'il se situe à un niveau durable. C'est-à-dire que les CPU crédits sont accumulés au même rythme qu'ils sont utilisés.

    Lorsque vous avez épuisé les CPU crédits d'une instance, vous constatez une baisse immédiate du nombre de crédits disponibles CPU 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 CPU créditeur n'est pas à un niveau durable, nous vous recommandons 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 (calcul d'échelle).

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

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

    Si une instance de lecteur n'a plus de CPU crédits avant l'instance d'écriture, le décalage qui en résulte peut entraîner un 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 constatez une augmentation soutenue du délai de réplication, assurez-vous que votre solde CPU créditeur pour les instances de lecteur de votre cluster de bases de données n'est pas épuisé.

    Si votre solde CPU créditeur n'est pas à un niveau durable, nous vous recommandons 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 (calcul d'échelle).

  • 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 SQL indexées Aurora My avec prélecture de clé asynchrone

Aurora My SQL peut utiliser la fonctionnalité asynchrone key prefetch (AKP) pour améliorer les performances des requêtes qui joignent des tables entre des index. Cette fonctionnalité améliore les performances en anticipant les lignes nécessaires pour exécuter des requêtes dans lesquelles une JOIN requête nécessite l'utilisation de l'algorithme Batched Key Access (BKA) Join et des fonctionnalités d'optimisation Multi-Range Read (MRR). Pour plus d'informations sur BKA etMRR, voir Bloquer les jointures d'accès aux clés en boucle imbriquée et par lots et Optimisation de la lecture à plages multiples dans la section Ma documentation. SQL

Pour tirer parti de AKP cette fonctionnalité, une requête doit utiliser à la fois BKA etMRR. Généralement, une telle requête se produit lorsque la JOIN clause d'une requête utilise un index secondaire, mais a également besoin de certaines colonnes de l'index principal. Par exemple, vous pouvez l'utiliser AKP lorsqu'une JOIN clause représente une équijointure sur les valeurs d'index entre une petite table extérieure et une grande table intérieure, et que l'index est très sélectif sur la plus grande table. AKPfonctionne de concert avec BKA et MRR pour effectuer une recherche d'index secondaire à primaire lors de l'évaluation de la JOIN clause. AKPidentifie les lignes requises pour exécuter la requête lors de l'évaluation de la JOIN clause. 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.

AKPest disponible pour Aurora My SQL version 2.10 et supérieure, ainsi que pour la version 3. Pour plus d'informations sur les SQL versions d'Aurora My, consultezMises à jour du moteur de base de données pour Amazon Aurora My SQL.

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

Vous pouvez activer AKP cette fonctionnalité en définissant aurora_use_key_prefetch une variable Mon SQL serveur suron. Par défaut, cette valeur indique on. Cependant, il ne AKP peut pas être activé tant que vous n'avez pas également activé l'algorithme BKA Join et désactivé la MRR fonctionnalité basée sur les coûts. Pour ce faire, vous devez définir les valeurs suivantes pour optimizer_switch une variable My SQL server :

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

  • Définissez mrr_cost_based sur off. Cette valeur contrôle l'utilisation des MRR fonctionnalités basées sur les coûts. Par défaut, cette valeur indique on.

Actuellement, vous pouvez uniquement configurer ces valeurs au niveau de la session. L'exemple suivant montre comment définir ces valeurs pour les activer AKP pour la session en cours en exécutant SET des instructions.

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

De même, vous pouvez utiliser SET des instructions pour désactiver AKP l'algorithme BKA Join et réactiver les MRR fonctionnalités basées sur les coûts pour la session en cours, 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'optimisation batched_key_access et mrr_cost_based, consultez la section Optimisations commutables dans la section Ma documentation. SQL

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

Vous pouvez vérifier si une requête peut tirer parti de AKP cette fonctionnalité. 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 AKP fonctionnalité 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 montre l'utilisation de EXPLAIN pour afficher le plan d'exécution d'une requête dont il est possible de tirer partiAKP.

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 EXPLAIN de sortie, consultez la section Format EXPLAIN de sortie étendu dans Ma SQL documentation.

Optimisation des requêtes Aurora My SQL join de grande taille avec des jointures par 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 My SQL :

  • Les jointures extérieures gauche-droite ne sont pas prises en charge pour Aurora My SQL version 2, mais le sont 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.

  • BLOBet les colonnes de type de données spatiales ne peuvent pas être jointes à des colonnes dans une jointure par hachage.

Activation des jointures par hachage

Pour activer les jointures par hachage :

  • Aurora My SQL version 2 — Définissez le paramètre de base de données ou le paramètre de cluster de bases aurora_disable_hash_join de données sur0. La désactivation de aurora_disable_hash_join définit optimizer_switch sur la valeur hash_join=on.

  • Aurora My SQL version 3 — Définissez le paramètre Mon SQL serveur optimizer_switch surblock_nested_loop=on.

Les jointures par hachage sont activées par défaut dans Aurora My SQL version 3 et désactivées par défaut dans Aurora My SQL version 2. L'exemple suivant montre comment activer les jointures par hachage pour Aurora My SQL 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 My SQL 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 My SQL version 2, la prise en charge de la jointure par hachage est disponible dans toutes les versions mineures. Dans Aurora My SQL version 2, la fonctionnalité de jointure par hachage est toujours contrôlée par la aurora_disable_hash_join valeur.

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. Pour ce fairehash_join_cost_based, définissez une variable Mon SQL serveur suroff. 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 montre l'utilisation de EXPLAIN pour afficher 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 EXPLAIN de sortie étendu, consultez la section Format EXPLAIN de sortie étendu dans la documentation de mon SQL produit.

Dans Aurora My SQL 2.08 et versions ultérieures, vous pouvez utiliser SQL des indices pour déterminer si une requête utilise la jointure par hachage ou non, et quelles tables utiliser pour les parties build et probe de la jointure. Pour plus de détails, consultez Aurora Mes SQL conseils.

Utilisation d'Amazon Aurora pour dimensionner les lectures de votre base de SQL données My database

Vous pouvez utiliser Amazon Aurora avec votre instance My SQL DB pour tirer parti des fonctionnalités de dimensionnement de lecture d'Amazon Aurora et étendre la charge de travail de lecture de votre instance My SQL DB. Pour utiliser Aurora pour dimensionner en lecture votre instance My SQL DB, créez un cluster Aurora My SQL DB et faites-en une réplique en lecture de votre instance My SQL DB. Connectez-vous ensuite au SQL cluster Aurora My pour traiter les requêtes de lecture. La base de données source peut être une RDS instance My SQL DB ou une instance My SQL DB exécutée en externe à AmazonRDS. Pour de plus amples informations, veuillez consulter Dimensionnement des lectures pour votre SQL base de données My avec Amazon Aurora.

Optimisation des opérations d'horodatage

Lorsque la valeur de la variable système time_zone est définie surSYSTEM, chaque appel de SQL fonction My qui nécessite un calcul de fuseau horaire effectue un appel de bibliothèque système. Lorsque vous exécutez SQL des instructions qui renvoient ou modifient de telles TIMESTAMP valeurs à un rythme élevé, vous risquez de constater une augmentation de la latence, des conflits de verrouillage et CPU une augmentation de l'utilisation. Pour plus d'informations, consultez time_zone dans la section Ma SQL documentation.

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 des paramètres d'un groupe de paramètres de cluster de base de données dans Amazon Aurora.

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.