MariaDB vs SQLite pour les pics d’écriture : qui gère les pointes sans drame

La vraie question : que signifie « pic » pour votre système

« Pic d’écriture » est une expression vague qui provoque des malentendus coûteux. Il existe au moins quatre bêtes différentes que les gens appellent un pic :

1. Pic court, forte concurrence : 500 requêtes arrivent en même temps, chacune effectuant un petit INSERT.
2. Sursaut soutenu : taux d’écriture multiplié par 10 pendant 10–30 minutes (jobs batch, backfills).
3. Explosion de latence en queue longue : le débit moyen semble correct, mais toutes les 20 secondes les commits se figent pendant 300–2000 ms.
4. Falaise I/O : le disque ou le système de stockage atteint un mur de flush (comportement fsync/vidage de cache), et tout se met en file d’attente derrière.

MariaDB vs SQLite face aux « pics » concerne principalement leur comportement sous concurrence et la façon dont ils paient la durabilité. Si vous n’avez qu’un seul écrivain et que vous pouvez tolérer un certain enfilement, SQLite peut être ridiculement bon. Si vous avez de nombreux écrivains, plusieurs processus, ou devez continuer à servir des lectures pendant que les écritures s’affolent, MariaDB est généralement l’adulte dans la pièce.

Mais il y a des pièges des deux côtés. Le piège de SQLite est le verrouillage. Le piège de MariaDB est de penser que le serveur de base de données est le goulet d’étranglement alors qu’en réalité c’est le sous-système de stockage (ou votre politique de commit).

Quelques faits et historiques qui comptent vraiment

Quelques points de contexte, courts, concrets et étonnamment prédictifs du comportement en cas de pic :

• SQLite est une bibliothèque, pas un serveur. Il n’y a pas de démon séparé ; votre application la lie et lit/écrit directement le fichier DB. C’est une superpuissance de performance et une contrainte opérationnelle.
• La conception originale de SQLite est optimisée pour les systèmes embarqués. Elle est devenue populaire sur desktop/mobile parce que c’est « juste un fichier » et qu’elle ne nécessite pas un DBA pour la surveiller.
• Le mode WAL dans SQLite a été introduit pour améliorer la concurrence. Il sépare lectures et écritures en ajoutant un write-ahead log, permettant aux lecteurs de continuer pendant des écritures — jusqu’à un certain point.
• SQLite a toujours la règle d’un seul écrivain au niveau de la base. WAL aide les lecteurs, mais plusieurs écrivains concurrents se sérialisent toujours sur le verrou d’écriture.
• MariaDB est un fork de MySQL. Le fork est survenu après l’acquisition d’Oracle ; MariaDB est devenu le choix « ami de la communauté » pour de nombreuses organisations.
• InnoDB est devenu le moteur par défaut pour une raison. Il repose sur MVCC, des redo logs, des flush en arrière-plan et la récupération après crash — des fonctionnalités importantes quand les pics arrivent.
• La performance de MariaDB pendant les pics dépend fortement du comportement des fsync. Votre politique de vidage des redo logs peut déplacer la douleur de « chaque commit consiste en un stall » à « certains commits subissent des stalls mais le débit s’améliore ». C’est un compromis, pas de l’argent gratuit.
• La plupart des incidents « la base est lente » pendant des pics d’écriture sont en réalité « le stockage est lent ». La base est juste la première à l’admettre en se bloquant sur fsync.

Anatomie du chemin d’écriture : MariaDB/InnoDB vs SQLite

SQLite : un fichier, un écrivain, très peu de cérémonial

SQLite écrit dans un seul fichier de base de données (plus, en mode WAL, un fichier WAL et un fichier d’index en mémoire partagée). Votre processus envoie du SQL ; SQLite le traduit en mises à jour de pages. Lors d’un commit de transaction, SQLite doit garantir la durabilité selon vos paramètres pragmas. Cela signifie généralement forcer les données sur un stockage stable à l’aide d’appels de type fsync, selon la plateforme et le système de fichiers.

En cas de pics, le détail critique de SQLite est la rapidité avec laquelle il peut enchaîner « acquérir le verrou d’écriture → écrire les pages/WAL → politique de sync → libérer le verrou ». Si les commits sont fréquents et petits, le coût est dominé par les appels de synchronisation et les transferts de verrou. Si les commits sont groupés, SQLite peut voler.

Le mode WAL change la forme : les écrivains ajoutent au WAL et les lecteurs peuvent continuer à lire le snapshot principal. Mais il n’y a toujours qu’un seul écrivain à la fois, et les checkpoints peuvent devenir un second type de pic (plus bas).

MariaDB/InnoDB : concurrence, mise en cache et I/O en arrière-plan

MariaDB est un processus serveur avec plusieurs threads workers. InnoDB maintient un buffer pool (cache) pour les pages, un redo log (write-ahead), et souvent un undo log pour MVCC. Quand vous committez, InnoDB écrit des enregistrements redo et — selon la configuration — les vide sur disque. Les pages modifiées sont vidées en arrière-plan.

En cas de pics, la superpuissance d’InnoDB est qu’il peut accepter de nombreux écrivains concurrents, mettre le travail en file, et lisser la charge avec du flushing en arrière-plan — à condition d’avoir dimensionné correctement et que votre I/O tienne la charge. Sa faiblesse est qu’il peut quand même atteindre un mur dur où le redo log ou le flushing des pages modifiées devient urgent, et alors les pics de latence ressemblent à un effondrement synchronisé.

