Comment résoudre les problèmes liés à un manque de mémoire libérable dans une base de données Amazon RDS for MySQL ?

Brève description

Dans Amazon RDS for MySQL, vous pouvez contrôler quatre états de la mémoire :

• Actif : mémoire activement consommée par des processus ou des threads de base de données.
• Tampon : un tampon est un espace temporaire en mémoire utilisé pour contenir un bloc de données.
• Mémoire libre : mémoire disponible pour utilisation.
• Cache : la mise en cache est une technique qui consiste à stocker temporairement des données en mémoire afin d’en assurer une récupération rapide.

Par défaut, lorsque vous créez une instance Amazon RDS for MySQL, des tampons et des caches sont alloués pour améliorer les opérations de base de données. Amazon RDS for MySQL dispose également d'un composant mémoire interne (comme key_buffers_size ou query_cache_size) qui crée des tables temporaires internes pour effectuer certaines opérations.

Lorsque vous utilisez Amazon RDS for MySQL, veillez à comprendre comment MySQL utilise et alloue la mémoire. Une fois que vous avez identifié les composants qui utilisent la mémoire, vous pouvez rechercher les goulots d'étranglement au niveau de l'instance et de la base de données. Ensuite, contrôlez ces métriques spécifiques et configurez vos sessions pour obtenir des performances optimales.

Solution

Utilisation de la mémoire par MySQL

Dans Amazon RDS for MySql, 80 % à 90 % de la mémoire disponible sur une instance sont alloués avec les paramètres par défaut. Cette allocation est optimale pour les performances, mais si vous définissez des paramètres qui utilisent plus de mémoire, vous devez alors modifier d’autres paramètres pour qu’ils utilisent moins de mémoire afin de compenser.

Vous pouvez calculer l'utilisation approximative de la mémoire par votre instance de base de données RDS for MySQL comme suit :

Maximum MySQL Memory Usage = innodb_buffer_pool_size + key_buffer_size + ((read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size) X max_connections)

Groupe de tampons

Les tampons et caches globaux incluent des composants tels que Innodb_buffer_pool_size, Innodb_log_buffer_size, key_buffer_size et query_cache_size. Le paramètre innodb_buffer_pool_size est la zone mémoire de la RAM où innodb met en cache les tables de base de données et les données associées à l'index. Un pool de tampons plus volumineux implique la réorientation d’un moins grand nombre d'opérations d'I/O 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}

Pensez à vérifier ce paramètre d'abord pour identifier la source de l'utilisation de la mémoire. Ensuite, envisagez de réduire la valeur de innodb_buffer_pool_size en modifiant la valeur du paramètre dans votre groupe de paramètres personnalisé.

Par exemple, la valeur par défaut DBInstanceClassMemory*3/4 peut être réduite à *5/8 ou *1/2. Assurez-vous que la valeur BufferCacheHitRatio de l'instance n'est pas trop basse. Si la valeur BufferCacheHitRatio est basse, il risque d’être nécessaire d'augmenter la taille de l'instance afin de disposer de plus de RAM. Pour plus d'informations, consultez Bonnes pratiques de configuration des paramètres pour Amazon RDS for MySQL, partie 1 : paramètres liés aux performances.

Threads MySQL

De la mémoire est également allouée pour chaque thread MySQL connecté à une instance de base de données MySQL. Les threads suivants nécessitent de la 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

De plus, MySQL crée des tables temporaires internes pour effectuer certaines opérations. Ces tables sont créées initialement en tant que tables basées sur la mémoire. Lorsque ces tables atteignent la taille spécifiée par tmp_table_size ou max_heap_table_size (selon le paramètre qui a la valeur la plus basse), la table est convertie en table basée sur un disque. Lorsque plusieurs sessions créent des tables temporaires internes, il est possible que vous constatiez une augmentation de l'utilisation de la mémoire. Pour réduire l'utilisation de la mémoire, évitez d'utiliser des tables temporaires dans les requêtes.

Remarque : lorsque vous augmentez les limites tmp_table_size et max_heap_table_size, les plus grandes tables temporaires peuvent résider en mémoire. Pour vérifier si une table temporaire implicite a été créée, utilisez la variable created_tmp_tables. Pour plus d'informations sur cette variable, consultez created_tmp_tables sur le site Web MySQL.

Opérations JOIN et SORT

L'utilisation de la mémoire augmentera si plusieurs tampons de même type, comme join_buffer_size ou sort_buffer_size, sont alloués lorsque la session exécute des opérations JOIN (JOINDRE) ou SORT (TRIER). Par exemple, MySQL alloue un tampon JOIN pour exécuter une opération JOIN entre deux tables. Si une requête implique des opérations JOIN sur plusieurs tables et que toutes les requêtes nécessitent un tampon JOIN, MySQL alloue un tampon JOIN de moins que le nombre total de tables. La définition des variables de session avec une valeur trop élevée peut entraîner des problèmes si les requêtes ne sont pas optimisées. Vous pouvez allouer la mémoire minimale aux variables au niveau de la session, telles que join_buffer_size et sort_buffer_size. Pour plus d'informations, consultez Utilisation des groupes de paramètres de base de données.

Si vous effectuez des insertions en bloc pour les tables MYISAM, bulk_insert_buffer_size octets de mémoire sont utilisés. Pour plus d'informations, consultez Bonnes pratiques d'utilisation des moteurs de stockage MySQL.

Le schéma des performances

De la mémoire peut être consommée par le schéma des performances si vous avez activé le schéma des performances pour Performance Insights sur Amazon RDS for MySQL. Lorsque le schéma des performances est activé, MySQL alloue des tampons internes au démarrage de l'instance et pendant les opérations de serveur. Pour plus d'informations sur la façon dont le schéma des performances utilise la mémoire, consultez la section Modèle d'allocation de mémoire du schéma des performances dans la documentation MySQL.

