MySQL vs MariaDB : innodb_buffer_pool_size — l’erreur de tuning par copier-coller qui tue les performances

octobre 13, 2025 • février 3, 2026 • Lecture : 30 min • Views: 8

Cet article vous a aidé ?

Rien ne gâche une permanence calme comme « la base de données est lente » sans autre contexte, suivi d’un graphique qui ressemble à un sismographe. Vous vérifiez le CPU : correct. Vous vérifiez les requêtes : majoritairement normales. Puis vous remarquez que l’hôte swappe, les E/S sont saturées, et la latence de la base de données vient d’apprendre le voyage dans le temps.

Une part déprimante de ces incidents provient d’une seule ligne bien intentionnée et copiée-collée : innodb_buffer_pool_size. Même nom de paramètre dans MySQL et MariaDB. Comportement par défaut similaire. Écosystème différent, réalité de comptabilité mémoire différente, et modes de défaillance différents quand vous dépassez les limites. C’est un réglage qui peut vous faire passer pour un génie — ou pour quelqu’un qui a « optimisé » la production en un cratère.

Ce qu’est réellement le buffer pool (et ce qu’il n’est pas)

Le buffer pool InnoDB est le cache principal pour les données et index InnoDB. Quand tout fonctionne bien, c’est de là que proviennent les lectures et où résident les pages modifiées avant d’être vidées sur disque. Quand tout va mal, c’est là que votre budget mémoire va mourir.

Ce qu’il met en cache

Pages de données (lignes de table stockées dans des pages)
Pages d’index (nœuds B-tree)
Entrées du tampon d’insertion / change buffer (selon la charge)
Structures d’index de hachage adaptatif (si activé, dépend de la charge)

Ce qu’il ne couvre pas

C’est là que commence l’erreur de tuning par copier-coller. Les gens fixent le buffer pool à « 80% de la RAM » et célèbrent, oubliant qu’InnoDB n’est pas la seule chose en mémoire.

Mémoire par connexion (sort buffers, join buffers, tables temporaires) peut dépasser vos attentes en cas de forte concurrence.
Cache binaire des logs, buffers de réplication et piles de threads existent même lorsque vous n’y pensez pas.
Buffer de log InnoDB et structures internes ne sont pas « gratuits ».
Le cache de page du système d’exploitation compte, même avec InnoDB. L’en priver peut pénaliser les E/S de métadonnées, le binlog et tout ce qui n’est pas servi depuis le buffer pool.
Autres daemons (agents de sauvegarde, monitoring, sidecars) ne cessent pas de consommer de la mémoire parce que vous avez écrit un fichier de configuration confiant.

Si vous voulez un modèle mental : innodb_buffer_pool_size est le plus grand compartiment prévisible, pas la toute la fête de la mémoire.

Blague #1 : Copier-coller des configs de base de données, c’est comme emprunter les lunettes de quelqu’un : vous verrez sûrement quelque chose, juste pas ce dont vous avez besoin.

MySQL vs MariaDB : pourquoi le copier-coller fait des dégâts

Au niveau des paramètres, MySQL et MariaDB paraissent souvent interchangeables. Au niveau opérationnel, ce sont des frères qui ont grandi dans des maisons différentes et ont maintenant des habitudes différentes.

Même bouton, valeurs par défaut environnantes différentes

Dans les deux produits, le buffer pool est le consommateur mémoire InnoDB dominant. Mais l’impact sur les performances de « le mettre haut » dépend de :

Comportements spécifiques à la version (surtout autour du vidage, des threads de nettoyage de pages et de la façon dont les métriques sont exposées)
Valeurs par défaut du packaging (limites systemd, plafonds cgroup, emplacements de tmpdir)
Autres fonctionnalités du moteur (MariaDB et MySQL ont divergé dans plusieurs sous-systèmes ; même quand InnoDB est « de la famille », les points d’intégration diffèrent)
Différences d’observabilité (ce que vous pouvez mesurer facilement change ce que vous pouvez ajuster en toute sécurité)

Le mode d’échec du copier-coller

L’erreur classique se déroule ainsi :

Vous avez un serveur MySQL où le buffer pool est réglé à 75–80% de la RAM. La vie est acceptable.
Vous déployez MariaDB (ou inversement) sur des hôtes ayant « la même RAM ».
Vous réutilisez la même section my.cnf avec la même taille de buffer pool.
Le nouvel environnement a des besoins d’espace tête différents : plus de connexions, comportement des tables temporaires différent, besoins de cache de système de fichiers différents, limites cgroup différentes, ou tout simplement une charge différente.
Vous atteignez une pression mémoire. Le kernel swappe. La latence explose. Votre sous-système d’E/S devient une lampe chauffante.

Le paramètre n’a pas changé. La physique a changé.

Pourquoi la surallocation tue les performances d’une manière très spécifique