Il y a une idée paraphrasée de Werner Vogels (CTO d’Amazon) que les ops répètent parce qu’elle reste vraie : tout échoue, donc concevez pour la récupération et minimisez le rayon d’impact (idée paraphrasée). En zone de pics, cela signifie souvent : attendez-vous à l’amplification d’écriture et attendez-vous à ce que le disque soit le premier à se plaindre.

Qui gère les pointes mieux (et quand)

Si vous voulez une règle honnête et simple : SQLite gère les pics sans drame lorsque vous pouvez transformer la charge d’écriture en moins de transactions et que vous n’avez pas beaucoup d’écrivains entre processus. MariaDB gère les pics sans drame lorsque vous avez de nombreux écrivains concurrents, plusieurs instances applicatives, et que vous avez besoin d’un comportement prévisible sous contention — à condition que votre stockage et votre configuration ne vous sabotent pas.

SQLite gagne quand

• Processus unique ou écrivains contrôlés : un thread écrivain, une file d’attente, ou un processus écrivain dédié.
• Transactions courtes, commits groupés : vous pouvez commit par N enregistrements ou toutes les T millisecondes.
• Disque local, fsync basse latence : NVMe, pas un filesystem réseau instable.
• Vous voulez de la simplicité : pas de serveur, moins de pièces mobiles, moins de personnes réveillées à 3 h du matin.
• Lecture majoritaire avec pics occasionnels : le mode WAL peut garder les lectures rapides pendant les écritures.

SQLite perd (bruyamment) quand

• Beaucoup d’écrivains concurrents : ils se serialisent et vos threads applicatifs s’accumulent derrière « database is locked ».
• Plusieurs processus écrivent en même temps : en particulier sur des hôtes ou conteneurs très chargés sans coordination.
• Le checkpoint devient un pic : le WAL grandit, le checkpoint se déclenche, et soudain vous avez une tempête d’écriture dans votre tempête d’écriture.
• Le stockage a des sémantiques fsync bizarres : certains stockages virtualisés ou réseau rendent la durabilité extrêmement coûteuse ou inconsistante.

MariaDB gagne quand

• Vous avez une vraie concurrence : plusieurs instances applicatives écrivent en même temps.
• Vous avez besoin d’outils opérationnels : réplication, sauvegardes, migrations en ligne, outils d’observabilité.
• Vous devez isoler la charge : le buffer pool absorbe les pics, les thread pools et la mise en file évitent l’effondrement complet.
• Vous avez besoin de sémantiques d’isolation prévisibles : MVCC avec lectures cohérentes sous charge d’écriture.

MariaDB perd quand

• Votre disque ne peut pas flush assez vite : les flushs du redo log bloquent le monde ; la latence explose.
• Vous avez mal dimensionné le buffer pool : trop petit et il thrash ; trop grand et l’OS cache + swapping deviennent problématiques.
• Vous « tunez » la durabilité à l’aveugle : vous achetez du débit en vendant à votre futur vous-même un incident de perte de données.
• Votre schéma crée des points chauds : compteurs sur une seule ligne, mauvais index, ou insert monotone qui se battent sur les mêmes structures.

Blague #1 : SQLite est l’ami toujours à l’heure — sauf si vous invitez trois autres amis à parler en même temps, alors il verrouille simplement la porte.

Les réglages de durabilité : ce que vous achetez réellement avec fsync

Les pics d’écriture sont l’endroit où les paramètres de durabilité cessent d’être théoriques. Ils deviennent une facture que votre stockage doit payer, immédiatement, en espèces.

Leviers de durabilité de SQLite

SQLite expose la durabilité via des pragmas. Les principaux pour les pics :

• journal_mode=WAL : généralement recommandé pour les lectures concurrentes et des performances d’écriture constantes.
• synchronous : contrôle la manière dont SQLite synchronise les données sur disque. Une durabilité supérieure signifie généralement un coût fsync plus élevé.
• busy_timeout : n’améliore pas le débit, mais évite des échecs inutiles en attendant les verrous.
• wal_autocheckpoint : contrôle quand SQLite tente de checkpoint (déplacer le contenu du WAL vers le fichier DB principal).

Voici la subtilité : en mode WAL, le système peut sembler excellent jusqu’à ce que le WAL grossisse et que le checkpointing devienne inévitable. Cette « taxe de checkpoint » apparaît souvent comme des pics de latence périodiques qui ressemblent à un hoquet de la base. Si vous journalisez ou insérez du time-series, cela peut mordre sévèrement.

Leviers de durabilité MariaDB/InnoDB

Dans InnoDB, les réglages critiques pour les pics concernent le flush du redo log et la rapidité d’écriture des pages modifiées :

• innodb_flush_log_at_trx_commit : le classique compromis durabilité/débit. La valeur 1 est la plus sûre (flush à chaque commit), 2 échange un peu de durabilité pour de la vitesse, 0 est plus rapide mais plus risqué.
• sync_binlog : si vous utilisez les binlogs pour la réplication, c’est un coût fsync additionnel.
• innodb_redo_log_capacity (ou le dimensionnement des logs anciens) : trop petit et vous subissez des checkpoints fréquents ; trop grand et le temps de récupération change. Les pics révèlent souvent des logs sous-dimensionnés.
• innodb_io_capacity / innodb_io_capacity_max : indique à InnoDB à quel point être agressif avec le flushing en arrière-plan.

