Vous avez ajouté des réplicas de lecture pour arrêter le pager “CPU 95%”. Les graphiques se sont améliorés. Puis le support arrive avec des captures d’écran : « Je viens de payer, mais la commande est marquée non payée. »
Les réplicas n’ont pas juste échoué à aider. Ils ont aidé de la pire façon possible : en ayant tort avec assurance.
Les réplicas de lecture sont un outil légitime d’évolutivité. Ils sont aussi un excellent moyen d’externaliser votre cohérence à la physique et à l’espoir. Ceci est un guide pratique sur
le comportement des réplicas MySQL et PostgreSQL en production, quand ils vous procurent de la marge et quand ils mentent à votre application.
Quand les réplicas aident vs quand ils mentent
Les réplicas aident lorsque les lectures sont de véritables lectures
Les réplicas de lecture brillent lorsque votre charge contient des lectures lourdes et répétables qui n’ont pas besoin des données les plus récentes :
reporting, analytics, indexation de recherche, navigation “liste de produits”, timelines tolérant une certaine obsolescence, et
requêtes de longue durée que vous préférez ne pas exécuter sur le primaire. Vous pouvez délester ces lectures et obtenir de la marge pour les écritures.
Ils aident aussi quand vous avez besoin de flexibilité opérationnelle : changements de schéma en ligne, soulagement de la pression de vacuum (Postgres),
environnements secondaires pour vérification, et fenêtres de maintenance plus sûres. Un réplica peut être l’endroit où vous exécutez des requêtes « bizarres »
sans jouer la totalité du cache et de la latence du primaire.
Les réplicas mentent lorsque vous les utilisez comme équilibrage de charge pour du trafic sensible à la cohérence
La réplication est généralement asynchrone. Cela signifie que le réplica est en retard par définition. La seule question est « de combien »
et « pendant combien de temps ». Le mensonge est subtil : le réplica répond vite et avec assurance, mais avec de l’histoire.
Si vous envoyez du trafic « lire mon propre écrit » vers un réplica sans garde-fous, vous créez une incohérence visible par l’utilisateur.
L’utilisateur fait quelque chose ; vous l’acceptez ; il relit ; vous affichez un état ancien. Ça ressemble à une perte de données, même si ce n’est que
du « retard ».
Première règle : si une lecture change ce que l’utilisateur fera ensuite (argent, authentification, inventaire, permissions),
ne la faites pas provenir d’un réplica asynchrone sauf si vous avez une logique explicite de cohérence de session.
Deuxième règle : les réplicas ne corrigent pas les requêtes lentes. Ils vous permettent seulement d’en exécuter davantage. Si votre chemin de lecture
est un ramassis non indexé, vous avez construit un système de gestion des déchets plus grand — pas une cuisine plus propre.
Blague n°1 : Le retard de réplication, c’est comme une réunion à laquelle vous n’étiez pas invité — vous ne découvrez qu’elle a eu lieu qu’après que les décisions soient prises.
À quoi ressemble « aider » dans les métriques
- La CPU du primaire baisse et reste basse pendant les pics de lectures.
- Le hit rate du buffer pool / shared buffers du primaire s’améliore (moins de churn dû aux requêtes orientées lecture).
- La latence p95 et p99 des transactions d’écriture s’améliore parce que la pression de locks et le churn de cache diminuent.
- Le lag des réplicas reste borné pendant les pics attendus (vous connaissez le budget et vous l’appliquez).
À quoi ressemble « mentir » dans les incidents
- « Je viens de changer mon mot de passe mais il accepte toujours l’ancien. »
- « L’inventaire affiche 3 restants, le paiement échoue. »
- « Le support le voit ; le client non. » (le support interroge le primaire ; les clients frappent les réplicas)
- Une bascule se produit et votre « réplica » devient primaire avec des écritures manquantes que vous pensiez engagées.
Réplication MySQL vs PostgreSQL : ce qui est réellement différent
Les deux systèmes peuvent fournir des réplicas de lecture. Les deux proposent des options asynchrones et quasi-synchrones. Les deux peuvent vous brûler si vous
prétendez que la réplication est un miroir magique. Mais les modes de défaillance diffèrent parce que les mécanismes diffèrent.
Réplication MySQL : binlogs, GTID et choix qu’on ne peut ignorer
La réplication classique MySQL repose sur le primaire qui écrit les changements dans le journal binaire, et les réplicas qui récupèrent et appliquent ces changements. Les réglages clés en production :
- Binlog basé sur les lignes vs basé sur les instructions : le mode basé sur les lignes est généralement plus sûr et plus déterministe ; le mode basé sur les instructions est plus compact mais peut être « créatif ».
- GTID : les identifiants globaux de transaction rendent la bascule et le suivi de position beaucoup moins pénibles.
- Application mono-thread vs parallèle : le goulet d’étranglement du thread SQL de réplication crée du lag même quand le réseau va bien.
- Semi-sync : réduit le risque de perte de données au commit en exigeant qu’au moins un réplica accuse réception (pas nécessairement l’application) avant que le commit ne retourne.
Dans MySQL, « le réplica a rattrapé » est souvent exprimé en termes d’ensemble de GTID exécutés, ou de seconds behind source, mais vous devez comprendre ce que ce métrique « secondes » signifie réellement : il est dérivé des horodatages dans les événements, pas d’une mesure monotone précise de vérité.
Réplication PostgreSQL : WAL, streaming et la réalité du « hot standby »
Les réplicas Postgres streament le WAL (write-ahead log). Les réplicas peuvent être des hot standbys : lisibles pendant l’application du WAL. C’est opérationnellement agréable, jusqu’à ce que vos requêtes de lecture entrent en collision avec le vacuum et les attentes de nettoyage des versions de lignes.
- Replication slots : protègent le WAL nécessaire aux réplicas, mais peuvent remplir les disques si un réplica prend du retard.
- Réception / replay du WAL : le lag peut se situer au stade de la réception, du flush, ou du replay — chacun pointe vers un goulot différent.
- Conflits en hot standby : les lectures longues peuvent bloquer le nettoyage ; le nettoyage peut annuler des lectures, selon la configuration.
- Réplication synchrone : peut imposer une attente de commit sur les standbys ; vous choisissez combien doivent acquitter.
Postgres fournit des métriques de lag riches et spécifiques par stade, ce qui est excellent car vous pouvez enfin arrêter de débattre avec votre équipe applicative pour savoir si « secondes de retard » est une impression ou un nombre.
La grande différence opérationnelle : ce que « durable » signifie sous réplication
Si vous accusez réception d’une écriture au client, le client la considère comme vraie. Si le primaire plante immédiatement après,
et que votre bascule promeut un réplica qui n’a jamais reçu cette écriture, votre système a pratiqué un voyage temporel accidentel.
Le semi-sync MySQL et la réplication synchrone Postgres mitigent cela, mais ils ont des sémantiques différentes. Le semi-sync MySQL veut généralement dire « au moins un réplica a reçu l’événement ». La réplication synchrone Postgres peut signifier « un ou plusieurs standbys ont confirmé le flush » (ou, dans des configurations récentes, des choix plus nuancés), ce qui se rapproche de « c’est écrit ailleurs sur disque ».
Le conseil pratique : si vous utilisez des réplicas pour la sécurité de bascule, définissez explicitement votre durabilité de commit requise.
Si vous utilisez des réplicas pour mettre à l’échelle les lectures, définissez explicitement votre obsolescence acceptable. Ce sont des objectifs différents.
Les mélanger sans reconnaître les compromis, c’est obtenir le pire des deux.
Modèles de cohérence que vous pouvez implémenter (et ce qu’ils coûtent)
1) « Les lectures sur réplicas sont au mieux un effort » (peu cher, dangereux)
Vous balancez des requêtes GET sur les réplicas et acceptez que parfois les utilisateurs voient des données anciennes. C’est acceptable pour du contenu non critique. Ce n’est pas acceptable pour des transitions d’état. Si vous ne savez pas classifier facilement les lectures, vous utiliserez accidentellement ce modèle pour tout.
2) Lire ses propres écritures par session (le choix adulte par défaut)
Après qu’un utilisateur ait écrit quelque chose, les lectures suivantes dans cette session doivent le voir. Vous pouvez l’implémenter de plusieurs manières :
- Pinning vers le primaire : après une écriture, router cet utilisateur vers le primaire pendant N secondes.
- Fencing sur position GTID/LSN : enregistrer la position de commit (GTID ou LSN) retournée par le primaire ; ne lire que depuis des réplicas qui ont replayé au-delà de cette position.
- Cache applicatif avec versionnement : garder en cache les résultats récents d’écritures et les fusionner avec les lectures de réplicas (plus difficile que ça en a l’air).
Coût : complexité dans le routage et l’instrumentation. Bénéfice : votre produit arrête de jouer avec les nerfs de vos clients.
3) Réplication synchrone pour les écritures sensibles à la cohérence (coûteux, parfois nécessaire)
Si vous ne devez pas perdre des transactions reconnues lors d’une défaillance d’un seul nœud, vous devez attendre les accusés de réception des réplicas avant de retourner le succès. Cela augmente la latence de commit et réduit le débit — surtout entre zones de disponibilité.
Utilisez-la pour les mouvements d’argent, les workflows irréversibles et les informations d’identification. N’activez pas cela globalement parce que quelqu’un a entendu l’expression « cohérence forte » et s’est emballé.
4) Réplication logique / flux de changements pour les modèles de lecture (souvent la meilleure histoire d’échelle)
Parfois, la bonne réponse est : arrêtez de faire servir à votre base primaire toutes les formes de lecture. Utilisez la réplication (décodage logique dans Postgres ; CDC basé sur binlog dans MySQL) pour construire des magasins optimisés pour la lecture : index de recherche, caches, tables dénormalisées, moteurs OLAP. Voilà l’évolutivité. Les réplicas sont une solution intermédiaire.
Faits intéressants et contexte historique
- Fait 1 : La réplication MySQL précède InnoDB comme moteur par défaut ; les premières hypothèses sur la réplication ont été façonnées par des moteurs de stockage plus simples et des attentes de durabilité plus lâches.
- Fait 2 : La réplication basée sur les instructions a été populaire en partie parce qu’elle réduisait la taille du binlog, mais elle exposait les utilisateurs à de la non-déterminisme (fonctions temporelles, ordre aléatoire sans ORDER BY, etc.).
- Fait 3 : Les GTID MySQL ont été un grand changement opérationnel : ils ont transformé « trouver le bon fichier binlog et la bonne position » en « vérifier l’identité de la transaction », ce qui est bien moins sujet aux erreurs lors d’une bascule.
- Fait 4 : La réplication basée sur le WAL de PostgreSQL a évolué du shipping de fichiers au streaming ; le streaming a rendu le « réplica en service » réaliste plutôt que de compter sur des jobs de restauration et des prières.
- Fait 5 : Le hot standby de Postgres a introduit une nouvelle classe de drames en production : les requêtes de lecture sur réplicas peuvent entrer en conflit avec le nettoyage vacuum, entraînant annulations de requêtes ou bloat.
- Fait 6 : Les replication slots de Postgres sont à la fois une ceinture de sécurité et un pistolet à pied : ils empêchent la perte de WAL pour des réplicas en retard, mais ils peuvent remplir rapidement les disques si un réplica disparaît.
- Fait 7 : Les métriques « seconds behind master/source » ont induit en erreur des ingénieurs pendant des décennies parce qu’elles ne sont pas une mesure universelle de fraîcheur ; elles sont un heuristique liée aux horodatages d’événements.
- Fait 8 : La réplication semi-synchrone dans MySQL est apparue parce que la réplication asynchrone plus la bascule automatique créaient des fenêtres de perte de données inacceptables pour de nombreuses entreprises.
- Fait 9 : La réplication synchrone Postgres permet de choisir combien de standbys doivent acquitter, autorisant une durabilité de type quorum au prix de la latence de tail.
Mode opératoire pour diagnostic rapide
Vous êtes en astreinte. Le trafic de lecture monte en flèche. Quelqu’un dit « ajoutez juste un autre réplica ». Avant ça, trouvez le goulot.
Ce playbook suppose que vous voulez une réponse en moins de 15 minutes, pas un débat philosophique.
Première étape : décidez si le primaire est réellement lié aux lectures
- Vérifiez la CPU et l’I/O wait du primaire : si la CPU est saturée mais l’I/O wait faible, vous êtes peut-être lié au calcul (requêtes mauvaises, index manquants, mauvais plan).
- Vérifiez le hit rate du buffer/cache : si votre working set ne tient pas en mémoire, les réplicas peuvent juste multiplier la douleur I/O.
- Vérifiez les requêtes dominantes : une seule requête peut dévorer une ferme de réplicas au petit-déjeuner.
Deuxième étape : mesurez correctement le lag de réplication
- MySQL : comparez les ensembles GTID, pas les impressions. Seconds behind source est un indice, pas une promesse.
- Postgres : séparez le lag de réception du lag de replay. Réseau vs disque vs CPU se manifestent différemment.
Troisième étape : vérifiez les throttles spécifiques au réplica
- MySQL : thread SQL de réplication qui n’avance pas, workers parallèles mal configurés, transactions longues sur le primaire générant des événements gigantesques.
- Postgres : replay WAL bloqué à cause de l’I/O, backlog de replication slot, conflits hot standby annulant des requêtes ou retardant le nettoyage.
Quatrième étape : validez le routage et le comportement lecture-après-écriture
- Confirmez quel pourcentage du trafic frappe les réplicas vs le primaire.
- Confirmez si les lectures sensibles à la cohérence sont épinglées ou mises en fence.
- Confirmez si votre load balancer échoue silencieusement vers les réplicas lors d’une surcharge du primaire.
Tâches pratiques : commandes, sorties, décisions
Ci-dessous des tâches de niveau production. Chacune inclut une commande, une sortie d’exemple, ce que cela signifie et quelle décision prendre.
Exécutez-les depuis un hôte admin sûr. Ajustez les noms d’utilisateur, sockets et hostnames à votre environnement.
Task 1 (MySQL) : Vérifier l’état de la réplication et le lag
cr0x@server:~$ mysql -e "SHOW REPLICA STATUS\G"
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: mysql-primary-1
Source_Log_File: binlog.003921
Read_Source_Log_Pos: 812993441
Relay_Log_File: relay.000778
Relay_Log_Pos: 813002118
Replica_SQL_Running: Yes
Replica_IO_Running: Yes
Seconds_Behind_Source: 7
Retrieved_Gtid_Set: 2f1c9b2a-...:1-91833122
Executed_Gtid_Set: 2f1c9b2a-...:1-91833098
Ce que cela signifie : les threads I/O et SQL tournent ; le réplica rapporte 7s de retard, mais le GTID montre qu’il manque 24 transactions.
Décision : si votre SLA de fraîcheur est < 1s (commun pour l’état utilisateur), ce réplica ne peut pas servir de lectures après écriture. Épinglez au primaire après les écritures ou faites du fencing sur GTID.
Task 2 (MySQL) : Comparer l’exécution des GTID entre primaire et réplica
cr0x@server:~$ mysql -h mysql-primary-1 -e "SELECT @@global.gtid_executed\G"
*************************** 1. row ***************************
@@global.gtid_executed: 2f1c9b2a-...:1-91833122
cr0x@server:~$ mysql -h mysql-replica-1 -e "SELECT @@global.gtid_executed\G"
*************************** 1. row ***************************
@@global.gtid_executed: 2f1c9b2a-...:1-91833098
Ce que cela signifie : le réplica a du retard sur une plage GTID spécifique ; vous pouvez quantifier la fraîcheur sans deviner.
Décision : utilisez cela pour l’automatisation : orientez la session vers les réplicas seulement après qu’ils aient atteint un GTID cible (ou dirigez vers le primaire d’ici là).
Task 3 (MySQL) : Déterminer si la réplication est limitée par une application mono-thread
cr0x@server:~$ mysql -e "SHOW VARIABLES LIKE 'replica_parallel_workers'; SHOW VARIABLES LIKE 'replica_parallel_type';"
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| replica_parallel_workers | 0 |
+--------------------------+-------+
+------------------------+----------+
| Variable_name | Value |
+------------------------+----------+
| replica_parallel_type | DATABASE |
+------------------------+----------+
Ce que cela signifie : les workers parallèles sont désactivés. Un seul thread SQL applique tout.
Décision : si le taux d’écriture du primaire est élevé et que le lag augmente sous charge, activez la réplication parallèle (prudemment), puis re-mesurez le lag et les contraintes d’ordre de commit.
Task 4 (MySQL) : Vérifier les grosses transactions provoquant des pics de lag
cr0x@server:~$ mysql -e "SHOW PROCESSLIST;"
+----+-----------+-----------------+------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------+-----------------+------+---------+------+------------------------+------------------+
| 12 | repl | 10.0.2.15:45512 | NULL | Binlog Dump GTID | 0 | Source has sent all binlog to replica | NULL |
| 33 | app | 10.0.3.21:60433 | prod | Query | 218 | updating | UPDATE orders ... |
+----+-----------+-----------------+------+---------+------+------------------------+------------------+
Ce que cela signifie : un UPDATE de longue durée sur le primaire peut créer d’énormes événements binlog ; les réplicas les appliquent plus tard, puis « rattrapent » en sauts brutaux.
Décision : fractionnez les mises à jour massives ou exécutez-les hors pic. Si vous ne pouvez pas, arrêtez de servir des lectures sensibles à la cohérence depuis les réplicas pendant l’opération.
Task 5 (Postgres) : Mesurer le lag de réception, flush et replay
cr0x@server:~$ psql -h pg-replica-1 -d postgres -c "SELECT now() AS ts, pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp();"
ts | pg_last_wal_receive_lsn | pg_last_wal_replay_lsn | pg_last_xact_replay_timestamp
-----------------------------+--------------------------+-------------------------+-------------------------------
2025-12-29 14:03:11.120+00 | 3A/9F12A7B0 | 3A/9F0C1028 | 2025-12-29 14:03:05.004+00
(1 row)
Ce que cela signifie : le réplica reçoit le WAL en avance par rapport à ce qu’il a replayé. Le replay accuse un retard ; la dernière transaction re-playée date d’environ ~6 secondes.
Décision : si la réception est en avance mais que le replay lagge, concentrez-vous sur la CPU/disque du réplica et les réglages de replay. Si la réception lagge aussi, regardez le réseau ou l’émetteur WAL du primaire.
Task 6 (Postgres primaire) : Identifier les standbys en retard et leur état
cr0x@server:~$ psql -h pg-primary-1 -d postgres -c "SELECT application_name, state, sync_state, write_lag, flush_lag, replay_lag FROM pg_stat_replication;"
application_name | state | sync_state | write_lag | flush_lag | replay_lag
------------------+-----------+------------+-----------+-----------+------------
pg-replica-1 | streaming | async | 00:00:01 | 00:00:01 | 00:00:06
pg-replica-2 | streaming | async | 00:00:00 | 00:00:00 | 00:00:01
(2 rows)
Ce que cela signifie : Replica-1 replaye avec 6s de retard ; replica-2 est proche du temps réel.
Décision : si votre routeur peut choisir, préférez replica-2 pour les lectures sensibles à la fraîcheur. Investiguer pourquoi replica-1 replaye plus lentement (I/O, CPU, conflits).
Task 7 (Postgres) : Détecter les annulations de requêtes en hot standby
cr0x@server:~$ psql -h pg-replica-1 -d postgres -c "SELECT datname, confl_lock, confl_snapshot, confl_bufferpin, confl_deadlock FROM pg_stat_database_conflicts;"
datname | confl_lock | confl_snapshot | confl_bufferpin | confl_deadlock
---------+------------+----------------+-----------------+----------------
prod | 0 | 17 | 0 | 0
(1 row)
Ce que cela signifie : des conflits de snapshot sont survenus 17 fois — des requêtes ont été annulées car le replay devait supprimer des versions de lignes dont une requête longue dépendait.
Décision : soit déplacez ces requêtes longues ailleurs, ajustez max_standby_streaming_delay, soit acceptez les annulations et implémentez une logique de retry. Ne prétendez pas que c’est « aléatoire ».
Task 8 (Postgres) : Vérifier les replication slots pour rétention runaway de WAL
cr0x@server:~$ psql -h pg-primary-1 -d postgres -c "SELECT slot_name, active, restart_lsn, wal_status FROM pg_replication_slots;"
slot_name | active | restart_lsn | wal_status
------------------+--------+-------------+------------
replica1_slot | f | 39/FF0012A0 | extended
replica2_slot | t | 3A/9E1000A0 | reserved
(2 rows)
Ce que cela signifie : replica1_slot est inactif ; Postgres peut retenir le WAL depuis restart_lsn, ce qui peut remplir le disque.
Décision : si replica-1 est définitivement parti, droppez le slot. S’il est temporairement hors-ligne, budgétez le disque et réparez rapidement le réplica. Ce n’est pas un travail « plus tard ».
Task 9 (Système) : Vérifier la latence disque sur le réplica (goulot commun de replay)
cr0x@server:~$ iostat -x 1 3
Linux 6.5.0 (pg-replica-1) 12/29/2025 _x86_64_ (8 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
12.30 0.00 3.90 24.10 0.00 59.70
Device r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
nvme0n1 120.0 380.0 8120.0 48000.0 279.0 9.80 21.40 1.20 60.00
Ce que cela signifie : l’iowait est élevé ; le temps d’attente moyen est ~21ms. Le replay WAL est probablement contraint par l’I/O.
Décision : arrêtez d’accuser la « réplication Postgres ». Corrigez le stockage : disque plus rapide, moins de contention, séparer le WAL, ajuster le checkpointing, ou réduire les charges concurrentes sur le réplica.
Task 10 (MySQL) : Vérifier si le semi-sync est activé (et ce que cela implique)
cr0x@server:~$ mysql -h mysql-primary-1 -e "SHOW VARIABLES LIKE 'rpl_semi_sync_master_enabled'; SHOW STATUS LIKE 'Rpl_semi_sync_master_status';"
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| rpl_semi_sync_master_enabled | ON |
+-------------------------------+-------+
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_master_status| ON |
+----------------------------+-------+
Ce que cela signifie : le semi-sync est activé et actif. Les commits devraient attendre qu’au moins un réplica acquitte la réception des événements.
Décision : bien pour réduire la perte de données à la bascule, mais ça ne garantit pas que le réplica est frais pour les lectures. Ne considérez pas cela comme « sûr pour lire immédiatement depuis les réplicas ».
Task 11 (Postgres) : Confirmer la configuration de réplication synchrone
cr0x@server:~$ psql -h pg-primary-1 -d postgres -c "SHOW synchronous_commit; SHOW synchronous_standby_names;"
synchronous_commit
-------------------
on
(1 row)
synchronous_standby_names
--------------------------
FIRST 1 (pg-replica-2)
(1 row)
Ce que cela signifie : le primaire attend un standby nommé (replica-2) pour les sémantiques de commit synchrone.
Décision : si vous comptez sur cela pour la durabilité, surveillez que le standby nommé est sain. S’il tombe, votre système peut se bloquer ou repasser en asynchrone selon les réglages et l’orchestration.
Task 12 (Postgres) : Identifier les requêtes lentes sur un réplica (les réplicas peuvent aussi être surchargés)
cr0x@server:~$ psql -h pg-replica-1 -d prod -c "SELECT pid, now()-query_start AS age, state, wait_event_type, wait_event, left(query,120) AS q FROM pg_stat_activity WHERE state <> 'idle' ORDER BY age DESC LIMIT 5;"
pid | age | state | wait_event_type | wait_event | q
------+----------+--------+-----------------+--------------+----------------------------------------------------------
8412 | 00:03:41 | active | IO | DataFileRead | SELECT ... FROM events WHERE ... ORDER BY created_at DESC
9120 | 00:01:12 | active | CPU | | SELECT ... FROM orders JOIN order_items ...
(2 rows)
Ce que cela signifie : le réplica est occupé. Une requête attend de l’I/O ; une autre est gourmande en CPU.
Décision : si les réplicas sont surchargés, ajouter plus de réplicas peut aider — mais seulement si vous corrigez le pattern de requêtes ou l’indexation. Sinon vous scalez la douleur horizontalement.
Task 13 (MySQL) : Observer la distribution des lectures (sanity check proxy/load balancer)
cr0x@server:~$ mysql -h mysql-primary-1 -e "SHOW GLOBAL STATUS LIKE 'Questions';"
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| Questions | 819332199 |
+---------------+-----------+
cr0x@server:~$ mysql -h mysql-replica-1 -e "SHOW GLOBAL STATUS LIKE 'Questions';"
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| Questions | 83322188 |
+---------------+-----------+
Ce que cela signifie : le primaire reçoit encore la majorité des requêtes. Soit le routage ne fonctionne pas, soit votre appli épingle trop agressivement.
Décision : avant d’ajouter des réplicas, corrigez le routage. Si vous ne pouvez pas prouver que le trafic de lecture a bougé, vous achetez juste du matériel pour embellir des dashboards.
Task 14 (Système) : Vérifier les retransmissions réseau (la réplication streaming n’aime pas la perte de paquets)
cr0x@server:~$ ss -ti dst 10.0.1.10:5432 | head -n 20
ESTAB 0 0 10.0.2.20:48932 10.0.1.10:5432
cubic wscale:7,7 rto:204 rtt:2.1/0.9 ato:40 mss:1448 pmtu:1500 rcvmss:1448 advmss:1448 cwnd:10 bytes_sent:9231123 bytes_acked:9231000 bytes_received:1840012 segs_out:6123 segs_in:5981 data_segs_out:5123 data_segs_in:4981 send 55.2Mbps lastsnd:8 lastrcv:7 lastack:7 pacing_rate 110Mbps retrans:14/231
Ce que cela signifie : il y a des retransmissions. Quelques-unes sont normales ; un nombre croissant avec du lag suggère des problèmes réseau provoquant le lag de réception.
Décision : si le lag de réception augmente avec les retransmissions, ne « tunez » pas Postgres. Réparez le chemin réseau ou déplacez le trafic de réplication sur un lien moins congestionné.
Trois mini-histoires tirées de la vie en entreprise
Mini-histoire 1 : Un incident causé par une mauvaise hypothèse
Une entreprise de taille moyenne avait une architecture propre sur le papier : base primaire pour les écritures, deux réplicas de lecture pour la « montée en charge », et un proxy qui routait les SELECT.
L’équipe feature a ajouté un flux « annuler l’abonnement ». Il écrivait sur le primaire, puis lisait immédiatement l’état de l’abonnement pour décider de l’UI suivante.
Sous faible charge tout semblait bon. Sous pic, c’est devenu un générateur de tickets support.
L’hypothèse erronée était simple : « le réplica est pratiquement en temps réel. » Le proxy n’avait aucune notion de cohérence de session. Il ne savait pas qu’un utilisateur venait d’écrire.
Parfois la lecture post-annulation tombait sur un réplica affichant encore « actif ». L’UI proposait de mauvaises actions. Les clients réessayaient. Les réessais généraient plus d’écritures.
Le lag empirait. Vous devinez la suite.
La partie amusante : les dashboards semblaient « sains ». CPU des réplicas basse. CPU du primaire modérée. Latence correcte. La seule métrique qui hurlait était les tickets support.
Les ingénieurs ont d’abord chassé le cache front-end, puis des narratifs d’« eventual consistency », puis ils ont essayé d’ajouter un troisième réplica. Cela n’a rien amélioré parce que le problème n’était pas la capacité. C’était la sémantique.
La correction fut ennuyeuse et immédiate : après une écriture dans ce workflow, ils ont épinglé les lectures au primaire pendant 30 secondes pour ce token utilisateur/session.
Ils ont aussi ajouté une métrique de « fraîcheur du réplica » au proxy pour éviter totalement les réplicas en retard.
L’incident a pris fin, et tout le monde a fait semblant d’avoir toujours su que c’était un problème de routage.
Mini-histoire 2 : Une optimisation qui s’est retournée contre eux
Une équipe entreprise a tenté de réduire la charge primaire en déplaçant « toutes les requêtes de reporting » vers un hot standby Postgres. Raisonnable. Sauf que les requêtes de reporting n’étaient pas des rapports ;
c’étaient des dashboards ad-hoc avec des scans de plusieurs minutes et une habitude d’exporter des CSV en fin de trimestre.
Sur le papier c’était sûr parce que le standby ne servait pas d’écritures. En réalité, ces snapshots longs retenaient les attentes de nettoyage et déclenchaient des conflits standby.
L’équipe ops a ajusté les réglages pour éviter d’annuler les requêtes — parce que les dirigeants détestent les dashboards partiels. Cela a réduit les annulations mais augmenté le délai de replay WAL.
Puis un test de bascule est arrivé. Le standby promu a mis plus de temps que prévu à devenir cohérent, et un lot de lectures sensibles au temps ont été servis depuis un nœud en retard de plusieurs minutes.
Rien n’était « corrompu ». Ce n’était simplement pas à jour. Pendant ce temps, l’utilisation du disque primaire a augmenté parce que le WAL était conservé plus longtemps que prévu.
L’optimisation contre-productive était de mélanger les charges « reporting à temps humain » avec les responsabilités d’un standby opérationnel. La bonne correction était de séparer les responsabilités :
un réplica analytique dédié avec annulation agressive autorisée, et un standby séparé configuré pour un replay rapide et un comportement de bascule prévisible.
Mini-histoire 3 : Une pratique ennuyeuse mais correcte qui a sauvé la mise
Une plateforme liée aux paiements utilisait à la fois MySQL et Postgres dans des domaines différents. Ils avaient une règle qui sonnait bureaucratique :
tout réplica utilisé pour les lectures doit publier un signal de fraîcheur, et le routeur doit le refuser s’il dépasse le budget de lag.
Les gens se sont plaints lors des réunions de planification. « On ajoutera juste plus de réplicas. » « On n’a pas besoin de cette complexité. »
La réponse SRE était cohérente : « Si on ne peut pas mesurer la fraîcheur, on ne peut pas garantir la cohérence. »
Ils ont donc mis en place du fencing : les workflows MySQL stockaient le dernier GTID de la transaction d’écriture ; les workflows Postgres stockaient le dernier LSN.
Un an plus tard, un contrôleur de stockage d’un réplica a commencé à caler les écritures de façon intermittente. Le réplica semblait « up », acceptait des connexions et servait des lectures.
Mais l’application WAL/relay ralentissait et le lag dérivait au-delà du budget.
Le routeur a automatiquement drainé ce réplica de la pool. Aucun impact client. L’astreinte a reçu une alerte claire : « fraîcheur du réplica violée », pas « les clients sont en colère ».
Ils ont remplacé le hardware défaillant pendant les heures de bureau, parce que des garde-fous ennuyeux vous achètent des incidents ennuyeux.
Erreurs courantes : symptôme → cause racine → correctif
1) Les utilisateurs ne voient pas leurs mises à jour
Symptôme : Après une mise à jour, l’UI affiche l’ancienne valeur pendant quelques secondes ; un rafraîchissement corrige parfois.
Cause racine : lecture après écriture routée vers des réplicas asynchrones ; pas de cohérence de session.
Correctif : épingler la session au primaire après les écritures, ou faire du fencing avec GTID/LSN. Ajouter l’application d’un budget de lag des réplicas dans le routeur.
2) « Seconds behind source » est bas mais les données manquent quand même
Symptôme : MySQL affiche 0–1s de retard, mais une ligne juste commitée n’est pas sur le réplica.
Cause racine : le metric est basé sur des timestamps et peut induire en erreur ; l’application SQL peut être momentanément bloquée ou l’horodatage de l’événement diffère.
Correctif : comparez les ensembles GTID ; faites du fencing basé sur le GTID exécuté. Traitez Seconds_Behind_Source comme un détecteur de fumée, pas comme une règle métrique.
3) Le lag du réplica croît linéairement pendant le pic, puis « rattrape » en rafales
Symptôme : le lag augmente régulièrement puis chute soudainement ; les lectures sont obsolètes par intermittence.
Cause racine : grosses transactions ou application mono-thread ; le réplica ne peut pas paralléliser le travail.
Correctif : fragmenter les écritures ; activer / vérifier l’application parallèle ; éviter de grosses mises à jour multi-tableaux aux heures de pointe.
4) Le standby Postgres annule des requêtes
Symptôme : les rapports échouent avec « canceling statement due to conflict with recovery ».
Cause racine : conflits hot standby entre le replay (cleanup) et des requêtes longues.
Correctif : rendre les requêtes plus rapides, déplacer le reporting hors standby, ou accepter les annulations avec retries. Ajustez intentionnellement les seuils de délai du standby.
5) Le disque du primaire se remplit de façon inattendue (Postgres)
Symptôme : le répertoire WAL grossit rapidement ; alertes disque ; réplication par ailleurs « ok ».
Cause racine : un replication slot inactif retient le WAL pour un réplica manquant ou bloqué.
Correctif : droppez les slots inutilisés ; surveillez le lag des slots ; traitez les slots inactifs comme un risque de production, pas une curiosité.
6) Ajouter des réplicas ne réduit pas la charge du primaire
Symptôme : vous ajoutez deux réplicas ; la CPU primaire change à peine.
Cause racine : le routage n’envoie pas le trafic significatif, ou le trafic important est lié aux écritures, ou l’appli épingle trop.
Correctif : mesurez le nombre de requêtes par nœud ; validez les règles du routeur ; classifiez les lectures par besoin de cohérence ; corrigez d’abord les requêtes lentes.
7) Les réplicas sont rapides mais l’application ralentit
Symptôme : les nœuds DB semblent corrects ; le p95 applicatif augmente ; les timeouts augmentent.
Cause racine : tempêtes de connexions, dimensionnement incorrect des pools, ou retries du load balancer provoquant des herds sur les réplicas.
Correctif : limitez les pools de connexions ; utilisez du pooling transactionnel si possible ; implémentez des coupe-circuits quand les réplicas dépassent le lag ou le taux d’erreur.
8) La bascule cause des « données manquantes » (en réalité : écritures reconnues perdues)
Symptôme : après le crash du primaire et la promotion, les dernières secondes de transactions ont disparu.
Cause racine : réplication asynchrone ; la bascule a promu un réplica qui n’avait pas reçu/appliqué ces écritures.
Correctif : utilisez la réplication synchrone pour ces transactions, ou acceptez explicitement le RPO. Alignez l’automatisation de bascule avec votre modèle de durabilité.
Blague n°2 : Un réplica de lecture est une base honnête avec une mauvaise mémoire. Malheureusement, vos utilisateurs se fichent de son histoire.
Listes de contrôle / plan étape par étape
Étape par étape : décider si les réplicas aideront votre charge
- Classifiez les lectures : marquez les endpoints/requêtes comme « fraîche requise » vs « obsolescence acceptable ». Si vous ne pouvez pas, supposez fraîche requise.
- Mesurez le goulot du primaire : CPU, iowait, temps de lock, hit rate du buffer, requêtes dominantes.
- Établissez un budget de lag : choisissez un chiffre (ex. 250ms, 1s, 5s) par classe de lecture. Notez-le.
- Choisissez une politique de routage : épinglage au primaire après écritures ou fencing GTID/LSN. Ne mettez pas les réplicas en production sans une politique.
- Instrumentez la fraîcheur des réplicas : exportez des métriques de lag reflétant votre mécanisme de fencing choisi.
- Testez en charge avec réplication : incluez des taux d’écriture réalistes ; mesurez le débit d’application des réplicas, pas seulement le débit de requêtes.
- Préparez la défaillance d’un réplica : que se passe-t-il si un réplica est lent, down, ou obsolète ? Votre routeur doit dégrader proprement vers le primaire.
Étape par étape : construire un parcours de lecture « les réplicas ne mentent pas »
- À l’écriture : capturez le GTID (MySQL) ou le LSN (Postgres) depuis le contexte de transaction.
- Stockez-le par session/requête : dans un cookie, token, ou store côté serveur.
- Avant une lecture sur réplica : choisissez un réplica qui a exécuté/replayé au moins cette position.
- Si aucun ne convient : lisez depuis le primaire, ou attendez un petit timeout (dangereux ; peut amplifier la charge).
- Appliquez un plafond global de lag : si un réplica dépasse le budget, draguez-le automatiquement.
- Auditez les endpoints trimestriellement : les nouvelles fonctionnalités ont tendance à glisser des lectures sensibles à la cohérence dans des pools « read-scaled ».
Checklist opérationnelle : les réplicas comme fonctionnalité SRE, pas comme slide d’architecture
- Alertes de lag des réplicas qui page au point d’incohérence visible par l’utilisateur, pas à « patience infinie ».
- Capacity plan pour les réplicas : CPU et I/O disque dimensionnés pour le replay et la charge de requêtes simultanément.
- Runbooks pour : reseeder les réplicas, récupérer d’un backlog slot/WAL, gérer des relay logs cassés, et bascule contrôlée.
- Test de chaos : tuez un réplica, introduisez 1% de perte de paquets, bridez le disque — vérifiez que le routage vide et que le fencing fonctionne encore.
- Des sauvegardes existent et sont testées. La réplication n’est pas une sauvegarde ; c’est la duplication de vos erreurs à la vitesse du réseau.
FAQ
1) Puis-je utiliser des réplicas de lecture pour mettre à l’échelle les écritures ?
Non. Les réplicas mettent à l’échelle les lectures (et certaines tâches opérationnelles). Les écritures restent un goulot sur le primaire à moins de sharder ou redessiner.
Si votre primaire est limité en écriture, les réplicas ne le résoudront pas ; ils peuvent même l’aggraver en ajoutant de l’overhead de réplication.
2) La réplication PostgreSQL est-elle « plus cohérente » que MySQL ?
Les deux peuvent être configurés pour des garanties fortes de durabilité avec la réplication synchrone, et les deux fonctionnent fréquemment en mode asynchrone pour la performance.
La différence n’est pas morale ; elle est mécanique et observable. Postgres donne une visibilité de lag plus granulaire ; l’outillage GTID de MySQL peut être excellent s’il est bien utilisé.
3) Le semi-sync MySQL signifie-t-il que mes lectures sur réplicas sont fraîches ?
Non. Le semi-sync signifie typiquement qu’un réplica a accusé réception de la réception des événements avant que le commit ne retourne. Il ne garantit pas que le réplica les a appliqués
et les rend visibles pour la lecture.
4) Quelle est la stratégie de routage la plus simple et sûre ?
L’épinglage au primaire après les écritures pour un court TTL est la plus simple. Ce n’est pas parfait, mais c’est généralement suffisant et facile à raisonner.
Si vous avez besoin d’une cohérence plus stricte avec moins de lectures primaires, passez au fencing GTID/LSN.
5) Combien de réplicas dois-je avoir ?
« Assez pour gérer le pic de lecture avec un réplica down » est un point de départ pratique. Ajoutez ensuite des exigences :
un standby séparé pour la bascule, un réplica séparé pour le reporting, et peut-être un autre pour la maintenance/test.
Mélanger tous les rôles sur un même réplica provoque des conflits de rôle pendant les incidents.
6) Pourquoi le lag des réplicas augmente-t-il pendant les sauvegardes ou la maintenance ?
Les sauvegardes peuvent saturer le disque et évincer le cache. Les jobs de maintenance génèrent de lourdes I/O et charge CPU. L’application de la réplication concurrence ces mêmes ressources.
La correction est l’isolation : dédier des réplicas pour backup/reporting, brider la maintenance, et surveiller le replay / thread SQL.
7) Les réplicas remplacent-ils le caching ?
Pas vraiment. Les réplicas exécutent toujours des requêtes et touchent toujours le stockage. Les caches absorbent les lectures répétées à moindre coût ; les réplicas répartissent l’exécution des requêtes.
Utilisez des caches pour les clés/popularité ou réponses calculées ; utilisez des réplicas pour délester des lectures complexes et isoler des workloads.
8) Quelle est la façon la plus rapide de dire si je dois ajouter un réplica ou corriger des requêtes ?
Si les quelques requêtes en tête dominent le temps et sont lentes à cause d’index manquants ou mauvais plans, corrigez les requêtes d’abord.
Si vous avez beaucoup de lectures modérément coûteuses et que le primaire est lié CPU, les réplicas peuvent aider.
Si vous êtes I/O-bound parce que le working set ne tient pas en mémoire, les réplicas multiplient souvent la contention disque.
9) Puis-je faire des réplicas multi-région pour faible latence ?
Oui, mais vous achetez de la latence contre de l’obsolescence. La réplication asynchrone inter-régions restera en retard sous tout débit d’écriture significatif.
Pour les lectures sensibles à la cohérence, vous aurez encore besoin d’épinglage au primaire ou de fencing, et ceux-ci ont tendance à ramener le trafic vers la région d’écriture.
10) Quelle citation dois-je retenir quand je suis tenté de « juste ajouter des réplicas » ?
« L’espoir n’est pas une stratégie. » — idée paraphrasée attribuée à la culture engineering/ops.
La version opérationnelle : mesurez le lag, appliquez des budgets, et concevez pour la cohérence dont vous avez réellement besoin.
Prochaines étapes réalisables cette semaine
Si vous voulez des réplicas qui aident plutôt que qui mentent, faites ceci dans l’ordre :
- Notez votre budget d’obsolescence : par endpoint ou par workflow. Si vous ne pouvez pas, supposez 0 pour tout ce qui implique argent, auth ou permissions.
- Mesurez le lag avec le bon primitif : ensembles GTID pour MySQL ; réception/replay et LSN pour Postgres.
- Ajoutez une règle de routage : pin-to-primary après les écritures, ou implémentez le fencing GTID/LSN pour les sessions.
- Drainer automatiquement les réplicas obsolètes : un réplica hors budget doit être traité comme une panne partielle, pas comme une petite gêne.
- Séparez les rôles : ne faites pas le reporting de fin de trimestre sur le même standby que celui que vous comptez promouvoir lors d’une panne primaire.
- Corrigez les pires requêtes : les réplicas ne sont pas une excuse pour garder une requête de 90 secondes en production. Optimisez-la ou isolez-la.
Les réplicas sont un outil. Les bons outils coupent toujours quand on les utilise imprudemment. Si vous concevez votre chemin de lecture avec des règles explicites de fraîcheur et que vous les appliquez,
les réplicas deviennent ennuyeux — et l’ennui est ce que vous voulez à 3h du matin.