MySQLTuner recommande de réduire la consommation globale de mémoire MySQL pour une meilleure stabilité du système : des solutions simples

Lorsque MySQLTuner recommande de « réduire votre empreinte mémoire MySQL globale pour une stabilité du système », cela signifie que MySQL consomme trop de mémoire, pouvant ainsi affecter la stabilité de votre système. Afin d’améliorer les performances et d’éviter les problèmes de stabilité, plusieurs stratégies peuvent être mises en œuvre.

1. Vérifier l’utilisation actuelle de la mémoire
Avant d’effectuer des changements, il est essentiel de vérifier combien de mémoire MySQL utilise et comment il est configuré. Vous pouvez exécuter les commandes suivantes dans MySQL pour obtenir des informations précises :

SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'table_open_cache';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'query_cache_size';
SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';
SHOW VARIABLES LIKE 'sort_buffer_size';
SHOW VARIABLES LIKE 'join_buffer_size';
SHOW VARIABLES LIKE 'key_buffer_size';
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';

2. Réduire max_connections si nécessaire
La variable max_connections définit le nombre de connexions simultanées que MySQL peut gérer. Si cette valeur est trop élevée, elle peut entraîner une consommation excessive de mémoire. Si le nombre maximum de connexions utilisées (Max_used_connections) est nettement inférieur à la limite fixée, envisagez de la réduire.

Pour vérifier l’utilisation des connexions maximales :

SHOW GLOBAL STATUS LIKE 'Max_used_connections';

Si Max_used_connections est, par exemple, 30, mais que max_connections est fixé à 500, il y a un excès d’allocation de mémoire.

✅ Solution : Réduisez la valeur de max_connections dans le fichier my.cnf :

[mysqld]
max_connections = 100

Vous pouvez également l’ajuster en temps réel sans redémarrer MySQL :

SET GLOBAL max_connections = 100;

3. Ajuster innodb_buffer_pool_size
Ce paramètre contrôle la quantité de mémoire utilisée par InnoDB pour le stockage en cache des données. En général, cela devrait représenter environ 60-70% de la mémoire RAM disponible sur un serveur dédié à MySQL. Vous pouvez voir la valeur actuelle :

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Si MySQL consomme trop de mémoire, il est possible de réduire ce paramètre dans my.cnf :

[mysqld]
innodb_buffer_pool_size = 2G  # Ajuster selon la taille de la base de données

Pour les serveurs avec plusieurs petites bases de données, envisagez de diviser le pool en plusieurs instances :

innodb_buffer_pool_instances = 2

4. Ajuster tmp_table_size et max_heap_table_size
Quand MySQL gère de grandes tables temporaires, il stocke les données en mémoire jusqu’à atteindre les limites définies par tmp_table_size et max_heap_table_size. Si ces variables sont trop élevées, elles peuvent accroître la consommation de mémoire.

Pour voir les valeurs actuelles :

SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';

Si elles sont trop hautes (par exemple, 256M ou plus), réduisez-les dans my.cnf :

[mysqld]
tmp_table_size = 64M
max_heap_table_size = 64M

Vous pouvez également appliquer temporairement ces réglages :

SET GLOBAL tmp_table_size = 64M;
SET GLOBAL max_heap_table_size = 64M;

5. Ajuster table_open_cache et table_definition_cache
Si trop de tables sont ouvertes, MySQL pourrait consommer plus de mémoire que nécessaire.

Pour vérifier la configuration actuelle :

SHOW VARIABLES LIKE 'table_open_cache';
SHOW VARIABLES LIKE 'table_definition_cache';

Réduisez les valeurs si elles sont trop élevées :

[mysqld]
table_open_cache = 2000
table_definition_cache = 1500

Vous pouvez appliquer ces changements en temps réel :

SET GLOBAL table_open_cache = 2000;
SET GLOBAL table_definition_cache = 1500;

6. Ajuster les buffers de connexion (sort_buffer_size, join_buffer_size)
Les buffers de tri et de jointure peuvent consommer une quantité significative de mémoire, surtout en cas de nombreuses connexions actives.

Pour voir les valeurs actuelles :

SHOW VARIABLES LIKE 'sort_buffer_size';
SHOW VARIABLES LIKE 'join_buffer_size';

Réduisez-les si elles sont trop élevées :

[mysqld]
sort_buffer_size = 2M
join_buffer_size = 2M

Vous pouvez également appliquer ces ajustements en temps réel :

SET GLOBAL sort_buffer_size = 2M;
SET GLOBAL join_buffer_size = 2M;

7. Examiner query_cache_size (si utilisé)
Le query_cache_size peut influencer les performances de MySQL et générer une utilisation élevée de la mémoire s’il est activé dans des environnements à forte écriture.

Vérifiez si la mise en cache est activée :

SHOW VARIABLES LIKE 'query_cache_type';
SHOW VARIABLES LIKE 'query_cache_size';

S’il est activé (query_cache_type = ON) et que de nombreuses écritures sont effectuées dans la base de données, il est conseillé de le désactiver :

[mysqld]
query_cache_type = OFF
query_cache_size = 0

Appliquez le changement en temps réel :

SET GLOBAL query_cache_type = OFF;
SET GLOBAL query_cache_size = 0;

8. Surveiller l’utilisation de la mémoire en temps réel
Pour vérifier la mémoire utilisée par MySQL en temps réel, vous pouvez exécuter :

SELECT * FROM sys.memory_global_total;

Ou sous Linux :

ps aux | grep mysqld

Pour une vue plus détaillée, utilisez MySQLTuner ou MyTop :

mysqltuner
mytop -u root -p

Conclusion
La réduction de la consommation de mémoire de MySQL est cruciale pour améliorer la stabilité du système et éviter les problèmes de performance. Les principaux ajustements à envisager incluent :

✅ Réduire max_connections en cas d’excès de connexions non utilisées.
✅ Ajuster innodb_buffer_pool_size afin d’éviter de surcharger la RAM.
✅ Limiter tmp_table_size et max_heap_table_size pour éviter que MySQL n’utilise trop de mémoire pour des tables temporaires.
✅ Optimiser table_open_cache, table_definition_cache et query_cache_size.
✅ Ajuster les buffers de connexion sort_buffer_size et join_buffer_size pour réduire les consommations inutiles.

En mettant en œuvre ces changements et en surveillant régulièrement l’utilisation de la mémoire, vous pourrez optimiser les performances et la stabilité de MySQL. 🚀