Pour tolérer les pics, vous voulez que la base absorbe la pointe et vide progressivement plutôt que de paniquer et vider tout d’un coup. Le flushing panique, c’est là où la latence devient « intéressante ».

Schémas courants de pics et ce qui casse en premier

Schéma : petites transactions à haut QPS

C’est la classique boucle « insert une ligne et commit », multipliée par la concurrence. C’est une tempête de commits.

• SQLite : contention de verrous + coût des fsync. Vous verrez « database is locked » ou de longs temps d’attente à moins de mettre en file les écritures et de regrouper les commits.
• MariaDB : peut gérer la concurrence, mais le fsync par commit peut dominer la latence. Vous verrez beaucoup de commits de trx, des attentes de log flush, et une saturation I/O.

Schéma : backfill avec index lourds

Vous ajoutez des colonnes, backfillez et mettez à jour des index secondaires. Maintenant chaque écriture se déploie en plusieurs mises à jour de B-tree.

• SQLite : l’écrivain unique le rend prévisible mais lent ; la fenêtre de verrou est plus longue, donc tout le monde attend plus longtemps.
• MariaDB : le débit dépend du buffer pool et de l’I/O. Les index chauds peuvent provoquer de la contention sur les latches ; trop de threads peut empirer la situation.

Schéma : le pic coïncide avec le cycle de checkpoint/flush

C’est le scénario « ça va, ça va, ça va… pourquoi tout brûle toutes les 30 secondes ? ».

• SQLite WAL checkpoint : les cycles longs de checkpoint peuvent bloquer ou ralentir les écritures, selon le mode et les conditions.
• InnoDB checkpoint : le redo log se remplit, les pages modifiées doivent être vidées, et le travail au premier plan commence à attendre l’I/O en arrière-plan.

Schéma : jitter de latence du stockage

Tout est normal jusqu’à ce que le disque fasse une pause. Volumes cloud, flushs de cache RAID, voisin bruyant, commits du journal du système de fichiers — choisissez votre méchant.

• SQLite : votre thread applicatif est la base ; il se bloque. Les pics de latence se répercutent directement sur la latence des requêtes.
• MariaDB : peut mettre en file et paralléliser, mais finalement les threads du serveur se bloquent aussi. La différence est que vous pouvez le voir depuis l’intérieur du moteur via des compteurs d’état et des logs.

Blague #2 : « Nous allons juste rendre ça synchrone et rapide » est l’équivalent base de données de « Je resterai calme et à l’heure pendant la file de sécurité à l’aéroport. »

Trois mini-récits d’entreprise depuis le terrain

Incident causé par une mauvaise hypothèse : « SQLite peut gérer quelques écrivains, non ? »

Une équipe produit de taille moyenne a livré un nouveau service d’ingestion. Chaque conteneur prenait des événements depuis une queue et les écrivait dans un fichier SQLite local pour « buffer temporaire », puis un autre job envoyait le fichier vers un stockage d’objets. L’hypothèse était que « c’est du disque local, donc ce sera rapide ». Et ça l’était — durant la démo en conditions idéales.

Puis la production est arrivée. L’autoscaling a lancé plusieurs conteneurs sur le même nœud, tous écrivant dans le même fichier SQLite via un hostPath partagé. Au moment où le trafic a monté, les écrivains se sont percutés. SQLite a fait ce pour quoi il est conçu : sérialiser les écritures. L’application a fait ce pour quoi elle est conçue : paniquer.

Les symptômes étaient désordonnés : timeouts de requêtes, erreurs « database is locked », et une boucle de retry qui a multiplié le pic. L’hôte lui-même semblait peu utilisé en CPU, ce qui a encouragé exactement le mauvais instinct de debugging : « ça ne peut pas être la base ; le CPU est inactif. »

La correction fut embarrassante par sa simplicité et adulte opérationnellement : un écrivain par fichier de base. Ils ont basculé vers des fichiers SQLite par conteneur, et introduit une file d’écriture explicite en processus. Quand ils ont eu besoin d’écritures cross-container, ils ont déplacé la couche de buffer vers MariaDB avec pooling de connexions et batching des transactions.

La leçon : SQLite est incroyable quand vous contrôlez volontairement la sérialisation des écritures. C’est le chaos quand vous découvrez la sérialisation par accident.

Optimisation qui s’est retournée contre eux : « Détendons le fsync et augmentons les threads »

Une plateforme admin interne tournait sur MariaDB. Pendant un import trimestriel, ils ont observé des pics de latence sur les commits. Quelqu’un (fatigué mais bien intentionné) a changé innodb_flush_log_at_trx_commit de 1 à 2 et a augmenté la concurrence de l’importateur de 16 à 128 threads. Ils voulaient « pousser le batch plus vite » et réduire la fenêtre de douleur.

Le débit s’est amélioré pendant environ cinq minutes. Puis le système a heurté un autre mur : churn du buffer pool plus amplification d’écriture des index secondaires. Les pages modifiées se sont accumulées plus vite que le flushing ne pouvait suivre. InnoDB a commencé un flushing agressif. La latence est passée de picotante à constamment mauvaise, et le primaire a commencé à accumuler du retard de réplication parce que le pattern de fsync du binlog a changé sous charge.