Quand vous réglez innodb_buffer_pool_size trop grand, vous n’obtenez pas « un peu pire ». Vous obtenez « un système différent ». L’hôte commence à récupérer la mémoire agressivement, potentiellement en swapant la mémoire de la base de données que vous supposiez « chaude ». Les lectures qui étaient des hits mémoire deviennent maintenant des fautes majeures. Les écritures se bloquent derrière les E/S. Le comportement de vidage d’InnoDB peut devenir erratique parce que l’OS est maintenant l’ordonnanceur de votre douleur.

Même sans swap, priver le cache de pages de l’OS peut nuire : binlogs, relay logs, métadonnées de tables et journalisation du système de fichiers se disputent les restes. Votre base de données commence à se comporter comme si elle tournait sur un lecteur réseau de 2009.

Faits et contexte historique intéressants (court, utile, pas des anecdotes)

InnoDB n’a pas toujours été le comportement par défaut. MySQL utilisait historiquement MyISAM ; l’attente moderne que « tout est InnoDB » est relativement récente dans la culture production.
Le buffer pool était autrefois plus simple à raisonner. Les versions plus anciennes avaient moins d’éléments mobiles (et moins de surfaces d’observabilité), donc « le rendre grand » semblait plus sûr qu’il ne l’était réellement.
MariaDB a été forkée depuis MySQL après l’ère Sun/Oracle. Les forks ont préservé de nombreux noms de variables, ce qui est bien pour la familiarité et terrible pour la confiance excessive au copier-coller.
Les instances du buffer pool ont été introduites pour réduire la contention. Diviser le buffer pool en instances a réduit la contention sur les mutex sur systèmes multi-cœurs, mais cela a aussi créé une autre dimension de réglage à deviner.
Le comportement de swap de Linux a changé opérationnellement au fil du temps. Les valeurs par défaut du kernel et l’usage des cgroups en environnements containerisés ont rendu la notion de « mémoire » moins stable que « RAM installée ».
Performance Schema a changé la façon de diagnostiquer la mémoire et les attentes. Une meilleure instrumentation a permis un meilleur tuning — si vous l’activez et l’interrogez réellement.
L’adoption des SSD a changé la courbe de pénalité. Sur disques tournants, un cache miss était lent ; sur SSD, c’est « moins lent », ce qui a tenté des équipes à sous-investir dans un cache correct jusqu’à ce que la concurrence les démente.
Les types d’instances cloud ont réécrit les attentes. Les ratios RAM/E/S, les crédits de burst et le stockage attaché au réseau ont fait que la même taille de buffer pool se comporte différemment selon l’environnement.
Transparent Huge Pages (THP) est devenu un coupable récurrent. Ce n’est pas un réglage de base de données, mais il interagit avec les patterns d’allocation de mémoire d’une manière qui se manifeste par des latences et des blocages.

Playbook de diagnostic rapide

Voici ce que vous faites quand votre équipe applicative hurle et que vous avez 15 minutes pour trouver le goulot d’étranglement. L’objectif n’est pas un tuning parfait. L’objectif est d’identifier si innodb_buffer_pool_size est impliqué et si vous mourez de pression mémoire, de saturation des E/S ou de contention de verrous.

Premier point : confirmez que l’hôte ne vous ment pas sur la mémoire

Vérifiez l’activité de swap et les fautes majeures. Si le swap est actif, arrêtez de considérer le tuning des requêtes comme la première étape.
Vérifiez les limites cgroup. En conteneurs ou slices systemd, « free -h » peut sembler correct pendant que la base de données est enfermée.
Vérifiez les logs de l’OOM killer. Si le kernel abat des processus, votre taille de buffer pool est maintenant une scène de crime.

Deuxième point : déterminez si les lectures sont des hits en cache ou des E/S disque

Taux de hit du buffer pool et lectures. Cherchez une augmentation des lectures physiques et un faible ratio de hits sous une charge stable.
Attente d’E/S et saturation du périphérique. Si les disques sont saturés, votre « lenteur de base de données » peut être « le stockage est le goulot ».

Troisième point : vérifiez si les écritures se bloquent derrière le vidage

Pourcentage de pages sales. S’il est élevé et ne baisse pas, vous êtes lié au vidage.
Âge du checkpoint / pression sur le log. Si la pression sur le redo log est élevée, les écritures se bloqueront indépendamment de la taille du buffer pool.

Arbre de décision (rapide et direct)

Swap ou OOM ? Réduisez d’abord l’utilisation mémoire. Généralement cela signifie réduire le buffer pool, contrôler la mémoire par connexion, ou les deux.
Pas de swap, mais E/S saturées et faible taux de hits ? Le buffer pool peut être trop petit ou la charge ne rentre pas dedans. Augmentez prudemment si vous avez de la marge.
E/S saturées avec haut taux de hits ? Probablement pression d’écriture, configuration de vidage, fsync, binlog, ou latence du stockage. N’« augmentez » pas le buffer pool pour régler ça.

