On trouve sur Internet des fichiers my.cnf de cent lignes, copiés de serveur en serveur, dont personne ne sait plus pourquoi chaque ligne est là. La plupart des valeurs par défaut de MariaDB sont raisonnables ; une dizaine de réglages seulement font une vraie différence. Ce tutoriel les passe en revue à partir d’un serveur réel : MariaDB 12.3 sur Rocky Linux, une trentaine de sites (WordPress, Matomo, Piwigo…), 32 Go de RAM partagés avec PHP, nginx et d’autres services, des disques rotatifs.

Pour chaque réglage : ce qu’il fait, comment choisir la valeur, et surtout comment vérifier qu’elle est bonne. Un réglage qu’on ne sait pas mesurer est un réglage qu’on ne devrait pas toucher.

Où vit la configuration, et comment lire la valeur réelle

MariaDB lit /etc/my.cnf puis les fichiers de /etc/my.cnf.d/. Les réglages du serveur vont dans une section [mariadb] (lue seulement par MariaDB) ou [mysqld] (lue aussi par les outils compatibles MySQL). Le piège classique : une même variable définie dans deux fichiers, la dernière lue l’emporte. Deux commandes donnent la vérité :

mariadbd --print-defaults          # ce que le serveur lira au prochain démarrage
mariadb -e "SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size'"   # la valeur en cours

Une variable mal orthographiée dans le fichier est ignorée avec un simple avertissement dans le journal d’erreurs. Comparez toujours la valeur en cours avec celle que vous croyez avoir mise.

Le fichier, en entier

[mariadb]
# --- Mémoire -------------------------------------------------------------
innodb_buffer_pool_size        = 12G
innodb_log_file_size           = 2G
innodb_log_buffer_size         = 256M
tmp_table_size                 = 256M
max_heap_table_size            = 256M

# --- Durabilité ----------------------------------------------------------
innodb_flush_log_at_trx_commit = 1
sync_binlog                    = 1
innodb_flush_method            = O_DIRECT

# --- Entrées/sorties (disques rotatifs) ----------------------------------
innodb_io_capacity             = 200
innodb_io_capacity_max         = 400

# --- Connexions ----------------------------------------------------------
max_connections                = 400
thread_cache_size              = 64
wait_timeout                   = 28800

# --- Tables et fichiers --------------------------------------------------
table_open_cache               = 4096
table_open_cache_instances     = 8
table_definition_cache         = 4096
open_files_limit               = 68000

# --- Binlogs, réplication ------------------------------------------------
server_id                      = 10
log_bin                        = /data/mysql/binlogs/logbin
binlog_format                  = ROW
binlog_expire_logs_seconds     = 432000
max_binlog_size                = 512M
log_slave_updates              = ON

# --- Hygiène -------------------------------------------------------------
character_set_server           = utf8mb4
collation_server               = utf8mb4_uca1400_ai_ci
transaction_isolation          = READ-COMMITTED
skip_name_resolve              = ON
local_infile                   = OFF
max_allowed_packet             = 256M
slow_query_log                 = ON
long_query_time                = 5
log_slow_verbosity             = query_plan,explain

La mémoire

innodb_buffer_pool_size, le réglage qui compte le plus

C’est le cache dans lequel InnoDB garde les pages de tables et d’index. Tant que les données réellement utilisées y tiennent, les lectures ne touchent pas le disque, et c’est souvent un facteur cent sur les temps de réponse. La règle habituelle : 60 à 75 % de la RAM sur un serveur dédié à la base, 30 à 40 % sur un serveur qui fait aussi tourner l’application et le serveur web. Ici, 12 Go sur 32.

Pour savoir s’il suffit, mesurez la proportion de lectures servies par le cache :