Ils n’ont pas perdu de données, mais ils ont perdu du temps : l’import a pris plus longtemps au final parce que le système oscillait entre des périodes de progrès et de longues pauses. Entre-temps, le trafic utilisateur a souffert car la base ne pouvait pas garder des temps de réponse stables.

La solution finale n’a pas été « plus de tuning ». Ce fut un modelage discipliné de la charge : limiter l’importateur, grouper les commits, et planifier le job avec une limite de débit prévisible. Ils ont gardé les paramètres de durabilité conservateurs et corrigé le vrai problème : l’importateur n’avait pas à se comporter comme un test DDoS.

La leçon : tourner des réglages sans contrôler la concurrence, c’est échanger un mode de défaillance contre un autre plus déroutant.

Pratique ennuyeuse mais correcte qui a sauvé la journée : « mesurer fsync, garder de la marge, répéter les restaurations »

Un service adjacent au paiement (du genre où vous ne pouvez pas être créatif sur la durabilité) utilisait MariaDB avec InnoDB. Toutes les quelques semaines ils avaient un pic : jobs de réconciliation plus une hausse de trafic. Cela n’a jamais causé d’incident, et personne n’a célébré cela. C’était le but.

Ils avaient une routine ennuyeuse. Ils mesuraient la latence disque (y compris fsync) en continu, pas seulement les IOPS. Ils gardaient une marge dans la capacité du redo log et dimensionnaient le buffer pool pour éviter le thrash pendant les pics. Ils répétaient aussi les restaurations selon un calendrier pour que personne n’apprenne le comportement des backups pendant un incident.

Un jour, le jitter de latence de stockage a doublé à cause d’un voisin bruyant sur l’infra sous-jacente. Le service ne s’est pas effondré. Il est devenu plus lent, les alertes se sont déclenchées tôt, et l’équipe a appliqué une mitigation connue : limiter temporairement les jobs batch et mettre en pause les écrivains non critiques. Le trafic utilisateur est resté dans le SLO.

Plus tard, quand ils ont migré vers un autre stockage, ils avaient déjà des baselines prouvant que le stockage était en cause. Les réunions d’achat sont beaucoup plus faciles quand vous montrez des graphes au lieu d’émotions.

La leçon : la pratique « ennuyeuse » de mesurer les bonnes choses et de garder de la marge est l’assurance la moins chère contre les pics.

Méthode de diagnostic rapide

Quand un pic d’écriture frappe et que tout devient bizarre, vous n’avez pas le temps de philosopher. Vous avez besoin d’un arbre de décision rapide : sommes-nous bloqués par les verrous, le CPU ou l’I/O ?

Premier point : confirmer la forme de la douleur (latence vs débit)

• Si le débit reste élevé mais que la latence p95/p99 explose : cherchez des stalls liés à fsync/journal/checkpoint.
• Si le débit s’effondre : cherchez la contention de verrous, l’épuisement des threads, ou la saturation du stockage.

Deuxième point : décider si c’est spécifique à SQLite ou MariaDB

• SQLite : erreurs du type « database is locked », longues attentes, croissance du fichier WAL, ou stalls de checkpoint.
• MariaDB : threads en attente de log flush, flushing de pages modifiées, waits de verrous de ligne, ou retard de réplication qui amplifie la pression.

Troisième point : prouver ou éliminer le stockage comme goulet

• Vérifiez la latence disque, la profondeur de file et le comportement fsync sous charge.
• Si le stockage est instable, presque toute base de données aura l’air coupable.

Quatrième point : arrêter d’empirer la situation

• Limiter la source du pic (job batch, importateur, boucle de retry).
• Grouper les commits. Réduire la concurrence. Désactiver les « retries infinis sans jitter ».
• Capturer des preuves avant de redémarrer les services. Les redémarrages effacent les indices et ne règlent rarement la physique.

Tâches pratiques : commandes, sorties et décisions

Voici ce que vous pouvez exécuter pendant un incident ou une session de tuning. Chaque élément inclut : commande, ce que la sortie signifie, et la décision à prendre.

1) Voir si le système est saturé en I/O (Linux)

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

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          12.1    0.0    4.3    31.7     0.0    51.9

Device            r/s     w/s   rkB/s   wkB/s  rrqm/s  wrqm/s  %util  await  svctm
nvme0n1          20.0  900.0   640.0 42000.0    0.0    50.0   98.7   22.4   0.8

Signification : un %util proche de 100% plus un await élevé indique que le périphérique est le limiteur ; iowait est aussi élevé.

Décision : Arrêtez de « tuner le SQL » en premier. Limitez les écrivains, réduisez la fréquence des commits, ou migrez vers un stockage plus rapide. Si c’est un volume partagé, enquêtez sur un voisin bruyant.

2) Identifier quel processus génère le plus d’I/O d’écriture

cr0x@server:~$ pidstat -d 1 3
Linux 6.5.0 (db01)  12/30/2025  _x86_64_  (8 CPU)

# Time   UID       PID   kB_rd/s   kB_wr/s kB_ccwr/s  Command
12:01:01  999     2145      0.00  52000.00     0.00  mariadbd
12:01:01 1000     8812      0.00   1200.00     0.00  importer

Signification : MariaDB effectue la majorité des écritures ; l’importer contribue mais n’est pas dominant.

