Utilisation de la mémoire par MySQL

MySQL alloue des tampons globaux au démarrage du serveur. Ceux-ci sont partagés entre toutes les connexions. La majorité de la mémoire de MySQL est utilisée par les tampons globaux, par exemple innodb_buffer_pool_size, innodb_log_buffer_size, key_buffer_size, etc.

Le pool de mémoire tampon InnoDB est généralement le plus gros consommateur de mémoire dans une instance MySQL. Il est configuré à l'aide du paramètre innodb_buffer_pool_size. Il sert à mettre en cache les données et les index de table, le tampon de modification, l'index de hachage adaptatif et d'autres structures internes. Cloud SQL pour MySQL, l'offre MySQL gérée de Google Cloud, configure innodb_buffer_pool_size pour utiliser jusqu'à 72 % de la mémoire de l'instance suivant la taille de l'instance. 

InnoDB réserve de la mémoire supplémentaire pour les tampons et les structures de données associées. La mémoire totale allouée est environ 10 % plus importante que la taille du pool de mémoire tampon spécifiée. Vous pouvez vérifier l'utilisation de la mémoire par le pool de mémoire tampon InnoDB dans la sortie de la commande "show engine innodb status\G".

mysql> show engine innodb status\G

…

----------------------

BUFFER POOL AND MEMORY

----------------------

Total large memory allocated 11511349248

…

Le tampon du journal InnoDB sert à contenir les modifications à écrire dans les fichiers journaux de rétablissement InnoDB sur le disque. Il est configuré à l'aide du paramètre innodb_log_buffer_size. La valeur par défaut dans la version communautaire de MySQL est de 16 Mo, et Cloud SQL pour MySQL utilise la même valeur.

Le tampon de clé permet à MySQL de mettre en cache en mémoire les index MyISAM. Il est configuré à l'aide du paramètre key_buffer_size. La valeur par défaut dans la version communautaire de MySQL est de 8 Mo, et Cloud SQL pour MySQL utilise la même valeur. Cloud SQL pour MySQL n'étant pas compatible avec les tables MyISAM, vous pouvez conserver la valeur par défaut.

La variable de configuration query_cache_size définit la quantité de mémoire allouée pour la mise en cache des résultats de requête. Elle est désactivée par défaut dans la version communautaire de MySQL 5.7 et dans Cloud SQL pour MySQL 5.7.

Le cache de requêtes présentait de graves problèmes d'évolutivité connus. Il a donc été déclaré obsolète dans MySQL 5.7.20 et supprimé dans MySQL 8.0. Si vous l'utilisez toujours pour votre instance MySQL 5.7, veuillez vérifier s'il est vraiment utile pour votre charge de travail. Pour en savoir plus, consultez cet article de blog. 

MySQL alloue des caches globaux partagés entre toutes les connexions, qui sont alloués de manière dynamique et dont les limites maximales sont définies par des variables de configuration.

MySQL utilise le cache des tables pour accélérer l'ouverture des tables. Le cache des tables MySQL est divisé en deux parties : un cache des tables ouvertes et un cache des définitions de tables, configurés respectivement à l'aide des paramètres table_open_cache et table_definition_cache.

