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. Cela se traduit souvent par l'exécution du Killer Linux OOM, un processus utilisé par le noyau Linux lorsque le système est très insuffisant en mémoire, ce 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 reprise après plantage, qui peut prendre plusieurs minutes), ce qui a un impact sur les utilisateurs et l'activité.

• Le serveur MySQL met plus de temps à 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, tel que 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 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 d'au moins 10 Go de RAM, le processus mysqld est limité à 92% de la RAM de l'instance, et le pool de mémoire tampon 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 tueur OOM Linux ferme 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 mémoire/zone_code/nom_instrument , où zone_code est une valeur telle que SQL ou Innodb et nom_instrument 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)

Si vous activez les instruments de mémoire au moment de l'exécution, seuls les objets mémoire créés après l'activation de l'instrumentation sont pris en compte. Par conséquent, nous vous recommandons d'attendre que le serveur collecte suffisamment de données à des fins de dépannage.

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 performance_schema dans des formes facilement compréhensibles. La plupart de ces vues sont présentées par paire. 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> affiche 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)

L'affichage sans le préfixe x$ est destiné à 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 des 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 la quantité de mémoire utilisée. 

Avant d'explorer chaque type d'allocation, il est utile 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 les problèmes 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 les détails de chaque type d'allocation.

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 par 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 la mémoire par hôte client. De nos jours, il est très fréquent d'avoir plusieurs serveurs clients/applications. Grâce à cette vue, vous pouvez déterminer si un client ou une charge de travail spécifiques sont à l'origine d'une utilisation intensive de la mémoire. Vous pouvez également commencer par déployer le changement sur un client/serveur d'applications, surveiller son impact sur la mémoire (en comparant l'utilisation de la mémoire à celle d'autres hôtes clients) et prendre des décisions éclairées.

Cette vue récapitule l'utilisation de la mémoire, regroupée par thread. Elle permet de trouver les threads qui utilisent la plus grande partie de la 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 les structures MySQL internes utilisent la mémoire. 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 à trouver la source du problème, et vous pourrez y remédier en modifiant la configuration/requête. 

Par exemple :

1. Si les tableaux temporaires internes nécessitent une grande quantité de mémoire, vous pouvez soit limiter la taille des tableaux temporaires à l'aide de tmp_table_size, soit réduire la valeur innodb_buffer_pool_size afin de libérer de l'espace pour les tampons par session.
2. Si un thread particulier consomme beaucoup de mémoire, vous pouvez filtrer les journaux de 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/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 ayant un comportement similaire ont été corrigés. 
• Effectuez la mise à niveau de MySQL vers la dernière version mineure pour voir si cela résout le problème de mémoire.
• Effectuez une recherche dans la base de données de bugs MySQL pour vérifier si d'autres utilisateurs de la communauté ont signalé un problème similaire.
• Créez un bug avec un scénario de test reproductible s'il n'existe aucun bug pour le même problème.