Décision : Concentrez-vous sur le comportement de flushing/redo d’InnoDB et la latence du stockage, pas seulement sur le batching applicatif.

3) Vérifier le système de fichiers et les options de montage (surprises de durabilité)

cr0x@server:~$ mount | grep -E " /var/lib/mysql | /data "
/dev/nvme0n1p2 on /var/lib/mysql type ext4 (rw,relatime,commit=5)
/dev/nvme1n1p1 on /data type xfs (rw,relatime,attr2,inode64,logbufs=8)

Signification : l’intervalle commit d’ext4 et le comportement de journalisation peuvent affecter des pics périodiques de latence.

Décision : Si vous observez des stalls périodiques alignés sur les commits du journal, considérez un tuning du filesystem ou déplacer les fichiers DB sur un volume avec de meilleures caractéristiques de latence.

4) Mesurer la latence brute de type fsync avec un test simple

cr0x@server:~$ dd if=/dev/zero of=/var/lib/mysql/sync-test.bin bs=4k count=4096 oflag=dsync
4096+0 records in
4096+0 records out
16777216 bytes (17 MB, 16 MiB) copied, 3.91 s, 4.3 MB/s

Signification : oflag=dsync force la synchronisation par bloc ; un faible débit implique un coût de sync élevé. Ce n’est pas un modèle parfait, mais cela révèle que « le stockage ment ».

Décision : Si cela paraît terrible même sur des disques « rapides », arrêtez et corrigez le stockage ou la configuration de virtualisation avant de blâmer la base.

5) MariaDB : confirmer la politique de flush d’InnoDB et la taille des redo

cr0x@server:~$ mariadb -e "SHOW VARIABLES WHERE Variable_name IN ('innodb_flush_log_at_trx_commit','sync_binlog','innodb_redo_log_capacity','innodb_io_capacity','innodb_io_capacity_max');"
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| innodb_flush_log_at_trx_commit | 1       |
| sync_binlog                   | 1        |
| innodb_redo_log_capacity      | 1073741824|
| innodb_io_capacity            | 200      |
| innodb_io_capacity_max        | 2000     |
+------------------------------+-----------+

Signification : Durabilité complète sur redo et binlog (coûteuse pendant les pics). La capacité redo peut être petite selon la charge.

Décision : Si le p99 meurt et que vous pouvez tolérer de petits compromis de durabilité, envisagez d’ajuster ces paramètres — mais seulement avec un accord commercial clair. Sinon augmentez la performance du stockage et songez au batching des commits.

6) MariaDB : vérifier si vous attendez le flush du log

cr0x@server:~$ mariadb -e "SHOW GLOBAL STATUS LIKE 'Innodb_log_waits'; SHOW GLOBAL STATUS LIKE 'Innodb_os_log_fsyncs';"
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_log_waits | 1834  |
+------------------+-------+
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| Innodb_os_log_fsyncs | 920044 |
+----------------------+--------+

Signification : Log waits signifie que des transactions ont dû attendre le flush du redo log. Pics + latence fsync = douleur.

Décision : Réduisez la fréquence des commits (batch), réduisez la concurrence, ou améliorez la latence des fsync. N’ajoutez pas juste du CPU.

7) MariaDB : vérifier la pression des pages modifiées (dette de flush)

cr0x@server:~$ mariadb -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty'; SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_total';"
+--------------------------------+--------+
| Variable_name                  | Value  |
+--------------------------------+--------+
| Innodb_buffer_pool_pages_dirty | 412345 |
+--------------------------------+--------+
+--------------------------------+--------+
| Variable_name                  | Value  |
+--------------------------------+--------+
| Innodb_buffer_pool_pages_total | 524288 |
+--------------------------------+--------+

Signification : Un ratio très élevé de pages dirty suggère que le système est en retard sur le flushing ; les checkpoints peuvent forcer des stalls.

Décision : Augmentez prudemment les paramètres d’I/O capacity, assurez-vous que le stockage peut soutenir les écritures, et réduisez le taux d’écriture entrant jusqu’à stabilisation des pages dirty.

8) MariaDB : identifier les waits de verrou et les tables chaudes

cr0x@server:~$ mariadb -e "SELECT * FROM information_schema.innodb_lock_waits\G"
*************************** 1. row ***************************
requesting_trx_id: 123456
blocking_trx_id: 123455
blocked_table: `app`.`events`
blocked_lock_type: RECORD
blocking_lock_type: RECORD

Signification : Vous avez de la contention sur une table/index spécifique.

Décision : Corrigez le point chaud : ajoutez un index, changez le pattern d’accès, évitez les compteurs mono-ligne, ou sharder par clé/temps. Mettre plus de threads sur de la contention de verrou aggrave le problème.

9) MariaDB : inspecter les états actuels des threads (sur quoi attendent-ils ?)

cr0x@server:~$ mariadb -e "SHOW PROCESSLIST;"
+-----+------+-----------+------+---------+------+------------------------+------------------------------+
| Id  | User | Host      | db   | Command | Time | State                  | Info                         |
+-----+------+-----------+------+---------+------+------------------------+------------------------------+
| 101 | app  | 10.0.0.12 | app  | Query   |   12 | Waiting for handler commit | INSERT INTO events ...     |
| 102 | app  | 10.0.0.13 | app  | Query   |   11 | Waiting for handler commit | INSERT INTO events ...     |
| 103 | app  | 10.0.0.14 | app  | Sleep   |    0 |                        | NULL                         |
+-----+------+-----------+------+---------+------+------------------------+------------------------------+

