Tuning MySQL my.cnf : guide des réglages de performance

Sur un serveur de jeu ou une application web très sollicitée, le goulot d'étranglement n'est souvent pas MySQL lui-même, mais le fait qu'il tourne encore avec ses réglages par défaut. Optimiser votre fichier MySQL my.cnf est le gain de performance le moins cher que vous puissiez obtenir sans acheter de nouveau matériel. Dans ce guide, je passe en revue les paramètres qui font réellement la différence — du buffer pool InnoDB aux limites de connexions, en passant par les logs et le cache de requêtes — en expliquant pourquoi chacun compte.

Où se trouve my.cnf et comment fonctionne-t-il ?

MySQL (et MariaDB) lisent leur configuration depuis plusieurs fichiers au démarrage. Sous Linux, les chemins les plus courants sont :

• /etc/my.cnf
• /etc/mysql/my.cnf
• /etc/mysql/mysql.conf.d/mysqld.cnf (Debian/Ubuntu)
• /etc/my.cnf.d/*.cnf (CentOS/Alma/Rocky)

Pour voir quels fichiers sont lus et dans quel ordre, exécutez :

mysqld --verbose --help | grep -A1 "Default options"

Les réglages se placent sous la section [mysqld]. Après chaque modification, vous devez redémarrer le service :

sudo systemctl restart mysql   # ou mariadb

Buffer pool InnoDB : le réglage le plus critique

Si vous ne pouviez régler qu'un seul paramètre, ce serait innodb_buffer_pool_size. InnoDB conserve les données et les index dans cette zone mémoire ; plus elle est grande, plus les lectures viennent de la RAM plutôt que du disque. La règle de base est 50 à 70 % de la RAM totale sur un serveur de base de données dédié. Si un serveur de jeu, PHP ou d'autres services partagent la même machine, soyez plus prudent.

[mysqld]
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4

Pour les grands pools (au-delà de 8 Go), vous pouvez répartir le pool en plusieurs instances afin de réduire la contention de verrous interne ; chaque instance devrait idéalement faire au moins 1 Go. Pour vérifier si le pool est suffisant, regardez le taux de succès :

SHOW ENGINE INNODB STATUS\G

Si le Buffer pool hit rate affiché est proche de 1000/1000, presque toutes les lectures viennent de la mémoire. S'il est bas, envisagez d'agrandir le pool.

Logs InnoDB et comportement d'écriture sur disque

Sur des charges fortement en écriture, la taille du redo log et la politique de flush sont décisives. Si innodb_log_file_size (sous MySQL 8, cela peut aussi se gérer via innodb_redo_log_capacity) est trop petit, MySQL effectue des checkpoints en permanence et les écritures ralentissent.

innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT

• innodb_flush_log_at_trx_commit = 1 offre une sécurité ACID complète ; chaque commit est écrit sur le disque. À conserver pour des données financières.
• La valeur 2 écrit le log sur disque une fois par seconde ; vous gagnez un débit d'écriture important au risque de perdre la dernière ~1 seconde lors d'une coupure de courant. Pour des données non critiques comme l'inventaire d'un jeu, c'est un compromis raisonnable.
• O_DIRECT contourne le cache du système d'exploitation et évite la double mise en cache.

Réglages de connexion : max_connections et le cache de threads

Quand de nombreux joueurs ou requêtes web arrivent simultanément et que max_connections est trop bas, vous obtenez l'erreur « Too many connections ». Mais l'augmenter aveuglément est tout aussi risqué : chaque connexion consomme de la mémoire.

max_connections = 200
thread_cache_size = 32
wait_timeout = 120
interactive_timeout = 120

thread_cache_size réutilise les threads des connexions fermées, ce qui réduit le coût d'ouverture d'une nouvelle connexion. wait_timeout ferme les connexions inactives afin que des clients bloqués ne monopolisent pas un emplacement indéfiniment. Si votre application utilise un pool de connexions (par exemple les connexions persistantes de Laravel), équilibrez le timeout en conséquence.

Cache de requêtes et différences de version

Attention à query_cache_size, qui apparaît dans beaucoup d'anciens tutoriels : le query cache était désactivé par défaut dans MySQL 5.7 et a été entièrement supprimé dans MySQL 8.0 car il provoquait de la contention de verrous en forte concurrence. Si vous utilisez MySQL 8 et ajoutez ces lignes à my.cnf, le service ne démarrera pas. L'approche moderne consiste à mettre en cache au niveau applicatif (Redis, Memcached) ou à s'appuyer sur le buffer pool InnoDB.

Ne gonflez pas non plus les tampons par connexion ; ils sont alloués séparément pour chaque connexion :

sort_buffer_size = 2M
join_buffer_size = 2M
tmp_table_size = 64M
max_heap_table_size = 64M

Vérifier et mesurer vos changements

Régler les valeurs est la moitié du travail ; mesurer leur effet est l'autre moitié. Vérifiez les valeurs actives :

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW GLOBAL STATUS LIKE 'Threads_connected';

Pour repérer les requêtes lentes, activez le slow query log et trouvez les vrais goulots d'étranglement :

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

Parmi les outils prêts à l'emploi, mysqltuner.pl analyse un serveur en fonctionnement et propose des recommandations concrètes ; mais ne les appliquez pas aveuglément — appliquez chacune seulement après en avoir compris la raison. Souvenez-vous : même le meilleur my.cnf ne sauvera pas une requête mal écrite ou un index manquant. Corrigez d'abord vos requêtes et vos index avec EXPLAIN, puis affinez la configuration.

Questions fréquentes

Dois-je redémarrer MySQL pour les modifications de my.cnf ?

Pour la plupart des réglages InnoDB (buffer pool, taille du fichier de log), oui. Cependant, de nombreuses variables peuvent être modifiées dynamiquement à chaud avec SET GLOBAL — par exemple SET GLOBAL max_connections = 300;. Les changements dynamiques sont perdus au redémarrage ; pour les rendre permanents, vous devez quand même les écrire dans my.cnf.

Que se passe-t-il si innodb_buffer_pool_size est trop grand ?

Vous risquez d'épuiser la RAM et de faire basculer le système ou d'autres services en swap ; à ce moment-là, les performances sont pires qu'avec un petit pool. Rappelez-vous qu'en plus du buffer pool, chaque connexion consomme aussi de la mémoire ; laissez donc une marge totale suffisante à la machine.

Est-ce que j'utilise MySQL ou MariaDB, et les réglages sont-ils identiques ?

Les réglages de base InnoDB et de connexion sont en grande partie communs. Il existe néanmoins des différences sur le query cache, le thread pool et certaines valeurs par défaut. Pour confirmer les noms de réglages propres à votre version, prenez la sortie de SHOW VARIABLES comme référence.

Votre base de données ralentit-elle votre serveur ? Si vous avez besoin d'aide pour le tuning MySQL/MariaDB, la conception d'index ou l'optimisation des requêtes pour un serveur de jeu ou un projet web, contactez-moi — j'examinerai votre configuration actuelle et établirai un plan d'amélioration concret.