Résoudre les problèmes de mémoire MySQL

Une condition de mémoire insuffisante (OOM) se produit lorsqu'un processus tente d'allouer de la mémoire et échoue, car le serveur n'a plus de mémoire disponible. Il en résulte souvent le robot tueur OOM Linux, un processus utilisé par le noyau Linux lorsque le système manque de mémoire critique, qui met fin au processus de base de données.

Un plantage de base de données dû à une condition OOM n'est pas idéal, car le processus de base de données est arrêté à l'aide de la directive SIGKILL, ce qui empêche l'application d'arrêter normalement MySQL. Cela entraîne les problèmes suivants :

• Le serveur MySQL est en panne (alors qu'il termine la récupération après un plantage, qui peut prendre plusieurs minutes), ce qui a un impact sur les utilisateurs et l'entreprise.
• Le serveur MySQL prend plus de temps pour préchauffer complètement les tampons et les caches afin d'atteindre des performances optimales
• Risque de corruption des données en raison d'un plantage inattendu

MySQL a ajouté une instrumentation de mémoire dans performance_schema à partir de la version 5.7, ce qui permet de mieux comprendre l'allocation de mémoire MySQL. Cet article vous aidera à résoudre les problèmes de mémoire MySQL à l'aide de performance_schema.

Chaque instance Cloud SQL est alimentée par une machine virtuelle (VM) exécutée sur un serveur hôte Google Cloud. Chaque VM exploite le programme de base de données, comme le serveur MySQL et les agents de service qui fournissent des services complémentaires, tels que la journalisation et la surveillance. Cloud SQL réserve une partie de la mémoire pour le système d'exploitation (OS) et les agents de service, et alloue de la mémoire restante aux programmes de base de données tels que mysqld.

Le tableau suivant indique l'allocation de mémoire pour le processus mysqld et la configuration par défaut du pool de mémoire tampon InnoDB pour chaque type de machine.

Pour les instances disposant de 10 Go de RAM ou plus, le processus mysqld est limité à 92 % de la RAM de l'instance, et le pool de tampons InnoDB est configuré entre 75 et 80 % de la mémoire disponible pour le processus mysqld. En plus du pool de mémoire tampon InnoDB, MySQL alloue plusieurs tampons et caches pour améliorer les performances. Veuillez consulter cet article pour en savoir plus sur l'allocation de mémoire dans MySQL.

Lorsque l'utilisation de la mémoire mysqld dépasse la limite configurée du processus mysqld, le robot tueur OOM Linux arrête le processus mysqld, car il s'agit du plus grand consommateur de mémoire de l'instance.

performance_schema est une fonctionnalité MySQL permettant de surveiller l'exécution des serveurs à un niveau inférieur. Cloud SQL pour MySQL permet d'activer performance_schema sur les instances dont la taille de RAM est supérieure ou égale à 3 Go. Elle est activée par défaut dans les instances disposant d'au moins 15 Go de RAM, à partir de la version MySQL 8.0.

Performance_schema est désactivée par défaut pour les versions 5.6 et 5.7 de MySQL. Vous pouvez l'activer à l'aide des options de base de données.

L'activation de performance_schema entraîne une surcharge des performances et des conséquences sur la mémoire. Consultez la section sur le modèle d'allocation de mémoire du schéma de performances pour comprendre son allocation de mémoire. 

L'instrumentation de mémoire performance_schema permet de surveiller l'utilisation de la mémoire dans MySQL. Les outils liés à la mémoire ont des noms ayant la forme memory/code_area/instrument_name, où code_area est une valeur telle que sql ou Innodb et instrument_name correspond au détail de l'instrument.

MySQL 5.7 active certains outils liés à la mémoire par défaut, mais pas tous. Vous pouvez les activer en mettant à jour la colonne ENABLED de la table performance_schema.setup_instruments. 

mysql> select version();

+-------------------+

| version()    |

+-------------------+

| 5.7.39-google-log |

+-------------------+

1 row in set (0.00 sec)

mysql> SELECT enabled, count(*) FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory%' group by enabled;

+---------+----------+

| enabled | count(*) |

+---------+----------+

| YES  |   70 |

| NO   |   306 |

+---------+----------+

1186 rows in set (0.03 sec)

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

Query OK, 306 rows affected (0.00 sec)

Lignes correspondantes : 376 Modifiées : 306 Avertissements : 0

mysql> SELECT enabled, count(*) FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory%' group by enabled;

+---------+----------+

| enabled | count(*) |

+---------+----------+

| YES  |   376 |

+---------+----------+

1 row in set (0.00 sec)

L'activation des instruments de mémoire au moment de l'exécution ne comptabilise que les objets mémoire créés après l'activation de l'instrumentation. Il est donc recommandé d'attendre que le serveur collecte suffisamment de données pour résoudre les problèmes.

MySQL 8.0 active tous les instruments de mémoire par défaut.

mysql> select version();

+---------------+

| version()  |

+---------------+

| 8.0.28-google |

+---------------+

1 row in set (0.00 sec)

mysql> SELECT enabled, count(*) FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory%' group by enabled;

+---------+----------+

| enabled | count(*) |

+---------+----------+

| YES  |   490 |

+---------+----------+

1 row in set (0.00 sec)

Le schéma système contient de nombreuses vues qui résument les données de performance_schema sous des formes facilement compréhensibles. La plupart de ces vues sont présentées par paires. Elles portent le même nom et l'une d'entre elles a le préfixe x$. Les vues suivantes sont disponibles dans MySQL 5.7 et 8.0 pour afficher l'allocation de mémoire à différents niveaux.

mysql> utilise sys;

Base de données modifiée

mysql> afficher des tables telles que '%memory%';

+-------------------------------------+

| Tables_in_sys (%memory%)      |

+-------------------------------------+

| memory_by_host_by_current_bytes  |

| memory_by_thread_by_current_bytes |

| memory_by_user_by_current_bytes  |

| memory_global_by_current_bytes   |

| memory_global_total        |

| x$memory_by_host_by_current_bytes |

| x$memory_by_thread_by_current_bytes |

| x$memory_by_user_by_current_bytes |

| x$memory_global_by_current_bytes  |

| x$memory_global_total       |

+-------------------------------------+

10 rows in set (0.02 sec)

La vue sys.memory_global_total résume l'utilisation totale de la mémoire dans un format lisible.

mysql> select * from sys.memory_global_total;

+-----------------+

| total_allocated |

+-----------------+

| 12.43 GiB   |

+-----------------+

1 row in set (0.44 sec)

sys.x$memory_global_total affiche les mêmes informations, mais en octets.

mysql> select * from sys.x$memory_global_total;

+-----------------+

| total_allocated |

+-----------------+

|  13351482992 |

+-----------------+

1 row in set (1.39 sec)

La vue sans le préfixe x$ est destinée à fournir une sortie plus conviviale et plus lisible. La vue avec le préfixe x$ qui affiche les mêmes valeurs au format brut est davantage destinée à être utilisée avec d'autres outils qui effectuent leur propre traitement sur les données.

La vue sys.memory_global_by_current_bytes indique l'utilisation actuelle de la mémoire, regroupée par type d'allocation (event_name), par exemple "memory/innodb/buf_buf_pool" (pool de mémoire tampon InnoDB). Par défaut, les lignes sont triées par ordre décroissant de quantité de mémoire utilisée.

Avant d'examiner en détail chaque type d'allocation, il est judicieux d'avoir un aperçu de l'utilisation de la mémoire pour chaque zone de code comme Innodb, sql, performance_schema, etc.

La requête suivante agrège la mémoire actuellement allouée par zone de code.

SELECT SUBSTRING_INDEX(event_name,'/',2) AS code_area,

FORMAT_BYTES(SUM(current_alloc)) AS current_alloc

FROM sys.x$memory_global_by_current_bytes

GROUP BY SUBSTRING_INDEX(event_name,'/',2)

ORDER BY SUM(current_alloc) DESC;

+---------------------------+---------------+

| code_area        | current_alloc |

+---------------------------+---------------+

| memory/innodb      | 11.28 GiB  |

| memory/performance_schema | 520.02 MiB  |

| memory/temptable     | 514.00 MiB  |

| memory/sql        | 106.12 MiB  |

| memory/mysys       | 43.25 MiB  |

| memory/vio        | 372.15 KiB  |

| memory/myisam      | 696 bytes  |

| memory/csv        | 88 bytes  |

| memory/blackhole     | 88 bytes  |

+---------------------------+---------------+

9 rows in set (1.02 sec)

Ce résultat montre qu'innodb est le plus grand consommateur de mémoire, suivi de performance_schema et des tables temporaires.

Désormais, l'interrogation de sys.memory_global_by_current_bytes affiche directement l'utilisation de la mémoire pour les structures internes MySQL, comme décrit dans la colonne event_name. Si vous tentez de résoudre un problème d'augmentation anormale de la mémoire, vous pouvez identifier la source en examinant le résultat de cette vue.

mysql> select event_name,current_alloc,high_alloc from sys.memory_global_by_current_bytes limit 5;

+-------------------------------+---------------+------------+

| event_name          | current_alloc | high_alloc |

+-------------------------------+---------------+------------+

| memory/innodb/buf_buf_pool  | 10.72 GiB  | 10.72 GiB |

| memory/temptable/physical_ram | 514.00 MiB  | 515.00 MiB |

| memory/innodb/hash0hash   | 292.69 MiB  | 292.69 MiB |

| memory/innodb/memory     | 191.77 MiB  | 197.94 MiB |

| memory/sql/TABLE       | 60.39 MiB  | 62.35 MiB |

+-------------------------------+---------------+------------+

5 rows in set (0.45 sec)

Vous trouverez ci-dessous des informations détaillées sur chaque type de répartition.

Cette vue récapitule l'utilisation de la mémoire regroupée par utilisateur. Si un utilisateur dispose de plusieurs connexions, elle additionne l'utilisation de la mémoire pour toutes les instances de l'utilisateur.

mysql> select user,current_allocated from memory_by_user_by_current_bytes;

+-----------------+-------------------+

| user      | current_allocated |

+-----------------+-------------------+

| sbtest     | 60.30 MiB    |

| background   | 2.68 MiB     |

| root      | 1.52 MiB     |

| event_scheduler | 16.38 KiB    |

+-----------------+-------------------+

4 rows in set (1.16 sec)

Si vous utilisez un utilisateur différent pour chaque application ou tâche (recommandé), cette vue peut vous aider à déterminer si une application ou une tâche en particulier consomme beaucoup de mémoire.

Cette vue récapitule l'utilisation de mémoire regroupée en fonction du nom d'hôte.

mysql> select host,current_allocated from memory_by_host_by_current_bytes;

+------------+-------------------+

| host   | current_allocated |

+------------+-------------------+

| 10.128.0.8 | 62.10 MiB    |

| background | 2.70 MiB     |

| 127.0.0.1 | 311.01 KiB    |

| localhost | 16.38 KiB    |

+------------+-------------------+

4 rows in set (1.58 sec)

Cette vue est utile pour identifier l'utilisation de mémoire par hôte client. De nos jours, il est très courant d'avoir plusieurs clients/serveurs d'applications. Cette vue vous aidera à déterminer si un client ou une charge de travail spécifique entraîne une utilisation élevée de la mémoire. Vous pouvez également déployer le changement en premier sur un client/serveur d'applications, surveiller son impact sur la mémoire (en comparant son utilisation à celle d'autres hôtes clients) et prendre des décisions éclairées.

Cette vue récapitule l'utilisation de la mémoire, regroupées par thread. Elle permet de trouver les threads qui occupent le plus de mémoire.

mysql> select thread_id,user,current_allocated from sys.memory_by_thread_by_current_bytes limit 5;

+-----------+-------------------+-------------------+

| thread_id | user       | current_allocated |

+-----------+-------------------+-------------------+

|  12999 | sbtest@10.128.0.8 | 3.80 MiB     |

|  12888 | sbtest@10.128.0.8 | 3.75 MiB     |

|  12760 | sbtest@10.128.0.8 | 3.68 MiB     |

|  13128 | sbtest@10.128.0.8 | 3.67 MiB     |

|  13221 | sbtest@10.128.0.8 | 3.47 MiB     |

+-----------+-------------------+-------------------+

5 rows in set (2.29 sec)

Si vous constatez qu'un thread particulier consomme beaucoup de mémoire, vous pouvez filtrer les journaux des requêtes lentes MySQL ou les journaux généraux à l'aide de thread_id et identifier la ou les sessions/requêtes qui ont entraîné la croissance de la mémoire.

Le dépannage d'une utilisation de mémoire élevée n'est pas une mince affaire. Heureusement, l'instrumentation de mémoire performance_schema aide les utilisateurs à comprendre comment la mémoire est utilisée par les structures MySQL internes. Les vues de schéma système facilitent l'interprétation des informations. Si vous rencontrez des problèmes liés à la mémoire, cette fonctionnalité vous aidera à en trouver la source. Vous pourrez alors y remédier en modifiant la configuration ou la requête.

Exemple :

1. Si les tables temporaires internes nécessitent une grande quantité de mémoire, vous pouvez soit limiter la taille des tables temporaires à l'aide de tmp_table_size, soit réduire la valeur innodb_buffer_pool_size pour faire de la place aux tampons par session.
2. Si un thread particulier consomme beaucoup de mémoire, vous pouvez filtrer les journaux des requêtes lentes à l'aide de thread_id pour identifier les requêtes problématiques et les ajuster afin de réduire l'espace mémoire utilisé par ce thread/cette session.

Imaginons qu'une instance MySQL utilise une quantité de mémoire supérieure à ce qu'elle devrait et que cette utilisation augmente continuellement jusqu'à ce que le serveur soit à court de mémoire. Dans ce cas, le champ performance_schema affiche un écart entre la mémoire totale et la mémoire instrumentée ou ne vous indique pas où va la mémoire, ce qui pourrait être le signe d'une fuite de mémoire. Si vous suspectez une fuite de mémoire, vous pouvez prendre les mesures suivantes :

• Examinez attentivement la configuration MySQL et vérifiez qu'aucun tampon/cache n'est suralloué. Cet article vous aidera à comprendre comment MySQL alloue de la mémoire pour différents tampons/caches.
• Identifiez la fonctionnalité, la configuration ou la requête à l'origine du problème de mémoire, puis essayez de reproduire le problème.
• Consultez les notes de version MySQL des prochaines versions mineures de la même série pour vérifier si des bugs avec un comportement similaire ont été corrigés.
• Mettez à niveau MySQL vers la dernière version mineure pour voir si le problème de mémoire est résolu.
• Parcourez la base de données de bugs MySQL pour vérifier si des problèmes similaires ont été signalés par d'autres utilisateurs de la communauté.
• Créez un bug avec un scénario de test reproductible s'il n'existe aucun bug pour le même problème.