Signification : « Waiting for handler commit » se corrèle souvent avec la pression sur commit/fsync.

Décision : Enquêtez sur les réglages redo/binlog et la latence disque ; envisagez le batching d’écriture.

10) SQLite : vérifier le journal_mode et les réglages synchronous

cr0x@server:~$ sqlite3 /data/app.db "PRAGMA journal_mode; PRAGMA synchronous; PRAGMA wal_autocheckpoint;"
wal
2
1000

Signification : Le mode WAL est activé ; synchronous=2 est FULL (durable, plus lent) ; autocheckpoint à 1000 pages.

Décision : Si vous subissez des pics et des stalls, demandez-vous si FULL est vraiment nécessaire. Planifiez aussi la stratégie de checkpoint (manuelle/contrôlée) plutôt que de laisser autocheckpoint vous surprendre.

11) SQLite : détecter la contention de verrou avec un test d’écriture contrôlé

cr0x@server:~$ sqlite3 /data/app.db "PRAGMA busy_timeout=2000; BEGIN IMMEDIATE; INSERT INTO events(ts, payload) VALUES(strftime('%s','now'),'x'); COMMIT;"

Signification : Si cela échoue de façon intermittente avec « database is locked », vous avez des écrivains concurrents ou des transactions longues.

Décision : Introduisez une file d’écriture single-writer, raccourcissez les transactions, et assurez-vous que les lecteurs n’ont pas de verrous trop longs (par ex. SELECT de longue durée dans une transaction).

12) SQLite : surveiller la croissance du WAL et l’état des checkpoints

cr0x@server:~$ ls -lh /data/app.db /data/app.db-wal /data/app.db-shm
-rw-r--r-- 1 app app 1.2G Dec 30 12:05 /data/app.db
-rw-r--r-- 1 app app 3.8G Dec 30 12:05 /data/app.db-wal
-rw-r--r-- 1 app app  32K Dec 30 12:05 /data/app.db-shm

Signification : Le WAL est plus gros que la DB principale. Ce n’est pas automatiquement fatal, mais c’est un signe que le checkpointing ne suit pas.

Décision : Lancez un checkpoint contrôlé pendant une fenêtre calme, ou ajustez votre charge pour que les checkpoints se produisent de façon prévisible. Enquêtez sur des lecteurs longuement vivants empêchant le progrès du checkpoint.

13) SQLite : vérifier si des lecteurs bloquent les checkpoints (base occupée)

cr0x@server:~$ sqlite3 /data/app.db "PRAGMA wal_checkpoint(TRUNCATE);"
0|0|0

Signification : Les trois nombres sont (busy, log, checkpointed). Des zéros après TRUNCATE suggèrent que le checkpoint a réussi rapidement et que le WAL a été tronqué.

Décision : Si « busy » n’est pas zéro ou si le WAL ne se tronque pas, chassez les transactions longues en lecture et corrigez-les (raccourcir les lectures, éviter de garder des transactions ouvertes).

14) MariaDB : confirmer le dimensionnement du buffer pool et la pression

cr0x@server:~$ mariadb -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';"
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 8589934592 |
+-------------------------+------------+
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Innodb_buffer_pool_reads| 18403921 |
+-------------------------+----------+

Signification : Si les lectures du buffer pool augmentent rapidement pendant le pic, vous manquez le cache et effectuez plus d’I/O physique que prévu.

Décision : Augmentez le buffer pool (si la RAM le permet), réduisez l’ensemble de travail (index, patterns de requête), ou shardez la charge. N’ignorez pas l’OS ; le swapping vous ruinera la journée.

15) Suspicion de stockage en réseau : vérifier rapidement la distribution de la latence

cr0x@server:~$ ioping -c 10 -W 2000 /var/lib/mysql
4 KiB <<< /var/lib/mysql (ext4 /dev/nvme0n1p2): request=1 time=0.8 ms
4 KiB <<< /var/lib/mysql (ext4 /dev/nvme0n1p2): request=2 time=1.1 ms
4 KiB <<< /var/lib/mysql (ext4 /dev/nvme0n1p2): request=3 time=47.9 ms
...
--- /var/lib/mysql ioping statistics ---
10 requests completed in 12.3 s, min/avg/max = 0.7/6.4/47.9 ms

Signification : Ce pic de latence max est exactement ce à quoi ressemble la latence de commit quand le disque fait un hoquet.

Décision : Si vous voyez ce genre de jitter, arrêtez de chasser les micro-optimisations SQL. Réparez la QoS du stockage, migrez les volumes, ou ajoutez du buffering/batching.

16) Trouver des tempêtes de retry dans les logs applicatifs (la « montée en charge auto-amplifiée »)

cr0x@server:~$ journalctl -u app-ingester --since "10 min ago" | grep -E "database is locked|retrying" | tail -n 5
Dec 30 12:00:41 db01 app-ingester[8812]: sqlite error: database is locked; retrying attempt=7
Dec 30 12:00:41 db01 app-ingester[8812]: sqlite error: database is locked; retrying attempt=8
Dec 30 12:00:42 db01 app-ingester[8812]: sqlite error: database is locked; retrying attempt=9
Dec 30 12:00:42 db01 app-ingester[8812]: sqlite error: database is locked; retrying attempt=10
Dec 30 12:00:42 db01 app-ingester[8812]: sqlite error: database is locked; retrying attempt=11

