Comment puis-je résoudre les problèmes liés à une faible quantité de mémoire disponible dans une base de données Amazon RDS for MySQL ?

Brève description

Pour résoudre les problèmes de faible capacité de mémoire, vérifiez d'abord comment Amazon RDS for MySQL utilise la mémoire. Pour plus d'informations, consultez la page 8.12.4.1 Comment MySQL utilise la mémoire sur le site Web de MySQL. Identifiez les composants qui utilisent la mémoire. Recherchez ensuite les goulots d'étranglement au niveau de l'instance et de la base de données. Enfin, surveillez les métriques de ces composants et configurez vos sessions pour des performances optimales.

Résolution

Vérifiez comment RDS for MySQL utilise la mémoire

Pour optimiser les performances, RDS for MySQL alloue 80 à 90 % de la mémoire disponible sur une instance aux paramètres par défaut. Si vous définissez des paramètres qui utilisent plus de mémoire, modifiez les autres paramètres pour utiliser moins de mémoire à titre de compensation.

Pour calculer l'utilisation approximative de la mémoire pour votre instance de base de données RDS for MySQL, utilisez la formule suivante : Utilisation maximale de la mémoire MySQL = innodb_buffer_pool_size + key_buffer_size + ((read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size) X max_connections)

Pools de mémoire tampon

Le paramètre innodb_buffer_pool_size correspond à la zone mémoire de la RAM où innodb met en cache les tables de base de données et les données liées aux index. Un pool de mémoire tampon plus important nécessite moins d'opérations d'I/O renvoyées vers le disque. Par défaut, innodb_buffer_pool_size utilise au maximum 75 % de la mémoire disponible allouée à l'instance de base de données Amazon RDS : innodb_buffer_pool_size = DBInstanceClassMemory*3/4.

Pour identifier la source d'utilisation de la mémoire, vérifiez d'abord le paramètre innodb_buffer_pool_size. Ensuite, si nécessaire, modifiez la valeur du paramètre dans votre groupe de paramètres personnalisé pour réduire innodb_buffer_pool_size.

Par exemple, vous pouvez réduire la valeur par défaut de DBInstanceClassMemory*3/4 à *5/8 ou *1/2. Vérifiez que la valeur de BufferCacheHitRatio de l'instance n'est pas trop faible. Si la valeur de BufferCacheHitRatio est faible, il peut être nécessaire d’augmenter la taille de l'instance pour augmenter la mémoire vive. Pour en savoir plus, consultez la section Bonnes pratiques en matière de configuration des paramètres d'Amazon RDS for MySQL, partie 1 : Paramètres liés à la performance.

Threads MySQL

Une certaine quantité de mémoire est également allouée à chaque thread MySQL connecté à une instance de base de données MySQL. Les threads suivants nécessitent une quantité de mémoire allouée :

• thread_stack
• net_buffer_length
• read_buffer_size
• sort_buffer_size
• join_buffer_size
• max_heap_table_size
• tmp_table_size
• binlog_cache_size

MySQL crée également des tables internes temporaires pour effectuer certaines opérations. Lorsque les tables atteignent la valeur la plus basse de tmp_table_size ou max_heap_table_size, MySQL convertit les tables de tables basées sur la mémoire en tables basées sur disque. Si plusieurs sessions créent des tables internes temporaires, il est possible que vous constatiez une augmentation de l'utilisation de la mémoire. Pour réduire l'utilisation de la mémoire, n'utilisez pas de tables temporaires dans vos requêtes.

Remarque : Lorsque vous augmentez les limites pour tmp_table_size et max_heap_table_size, des tables temporaires de plus grande taille peuvent être stockées en mémoire. Pour vérifier que MySQL a créé une table temporaire implicite, utilisez la variable created_tmp_tables. Pour en savoir plus sur cette variable, consultez la page Created_tmp_tables du site Web de MySQL.

Opérations JOIN et SORT

L'utilisation de la mémoire augmente si MySQL alloue plusieurs tampons du même type, tels que join_buffer_size ou sort_buffer_size lors d'une opération JOIN ou SORT. Par exemple, MySQL alloue un tampon JOIN pour effectuer une opération JOIN entre deux tables. Pour les requêtes qui utilisent des opérations JOIN multitables où toutes les requêtes requièrent un tampon JOIN, MySQL alloue un tampon JOIN de moins que le nombre total de tables. Si vous configurez vos variables de session avec une valeur trop élevée, vous rencontrez des problèmes si les requêtes ne sont pas optimisées. Allouez la mémoire minimale requise aux variables de niveau session telles que join_buffer_size et sort_buffer_size.

Remarque : Si vous effectuez des insertions groupées dans des tables MYISAM, MySQL utilise des octets de mémoire bulk_insert_buffer_size. Pour en savoir plus, consultez la page Bonnes pratiques d'utilisation de MySQL.

Schéma de performance

Si vous activez Performance Insights, MySQL alloue des tampons internes pour le schéma de performance lorsque vous démarrez l'instance et pendant les opérations du serveur. Pour en savoir plus sur la façon dont le schéma de performance utilise la mémoire, consultez la page 29.17 Modèle d'allocation de mémoire du schéma de performance du site Web de MySQL.

Outre les tables du schéma de performance, vous pouvez également utiliser le schéma sys MySQL. Par exemple, utilisez l'événement performance_schema pour afficher la quantité de mémoire que MySQL alloue aux tampons internes utilisés par le schéma de performance. Vous pouvez également exécuter la requête suivante pour connaître la quantité de mémoire allouée :