Tâches pratiques : commandes, sorties et décisions (12+)

Ce sont des commandes réelles que vous pouvez exécuter sur un hôte Linux et un serveur MySQL/MariaDB. Chaque tâche inclut (1) la commande, (2) ce que la sortie typique signifie, et (3) la décision à prendre.

Task 1: Confirm which server you’re actually running

cr0x@server:~$ mysql --version
mysql  Ver 15.1 Distrib 10.11.6-MariaDB, for Linux (x86_64) using readline 5.1

Signification : C’est MariaDB 10.11.x. Si vous attendiez Oracle MySQL 8.0, votre runbook vient juste de devenir fanfiction.

Décision : Utilisez l’instrumentation spécifique à MariaDB et soyez prudent avant de supposer des comportements ou valeurs par défaut de MySQL 8.0.

Task 2: Check current buffer pool size and related knobs

cr0x@server:~$ mysql -NBe "SHOW VARIABLES WHERE Variable_name IN ('innodb_buffer_pool_size','innodb_buffer_pool_instances','innodb_page_size','innodb_flush_method','innodb_flush_neighbors')"
innodb_buffer_pool_size	25769803776
innodb_buffer_pool_instances	8
innodb_page_size	16384
innodb_flush_method	O_DIRECT
innodb_flush_neighbors	0

Signification : Buffer pool de 24 GiB, 8 instances, pages de 16 KiB, E/S directes activées. O_DIRECT réduit la double mise en tampon mais augmente votre dépendance à un buffer pool correctement dimensionné.

Décision : Comparez cette taille aux limites mémoire réelles et à l’empreinte totale de MySQL. Ne supposez pas que « 24 GiB est OK » parce que la machine a « 32 GiB installés ».

Task 3: Check memory at OS level (and whether swap is being used)

cr0x@server:~$ free -h
               total        used        free      shared  buff/cache   available
Mem:            31Gi        29Gi       512Mi       1.2Gi       1.6Gi       1.1Gi
Swap:          4.0Gi       2.6Gi       1.4Gi

Signification : Le swap est actif et significatif. La mémoire « available » est faible. Le kernel est sous pression.

Décision : Arrêtez de tuner les requêtes en premier. Prévoyez de réduire la consommation mémoire de MySQL (buffer pool et/ou buffers par connexion), ou d’ajouter de la RAM, ou de réduire la concurrence.

Task 4: Verify swapping is current, not historical

cr0x@server:~$ vmstat 1 5
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 3  1 2700000 480000  24000 820000  12   45  1800  2400  900 1400 18  6 48 28  0
 4  2 2702000 470000  24000 810000  20   60  2000  2600 1100 1600 20  7 41 32  0
 2  1 2705000 460000  24000 800000  10   30  1700  2100  980 1500 17  5 52 26  0

Signification : Des valeurs si/so non nulles indiquent des swaps-in et swaps-out en cours. Le wa CPU (IO wait) est élevé.

Décision : Traitez ceci comme une urgence : réduisez rapidement la pression mémoire ou attendez-vous à une latence en cascade.

Task 5: Confirm the service memory constraints (systemd)

cr0x@server:~$ systemctl show mariadb -p MemoryMax -p MemoryHigh -p TasksMax
MemoryMax=0
MemoryHigh=0
TasksMax=3096

Signification : Pas de plafond mémoire explicite systemd ici. Si vous êtes containerisé, cela ne racontera pas toute l’histoire.

Décision : Si c’est dans Kubernetes/Docker, vérifiez ensuite les limites cgroup. Si c’est bare metal/VM, passez à l’examen de la mémoire du processus MySQL.

Task 6: Check cgroup memory limit (containers or slices)

cr0x@server:~$ cat /sys/fs/cgroup/memory.max
21474836480

Signification : Le groupe de processus est plafonné à 20 GiB. Si votre buffer pool est de 24 GiB, vous avez conçu un événement OOM avec paperasse.

Décision : Diminuez innodb_buffer_pool_size en dessous de la limite cgroup en laissant de la marge pour les connexions et l’OS, ou augmentez la limite.

Task 7: Find the real MySQL/MariaDB process RSS and mapping behavior

cr0x@server:~$ ps -o pid,cmd,rss,vsz --sort=-rss -C mysqld | head -n 5
 1423 /usr/sbin/mariadbd 28765432 32298124

Signification : RSS ~27.4 GiB, VSZ ~30.8 GiB. Le processus est déjà proche de la RAM. Cela n’inclut pas toute la mémoire kernel ni le page cache.

Décision : Si swap/pression existent, réduisez le buffer pool et/ou d’autres buffers ; envisagez de limiter les connexions.

