MariaDB vs PostgreSQL sur HDD : qui souffre le plus sous pression disque (et pourquoi)

La réponse directe : qui souffre le plus sur HDD

Sous pression disque sur HDD, les deux souffrent. Mais ils souffrent de façons différentes, et la réponse « qui souffre le plus » dépend
de quel type de pression disque vous entendez.

• Pression lecture aléatoire (le working set ne tient pas en RAM, beaucoup de recherches point) :
  MariaDB/InnoDB décline souvent plus fort. La falaise de performance d’InnoDB sur HDD est raide quand les misses du buffer pool deviennent des seeks aléatoires.
• Pression écriture/commit (beaucoup de transactions, durabilité activée) :
  PostgreSQL peut sembler pire si le WAL (et le comportement d’fsync) se trouve sur le même périphérique lent et que le tuning des checkpoints est négligé.
  Vous verrez des pics de latence de commit et des « tempêtes de checkpoints ».
• OLTP mixte (charge web typique) :
  MariaDB a tendance à être plus sensible aux I/O aléatoires et aux schémas d’index secondaires ; PostgreSQL est plus sensible au WAL/checkpoint/vacuum mal réglés.

Si vous m’obligez à un titre : MariaDB tend à « souffrir plus » sur HDD quand vous êtes limité par les IOPS en lectures aléatoires ;
PostgreSQL tend à « souffrir plus » quand vous êtes lié par fsync/WAL/checkpoint sur des écritures et que vous ne touchez à rien.
En production réelle, vous pouvez rendre l’un ou l’autre catastrophique avec la bonne combinaison de paramètres par défaut, de schéma et d’optimisme.

Conseil opérationnel : si vous devez impérativement tourner sur HDD, orientez-vous vers
moins d’I/O aléatoires, plus de mémoire, vidages plus lents mais réguliers, et isolation entre chemins d’écriture séquentiels et lectures aléatoires.
Les deux bases peuvent devenir survivables sur HDD, mais aucune n’est tolérante quand votre charge devient une fête des seeks.

Ce que le HDD déteste (et ce qu’il tolère)

Le HDD n’est pas « lent ». Il est inconsistant. Le débit séquentiel peut paraître correct sur le papier. Le problème est la latence de seek et l’encombrement des files.
Dès que vous faites beaucoup d’I/O aléatoire avec de petits blocs, un seul disque se comporte comme un petit portail que tout le monde essaie de traverser en même temps.

Le HDD est allergique aux I/O aléatoires

Les lectures aléatoires sont coûteuses parce que la tête doit se déplacer. Ajoutez de la concurrence et vous n’obtenez pas de « parallélisme », vous obtenez des « chaises musicales ».
Les schémas OLTP (recherches point, sondages d’index secondaires, boucles imbriquées) se transforment en lectures dispersées et le disque devient le mutex global.

Le HDD tolère les écritures séquentielles… jusqu’à ce qu’il ne le fasse plus

Les écritures de journaux séquentielles peuvent aller sur HDD : append-heavy, majoritairement linéaire. C’est là que WAL de PostgreSQL et redo logs d’InnoDB
peuvent être vos amis — si le système les garde séquentiels. Mais les checkpoints, doublewrite, flush de pages et vacuum peuvent tous transformer
le « séquentiel-ish » en « aléatoire-ish », et votre profondeur de file devient une plaisanterie.

Blague n°1 : Faire tourner une base orientée écriture sur un unique HDD, c’est comme faire du distribué avec des post‑it — techniquement possible, socialement coûteux.

Le vrai goulot observé sur HDD : la latence, pas la bande passante

Lors d’incidents en production, le signal est habituellement :
les temps d’attente montent, la latence p99 applicative suit, et le débit chute.
Les gens regardent les graphiques « MB/s » et manquent l’histoire réelle : un seek à 10ms devient 100ms quand la file s’allonge, et maintenant l’fsync
de votre transaction attend derrière un vacuum et un checkpoint.

Pourquoi InnoDB et PostgreSQL se comportent différemment sous pression

InnoDB (MariaDB) : les misses du buffer pool deviennent des lectures aléatoires

InnoDB est un moteur basé sur des pages. Les données et index vivent dans des B‑trees. Quand une page n’est pas dans le buffer pool, elle est lue depuis le disque.
Sur SSD, vous payez surtout un petit surcoût. Sur HDD, chaque miss peut signifier un seek.

Les forces d’InnoDB — adaptive hash index, change buffering, flush en arrière‑plan — aident, mais elles ne peuvent pas annuler la physique.
Si votre hot set ne tient pas en RAM et que le pattern d’accès est aléatoire, InnoDB devient un générateur d’I/O aléatoires.

• Doublewrite buffer : protège contre les écritures partielles de page (important sur HDD), mais ajoute un overhead d’écriture.
• Redo log : plutôt séquentiel, mais les politiques de flush peuvent forcer des fsync fréquents.
• Change buffer (insert buffer) : peut différer une partie de la maintenance des index secondaires, réduisant des écritures aléatoires, mais les merges ultérieurs peuvent provoquer des pics d’I/O.