SELECT * FROM performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/performance_schema/%';

Les instruments de mémoire se trouvent dans la table setup_instruments au format memory/code_area/instrument_name.

Pour activer l'instrumentation à mémoire, définissez la colonne ACTIVÉ de l'instrument sur OUI dans la table setup_instruments :

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'memory/%';

Surveiller l'utilisation de la mémoire dans votre instance

Métriques CloudWatch

Lorsque la mémoire disponible est faible, utilisez l'onglet Surveillance de la console Amazon RDS pour surveiller les métriques Amazon CloudWatch DatabaseConnections, CPUUtilization, ReadIOPS et WriteIOPS.

Pour DatabaseConnections, chaque connexion établie à la base de données nécessite que de la mémoire lui soit allouée. Par conséquent, un pic de connexions à la base de données peut entraîner une baisse de la mémoire disponible. Utilisez la formule suivante pour calculer le quota max_connections maximum estimé : DBInstanceClassMemory/12582880. Pour vérifier si vous dépassez ce quota, consultez la métrique DatabaseConnections.

Pour vérifier la pression de la mémoire, surveillez les métriques CloudWatch SwapUsage et FreeableMemory. Une utilisation élevée des échanges et une faible quantité de mémoire disponible peuvent entraîner une pression de mémoire élevée pour votre instance. Il est recommandé de maintenir les niveaux de pression de la mémoire en dessous de 95 %. Une pression mémoire élevée affecte les performances de la base de données. Pour plus d’informations, consultez la section Pourquoi mon instance de base de données Amazon RDS utilise-t-elle de la mémoire d’échange quand je dispose d’une quantité suffisante de mémoire ?

Surveillance améliorée

Pour surveiller l'utilisation des ressources dans une instance de base de données, activez la surveillance améliorée. Puis, définissez une granularité comprise entre 1 et 5 secondes. La granularité par défaut est de 60 secondes. Grâce à la surveillance améliorée, vous pouvez surveiller la mémoire libérable et active en temps réel.

Pour surveiller les threads qui consomment le plus d’UC et de mémoire, exécutez la commande suivante pour répertorier les threads de votre instance de base de données :

mysql> select THREAD_ID, PROCESSLIST_ID, THREAD_OS_ID from performance_schema.threads;

Puis, exécutez ensuite la commande suivante pour mapper le thread_OS_ID au thread_ID :

select p.* from information_schema.processlist p, performance_schema.threads t
where p.id=t.processlist_id and t.thread_os_id=thread-ID;

Remarque : Remplacez thread-ID par l'ID du thread.

Résoudre les problèmes liés à la faible quantité de mémoire libérable

Assurez-vous que vous disposez de suffisamment de ressources allouées à votre base de données pour exécuter vos requêtes. Avec Amazon RDS, la quantité de ressources allouée dépend du type d'instance. En outre, certaines requêtes, telles que les procédures stockées, peuvent utiliser une quantité illimitée de mémoire lors de leur exécution. Divisez vos requêtes volumineuses en requêtes de plus petite taille pour éviter les transactions trop longues.

Pour afficher toutes les connexions et requêtes actives dans votre base de données, utilisez la commande SHOW FULL PROCESSLIST. Pour plus d'informations sur SHOW FULL PROCESSLIST et des exemples de commandes, consultez la page 13.7.5.29 Instruction SHOW PROCESSLIST sur le site Web de MySQL. Si vous trouvez une requête contenant des opérations JOIN ou SORTS qui s'exécute depuis longtemps, vous devez disposer d’une quantité suffisante de RAM pour que l'optimiseur calcule le plan. Par ailleurs, si vous identifiez une requête qui nécessite une table temporaire, vous devez disposer d’une mémoire supplémentaire à allouer à cette table.

Pour afficher les transactions qui s’exécutent depuis longtemps, les statistiques d'utilisation de la mémoire ou les verrouillages, utilisez la commande SHOW ENGINE INNODB STATUS. Pour plus d'informations sur les commandes SHOW ENGINE, consultez la page 13.7.5.15 Instruction SHOW ENGINE sur le site Web de MySQL. Examinez la sortie et vérifiez l’entrée BUFFER POOL AND MEMORY pour rechercher des informations sur l'allocation de la mémoire pour InnoDB, telles que Total Memory Allocated, Internal Hash Tables et Buffer Pool Size. Le statut d'InnoDB fournit également des informations sur les verrous, les verrouillages et les blocages. Si votre charge de travail rencontre souvent des blocages, modifiez le paramètre innodb_lock_wait_timeout dans votre groupe de paramètres personnalisé. InnoDB s'appuie sur le paramètre innodb_lock_wait_timeout pour annuler les transactions en cas de blocage.

Pour optimiser les performances de la base de données, optimisez et ajustez vos requêtes. Utilisez Amazon RDS Performance Insights pour surveiller les instances de base de données et identifier les requêtes problématiques. Aussi, définissez une alarme CloudWatch sur la métrique FreeableMemory afin de recevoir une notification lorsque la mémoire disponible atteint 95 %. Il est recommandé de garder au moins 5 % de la mémoire de l'instance disponible.

Mettez régulièrement à jour la version mineure de MySQL de votre instance. Les versions mineures antérieures peuvent contenir des bogues liés à des fuites de mémoire. Pour plus d'informations sur les versions de MySQL, consultez les notes de publication de MySQL 8.0 sur le site Web de MySQL.

Informations connexes

Outils de surveillance pour Amazon RDS