Task 8: Check MySQL status: buffer pool usage, reads, and dirty pages

cr0x@server:~$ mysql -NBe "SHOW GLOBAL STATUS WHERE Variable_name IN ('Innodb_buffer_pool_pages_total','Innodb_buffer_pool_pages_free','Innodb_buffer_pool_read_requests','Innodb_buffer_pool_reads','Innodb_buffer_pool_pages_dirty','Innodb_buffer_pool_wait_free')"
Innodb_buffer_pool_pages_total	1572864
Innodb_buffer_pool_pages_free	1024
Innodb_buffer_pool_read_requests	9842331123
Innodb_buffer_pool_reads	83422119
Innodb_buffer_pool_pages_dirty	210432
Innodb_buffer_pool_wait_free	9821

Signification : Le buffer pool est pratiquement plein (seulement 1024 pages libres). Les lectures physiques sont substantielles. wait_free indique des threads en attente de pages libres — signe classique de pression et/ou de mauvais vidage.

Décision : Si la mémoire hôte est serrée, vous êtes peut-être en surallocation et en train de swapper. Si la mémoire hôte est fine, augmentez le buffer pool (avec prudence) ou corrigez les E/S/vidage. Ne devinez pas : corrélez avec les métriques système.

Task 9: Compute buffer pool hit ratio (quick approximation)

cr0x@server:~$ mysql -NBe "SELECT ROUND((1- (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME='Innodb_buffer_pool_reads')/(SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME='Innodb_buffer_pool_read_requests'))*100,2) AS hit_ratio_percent;"
99.15

Signification : Ratio de hit ~99.15%. Ça sonne bien, mais peut encore être insuffisant à grande échelle. 0.85% de misses sur des milliards de requêtes représente beaucoup de lectures disque.

Décision : Si les E/S sont saturées et que les misses corrèlent avec la charge, le buffer pool peut être trop petit. Si les E/S sont saturées mais que les misses ne montent pas, regardez du côté des écritures/binlogs/fsync/vidage.

Task 10: Check dirty page percentage and flushing pressure

cr0x@server:~$ mysql -NBe "SELECT ROUND(100*(SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME='Innodb_buffer_pool_pages_dirty')/(SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME='Innodb_buffer_pool_pages_total'),2) AS dirty_pct;"
13.38

Signification : Pages sales ~13%. Pas fou en soi. Mais si ça monte et reste élevé avec des blocages, vous êtes à la traîne sur le vidage.

Décision : Si la latence d’écriture monte et que le pourcentage de pages sales reste élevé, examinez la capacité E/S, les paramètres de vidage et le dimensionnement/pression du redo log. N’augmentez pas bêtement le buffer pool sans marge d’E/S.

Task 11: Check current connection count and max connections

cr0x@server:~$ mysql -NBe "SHOW GLOBAL STATUS LIKE 'Threads_connected'; SHOW VARIABLES LIKE 'max_connections';"
Threads_connected	482
max_connections	2000

Signification : 482 connexions actives. Avec un max_connections permissif, vous pouvez obtenir une mort par mémoire via mille piles de threads et buffers par session.

Décision : Si pression mémoire existe, limitez les connexions, utilisez le pooling et auditez les buffers par connexion. Le dimensionnement du buffer pool sans discipline des connexions est du souhaitalisme.

Task 12: Inspect per-connection memory defaults that often explode

cr0x@server:~$ mysql -NBe "SHOW VARIABLES WHERE Variable_name IN ('sort_buffer_size','join_buffer_size','read_buffer_size','read_rnd_buffer_size','tmp_table_size','max_heap_table_size','thread_stack')"
sort_buffer_size	262144
join_buffer_size	262144
read_buffer_size	131072
read_rnd_buffer_size	262144
tmp_table_size	16777216
max_heap_table_size	16777216
thread_stack	299008

Signification : Les valeurs par défaut sont modestes ici, mais « modeste × centaines de connexions » s’additionne, et les tables temporaires peuvent déborder sur disque ou consommer de la mémoire selon les requêtes.

Décision : Si vous voyez une pression mémoire avec haute concurrence, ne vous contentez pas de réduire le buffer pool — corrigez aussi le pooling et les requêtes qui créent de gros tmp tables.

Task 13: Check whether temp tables are hitting disk

cr0x@server:~$ mysql -NBe "SHOW GLOBAL STATUS LIKE 'Created_tmp%tables';"
Created_tmp_disk_tables	1284921
Created_tmp_tables	4422103

Signification : Une fraction significative des tables temporaires est sur disque. C’est des E/S supplémentaires, souvent mal diagnostiquées comme « buffer pool trop petit ».

Décision : Passez en revue les requêtes et les index ; envisagez d’augmenter les limites de tmp table seulement si vous avez prouvé la marge mémoire et que la charge en bénéficie.