table_open_cache est un cache mémoire servant à stocker les descripteurs de fichier des tables ouvertes par tous les threads connectés. Augmenter cette valeur accroît le nombre de descripteurs de fichiers requis par le programme mysqld (c'est-à-dire le serveur MySQL). Assurez-vous que votre système d'exploitation peut gérer le nombre de descripteurs de fichier ouverts impliqués par le paramètre table_open_cache.

Plusieurs sessions client peuvent accéder simultanément à une table donnée, et cette table est ouverte indépendamment par chaque session client simultanée. C'est pourquoi le nombre de tables ouvertes peut être supérieur au nombre de tables hébergées sur le serveur. Lorsque le cache des tables est saturé, le serveur libère les tables qui ne sont actuellement pas utilisées, en commençant par celle utilisée le moins récemment.

table_definition_cache est un cache mémoire servant à stocker les définitions de table. Il est global et partagé entre toutes les connexions.

Cloud SQL pour MySQL 5.7 utilise comme valeurs par défaut respectives 2 000 et 1 400 pour table_open_cache et table_definition_cache.

Cloud SQL pour MySQL 8.0 utilise comme valeurs par défaut respectives 4 000 et 2 000 pour table_open_cache et table_definition_cache.

Pour chaque connexion client, MySQL attribue un thread dédié qui exécute toutes les requêtes et renvoie les résultats au client, et ce jusqu'à la déconnexion de ce dernier. MySQL met en cache les threads afin d'éviter d'avoir à créer et détruire des threads pour chaque connexion. Le nombre de threads dans le cache de threads est configuré à l'aide de la variable thread_cache_size.

Cloud SQL pour MySQL utilise 48 comme valeur par défaut pour thread_cache_size.

InnoDB dispose de son propre cache pour le stockage des définitions de table, qui diffère des caches des tables ouvertes et des définitions de tables. Vous pouvez vérifier la mémoire allouée au dictionnaire de données InnoDB dans la sortie de la commande "show engine innodb status\G".

----------------------

BUFFER POOL AND MEMORY

----------------------

…

Dictionary memory allocated 65816817

Le paramètre table_definition_cache définit une limite flexible sur le nombre d'instances de table dans le cache du dictionnaire de données InnoDB : si le nombre d'instances de table dans le cache du dictionnaire de données InnoDB dépasse la limite table_definition_cache, le mécanisme LRU commence à marquer les instances de table en vue de leur éviction, puis finit par les supprimer de ce cache. 

Cela signifie-t-il que le nombre d'instances de table dans le cache du dictionnaire de données InnoDB sera toujours inférieur à la limite table_definition_cache ? Ce n'est pas le cas. Les instances de table dotées de relations de clé étrangère ne sont pas placées dans la liste LRU. Elles restent en cache et peuvent causer un dépassement de la limite table_definition_cache, ce qui entraîne une utilisation de mémoire supplémentaire. La mémoire consommée par les tables dotées de relations de clé étrangère n'est libérée que lors d'un événement d'arrêt/redémarrage MySQL. Ce problème existe dans MySQL 5.7 et 8.0, et c'est un bug connu et validé.

Si votre instance MySQL comporte un grand nombre de tables dotées de relations de clés étrangères, le cache du dictionnaire de données InnoDB peut consommer plusieurs gigaoctets de mémoire. Cet aspect est souvent négligé lors de la configuration des tampons/caches de MySQL, et cela peut être l'une des raisons expliquant des problèmes inattendus d'utilisation ou de saturation de la mémoire.

Exemple de résultat illustrant une petite instance qui consomme 4,16 Go pour le cache du dictionnaire de données InnoDB.

$ mysql -e "show engine innodb status\G" | grep -i memory

BUFFER POOL AND MEMORY

Total large memory allocated 7696023552

Dictionary memory allocated 4465193358

Les tampons de session sont une autre fonctionnalité de MySQL qui consomme de la mémoire. Ces tampons sont alloués par session et, dans certains cas, plusieurs instances de ces tampons peuvent être allouées à une même requête (join_buffer_size, en particulier).

• sort_buffer_size 
• join_buffer_size
• read_buffer_size
• read_rnd_buffer_size

Ces tampons ne sont alloués que lorsqu'une requête en a besoin (pour le tri, les jointures, les analyses de table/index complets, etc.). Lorsqu'ils s'avèrent nécessaires, leur taille réelle totale est allouée, même si en réalité, seule une faible portion du tampon est nécessaire. Configurer ces tampons avec une valeur élevée peut donc entraîner un gaspillage de mémoire.

Les valeurs par défaut sont les mêmes dans la version communautaire de MySQL et dans Cloud SQL pour MySQL.

MySQL utilise le cache du journal binaire pour conserver les modifications apportées au journal binaire pendant l'exécution d'une transaction. Il est configuré à l'aide du paramètre binlog_cache_size. Ce cache est alloué à chaque client si la journalisation binaire est activée (log_bin=ON).

La valeur par défaut de binlog_cache_size est identique dans la version communautaire de MySQL et dans Cloud SQL pour MySQL.

MySQL crée des tables temporaires internes pour stocker les résultats intermédiaires lors du traitement de certains types de requêtes, tels que "GROUP BY", "ORDER BY", "DISTINCT" et "UNION". Ces tables temporaires internes sont d'abord créées en mémoire, puis converties en tables sur disque lorsque la taille maximale est atteinte. La taille maximale des tables temporaires internes est déterminée comme étant le minimum des deux variables tmp_table_size et max_heap_table_size. 

Les valeurs par défaut de tmp_table_size et de max_heap_table_size sont identiques dans la version communautaire de MySQL et dans Cloud SQL pour MySQL.

Remarque : Étant donné que les tampons par session et les tables temporaires en mémoire allouent de la mémoire séparément pour chaque connexion, l'utilisation globale de la mémoire peut être très élevée si un grand nombre de connexions en ont besoin. Il est recommandé de ne pas définir des valeurs trop élevées. Effectuez des tests pour trouver la valeur la mieux adaptée pour votre charge de travail.

Chaque thread nécessite peu de mémoire pour gérer la connexion client. Les variables suivantes contrôlent leur taille.

• thread_stack : la taille de la pile pour chaque thread, qui est de 256 Ko par défaut.
• net_buffer_length : chaque client est associé à un tampon de connexion et un tampon de résultat de longueur net_buffer_length. Ce nombre peut croître au plus jusqu'à la taille de max_allowed_packet.

Si la fonctionnalité performance_schema est activée, elle permet de surveiller l'exécution du serveur MySQL à un niveau inférieur. Performance_schema alloue de la mémoire de manière dynamique et n'est libérée qu'au moment d'un arrêt/redémarrage de MySQL.

Cloud SQL pour MySQL permet d'activer performance_schema sur les instances disposant de 15 Go de RAM ou plus. Elle est activée par défaut à partir de la version MySQL 8.0.26. Performance_schema est désactivée par défaut pour les versions 5.6, 5.7 et 8.0.18 de MySQL. Vous pouvez l'activer à l'aide des options de base de données.

Cloud SQL pour MySQL configure automatiquement les paramètres liés à la mémoire pour obtenir de bonnes performances dans une base de données prête à l'emploi. Votre instance peut toujours rencontrer des problèmes de saturation de mémoire si la charge de travail nécessite des caches plus volumineux pour accepter un grand nombre de tables et/ou de connexions. Un grand nombre de threads allouant des tampons de session simultanément peut également entraîner des problèmes de mémoire. Plus la mémoire allouée aux tampons/caches globaux est importante, moins il y a de mémoire disponible pour les connexions, les tampons de session et inversement : trouver un équilibre est la clé d'un bon fonctionnement.

Si votre charge de travail nécessite des caches ou des tampons de session plus importants, vous pouvez réduire la taille du tampon global via innodb_buffer_pool_size. Vous pouvez modifier les paramètres de configuration de vos instances Cloud SQL pour MySQL à l'aide des options de base de données. Si vous rencontrez toujours des problèmes de performances ou de saturation de mémoire, vous pouvez augmenter la taille de la mémoire en mettant à niveau l'instance.