PostgreSQL : le WAL est séquentiel ; les checkpoints décident si vous dormirez

PostgreSQL utilise aussi des pages de taille fixe (blocks) et un cache partagé. Mais son chemin d’écriture est centré sur le WAL :
un commit écrit des enregistrements WAL, et les pages de données sont écrites plus tard par le background writer / checkpointer.

Sur HDD, le fait que le WAL soit séquentiel est un avantage. Le piège est que le modèle de durabilité et le checkpointing de PostgreSQL peuvent créer des rafales :
un gros checkpoint peut forcer l’écriture de nombreuses buffers sales rapidement, provoquant des écritures aléatoires et une concurrence avec les lectures. Si vous ne faites pas de tuning,
vous obtiendrez des scies de latence : calme, puis chaos, répétition selon un horaire.

• WAL fsync : la latence de commit dépend du comportement d’fsync et des performances du device WAL.
• Checkpoints : forcent les pages sales sur disque ; trop fréquents ou trop brefs = tempêtes I/O.
• Autovacuum : contrôle le bloat mais peut créer une charge de lecture/écriture de fond ; mal configuré, il devient une taxe disque silencieuse.

Où la douleur HDD se cache : amplification d’écriture et travaux de fond

Quand on parle de « pression disque », on veut souvent dire « la base fait plus d’I/O que je ne le pensais ».
C’est généralement de l’amplification d’écriture :

• Trop de pages sales flushées par unité de travail utile (mauvais comportement de checkpoint/flush).
• Bloat et fragmentation qui font que les lectures/écritures touchent plus de pages.
• Indexes qui multiplient les écritures (chaque insert/update touche plusieurs B‑trees).
• Paramètres de réplication/journalisation qui forcent un comportement synchrone du disque à une fréquence plus élevée que nécessaire.

Une citation fiabilité à afficher sur chaque mur ops

L’espoir n’est pas une stratégie. — Gen. Gordon R. Sullivan

Sur HDD, espérer que le cache noyau ou « un réglage plus tard » vous sauvera, c’est finir en intervention d’incident avec son déjeuner froid.

Modes de panne liés à la pression disque que vous verrez vraiment

1) L’incident « tout est lent mais le CPU est inactif »

Le CPU a l’air ennuyé, la moyenne de charge est élevée, et les threads DB « s’exécutent » mais ne font rien de productif.
Sur Linux, c’est souvent iowait et tâches bloquées. La profondeur de file du HDD monte, l’await augmente, et votre p95 devient une excuse p95e.

2) Pics de latence de commit (stalls fsync)

Dans PostgreSQL, cela se manifeste souvent par des commits lents quand WAL fsync est retardé par un disque saturé.
Dans MariaDB/InnoDB, les flushs de redo log et les fsync peuvent de la même façon bloquer les commits selon les réglages de flush.

3) Maintenance de fond qui se bat avec le trafic de premier plan

Checkpoints, vacuum/autovacuum et les nettoyeurs de page d’InnoDB sont « utiles » jusqu’à ce qu’ils ne le soient plus.
Sur HDD, le pire schéma est : le travail de fond devient agressif, la latence disque explose, et alors le travail de premier plan ralentit,
ce qui crée plus d’arriéré, ce qui provoque plus de pages sales, rendant le travail de fond encore plus agressif. C’est une boucle de rétroaction avec un système d’alarme.

4) La surprise « on a ajouté un index et tout s’est empiré »

Les index accélèrent les lectures mais multiplient les écritures. Sur HDD, ce multiplicateur d’écriture est une taxe que vous payez avec intérêts : plus de pages modifiées, plus de flush,
plus d’I/O aléatoire, plus de bloat. Si vous ne mesurez pas, vous optimisez votre chemin vers une panne.

5) Réplication lente et arriéré du « IO thread »

La saturation disque ralentit l’écriture des WAL/binlogs et l’application des changements. Les réplicas prennent du retard, et le basculement devient excitant pour de mauvaises raisons.
HDD vous laisse moins de marge pour être négligent quant à l’endroit où les logs résident et combien d’fsyncs vous exigez par seconde.

Feuille de route pour diagnostic rapide

C’est l’ordre que j’utilise quand une base sur HDD fond. Il est conçu pour trouver rapidement le goulot, pas pour être « complet ».
Vous pouvez faire ça en 5–10 minutes si vous avez l’accès.

Première étape : confirmer que c’est la latence disque (pas le CPU, pas les locks)

1. Vérifier await et %util par périphérique. Si await est élevé et util plafonne, vous êtes lié par l’I/O.
2. Vérifier si la DB attend sur fsync ou buffer I/O vs sur des locks.
3. Confirmer la pression mémoire : si vous swappez ou que le hit rate du cache chute, le HDD se voit demander des lectures aléatoires qu’il ne peut pas gérer.

Deuxième étape : identifier si ce sont les lectures ou les écritures qui causent la douleur