Task 14: Identify IO saturation and latency on the database volume

cr0x@server:~$ iostat -x 1 3
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          18.22    0.00    6.11   31.45    0.00   44.22

Device            r/s     w/s   rkB/s   wkB/s  await  aqu-sz  %util
nvme0n1         620.0  1140.0 49600.0 88400.0   9.80    3.10  96.00

Signification : Le périphérique est ~96% utilisé, await ~10ms. Si c’est un NVMe, 10ms est suspect et signifie généralement file d’attente et contention.

Décision : Si les E/S sont proches de la saturation, un buffer pool plus grand peut aider les lectures si vous avez de la RAM libre, mais il ne corrigera pas les blocages d’écritures causés par fsync/binlog/vide. Confirmez quel type d’E/S domine.

Task 15: Check top IO consumers (is it mysqld or something else?)

cr0x@server:~$ sudo iotop -o -b -n 3
Total DISK READ: 45.21 M/s | Total DISK WRITE: 86.73 M/s
  PID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND
 1423 be/4 mysql     42.10 M/s   80.22 M/s   12.00 %  92.00 % mariadbd
 2211 be/4 root       0.10 M/s    6.20 M/s    0.00 %   4.00 % backup-agent

Signification : MariaDB est le consommateur E/S dominant et swappe en entrée (SWAPIN%). C’est le combo cauchemar : lié aux E/S et thrashing de swap.

Décision : Mitigation immédiate : réduisez l’empreinte mémoire et stoppez le swap ; envisagez de réduire temporairement la concurrence ou d’alléger la charge. Puis tunez.

Task 16: Check InnoDB engine status for the story behind the metrics

cr0x@server:~$ mysql -NBe "SHOW ENGINE INNODB STATUS\G" | sed -n '1,120p'
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2025-12-29 10:12:19 0x7f3c9c2fe700 INNODB MONITOR OUTPUT
=====================================
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 27487790694
Dictionary memory allocated 612345678
Buffer pool size   1572864
Free buffers       1024
Database pages     1559000
Old database pages 574000
Modified db pages  210432
Pending reads 0
Pending writes: LRU 0, flush list 48, single page 0
Pages made young 112334455, not young 99887766
...

Signification : Vous pouvez voir des écritures en attente et des pages modifiées. Les écritures en attente sur la liste de flush indiquent une pression de vidage ; la mémoire du dictionnaire indique une empreinte supplémentaire au-delà du buffer pool.

Décision : Si les écritures en attente restent non nulles et augmentent, examinez la capacité E/S et la configuration de vidage ; si la mémoire du dictionnaire est énorme, envisagez la prolifération de schéma/index et la pression du cache de métadonnées.

Trois mini-récits d’entreprise issus du terrain

Mini-récit 1 : L’incident causé par une mauvaise hypothèse

Ils ont migré un service orienté client de MySQL vers MariaDB parce que l’équipe achats aimait l’histoire de licence et que les ingénieurs aimaient l’idée de « compatibilité drop-in ». Le fichier de configuration est venu avec le voyage, y compris un fier innodb_buffer_pool_size réglé sur un pourcentage gras de la RAM.

Le déploiement paraissait correct en staging. Bien sûr : la staging avait moins de concurrence et moins de jobs d’arrière-plan. En production, les nouveaux nœuds MariaDB vivaient dans un runtime de conteneurs avec une limite mémoire dont personne dans l’équipe BDD n’avait été informé. L’hôte avait beaucoup de RAM. Le conteneur non.

Deux heures après la bascule, la courbe de latence a commencé à monter en une ligne lisse et horrifiante. Puis elle a éclaté en pics. Au début ils ont accusé les requêtes. Puis le load balancer. Puis « le cloud ». Finalement quelqu’un a exécuté une commande au bon endroit et a vu la limite cgroup.

Le buffer pool était plus grand que la limite mémoire. Le kernel a fait ce que font les kernels : OOM kills ou thrashing de swap jusqu’à ce que le service ressemble à un fantôme. Ils ont réduit le buffer pool, plafonné les connexions, et le système s’est rétabli — sans changer une seule requête.

La mauvaise hypothèse n’était pas MySQL vs MariaDB. C’était de croire que « la RAM sur la machine » égalait « la RAM disponible pour mysqld ». En 2025, ce n’est pas une hypothèse. C’est un bug.

Mini-récit 2 : L’optimisation qui s’est retournée contre eux

Une autre équipe avait une charge très orientée écriture et voyait les lectures disque augmenter. Quelqu’un a proposé la solution classique : rendre le buffer pool énorme pour que davantage de pages restent chaudes. Ils l’ont augmenté agressivement pendant une fenêtre de maintenance, ont vu le taux de hit s’améliorer, et ont célébré.