Signification : Vous n’êtes pas seulement en train d’expérimenter de la contention ; vous la multipliez avec des retries.

Décision : Ajoutez un backoff exponentiel avec jitter, plafonnez les retries, et considérez une file d’écriture single-writer. Retenter agressivement transforme un pic en incident.

Erreurs courantes (symptômes → cause racine → correction)

1) Symptom : erreurs « database is locked » pendant les pics (SQLite)

Cause racine : Plusieurs écrivains concurrents ou transactions longuement ouvertes tenant des verrous ; la réalité d’un seul écrivain se heurte à une charge multi-écrivains.

Correction : Sérialisez explicitement les écritures (un seul thread/processus écrivain), utilisez le mode WAL, définissez un busy_timeout raisonnable, et regroupez les commits. Évitez de garder des transactions de lecture ouvertes pendant les écritures.

2) Symptom : stalls périodiques de 200–2000 ms toutes les N secondes (SQLite)

Cause racine : Cycles de checkpoint WAL ou commits du journal du filesystem créant un comportement sync en rafales.

Correction : Contrôlez les checkpoints (manuels pendant les fenêtres calmes), ajustez wal_autocheckpoint, réduisez le niveau synchronous seulement si les exigences de durabilité le permettent, et validez le jitter de latence du stockage.

3) Symptom : p99 de MariaDB qui explose alors que le CPU est bas

Cause racine : Commits liés à l’I/O : la latence des fsync redo/binlog domine ; les threads attendent le log flush ou handler commit.

Correction : Groupez les transactions, réduisez la concurrence, révisez innodb_flush_log_at_trx_commit et sync_binlog avec approbation métier, et améliorez la latence du stockage.

4) Symptom : le débit s’effondre quand vous « ajoutez plus de workers » (MariaDB)

Cause racine : Contention de verrous/latches ou pression de flush amplifiée par le thrash des threads ; plus de concurrence augmente les context switches et la contention.

Correction : Limitez la concurrence, utilisez du pooling de connexions, corrigez les index/tables chauds, et tunez le flushing en arrière-plan d’InnoDB plutôt que d’ajouter des threads.

5) Symptom : le fichier WAL grossit indéfiniment (SQLite)

Cause racine : Lecteurs longuement ouverts empêchent le checkpoint de se terminer ; ou wal_autocheckpoint ne correspond pas à la charge.

Correction : Assurez-vous que les lecteurs ne gardent pas des transactions ouvertes, lancez wal_checkpoint pendant des fenêtres contrôlées, et envisagez de scinder la charge sur plusieurs fichiers DB si la contention est structurelle.

6) Symptom : pics de lag de réplication MariaDB pendant les imports

Cause racine : Fsync du binlog et flush redo sous forte écriture ; l’application single-threaded (selon la configuration) ne peut pas suivre.

Correction : Groupez les écritures, planifiez les imports, révisez les paramètres de durabilité du binlog, et assurez-vous que la configuration d’application du replica correspond à la charge. Ne considérez pas la réplication comme « gratuite ».

7) Symptom : « C’est rapide sur mon laptop, lent en prod » (les deux)

Cause racine : Les sémantiques de stockage diffèrent : NVMe sur laptop vs volume cloud partagé ; fsync et jitter de latence sont des univers différents.

Correction : Benchmarquez sur un stockage proche de la production, mesurez la distribution de latence, et fixez des SLO autour de la latence de commit p99 — pas seulement du débit moyen.

Listes de vérification / plan étape par étape

Si vous choisissez entre MariaDB et SQLite pour des écritures en rafales

1. Comptez les écrivains, pas les requêtes. Combien de processus/hôtes peuvent écrire simultanément ?
2. Décidez si vous pouvez imposer un écrivain unique. Si oui, SQLite reste une option.
3. Définissez clairement les exigences de durabilité. « Nous pouvons perdre 1 seconde de données » est une exigence réelle ; « doit être durable » ne suffit pas.
4. Mesurez la latence fsync du stockage. Si elle est instable, les deux bases auront l’air fragiles lors des pics.
5. Planifiez les backfills. Si vous importerez ou retraiterez régulièrement des données, concevez le throttling et le batching dès le départ.

Plan de durcissement pour SQLite (pratique)

1. Activez le mode WAL et vérifiez qu’il reste activé.
2. Fixez busy_timeout à une valeur non triviale (des centaines à milliers de ms), et gérez SQLITE_BUSY avec backoff + jitter.
3. Batcher les commits : commit tous les N enregistrements ou toutes les T millisecondes.
4. Introduisez une file d’écriture avec un thread écrivain. Si plusieurs processus existent, introduisez un processus écrivain unique.
5. Contrôlez les checkpoints : lancez wal_checkpoint pendant les périodes calmes ; ajustez wal_autocheckpoint.
6. Surveillez la taille du WAL et le succès des checkpoints comme métriques de premier plan.

Plan de durcissement pour MariaDB (pratique)