1. Regarder les IOPS lecture/écriture et la taille moyenne des requêtes. Les lectures aléatoires ressemblent à de petites lectures avec un await élevé.
2. Vérifier les stats DB : ratio de cache, génération WAL/redo, activité des checkpoints, flush des pages sales.

Troisième étape : choisir la moindre des mauvaises atténuations

1. Si lectures aléatoires : augmenter la cache effective (RAM, buffer pool/shared_buffers), réduire le working set, corriger requêtes/indexes.
2. Si rafales d’écritures : lisser les checkpoints/flushs, séparer WAL/redo sur son propre périphérique si possible, réduire la fréquence de sync uniquement si acceptable.
3. Si la maintenance est le bourreau : brider vacuum ou page cleaners, planifier les tâches lourdes hors pics, et traiter correctement le bloat/fragmentation.

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

Ce sont des contrôles de qualité production. Chacun inclut : commande, ce que signifie la sortie, et la décision à prendre.
Exécutez-les sur l’hôte de la base (et parfois dans psql/mariadb). Ajustez chemins et identifiants pour votre environnement.

Task 1: Check per-disk latency and saturation

cr0x@server:~$ iostat -x 1 5
Linux 6.5.0 (db01) 	12/30/2025 	_x86_64_	(8 CPU)

Device            r/s     w/s   rMB/s   wMB/s  avgrq-sz avgqu-sz await r_await w_await  %util
sda              35.0   120.0    1.2     6.8      128     7.50   58.2   42.1    62.9  99.4

Signification : %util proche de 100% plus await à plusieurs dizaines de ms indique que le disque est saturé. avgqu-sz montre une file d’attente.

Décision : considérer ceci comme un incident I/O. Cesser de blâmer le CPU. Ensuite, déterminer si ce sont les lectures ou les écritures qui dominent et si WAL/redo partage le même périphérique.

Task 2: See if the kernel is spending time in iowait

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 12      0  51200  12000 820000    0    0   980  5400  900 1800 10  6 40 44  0

Signification : un b élevé (bloqués) et un wa élevé signifie que les threads attendent le disque.

Décision : procéder à l’analyse des waits côté DB ; vérifier aussi le swap (si/so) car swap sur HDD est de l’incendie de performance.

Task 3: Confirm you’re not swapping (or worse, thrashing)

cr0x@server:~$ free -h
               total        used        free      shared  buff/cache   available
Mem:            31Gi        26Gi       120Mi       1.1Gi       4.9Gi       2.2Gi
Swap:          4.0Gi       1.8Gi       2.2Gi

Signification : l’utilisation de swap sur un hôte DB est un signal rouge ; available est dangereusement faible.

Décision : réduire l’empreinte mémoire, ajuster les caches DB pour éviter OOM mais prévenir le swap, ou ajouter de la RAM. Sur HDD, l’usage de swap corrèle souvent avec des orages de lectures aléatoires.

Task 4: Identify top I/O consumers at process level

cr0x@server:~$ sudo iotop -oPa
Total DISK READ: 18.25 M/s | Total DISK WRITE: 72.10 M/s
  PID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND
 2211 be/4  postgres   2.10 M/s  18.50 M/s   0.00 %  89.00 % postgres: checkpointer
 1998 be/4  postgres   0.50 M/s   9.20 M/s   0.00 %  45.00 % postgres: autovacuum worker
 1870 be/4  mysql      1.20 M/s  22.30 M/s   0.00 %  70.00 % mariadbd

Signification : vous pouvez voir si checkpointer/autovacuum (Postgres) ou mariadbd (flush InnoDB) domine les écritures.

Décision : si les processus de fond dominent, ajuster checkpoint/vacuum (Postgres) ou les réglages de flush (InnoDB), et séparer logs/données si possible.

Task 5: Verify filesystem mount options (barriers, atime)

cr0x@server:~$ mount | egrep ' /var/lib/(postgresql|mysql) '
/dev/sda2 on /var/lib/postgresql type ext4 (rw,relatime,errors=remount-ro)
/dev/sda3 on /var/lib/mysql type ext4 (rw,relatime,errors=remount-ro)

Signification : relatime est acceptable ; noatime peut réduire les écritures métadonnées. La question majeure est de savoir si logs et données partagent des spindles.

Décision : envisager noatime si approprié ; surtout, planifier la séparation des périphériques pour WAL/redo et données sur HDD.

Task 6: Check PostgreSQL wait events for I/O vs locks

cr0x@server:~$ sudo -u postgres psql -c "select wait_event_type, wait_event, count(*) from pg_stat_activity where state='active' group by 1,2 order by 3 desc;"
 wait_event_type |  wait_event   | count
-----------------+---------------+-------
 IO              | DataFileRead  |    18
 IO              | WALWrite      |     6
 Lock            | relation      |     2

Signification : beaucoup de sessions bloquées sur DataFileRead indique des lectures aléatoires ; WALWrite indique une pression d’écriture de journal.

Décision : si DataFileRead domine, attaquer le hit rate cache, les requêtes lentes et le bloat. Si WALWrite domine, se concentrer sur le device WAL et le tuning des checkpoints.