Puis le retard de réplication est apparu. Pas immédiatement. Quelques jours plus tard, pendant le pic de trafic, la réplication est restée derrière et n’a jamais rattrapé son retard. Le primaire était « correct », mais les réplicas se noyaient. Le trafic de lecture applicatif était routé vers les réplicas, donc les utilisateurs ont encore ressenti la douleur.

La cause racine n’était pas mystique. Avec le buffer pool gonflé, le cache de pages OS s’est retrouvé compressé. Les binlogs et relay logs n’étaient plus efficacement mis en cache. Les threads IO et SQL des réplicas sont devenus un défilé constant de misses de cache et d’attentes fsync. Par ailleurs, le gros buffer pool a aussi augmenté la quantité de données sales pouvant s’accumuler, augmentant le coût de rattrapage pendant les pointes.

Ils ont réduit légèrement le buffer pool, assuré que les E/S redo/binlog aient de la marge, et utilisé des métriques pour confirmer le compromis. L’« optimisation » n’était pas fausse en principe — elle était hors contexte. La performance est un budget, pas un souhait.

Mini-récit 3 : La pratique ennuyeuse mais correcte qui a sauvé la mise

Cette histoire est peu glamour, ce qui explique pourquoi elle a fonctionné. Une entreprise exploitant des flottes mixtes MySQL et MariaDB avait une pratique standard : chaque hôte de base de données rapportait (1) les limites mémoire effectives, (2) le RSS de mysqld, (3) l’activité de swap, et (4) les métriques du buffer pool dans le même dashboard. Pas comme un « nice to have ». Comme une porte de déploiement.

Quand une équipe a demandé un buffer pool plus grand « parce que les lectures sont lentes », le SRE a exigé une feuille de capacité : RSS actuel, nombre de connexions attendu, plafonds de buffers par connexion, et un profil E/S mesuré. Pas de feuille, pas de changement.

Un vendredi, un nouveau service a été lancé et a accidentellement ouvert bien plus de connexions que prévu. La base de données n’est pas morte. Elle est devenue plus lente, mais elle est restée en ligne. Pourquoi ? Parce qu’ils avaient laissé volontairement de la marge et avaient fixé des limites de connexion sensées. Le buffer pool n’était pas fixé à un pourcentage macho ; il était fixé à ce que le système pouvait se permettre.

Ils ont corrigé l’orage de connexions, les performances sont revenues, et personne n’a dû faire le rituel de 2h du matin « éteindre et rallumer » une base de données qui se paginait jusqu’à la mort.

Comment dimensionner innodb_buffer_pool_size en toute sécurité

Voici des conseils opinionnés : arrêtez d’utiliser une règle de pourcentage unique sur toutes les flottes. Utilisez une méthode. Si vous voulez une règle empirique, d’accord — gagnez-la en mesurant.

Étape 1 : Connaître votre modèle de déploiement

Bare metal / VM : Vous avez généralement une RAM prévisible, mais vous la partagez quand même avec le cache de pages OS et les services d’arrière-plan.
Container / limité par cgroup : La limite est la vérité. Tout le reste est du storytelling.

Étape 2 : Budgétez la mémoire explicitement (ne balayez pas « autre » d’un revers de main)

Un budget pratique en production ressemble à :

Buffer pool : le gros morceau
Mémoire par connexion : estimation pire cas, ou imposez des limites pour que le pire cas ne puisse pas arriver
Surcoût InnoDB : dictionnaire de données, structures internes, adaptive hash (si utilisé), purge, etc.
OS et cache système de fichiers : important pour les binlogs/relay logs et les métadonnées
Marge opérationnelle : sauvegardes, changements de schéma, pics de charge, et le fait que votre futur vous fera quelque chose de risqué

Étape 3 : Traitez le swap comme un aveux de défaite

En terre de bases de données, le swap est rarement « acceptable ». C’est une falaise de performance. Si vous voyez un swap actif, ce n’est pas « utiliser toutes les ressources ». C’est l’étouffement.

Blague #2 : Si votre base de données swappe, ce n’est pas du « multitâche ». Elle tente simplement de se souvenir où elle a mis ses clés.

Étape 4 : Utilisez correctement les instances du buffer pool

Les instances du buffer pool aident à réduire la contention, mais il y a une plage de bon sens :

Trop peu d’instances sur de grands pools peut augmenter la contention.
Trop d’instances peut augmenter l’overhead et réduire l’efficacité. Vous rendez aussi certaines métriques plus bruyantes.

Choisissez un nombre raisonnable pour votre taille et votre CPU, puis mesurez. Ne copiez pas « 8 » en mode cargo-cult parce que vous l’avez vu dans un billet d’époque SATA SSD.

Étape 5 : Ne privez pas le sous-système E/S en affamant le cache OS

