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.
Rubriques
- Utilisation de classes d'instances T pour le développement et les tests
- Optimisation des requêtes de jointure SQL indexées Aurora My avec prélecture de clé asynchrone
- Optimisation des requêtes Aurora My SQL join de grande taille avec des jointures par hachage
- Utilisation d'Amazon Aurora pour dimensionner les lectures de votre base de SQL données My database
- Optimisation des opérations d'horodatage
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.t2
db.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'utiliserdb.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 que
OFF
, 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
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
suron
. Cette valeur contrôle l'utilisation de l'algorithme BKA Join. Par défaut, cette valeur indiqueoff
. Définissez
mrr_cost_based
suroff
. Cette valeur contrôle l'utilisation des MRR fonctionnalités basées sur les coûts. Par défaut, cette valeur indiqueon
.
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';
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
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
etbit
. -
Vous pouvez comparer n'importe quoi dans la catégorie des types de données numériques approximatives, tels que
float
etdouble
. -
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 deaurora_disable_hash_join
définitoptimizer_switch
sur la valeurhash_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 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
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.