Task 7: PostgreSQL checkpoint behavior (are you causing storms?)

cr0x@server:~$ sudo -u postgres psql -c "select checkpoints_timed, checkpoints_req, checkpoint_write_time, checkpoint_sync_time, buffers_checkpoint from pg_stat_bgwriter;"
 checkpoints_timed | checkpoints_req | checkpoint_write_time | checkpoint_sync_time | buffers_checkpoint
------------------+-----------------+-----------------------+----------------------+-------------------
              120 |             310 |              9876543  |              432109 |           9823410

Signification : un checkpoints_req élevé vs timed suggère que vous atteignez max_wal_size ou que des triggers de checkpoint surviennent souvent ; de grands temps d’écriture/sync suggèrent des flushs douloureux.

Décision : augmenter max_wal_size, tuner checkpoint_timeout et checkpoint_completion_target, et envisager de déplacer WAL sur un spindle dédié.

Task 8: PostgreSQL cache hit ratio (quick and dirty)

cr0x@server:~$ sudo -u postgres psql -c "select sum(blks_hit) as hit, sum(blks_read) as read, round(100.0*sum(blks_hit)/nullif(sum(blks_hit)+sum(blks_read),0),2) as hit_pct from pg_stat_database;"
    hit    |  read   | hit_pct
-----------+---------+---------
 987654321 | 5432109 | 99.45

Signification : ~99% peut encore être mauvais si le 1% restant est énorme à votre QPS. Sur HDD, ce 1% peut représenter toute la panne.

Décision : si hit_pct chute pendant l’incident, augmenter l’efficacité mémoire et réduire le working set (indexes, plans, partitionnement, cache applicatif).

Task 9: PostgreSQL find heavy queries causing reads/writes

cr0x@server:~$ sudo -u postgres psql -c "select queryid, calls, rows, shared_blks_read, shared_blks_hit, (shared_blks_read*8) as read_kb, left(query,120) from pg_stat_statements order by shared_blks_read desc limit 5;"
 queryid  | calls | rows  | shared_blks_read | shared_blks_hit | read_kb | left
----------+-------+-------+------------------+-----------------+---------+-------------------------------
 12345678 |  9200 | 18400 |          812345  |        91234567 | 6498760 | SELECT * FROM events WHERE ...

Signification : quelques requêtes dominent souvent les lectures. Sur HDD, ce sont vos cibles pour corrections de requêtes/index.

Décision : corriger les plus gros coupables : ajouter/ajuster des index (prudemment), supprimer les patterns N+1, éviter gros sorts/hashes sur disque, réduire les colonnes retournées.

Task 10: MariaDB check InnoDB buffer pool and read pressure

cr0x@server:~$ sudo mariadb -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';"
+---------------------------------------+------------+
| Variable_name                         | Value      |
+---------------------------------------+------------+
| Innodb_buffer_pool_read_requests      | 9876543210 |
| Innodb_buffer_pool_reads              | 43210987   |
+---------------------------------------+------------+

Signification : Innodb_buffer_pool_reads sont des lectures physiques. Sur HDD, si cela augmente rapidement, vous effectuez des seeks disque.
Le ratio donne un indice ; le taux pendant l’incident est plus important que le total vie entière.

Décision : si les lectures physiques grimpent, augmenter innodb_buffer_pool_size (dans les limites de la RAM), corriger indexes/requêtes, et réduire les scans de table.

Task 11: MariaDB check redo log flush behavior and fsync pressure

cr0x@server:~$ sudo mariadb -e "SHOW GLOBAL VARIABLES LIKE 'innodb_flush_log_at_trx_commit'; SHOW GLOBAL STATUS LIKE 'Innodb_os_log_fsyncs';"
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_flush_log_at_trx_commit| 1     |
+-------------------------------+-------+
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| Innodb_os_log_fsyncs| 55443322 |
+---------------------+----------+

Signification : innodb_flush_log_at_trx_commit=1 est le plus sûr (fsync à chaque commit). Sur HDD avec un TPS élevé, cela peut être brutal.
Innodb_os_log_fsyncs indique la fréquence.

Décision : ne changez pas la durabilité à la légère. Si le business l’autorise, 2 réduit la fréquence des fsync ; sinon, déplacer le redo log vers un stockage plus rapide ou réduire le taux de commit (batching).

Task 12: MariaDB inspect InnoDB dirty page flushing and stalls

cr0x@server:~$ sudo mariadb -e "SHOW ENGINE INNODB STATUS\G" | egrep -i 'Modified db pages|buffer pool size|free buffers|Pending writes|Page cleaner'
Modified db pages   245678
Buffer pool size    2097152
Free buffers        1024
Pending writes: LRU 0, flush list 37, single page 0
Page cleaner: 1000ms intended loop took 8450ms

Signification : beaucoup de pages modifiées + la boucle du page cleaner prenant bien plus de temps que prévu implique qu’InnoDB n’arrive pas à flusher assez vite.
Sur HDD cela précède souvent des stalls et des explosions soudaines de latence.