En plus des tables du schéma des performances, vous pouvez également utiliser le schéma sys MySQL. Par exemple, vous pouvez utiliser l'événement performance_schema pour afficher la quantité de mémoire allouée pour les tampons internes utilisés par le schéma des performances. Vous pouvez une requête comme celle-ci pour déterminer 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 sont répertoriés dans la table setup_instruments, selon un format « mémire/code_zone/nom_instrument ». Pour activer l'instrumentalisation de la mémoire, mettez à jour la colonne ENABLED (ACTIVÉ) des instruments appropriés dans la table setup_instruments :

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

Surveillance de l'utilisation de la mémoire sur votre instance

Métriques Amazon CloudWatch

Contrôlez les métriques Amazon CloudWatch pour DatabaseConnections, CPUUtilization, ReadIOPS et WriteIOPS lorsque la quantité de mémoire disponible est faible.

Pour DatabaseConnections, il est important de noter que chaque connexion établie à la base de données nécessite l’allocation d’une certaine quantité de mémoire. De ce fait, un pic de connexions à la base de données peut entraîner une diminution de la mémoire libre. Dans Amazon RDS, la limite logicielle pour max_connections se calcule comme suit :

{DBInstanceClassMemory/12582880}

Contrôlez le dépassement de cette limite logicielle en vérifiant la métrique DatabaseConnections dans Amazon CloudWatch.

En outre, contrôlez la forte sollicitation de la mémoire en surveillant les métriques CloudWatch pour SwapUsage en plus de FreeableMemory. Si vous constatez qu'une grande quantité de l'espace d'échange est utilisé et que la quantité de mémoire libérable (FreeableMemory) est faible, il se peut que l'instance de base de données subisse une forte sollicitation de la mémoire. Une forte sollicitation de la mémoire affecte les performances de la base de données. Il est recommandé de maintenir les niveaux de sollicitation de la mémoire en dessous de 95 %. Pour plus d'informations, consultez Pourquoi mon instance Amazon RDS utilise-t-elle l'espace d'échange alors que je dispose d'une quantité de mémoire suffisante ?

Surveillance améliorée

Pour contrôler l'utilisation des ressources sur une instance de base de données, activez Enhanced Monitoring (surveillance améliorée). Ensuite, définissez une granularité d'une ou de cinq secondes (la valeur par défaut est de 60 secondes). Avec la surveillance améliorée, vous pouvez contrôler en temps réel la mémoire libérable et la mémoire active.

Vous pouvez également contrôler les threads qui consomment un maximum de CPU et de mémoire en répertoriant les threads de votre instance de base de données :

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

Ensuite, mappez Thread_OS_ID sur 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 from EM processlist>;

Résolution des problèmes d’insuffisance de mémoire libérable

Si vous rencontrez des problèmes d’insuffisance de mémoire libérable, tenez compte des conseils de dépannage suivants :

• Assurez-vous que suffisamment de ressources sont allouées à votre base de données pour exécuter vos requêtes. Avec Amazon RDS, la quantité de ressources allouées dépend du type d'instance. De plus, certaines requêtes, telles que les procédures stockées, peuvent prendre une quantité illimitée de mémoire à l'exécution.
• Évitez les transactions à longue exécution en décomposant les requêtes volumineuses en requêtes plus petites.
• Pour voir toutes les connexions et requêtes actives dans votre base de données, utilisez la commande SHOW FULL PROCESSLIST (AFFICHER LA LISTE COMPLÈTE DES PROCESSUS). Si vous observez une requête à longue exécution avec des opérations JOIN ou SORTS, vous devez disposer de suffisamment de RAM pour que l'optimiseur puisse calculer le plan. De même, si vous identifiez une requête nécessitant une table temporaire, vous devez disposer de mémoire supplémentaire à allouer à cette table.
• Pour afficher les transactions à longue exécution, les statistiques d'utilisation de la mémoire et les verrous, utilisez la commande SHOW ENGINE INNODB STATUS (AFFICHER LE STATUT INNODB DU MOTEUR). Examinez la sortie et vérifiez les entrées BUFFER POOL AND MEMORY (Pool de tampons et mémoire). L'entrée BUFFER POOL AND MEMORY fournit des informations sur l'allocation de mémoire pour InnoDB, telles que « Mémoire totale allouée », « Tables de hachage internes » et « Taille du pool de tampons ». Le statut InnoDB permet également de fournir des informations supplémentaires concernant les verrous et les blocages.
• Si votre charge de travail connaît 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, assurez-vous que vos requêtes sont correctement réglées. À défaut, vous risquez de rencontrer des problèmes de performances et de longs temps d'attente.
• Utilisez Amazon RDS Performance Insights pour vous aider à contrôler les instances de base de données et à identifier les requêtes qui posent problème.
• Contrôlez les métriques Amazon CloudWatch, telles que l'utilisation du processeur, les IOPS, la mémoire et l'utilisation de l’espace d’échange, pour que l'instance ne soit pas ralentie.
• Définissez une alarme CloudWatch sur la métrique FreeableMemory pour recevoir une notification lorsque la mémoire disponible atteint 95 %. Il est recommandé de maintenir au moins 5 % de la mémoire de l'instance libre.
• Mettez régulièrement à niveau votre instance vers une version mineure plus récente de MySQL. Les versions mineures plus anciennes sont plus susceptibles de contenir des bogues liés aux fuites de mémoire.

---

Informations connexes

Présentation de la surveillance Amazon RDS

Pourquoi mon instance de base de données Amazon RDS utilise la mémoire d'échange alors que je dispose d'assez de mémoire ?