SELECT ROUND(100 * (1 - r.v / q.v), 2) AS hit_ratio_pct
FROM (SELECT VARIABLE_VALUE v FROM information_schema.GLOBAL_STATUS
      WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') r,
     (SELECT VARIABLE_VALUE v FROM information_schema.GLOBAL_STATUS
      WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') q;

Au-dessus de 99 % sur une journée d’activité normale, la mémoire n’est pas votre problème. En dessous de 95 %, chaque requête qui touche des données froides va lire le disque : augmentez le cache si la RAM le permet, ou cherchez les requêtes qui balaient des tables entières. Depuis MariaDB 10.5, il n’y a plus lieu de découper le cache en innodb_buffer_pool_instances : c’est géré en interne.

innodb_log_file_size : dimensionner le journal redo

Chaque modification est d’abord écrite dans le journal redo, puis les pages modifiées sont écrites dans les fichiers de données en tâche de fond. Un journal trop petit oblige InnoDB à vider ses pages en urgence quand il se remplit, ce qui produit des pics de latence. Une bonne taille couvre environ une heure d’écritures. Mesurez-la :

SELECT VARIABLE_VALUE INTO @a FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME='Innodb_lsn_current';
SELECT SLEEP(3600);
SELECT VARIABLE_VALUE INTO @b FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME='Innodb_lsn_current';
SELECT ROUND((@b - @a) / 1024 / 1024) AS redo_mb_per_hour;

Si le résultat est de 400 Mo par heure, un journal de 512 Mo à 1 Go suffit ; ici 2 Go laissent de la marge pour les imports. Un journal plus grand allonge seulement la récupération après un crash, de quelques secondes à quelques minutes.

La mémoire par connexion : le piège de la multiplication

sort_buffer_size, join_buffer_size, read_buffer_size, read_rnd_buffer_size sont alloués par connexion, et parfois plusieurs fois par requête. Une valeur de 64 Mo pour join_buffer_size semble anodine, mais multipliée par 400 connexions elle peut théoriquement réclamer 25 Go. Gardez les valeurs par défaut sauf mesure contraire, et relevez-les seulement pour une session précise (SET SESSION) lors d’un traitement lourd.

tmp_table_size et max_heap_table_size vont ensemble : la plus petite des deux fixe la taille maximale d’une table temporaire en mémoire avant son passage sur disque. Surveillez le rapport entre Created_tmp_disk_tables et Created_tmp_tables : s’il dépasse 10 à 20 %, des requêtes trient ou regroupent de gros volumes.

La durabilité

innodb_flush_log_at_trx_commit = 1 écrit et synchronise le journal redo à chaque validation ; sync_binlog = 1 fait de même pour le binlog. Ensemble, ils garantissent qu’une coupure de courant ne fait perdre aucune transaction validée et que le réplica reste cohérent avec le serveur principal. Les valeurs 2 ou 0 réduisent le nombre d’écritures synchrones, au prix de jusqu’à une seconde de transactions perdues en cas de crash du système : acceptable sur un réplica qu’on reconstruit, jamais sur le principal.

innodb_flush_method = O_DIRECT fait écrire InnoDB directement sur le disque sans passer par le cache du système : les données ne sont pas mises en cache deux fois (dans le cache d’InnoDB et dans celui du noyau). C’est la valeur par défaut des versions récentes ; vérifiez-la plutôt que de la supposer.

Les entrées/sorties

innodb_io_capacity indique à InnoDB combien d’opérations par seconde le stockage peut absorber pour ses travaux de fond (vidage des pages, purge). La valeur par défaut convient aux SSD. Sur des disques rotatifs, 200 évite qu’InnoDB sature le disque au détriment des requêtes ; innodb_io_capacity_max fixe le plafond en cas de retard. Pour vérifier : iostat -x 5 pendant une période chargée, la colonne %util ne doit pas rester collée à 100.

Les connexions

max_connections doit couvrir la somme des connexions que les applications peuvent ouvrir, avec une marge, sans plus : chaque connexion consomme de la mémoire, et augmenter cette limite pour « réparer » des erreurs Too many connections cache souvent une application qui ne ferme pas ses connexions. Regardez le pic réel :

SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW GLOBAL STATUS LIKE 'Aborted_c%';
SHOW GLOBAL STATUS LIKE 'Threads_created';

thread_cache_size garde des fils d’exécution prêts à resservir : si Threads_created augmente rapidement, augmentez-le. wait_timeout ferme les connexions inactives ; 8 heures conviennent aux applications web, qui se reconnectent d’elles-mêmes.

Les tables et les fichiers

table_open_cache garde les tables ouvertes entre deux requêtes ; il est découpé en table_open_cache_instances pour limiter la contention entre fils. Si Opened_tables augmente sans cesse, le cache est trop petit.

Le piège qui m’a coûté du temps : ce cache dépend du nombre de fichiers que le processus a le droit d’ouvrir. MariaDB calcule ses limites à partir d’open_files_limit, mais systemd impose sa propre limite (LimitNOFILE). Si celle-ci est plus basse, MariaDB réduit silencieusement ses caches au démarrage : j’ai ainsi trouvé un serveur réglé sur 8 instances qui n’en utilisait que 3. La correction est un fichier de surcharge systemd :

# /etc/systemd/system/mariadb.service.d/limits.conf
[Service]
LimitNOFILE=68000
systemctl daemon-reload && systemctl restart mariadb
mariadb -e "SHOW GLOBAL VARIABLES WHERE Variable_name IN ('open_files_limit','table_open_cache_instances')"

Les binlogs et la réplication

Le binlog enregistre chaque modification ; il sert à la réplication et à la restauration à un instant précis. binlog_format = ROW enregistre les lignes modifiées plutôt que les requêtes : plus volumineux, mais sans ambiguïté (une requête avec NOW() ou RAND() ne donne pas le même résultat rejouée ailleurs). binlog_expire_logs_seconds purge les anciens fichiers : gardez au moins l’intervalle entre deux sauvegardes complètes, sinon la restauration à un instant précis devient impossible pour la période intermédiaire. server_id doit être unique dans toute la topologie de réplication, et log_slave_updates permet à un réplica de servir lui-même de source.

L’hygiène

  • utf8mb4 : le vrai UTF-8 sur 4 octets, émojis compris. Le jeu utf8 historique est limité à 3 octets et tronque certains caractères ;
  • collation_server : les versions récentes proposent les collations UCA 14.0 (utf8mb4_uca1400_ai_ci), plus justes pour les comparaisons et les tris en français ;
  • READ-COMMITTED : moins de verrous que le REPEATABLE-READ par défaut, adapté aux applications web ;
  • skip_name_resolve : aucune résolution DNS à chaque connexion (un DNS lent rendrait les connexions lentes), et les droits se donnent par adresse IP ;
  • local_infile = OFF : ferme une porte qui permet à un client de faire lire des fichiers ;
  • max_allowed_packet : la taille maximale d’une requête ou d’une ligne ; trop petite, les imports et les gros contenus échouent ;
  • le journal des requêtes lentes : au-delà de 5 secondes, avec le plan d’exécution. À relire chaque semaine : c’est la meilleure source d’optimisation qui soit.

La méthode

  • changez un seul réglage à la fois, et notez la mesure avant et après ;
  • beaucoup de variables se modifient à chaud (SET GLOBAL) pour un essai ; écrivez-les ensuite dans le fichier, sinon elles disparaissent au redémarrage ;
  • celles qui exigent un redémarrage (taille du journal redo, open_files_limit) se planifient ;
  • gardez la configuration sous gestion de version, avec en commentaire la raison de chaque valeur non standard. Dans six mois, ce commentaire vaudra plus que la valeur elle-même.

Vérifications après un changement

systemctl restart mariadb && systemctl is-active mariadb
journalctl -u mariadb -n 30 --no-pager | grep -iE 'warn|error'
mariadb -e "SHOW GLOBAL VARIABLES" > /tmp/vars.after
diff <(sort /tmp/vars.before) <(sort /tmp/vars.after)

Si un exporteur Prometheus surveille la base, comparez sur quelques jours le taux de réussite du cache, les tables temporaires sur disque, les connexions et la latence des requêtes. Un réglage n’est bon que si ces courbes le confirment.

Une vraie vérification, 75 minutes après un redémarrage

Pour montrer ce que donnent ces vérifications sur un vrai serveur, voici le MariaDB 12.3 de mon serveur web le 2 novembre 2026, lu avec le compte de supervision (qui n’a besoin que de lire variables et compteurs). La machine avait redémarré 75 minutes plus tôt, ce qui rend les chiffres plus instructifs, pas moins.

SELECT @@innodb_buffer_pool_size/1024/1024/1024 AS buffer_pool_gb,
       @@innodb_log_file_size/1024/1024        AS redo_mb,
       @@max_connections, @@innodb_flush_log_at_trx_commit, @@sync_binlog;
+----------------+---------+-------------------+---------------------------------+---------------+
| buffer_pool_gb | redo_mb | @@max_connections | @@innodb_flush_log_at_trx_commit| @@sync_binlog |
+----------------+---------+-------------------+---------------------------------+---------------+
|  12.31         | 2048    |               400 |                               1 |             1 |
+----------------+---------+-------------------+---------------------------------+---------------+

SHOW GLOBAL STATUS WHERE Variable_name IN (...);
| Uptime                           | 4487     |
| Innodb_buffer_pool_read_requests | 5163358  |
| Innodb_buffer_pool_reads         | 121600   |
| Innodb_log_waits                 | 0        |
| Max_used_connections             | 61       |
| Threads_created                  | 61       |
| Connections                      | 11846    |
| Created_tmp_tables               | 37637    |
| Created_tmp_disk_tables          | 5223     |

Ma lecture, ligne par ligne :

  • Taux de réussite du buffer pool : 1 − 121 600 / 5 163 358 = 97,6 %. Sur un serveur démarré depuis des semaines, j’attends plus de 99,9 %. Ici, le cache se remplit encore : chaque page lue sur disque depuis le redémarrage compte comme un échec. C’est pour cela que le taux se compare sur plusieurs jours, jamais juste après un redémarrage, et que la première heure qui suit est plus lente.
  • Innodb_log_waits = 0 : aucune transaction n’a dû attendre de la place dans le tampon du journal. Avec un journal de reprise de 2 Go, même l’activité du démarrage ne l’a pas saturé.
  • Max_used_connections = 61 sur 400 autorisées, et Threads_created = 61 pour 11 846 connexions : le cache de threads réutilise les threads au lieu d’en créer un par connexion. Si Threads_created grimpait presque aussi vite que Connections, thread_cache_size serait trop petit.
  • Tables temporaires sur disque : 5 223 sur 37 637, soit 14 %. Ce sont surtout des requêtes WordPress qui trient des colonnes de texte, lesquelles partent sur disque quelle que soit la valeur de tmp_table_size. Au-delà de 25 %, il vaut la peine de chercher les requêtes responsables avant de relever une limite.
  • innodb_flush_log_at_trx_commit = 1 et sync_binlog = 1 : durabilité complète sur le primaire. Le réplica tourne avec 2 et 0 ; les raisons sont dans la série consacrée à MariaDB.

Temps total : deux requêtes, une minute. Lancez-les avant et après chaque changement, et gardez les sorties : c’est ce qui transforme « j’ai l’impression que c’est plus rapide » en mesure.

Ce billet fait partie d’une série de tutoriels d’administration système (tag « pas à pas »).
Série « Pas à pas », tutoriel 6/77. · ← Tutoriel précédent · Tutoriel suivant → · English version