Décision : régler innodb_io_capacity/innodb_io_capacity_max pour correspondre à la réalité HDD (pas à la fantaisie), réduire le taux d’écriture, et s’assurer que la taille des redo logs est raisonnable.

Task 13: Check whether WAL/redo and data are on the same device

cr0x@server:~$ lsblk -o NAME,SIZE,TYPE,MOUNTPOINTS
NAME   SIZE TYPE MOUNTPOINTS
sda   1.8T  disk
├─sda1 512M part /boot
├─sda2 900G part /var/lib/postgresql
└─sda3 900G part /var/lib/mysql

Signification : tout sur un disque signifie que les écritures WAL/redo et les lectures de données se disputent le mouvement de tête. Sur HDD, c’est une blessure auto‑infligée.

Décision : séparez : mettez WAL/binlog/redo sur un spindle différent (ou un ensemble RAID10) si possible. Même un disque dédié « moyen » peut stabiliser la latence.

Task 14: Identify disk queueing and scheduler

cr0x@server:~$ cat /sys/block/sda/queue/scheduler
[mq-deadline] none kyber bfq

Signification : le scheduler impacte la latence sous workloads mixés. Pour HDD, un ordonnanceur de type deadline se comporte souvent mieux que « none ».

Décision : si vous utilisez none sur HDD, envisagez mq-deadline (tester d’abord). Ne cargo‑cultivez pas ; mesurez await et la latence tail.

Task 15: PostgreSQL WAL volume and location

cr0x@server:~$ sudo -u postgres psql -c "show data_directory; show wal_level; show synchronous_commit;"
      data_directory
-------------------------
 /var/lib/postgresql/16/main
(1 row)

 wal_level
-----------
 replica
(1 row)

 synchronous_commit
-------------------
 on
(1 row)

Signification : les réglages WAL déterminent combien de WAL est généré et quand les commits attendent. Si synchronous_commit est activé (par défaut), les commits attendent le flush WAL.

Décision : garder synchronous_commit activé à moins d’accepter explicitement de perdre des transactions récentes. Pour soulager le HDD, déplacer WAL sur un périphérique séparé et lisser les checkpoints.

Task 16: PostgreSQL bloat indicator (quick table size reality check)

cr0x@server:~$ sudo -u postgres psql -c "select relname, pg_size_pretty(pg_total_relation_size(relid)) as total, n_live_tup, n_dead_tup from pg_stat_user_tables order by pg_total_relation_size(relid) desc limit 5;"
  relname  | total  | n_live_tup | n_dead_tup
-----------+--------+------------+------------
 events    | 220 GB | 410000000  | 98000000
 sessions  |  85 GB | 150000000  | 72000000

Signification : beaucoup de tuples morts implique du bloat. Sur HDD, le bloat = pages supplémentaires = seeks supplémentaires = souffrance accrue.

Décision : régler autovacuum pour ces tables, envisager le partitionnement, et planifier vacuum/rewrite soigneusement (ce sont des opérations I/O lourdes).

Trois mini-histoires du monde corporate

Mini‑histoire 1 : l’incident causé par une mauvaise hypothèse

Une entreprise SaaS de taille moyenne faisait tourner MariaDB sur une paire de gros HDD en RAID1. La charge était classique OLTP : sessions, événements de facturation,
et quelques requêtes « analytique utiles » qui s’infiltraient en production.
L’équipe supposait que RAID1 signifiait « les lectures sont à peu près correctes ». Ils supposaient aussi que leur ratio de buffer pool était « suffisamment bon »
parce qu’il était souvent supérieur à 98%.

Puis le marketing a livré une fonctionnalité ajoutant un endpoint « recherche par préfixe d’email ». La requête semblait inoffensive,
mais en pratique elle ratait des index sous certains réglages de collation et produisait des scans de plage sur une grande table.
Pendant les heures de pointe, ces scans ont causé une vague de churn du buffer pool. Le taux de miss ne semblait pas dramatique en pourcentage.
Le nombre absolu de lectures physiques l’était.

Sur HDD, les lectures aléatoires étaient létales. Pas parce que le disque ne pouvait pas fournir du débit — il pouvait lire des mégaoctets par seconde.
Mais il ne pouvait pas seeker assez vite. La latence a augmenté, les requêtes se sont accumulées, et les pools de connexions se sont saturés. L’application a commencé à retenter.
Les retries ont doublé la pression. Vous connaissez la suite.

La correction n’était pas exotique. Ils ont ajouté l’index composite correct, forcé la requête à l’utiliser, puis augmenté le buffer pool
pour que le working set chaud tienne réellement. Ils ont aussi placé les endpoints « analytique-ish » sur une réplique où les requêtes lentes pouvaient échouer
sans transformer le primaire en générateur d’I/O aléatoires.

La leçon retenue : RAID1 ne rend pas les seeks aléatoires bon marché. Il vous donne juste deux têtes pour vous décevoir.

Mini‑histoire 2 : l’optimisation qui a échoué