1. Confirmez que vous êtes sur InnoDB pour les tables à écritures rafales.
2. Dimensionnez le buffer pool pour que l’ensemble de travail tienne autant que raisonnable sans swapping.
3. Vérifiez la capacité du redo log ; évitez un redo trop petit qui force des checkpoints fréquents.
4. Alignez innodb_io_capacity sur la capacité réelle du stockage (pas sur l’optimisme).
5. Plafonnez la concurrence applicative ; utilisez du pooling de connexions ; évitez les tempêtes de threads.
6. Batcher les écritures et utilisez des INSERT multi-row quand c’est sûr.
7. Mesurez et alertez sur les log waits, les indicateurs de latence fsync, et le ratio de pages dirty.

Quand migrer de SQLite vers MariaDB (ou vice versa)

• Migrer SQLite → MariaDB quand vous ne pouvez pas imposer un écrivain unique, vous avez besoin d’écritures multi-hôtes, ou les outils opérationnels (réplication/sauvegardes en ligne) comptent.
• Migrer MariaDB → SQLite quand la charge est locale, single-writer, et que vous payez un overhead opérationnel inutile pour un petit jeu de données embarqué.

FAQ

1) SQLite peut-il gérer un débit d’écriture élevé ?

Oui — si vous batcher les transactions et gardez les écrivains sérialisés. SQLite peut être extrêmement rapide par cœur car il évite les sauts réseau et le overhead serveur.

2) Pourquoi SQLite dit-il « database is locked » au lieu de mettre les écrivains en file ?

Le modèle de verrouillage de SQLite est simple et intentionnel. Il attend que l’application contrôle la concurrence (busy_timeout, retries, et idéalement un écrivain unique). Si vous voulez que la base gère une forte concurrence multi-écrivains, vous décrivez une base de données serveur.

3) Le mode WAL est-il toujours le bon choix pour SQLite sous pics ?

Souvent, mais pas toujours. WAL aide les lectures concurrentes pendant les écritures et peut lisser une charge d’écriture constante. Il introduit aussi un comportement de checkpoint que vous devez gérer. Si vous ignorez les checkpoints, vous aurez des stalls périodiques et des fichiers WAL gigantesques.

4) Pour MariaDB, quel paramètre affecte le plus le comportement en cas de pic ?

innodb_flush_log_at_trx_commit et (si le binlog est utilisé) sync_binlog. Ils déterminent directement la fréquence à laquelle vous payez le coût fsync. Les changer modifie la durabilité, donc traitez-les comme une décision métier.

5) Pourquoi les pics d’écriture semblent parfois pire après avoir ajouté des index ?

Les index augmentent l’amplification d’écriture. Un insert devient plusieurs mises à jour de B-tree et plus de pages modifiées. Sous pic, la différence entre « une écriture » et « cinq écritures » n’est pas théorique ; c’est votre p99.

6) Dois-je mettre SQLite sur un stockage réseau ?

D’habitude non. SQLite dépend d’un verrouillage et de sémantiques de sync corrects et de faible latence. Les systèmes de fichiers réseau et certains volumes distribués peuvent rendre le verrouillage imprévisible et fsync douloureusement lent. Si vous devez le faire, testez précisément l’implémentation de stockage sous charge.

7) Si MariaDB est lent pendant les pics, dois-je simplement augmenter le CPU ?

Seulement après avoir prouvé que vous êtes CPU-bound. La plupart des douleurs lors des pics sont liées à la latence I/O ou à la contention. Ajouter du CPU à un goulet fsync est comme ajouter plus de caissiers alors que le magasin n’a qu’une seule caisse.

8) Quelle est la façon la plus simple de faire tenir mieux l’une ou l’autre base aux pics ?

Batcher les commits et limiter la concurrence. Les pics sont souvent auto-infligés par des « workers illimités » et des « commit par ligne ». Corrigez cela en premier.

9) Laquelle est la plus sûre pour la durabilité pendant les pics ?

Les deux peuvent être sûres ; les deux peuvent être configurées de manière dangereuse. Les valeurs par défaut de MariaDB ont tendance à être conservatrices pour les charges serveur. SQLite peut aussi être totalement durable, mais le coût en performance pendant les pics est plus visible car il est dans votre chemin de requête.

10) Comment savoir si je suis limité par le checkpointing ?

SQLite : le WAL grandit et les checkpoints rapportent « busy » ou ne se tronquent pas. MariaDB : les log waits augmentent, les pages dirty montent, et vous voyez des stalls liés au flushing. Dans les deux cas, corrélez avec des pics de latence disque.

Conclusion : étapes pratiques suivantes

Si vous avez des écritures en rafales et que vous hésitez entre MariaDB et SQLite, ne commencez pas par l’idéologie. Commencez par le modèle d’écriture.

• Si vous pouvez imposer un seul écrivain, batcher les commits, et garder la base sur un stockage local à faible latence, SQLite gérera les pics discrètement et à moindre coût.
• Si vous avez beaucoup d’écrivains répartis sur processus/hôtes et que vous avez besoin d’outils opérationnels comme la réplication et une observabilité robuste, MariaDB est le pari le plus sûr — à condition de respecter la physique des fsync et de tuner avec soin.

Puis faites le travail peu glamour qui évite le drame : mesurez la latence fsync, plafonnez la concurrence, batcher les écritures, et faites des checkpoints/flushs une partie contrôlée du système plutôt qu’une surprise. Votre futur vous sera toujours fatigué, mais au moins il sera ennuyé. C’est le but.