Surtout dans les environnements fortement répliqués, l’I/O des binlogs et relay logs peut décider de votre destin. Même si les lectures InnoDB sont servies par le buffer pool, votre réalité opérationnelle implique des logs, des métadonnées et d’autres E/S de fichiers. Si vous donnez tout au buffer pool, vous forcez le reste du système à aller sur disque à chaque étape. Puis vous accusez InnoDB. Classique.

Une citation fiabilité (idée paraphrasée)

Idée paraphrasée : la fiabilité vient de la conception pour l’échec, pas de l’hypothèse qu’il n’arrivera pas. — John Allspaw

Erreurs courantes : symptôme → cause racine → correction

Cette section doit se lire avec un sentiment de déjà-vu gênant.

1) Symptom: sudden latency spikes after “memory tuning”

Cause racine : Buffer pool réglé trop haut, l’hôte commence à swapper ou à récupérer agressivement la mémoire.

Correction : Réduire innodb_buffer_pool_size pour restaurer la marge ; plafonner les connexions ; confirmer que le swap est désactivé ou non utilisé ; vérifier les limites cgroup.

2) Symptom: high IO utilization, but buffer pool hit rate looks “good”

Cause racine : Les E/S sont dominées par les écritures (redo, flush, binlogs) ou les tables temporaires, pas par les lectures InnoDB.

Correction : Inspecter les pages sales, écritures en attente, comportement de fsync du binlog, usage des tables temporaires ; tuner le vidage et le stockage ; n’augmentez pas aveuglément le buffer pool.

3) Symptom: replicas lag more after increasing buffer pool

Cause racine : Le cache de pages OS a été compressé ; les E/S de relay log/binlog deviennent non mises en cache ; les réplicas deviennent liés à la latence E/S.

Correction : Réduire légèrement le buffer pool ; assurer que les E/S de logs ont de la marge ; envisager de séparer les logs sur des dispositifs différents si approprié.

4) Symptom: performance degrades only during peak connections

Cause racine : Pics de mémoire par connexion ; trop de threads ; les tables temporaires créent pression IO et mémoire.

Correction : Utiliser du pooling de connexions ; baisser max_connections ; revoir les tailles de buffers par session ; corriger les requêtes qui déversent sur disque.

5) Symptom: OOM killer takes out mysqld after a restart

Cause racine : Buffer pool et autres allocations se produisent rapidement au démarrage ; combiné aux limites cgroup ou à une faible marge, vous déclenchez OOM.

Correction : Dimensionner correctement le buffer pool selon la limite réelle ; garantir de la marge pendant le warm-up ; envisager des redémarrages étagés et des vérifications préalables.

6) Symptom: “We increased buffer pool but nothing improved”

Cause racine : Le working set ne rentre pas de toute façon, ou le goulot est ailleurs (verrous, CPU, latence stockage, réseau, réplication).

Correction : Mesurer le volume de misses du buffer pool, pas seulement le ratio ; identifier les principaux wait events ; analyser la latence E/S ; tuner les requêtes et index selon les vrais goulots.

Listes de contrôle / plan étape par étape

Checklist A: Pre-change safety checks (do this before touching buffer pool)

Confirmer le type et la version du serveur (mysql --version).
Confirmer les limites mémoire (systemd et cgroups).
Enregistrer les métriques actuelles du buffer pool : taille, pages libres, lectures, wait_free, pages sales.
Enregistrer les métriques OS : usage swap, fautes majeures, utilisation E/S, latence E/S.
Enregistrer les métriques de connexion : Threads_connected, max_connections, et pic.
Vérifier l’usage des tables temporaires et la création sur disque.

Checklist B: Step-by-step tuning plan that won’t ruin your weekend

Décidez l’objectif. Cherchez-vous à réduire les E/S de lecture ? Réduire les pics de latence ? Stopper le swap ? Ce sont des problèmes différents.
Fixez une cible de marge. Choisissez un nombre concret (par ex., garder plusieurs GiB libres/available, et garder le swap inactif).
Ajustez le buffer pool par petits incréments. Les grands sauts cachent la causalité.
Surveillez les bonnes métriques pendant au moins un cycle de charge. Pas cinq minutes. Un cycle.
Si le swap apparaît, revenez en arrière immédiatement. Ne « attendez de voir ». Le swap ne guérit pas lui-même sous charge.
Après stabilisation, réévaluez les patterns de requêtes. Si vous déversez des tables temporaires ou faites des scans complets, le buffer pool n’est pas votre seul levier.

Checklist C: Emergency mitigation when you are already swapping

Réduire la charge entrante (limitation de débit, délestage des lectures, désactiver les jobs non critiques).
Abaisser rapidement la concurrence des connexions (limites de pool applicatif, limites de proxy).
Réduire la taille du buffer pool à la prochaine opportunité sûre (peut nécessiter un redémarrage selon la version).
Arrêter les tâches d’arrière-plan gourmandes en mémoire (requêtes analytiques ad-hoc, sauvegardes si elles thrashent les E/S).
Confirmer que le swap s’arrête (vmstat, iotop).