Une plateforme proche des paiements faisait tourner PostgreSQL sur HDD parce que l’environnement de conformité était « stable » et que le service achat avançait au rythme des glaciers.
Ils ont été frappés par la latence de commit lors des fenêtres d’import batch.
Quelqu’un a proposé une « solution simple » : faire les checkpoints plus fréquemment pour que chaque checkpoint écrive moins et termine plus vite.

Ils ont réduit checkpoint_timeout et gardé max_wal_size petit. Les checkpoints finissaient « plus vite ».
Sauf qu’ils se produisaient constamment. Cela a transformé ce qui aurait pu être un motif d’écriture de fond plus lisse en un état quasi permanent de flushing.
Le checkpointer et les writes backend se disputaient les lectures. L’autovacuum existait toujours, parce que l’entropie est invincible.

Le symptôme visible utilisateur était brutal : la latence p99 développait un motif rythmique.
Toutes les quelques minutes, l’API ralentissait suffisamment pour déclencher des timeouts.
L’astre de garde a vu des messages « checkpoint complete » dans les logs comme un métronome infernal.

La correction finale fut l’inverse : permettre à plus de WAL de s’accumuler (augmenter max_wal_size), augmenter
checkpoint_completion_target pour étaler les écritures, et déplacer WAL sur son propre disque.
Les fenêtres d’import sont redevenues ennuyeuses. Personne ne fait la fête pour « ennuyeux », mais c’est ce que vous voulez.

Mini‑histoire 3 : la pratique ennuyeuse mais correcte qui a sauvé la mise

Un grand système interne faisait tourner MariaDB et PostgreSQL côte à côte pour des services différents.
Tout vivait sur des arrays HDD parce que l’infrastructure avait été construite des années auparavant et la « couche rapide » était réservée à d’autres systèmes.
L’équipe ne pouvait pas changer le matériel rapidement, alors elle a fait quelque chose d’impopulaire : elle a rendu l’I/O ennuyeuse.

Ils ont séparé les chemins de logs des chemins de données partout où c’était possible. Le WAL PostgreSQL a eu des spindles dédiés. Les redo logs et binlogs MariaDB ont été séparés aussi.
Ils ont aussi imposé une règle : pas de changements de schéma non examinés pendant les heures de pointe, et chaque nouvel index exigeait de mesurer le coût en écriture sur un staging
avec des volumes de données proches de la production.

Ils ont planifié les opérations lourdes de vacuum et de maintenance InnoDB hors‑pics, et surveillé les métriques « travail de fond » comme des faucons :
temps d’écriture des checkpoints, lectures du buffer pool, pages sales, et lag de réplication.
Quand des incidents arrivaient, ils avaient une feuille de diagnostic rapide et les gens l’utilisaient vraiment au lieu de lancer un protocole de routage des blâmes.

Le résultat n’était pas « rapide ». Il était stable. Le business n’a pas remarqué que les disques étaient lents parce que le système a cessé
d’osciller entre ok et en feu. L’équipe a dormi. Le sommeil, c’est une fonctionnalité.

Erreurs courantes : symptômes → cause racine → correction

1) Symptom : pics de latence p99 toutes les quelques minutes (PostgreSQL)

Cause racine : rafales de checkpoints (max_wal_size trop petit, réglages de checkpoint trop agressifs, WAL et données en lutte).

Correction : augmenter max_wal_size, relever checkpoint_completion_target (étaler les écritures), et déplacer WAL sur un disque séparé si possible.

2) Symptom : SELECT « simples » lents, CPU bas, await disque élevé (MariaDB)

Cause racine : misses du buffer pool générant des lectures physiques aléatoires ; index manquants ou incorrects ; hot set qui ne tient pas en RAM.

Correction : augmenter innodb_buffer_pool_size, corriger indexes/plans, réduire les scans de table, envisager partitionnement ou cache applicatif.

3) Symptom : commits qui se bloquent de manière intermittente ; « fsync » partout

Cause racine : device de log saturé ; trop de transactions forçant des fsync fréquents ; WAL/binlog/redo partageant le HDD avec les données.

Correction : séparer les logs sur des spindles dédiés ; regrouper les écritures ; garder les réglages de durabilité sauf si vous acceptez explicitement de perdre des données.

4) Symptom : le lag de réplication augmente pendant les pics

Cause racine : disque primaire saturé ; le replica n’arrive pas à appliquer les changements ; la maintenance de fond vole le budget I/O.

Correction : brider la maintenance, tuner le parallélisme d’application là où c’est applicable, séparer log/données, et réduire l’amplification d’écriture (indexes, bloat).

5) Symptom : après ajout d’un index, tout ralentit

Cause racine : amplification d’écriture (plus de maintenance d’index), plus de pages modifiées, plus de flushs et d’I/O aléatoire.

Correction : n’ajouter que des index qui rapportent, envisager index partiels/couvrants (Postgres), supprimer les index inutilisés, et valider avec des tests en charge écriture.

6) Symptom : autovacuum « blesse » la perf de façon aléatoire (PostgreSQL)

Cause racine : accumulation de bloat et tuples morts ; autovacuum forcé à travailler plus pendant les pics, scannant heaps et indexes.