FAQ

1) Can I use “80% of RAM” for innodb_buffer_pool_size on both MySQL and MariaDB?

Vous pouvez, mais vous choisissez la superstition plutôt que l’ingénierie. Commencez par une taille conservative qui laisse une marge réelle, puis ajustez selon l’activité de swap, les métriques E/S et le comportement de la charge.

2) Why did increasing innodb_buffer_pool_size make performance worse?

Le plus souvent : vous avez induit du swap ou compressé tellement le cache de pages OS que les E/S non-InnoDB (logs, métadonnées, fichiers temporaires) sont devenues liées au disque. Un cache plus grand n’est pas meilleur s’il pousse le kernel à évincer les mauvaises choses.

3) Is swapping always bad for MySQL/MariaDB?

Le swap actif est presque toujours mauvais. Une petite quantité de swap stable et immuable peut être un résidu historique ; un swap continu pendant la charge est une urgence de performance.

4) How do I know if reads are the problem or writes are the problem?

Corrélez les misses du buffer pool (Innodb_buffer_pool_reads), l’utilisation/await du périphérique (iostat -x) et les écritures en attente/pages sales d’InnoDB (SHOW ENGINE INNODB STATUS). Si les E/S sont saturées mais que les misses n’augmentent pas, suspectez les écritures ou les tables temporaires.

5) Does using O_DIRECT change how I should size the buffer pool?

Oui. Avec O_DIRECT, vous réduisez la double mise en tampon et vous vous reposez davantage sur le cache InnoDB. Cela peut améliorer la prévisibilité, mais punit aussi les sous-dimensionnements et rend la planification de la marge plus critique.

6) Should I set innodb_buffer_pool_instances manually?

Seulement si vous avez une raison et que vous mesurez la contention et le débit. Trop peu d’instances peut créer un goulot mutex ; trop d’instances est de l’overhead. Choisissez une valeur sensée et réexaminez si vous voyez des symptômes de contention.

7) Why is my buffer pool hit ratio high but latency still bad?

Parce que le ratio de hit n’est pas le débit, et il masque le nombre absolu de misses. De plus, la latence peut provenir des écritures, du comportement fsync, des attentes de verrous, de la réplication, du réseau ou du stockage même quand les lectures sont en cache.

8) What’s the safest way to change innodb_buffer_pool_size?

Le plus sûr : fenêtre de changement planifiée, petits incréments, plan de rollback et surveillance. Si le redimensionnement est dynamique dépend de la version et du comportement du moteur ; ne supposez pas que le redimensionnement en ligne sera indolore sous charge.

9) Is MariaDB “worse” or “better” than MySQL for buffer pool behavior?

Ni pire ni meilleur universellement. Le problème est de supposer qu’ils sont identiques. Opérationnellement, les différences de packaging, de valeurs par défaut et de contexte de charge importent plus que la marque.

10) If I have plenty of RAM, should I just max out the buffer pool?

Non. Vous avez toujours besoin du cache OS, d’une marge pour les connexions et d’un espace pour les pics opérationnels (sauvegardes, changements de schéma, basculements). Utilisez la RAM pour acheter de la stabilité, pas pour gagner une capture d’écran de benchmark.

Prochaines étapes (que faire cette semaine)

Faites-les dans l’ordre. Elles sont ennuyeuses. Elles fonctionnent.

Inventaire de la réalité : Sur chaque nœud de base de données, enregistrez la version MySQL/MariaDB, les limites cgroup/systemd, la RAM, les réglages swap et la taille actuelle du buffer pool.
Construisez un tableau de bord minimal : activité swap, RSS de mysqld, device await/%util, lectures buffer pool vs read requests, pourcentage de pages sales, Threads_connected.
Choisissez une politique de marge : Décidez ce que signifie « sûr » (pas de swap actif ; une mémoire available minimale ; un plafond de connexions).
Corrigez la discipline des connexions : Si vous ne pouvez pas prévoir la concurrence, vous ne pouvez pas prévoir la mémoire. Utilisez du pooling ; réduisez max_connections à ce que vous pouvez réellement supporter.
Tunez le buffer pool en mesurant : Augmentez ou diminuez selon le comportement observé des E/S et du swap, pas selon des pourcentages hérités.
Rédigez le runbook que vous auriez voulu avoir : Incluez le playbook de diagnostic rapide et les 12+ commandes ci-dessus. Votre futur vous sera fatigué et peu impressionné par des directives vagues.

Si vous ne retenez qu’une leçon : innodb_buffer_pool_size n’est pas un nombre « régler-et-oublier ». C’est un contrat entre InnoDB, l’OS, votre charge et votre discipline opérationnelle. Rompez le contrat, et la production appliquera la clause pénale.