Correction : régler autovacuum au niveau table, garder peu de tuples morts ; envisager partitionnement ; planifier vacuum/rewrite manuellement si nécessaire.

7) Symptom : InnoDB se bloque avec avertissements « page cleaner »

Cause racine : le flush n’arrive pas à suivre ; accumulation de pages sales ; pression d’âge de checkpoint forçant des flushs agressifs.

Correction : ajuster innodb_io_capacity de façon appropriée, augmenter la capacité des redo logs si pertinent, réduire les pics d’écriture, et s’assurer que le buffer pool n’est pas outrageusement dimensionné pour le disque.

8) Symptom : « On a beaucoup de MB/s de marge » mais la latence est horrible

Cause racine : vous êtes lié par les IOPS/seeks, pas par la bande passante. De petites I/O aléatoires vous tuent tandis que les graphiques MB/s restent satisfaits.

Correction : focalisez‑vous sur await/profondeur de file, ratios de cache, et réduction des accès aléatoires ; arrêtez de vous réconforter avec des graphiques de débit.

Listes de contrôle / plan étape par étape (survie HDD)

Étape par étape : stabiliser un incident en cours

1. Confirmer la saturation I/O : iostat -x montre await élevé + util élevée.
2. Arrêter l’hémorragie : brider temporairement les jobs batch, rapports lourds, et maintenances longues.
3. Séparer lectures et écritures quand possible : router l’analytics vers des réplicas ; mettre en pause les écritures non critiques.
4. Identifier les requêtes dominantes : utiliser pg_stat_statements ou le slow query log ; tuer les pires si sûr.
5. Réduire l’agressivité checkpoint/vacuum (Postgres) : éviter des réglages d’urgence qui augmentent la fréquence des checkpoints.
6. Vérifier la pression mémoire : si swap, réduire les caches ou redémarrer en sécurité après correction de la config (prudemment) pour récupérer de la mémoire.

Checklist : prévenir les spirales de mort par lectures aléatoires

• Dimensionner réalistement les caches : MariaDB innodb_buffer_pool_size, Postgres shared_buffers plus cache OS.
• Garder le working set petit : supprimer indexes inutilisés, archiver données froides, partitionner les tables append‑only larges.
• Corriger les plans de requête : éviter les sequential scans sur les chemins chauds ; surveiller les nested loops provoquant beaucoup de lookups.
• Préférer moins d’index mieux choisis plutôt que « indexer tout ».

Checklist : prévenir les désastres de rafales d’écriture

• Séparer WAL/redo/binlog des données sur HDD.
• Postgres : augmenter la place WAL et étaler les checkpoints (max_wal_size, checkpoint_completion_target).
• MariaDB : surveiller les pages sales, le lag du page cleaner, la pression du redo log ; régler la capacité I/O pour correspondre au device réel.
• Considérer autovacuum et flush InnoDB comme des workloads de première classe, pas de la « magie de fond ».

Checklist : quand HDD est non négociable

• Préférer RAID10 plutôt que RAID5 pour des workloads aléatoires lourds en écriture si vous devez choisir.
• Garder fsync/durabilité activés sauf si le business approuve une perte de transactions.
• Mesurer la latence tail, pas uniquement le débit.
• Documenter la feuille de diagnostic rapide et la pratiquer.

Faits intéressants & brève histoire (le contexte qu’on oublie)

1. InnoDB n’a pas toujours été le moteur par défaut dans MySQL : MyISAM était courant, et il se comportait très différemment sous pression d’écriture et récupération crash.
2. MariaDB a forké de MySQL en 2009 : des inquiétudes de gouvernance après l’acquisition Sun/Oracle ont poussé beaucoup d’équipes à se couvrir.
3. Le design WAL de PostgreSQL est ancien et éprouvé : la recovery basée sur WAL est centrale depuis des décennies, d’où l’importance de l’I/O séquentiel du journal.
4. InnoDB utilise par défaut un doublewrite buffer : c’est pour protéger contre les écritures partielles de page — plus pertinent sur des environnements sujets aux pertes de courant et à l’ère HDD.
5. Les checkpoints PostgreSQL sont volontairement paramétrables : les valeurs par défaut visent la sécurité, pas « HDD avec workload méchant et aucune patience ».
6. Autovacuum a été introduit pour réduire la charge opérationnelle : mais un autovacuum mal réglé est une source classique d’I/O inattendue sur disques tournants.
7. Les ordonnanceurs I/O Linux ont changé avec blk-mq : ce qui marchait sur d’anciens kernels n’est pas forcément optimal aujourd’hui ; le HDD bénéficie toujours d’une planification consciente de la latence.
8. Le firmware HDD ment parfois : caches d’écriture et reorderings rendent la latence imprévisible ; les bases compensent avec la sémantique fsync et des hypothèses conservatrices.

Blague n°2 : Le moyen le plus rapide d’apprendre le temps de seek d’un HDD est d’y mettre votre base et de regarder votre carrière chercher de nouvelles opportunités.

FAQ

1) Si je suis coincé avec HDD, devrais‑je choisir MariaDB ou PostgreSQL ?

Choisissez selon votre charge et votre maturité opérationnelle. Si vous pouvez contrôler les requêtes et garder le hot set en RAM, les deux peuvent bien se comporter.
Si vous attendez une pression de lectures aléatoires, PostgreSQL se dégrade souvent de manière plus prévisible ; si vous attendez des taux de commit élevés et ne pouvez pas séparer le WAL, MariaDB peut sembler plus fluide jusqu’à ce que la pression fsync arrive.
La vraie réponse : choisissez ce que votre équipe sait tuner et exploiter, puis concevez pour moins d’I/O aléatoires.

2) Quel est le meilleur changement matériel unique sur HDD ?

Séparer les écritures de journal des lectures de données. Mettez le WAL PostgreSQL (et redo/binlog MariaDB) sur des spindles dédiés ou un array séparé.
Cela réduit la contention des têtes et stabilise la latence de commit.

3) Dois‑je désactiver fsync ou relâcher la durabilité pour survivre ?

Seulement avec l’approbation explicite du business. PostgreSQL fsync=off invite à la corruption après un crash.
MariaDB innodb_flush_log_at_trx_commit=2 peut être acceptable dans certains cas (vous risquez de perdre ~1 seconde de transactions),
mais que ce soit une décision produit, pas un bricolage à minuit.

4) Pourquoi mes graphiques montrent des MB/s décents mais l’app timeout‑e ?

Parce que le HDD est lié par IOPS/latence. Quelques MB/s de petites lectures aléatoires peuvent saturer les seeks et créer d’énormes files d’attente.
Surveillez await, la profondeur de file et les wait events DB — pas seulement le débit.

5) Augmenter shared_buffers de PostgreSQL résout‑il la douleur HDD ?

Parfois, mais ce n’est pas magique. PostgreSQL s’appuie fortement aussi sur le cache OS. Surdimensionner shared_buffers peut réduire le cache OS efficace et se retourner contre vous.
Sur HDD, l’objectif est : garder le hot set en cache quelque part et éviter le swap.

6) Augmenter innodb_buffer_pool_size aide‑t‑il toujours MariaDB sur HDD ?

Cela aide tant que ça ne provoque pas de swap ou ne prive pas l’OS. Sur HDD, swap est catastrophique. Augmentez le buffer pool prudemment et mesurez les lectures physiques et la latence.
Assurez‑vous aussi que vos requêtes en bénéficient ; une mauvaise requête balaiera tout ce que vous cachez.

7) Quelle est l’erreur de tuning PostgreSQL la plus courante sur HDD ?

Sous‑allouer l’espace WAL et déclencher des checkpoints demandés fréquents, puis se demander pourquoi la latence spike rythmiquement.
Donnez de la place au WAL et étalez l’I/O des checkpoints.

8) Quelle est l’erreur InnoDB la plus courante sur HDD ?

Prétendre que le disque est plus rapide qu’il ne l’est. Fixer innodb_io_capacity trop haut peut provoquer des patterns de flush agressifs qui se battent avec les lectures en premier plan.
Réglez‑le en fonction de la capacité mesurée du device et surveillez le comportement du page cleaner.

9) Le RAID peut‑il corriger ça ?

Le RAID peut aider en ajoutant des spindles (plus de têtes) et une meilleure redondance, mais il ne transformera pas l’I/O aléatoire en I/O pas chère.
RAID10 est généralement plus amiable pour des workloads mixtes aléatoires que les RAID à parité. Toutefois, les gains majeurs viennent souvent de l’ajustement du cache et du modelage de la charge.

10) Y a‑t‑il des schémas particulièrement pénibles sur HDD ?

Oui : tables larges avec beaucoup d’index secondaires et mises à jour fréquentes ; tables d’« events » non partitionnées et non bornées ; et conceptions qui imposent de nombreux lookups aléatoires par requête.
Sur HDD, vous voulez de la localité et moins de touches de page par transaction.

Prochaines étapes pratiques

Si vous faites tourner MariaDB ou PostgreSQL sur HDD et que la pression disque est déjà un problème, ne commencez pas par des réglages.
Commencez par la mesure, puis l’architecture, puis les réglages.

1. Mesurer correctement la latence : iostat -x, wait events, et stats DB pendant les pics.
2. Séparer les logs des données : isolation WAL/redo/binlog est le changement à ROI le plus élevé sur disques tournants.
3. Faire tenir le hot set : dimensionner correctement les caches et arrêter le swap. Si vous ne pouvez pas, réduire le working set (partitionner/archiver/supprimer indexes inutiles).
4. Corriger les 5 requêtes principales : ce n’est généralement pas « la base », ce sont une ou deux patterns qui transforment le HDD en générateur de seeks.
5. Lisser le travail de fond : tuning des checkpoints (Postgres) et du comportement de flush (InnoDB) doit viser l’état stable, pas les coups d’éclat.
6. Documenter votre playbook : utiliser l’ordre de diagnostic rapide, et le répéter avant que le prochain incident ne vous fasse la leçon.