Latence de réplication MySQL vs PostgreSQL : pourquoi elle survient et comment la réduire

Ce qu’est réellement la latence de réplication (et ce qu’elle n’est pas)

La latence de réplication est un écart entre le commit sur le primaire et la visibilité sur la réplique. Cela semble évident jusqu’à ce que vous réalisiez qu’il existe au moins trois « écarts » cachés sous le même mot :

• Latence de transport : le primaire a généré des changements, mais la réplique ne les a pas encore reçus (réseau, limitation, rafale).
• Latence d’application/rejeu : la réplique a reçu les changements mais ne les a pas appliqués (CPU, I/O, contention, application mono-thread, conflit).
• Latence de visibilité : les changements sont appliqués mais ne sont pas visibles pour une requête/session en particulier à cause des règles de snapshot (fréquent en Postgres avec des requêtes longues).

Si vous traitez tout cela comme une seule métrique, vous « corrigez » la mauvaise chose. La latence est toujours une file d’attente. Les files ont deux remèdes : augmenter le débit de service, ou réduire le taux d’arrivée. Tout le reste est de la décoration.

Deux définitions à faire respecter en interne

Latence opérationnelle : « Combien de temps avant qu’une écriture commitée sur le primaire devienne interrogeable sur la réplique ? » C’est ce qui importe aux applications.

État de santé du pipeline de réplication : « À quel point la réplique est proche d’ingérer et de rejouer le flux de changements du primaire ? » C’est ce que surveillent les SRE.

Faits intéressants et contexte historique (parce que l’histoire évite de la répéter)

1. La réplication MySQL a commencé par être basée sur des instructions, ce qui rendait le terme « déterministe » plus une prière qu’une garantie. Le logging basé sur les lignes est devenu le choix sensé par la suite.
2. La réplication de streaming intégrée de PostgreSQL (WAL physique) est arrivée durant l’ère 9.x et a transformé la « standby » de « restauration depuis une archive » à « quasi-temps réel ».
3. La réplication semi-synchrone de MySQL a été introduite pour réduire le risque de perte de données, mais elle peut échanger latence contre durabilité — vos SLOs le ressentiront.
4. Les GTID dans MySQL ont rendu le basculement et les changements de topologie moins sujets aux erreurs, mais ils ont aussi facilité la construction d’automatisations dangereusement confiantes.
5. Le hot standby de Postgres a permis des requêtes en lecture seule sur les répliques, mais il a aussi introduit des comportements de conflit de requêtes qui ressemblent à des « annulations aléatoires » si vous n’y prenez pas garde.
6. La réplication logique dans Postgres est arrivée après la réplication physique et n’est pas « physique mais meilleure » ; c’est un outil différent avec des modes de panne différents.
7. Le parallélisme de réplication dans MySQL a évolué selon les versions : les premières implémentations étaient limitées, les suivantes ont mieux parallélisé l’application. Votre version compte.
8. Les métriques de latence des réplicas mentent par omission : « seconds behind master » dans MySQL n’est pas une vérité universelle ; c’est une estimation basée sur un timestamp avec des angles morts.

Une citation à garder sur votre mur, car elle empêche les comportements héroïques pendant les incidents :

« L’espoir n’est pas une stratégie. » — Vince Lombardi

La latence de réplication est l’endroit où l’espoir vient mourir. Tant mieux. Maintenant vous pouvez concevoir.

Comment la réplication MySQL crée de la latence (et comment elle la rattrape)

La réplication MySQL classique est trompeusement simple : le primaire écrit les binlogs, les réplicas les récupèrent et les appliquent. Le diable est dans la manière dont ils les appliquent.

Le pipeline de réplication MySQL (vue pratique)

• Primaire génère des événements binlog (basé sur les instructions ou les lignes ; en production on privilégie généralement le mode ligne).
• Thread I/O sur la réplique lit les événements du primaire et les écrit dans les relay logs.
• Thread(s) SQL appliquent les événements des relay logs aux données de la réplique.

La latence survient quand les relay logs croissent plus vite que les threads SQL ne peuvent appliquer. C’est tout. Le reste explique pourquoi cela s’est produit.

Formes de latence communes en MySQL

• Grosse transaction unique : la réplique semble « coincée » puis rattrape soudainement. Le binlog est correct ; l’application est occupée à mâcher.
• Beaucoup de petites transactions : la réplique dérive lentement en période de pointe et ne récupère jamais. Il faut plus de débit d’application ou moins d’écritures.
• Contention de verrous sur la réplique : les requêtes de lecture sur la réplique bloquent l’application (ou inversement), produisant un graphique de latence en dents de scie.

Une vérité sèche : dans MySQL, la réplique n’est pas votre miroir passif. C’est un second serveur de base de données qui travaille vraiment, en concurrence pour le CPU, l’I/O et le buffer pool.

Comment la réplication PostgreSQL crée de la latence (et comment elle la rattrape)

La réplication de streaming PostgreSQL est le shipping du WAL sur une connexion persistante. Les réplicas (standbys) reçoivent des enregistrements WAL et les rejouent. Avec le hot standby, ils servent aussi des requêtes en lecture seule.

Le pipeline de réplication Postgres (streaming physique)

• Primaire écrit des enregistrements WAL pour les changements.
• WAL sender stream le WAL vers les réplicas.
• WAL receiver sur la réplique écrit le WAL reçu sur disque.
• Processus de startup/replay rejoue le WAL pour rendre les fichiers de données cohérents avec la timeline du primaire.

La latence survient quand le processus de replay ne peut pas suivre, ou quand il est forcé de faire une pause/ralentir à cause de conflits ou de contraintes de ressources.

Réalités spécifiques de la latence Postgres

• Le replay peut être bloqué par des conflits de recovery (hot standby) : des requêtes longues peuvent empêcher le cleanup par vacuum sur le primaire, et sur la réplique elles peuvent être annulées ou provoquer un retard de replay selon les paramètres.
• Des pics de volume WAL peuvent être causés par des mises à jour massives, des rebuilds d’index ou des réécritures de tables ; c’est plus de la « physique » que du « tuning ».
• Les replication slots empêchent la suppression du WAL ; si une réplique est hors ligne ou lente, le WAL s’accumule sur le primaire jusqu’à ce que votre disque panique.

Deuxième blague (et dernière) : la latence de réplication ressemble à une réunion d’entreprise — si une personne insiste pour parler sans fin, tout le monde prend du retard.

Méthode de diagnostic rapide

C’est l’ordre qui trouve le goulet le plus vite dans des systèmes réels. Ne faites pas de freestyle. Votre intuition n’est pas observable.

Premier : classer le type de latence

1. Latence de transport ? Le primaire génère des changements, la réplique ne les reçoit pas assez vite.
2. Latence d’application/replay ? La réplique a les données (relay/WAL reçu), mais le replay est en retard.
3. Latence de visibilité ? C’est appliqué, mais les requêtes voient toujours d’anciens snapshots (transactions longues, sémantique repeatable read, etc.).

Second : décider où la file grossit

• MySQL : taille des relay logs, état du thread SQL, états des workers de réplication.
• Postgres : LSN reçu vs LSN rejoué, délai de replay, conflits, statut du WAL receiver.

Troisième : trouver la contrainte de ressources

• Limitation I/O : await élevé, faible marge IOPS, vidage de pages sales, pression WAL/fsync.
• Limitation CPU : CPU élevé, noyau unique saturé (souvent le thread d’application), surcharge compression/décompression.
• Verrou/contestation : application en attente de verrous, DDL, ou conflits hot standby.
• Borne réseau : perte de paquets, faible débit, tailles de buffers TCP mal adaptées, réalité cross-région.

Quatrième : choisir le levier le moins dangereux

• Privilégiez les changements de charge (taille des batchs, taille des transactions, index, modèles de requêtes) plutôt que les « boutons magiques ».
• Privilégiez l’ajout de parallélisme là où c’est sûr (apply parallèle MySQL, plus de capacité I/O) plutôt que « désactiver la durabilité ».
• Privilégiez le déplacement des lectures hors de la réplique pendant le rattrapage (feature flags, circuit breakers) plutôt que de laisser les réplicas se noyer.

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

Voici des vérifications réelles et exécutables que vous pouvez faire pendant un incident. Chacune inclut ce que signifie la sortie et quelle décision prendre. Suivez cet ordre sauf preuve contraire.

Task 1 (MySQL): Check basic replica status and identify the bottleneck thread

cr0x@server:~$ mysql -e "SHOW SLAVE STATUS\G" | egrep "Seconds_Behind_Master|Slave_IO_Running|Slave_SQL_Running|Slave_SQL_Running_State|Last_SQL_Error|Last_IO_Error|Relay_Log_Space"
Seconds_Behind_Master: 187
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Slave_SQL_Running_State: Waiting for dependent transaction to commit
Relay_Log_Space: 4294967296
Last_SQL_Error:
Last_IO_Error:

Signification : Le thread I/O est sain (transport probablement OK). Le thread SQL s’exécute mais est bloqué sur une dépendance de commit ; les relay logs sont énormes. L’application est la file.

Décision : Inspecter les paramètres de réplication parallèle et l’état des workers. Si les attentes de dépendance persistent, rechercher de grosses transactions ou des goulots liés à l’ordre des commits. Envisager d’augmenter l’IO/CPU sur la réplique et de réduire les lectures longues.

Task 2 (MySQL): Inspect replication workers (parallel apply health)

cr0x@server:~$ mysql -e "SELECT WORKER_ID, THREAD_ID, SERVICE_STATE, LAST_APPLIED_TRANSACTION, LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP, APPLYING_TRANSACTION FROM performance_schema.replication_applier_status_by_worker\G"
*************************** 1. row ***************************
WORKER_ID: 1
THREAD_ID: 52
SERVICE_STATE: ON
LAST_APPLIED_TRANSACTION: 0f3a2b1c-11aa-11ee-9d5b-0800272b3f7a:912334
LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 2025-12-31 11:58:01.123456
APPLYING_TRANSACTION: 0f3a2b1c-11aa-11ee-9d5b-0800272b3f7a:912390
*************************** 2. row ***************************
WORKER_ID: 2
THREAD_ID: 53
SERVICE_STATE: ON
LAST_APPLIED_TRANSACTION: 0f3a2b1c-11aa-11ee-9d5b-0800272b3f7a:912333
LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 2025-12-31 11:58:01.120000
APPLYING_TRANSACTION:

Signification : Certains workers sont inactifs tandis qu’un seul applique. Le parallélisme existe mais n’est pas utilisé efficacement (la charge n’est pas parallélisable ou est bloquée par l’ordre des commits).

Décision : Si la charge porte sur une seule ligne chaude ou un seul objet de schéma, l’application parallèle ne vous sauvera pas. Concentrez-vous sur des changements de schéma/charge (sharding des points chauds, réduire la contention) ou acceptez la latence et redirigez les lectures différemment.

Task 3 (MySQL): Identify the SQL thread’s current wait (locks vs I/O vs commit)

cr0x@server:~$ mysql -e "SHOW PROCESSLIST" | awk 'NR==1 || $4 ~ /Connect|Binlog Dump|SQL/ {print}'
Id	User	Host	db	Command	Time	State	Info
14	system user		replication	Connect	187	Waiting for relay log		NULL
15	system user		replication	Connect	187	Waiting for dependent transaction to commit	NULL
102	app_ro	10.0.2.55:61234	prod	Query	32	Sending data	SELECT ...

Signification : L’application SQL de la réplique attend des dépendances de commit ; une requête de lecture tourne depuis 32s et peut provoquer du churn de buffer ou une pression de verrous.

Décision : Si les lectures sont lourdes, les limiter/tuer pendant le rattrapage, ou les déplacer vers une réplique dédiée analytique. Si les attentes de dépendance de commit dominent, explorer les limites de group commit et le tuning de l’application parallèle.

Task 4 (MySQL): Confirm binlog format and row image (WAL/binlog volume control)

cr0x@server:~$ mysql -e "SHOW VARIABLES WHERE Variable_name IN ('binlog_format','binlog_row_image','sync_binlog','innodb_flush_log_at_trx_commit')"
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| binlog_format                | ROW       |
| binlog_row_image             | FULL      |
| innodb_flush_log_at_trx_commit | 1       |
| sync_binlog                  | 1         |
+------------------------------+-----------+

Signification : Paramètres durables (bien) mais volume de binlog potentiellement élevé si image de ligne FULL sur des tables larges.

Décision : Envisager binlog_row_image=MINIMAL seulement si vous avez validé la compatibilité des outils et la sécurité pour votre charge ; sinon optimiser d’abord le schéma et la taille des transactions.

Task 5 (MySQL): Check InnoDB flush pressure on the replica

cr0x@server:~$ mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty'; SHOW GLOBAL STATUS LIKE 'Innodb_data_pending_fsyncs'; SHOW GLOBAL STATUS LIKE 'Innodb_os_log_pending_fsyncs';"
+--------------------------------+--------+
| Variable_name                  | Value  |
+--------------------------------+--------+
| Innodb_buffer_pool_pages_dirty | 184223 |
+--------------------------------+--------+
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| Innodb_data_pending_fsyncs| 67    |
+---------------------------+-------+
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| Innodb_os_log_pending_fsyncs | 29 |
+---------------------------+-------+

Signification : Pages dirty et fsyncs en attente impliquent que le stockage est le facteur limitant ; l’application ne peut pas flusher assez vite en toute sécurité.

Décision : Ajouter des IOPS (disques plus rapides, stockage mieux provisionné), tuner le flushing, ou réduire l’amplification des écritures (index, batching de transactions). Ne « corrigez » pas cela en désactivant fsync sauf si vous aimez les exercices de perte de données.

Task 6 (Postgres): Check received vs replayed LSN (transport vs replay)

cr0x@server:~$ psql -x -c "SELECT now() AS ts, pg_last_wal_receive_lsn() AS receive_lsn, pg_last_wal_replay_lsn() AS replay_lsn, pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) AS bytes_pending;"
-[ RECORD 1 ]--+------------------------------
ts             | 2025-12-31 12:00:12.1122+00
receive_lsn    | 3A/9F1200A0
replay_lsn     | 3A/9E8803D8
bytes_pending  | 58982400

Signification : La réplique reçoit le WAL mais a environ 56Mo de retard en replay. Le transport est OK ; le replay est la file.

Décision : Vérifier CPU/I/O sur la réplique et les conflits hot standby. Si bytes_pending croît en période de pointe, il vous faut plus de débit de replay ou moins d’écritures générant beaucoup de WAL.

Task 7 (Postgres): Measure time-based lag as seen by the system

cr0x@server:~$ psql -x -c "SELECT now() AS ts, pg_last_xact_replay_timestamp() AS last_replay_ts, now() - pg_last_xact_replay_timestamp() AS replay_delay;"
-[ RECORD 1 ]---+------------------------------
ts              | 2025-12-31 12:00:20.004+00
last_replay_ts  | 2025-12-31 11:56:59.771+00
replay_delay    | 00:03:20.233

Signification : Environ 3m20s de délai de replay. C’est plus proche de « ce que ressentent les applications » que le nombre d’octets.

Décision : Si votre activité ne peut pas tolérer cela, rediriger les lectures critiques vers le primaire, implémenter read-your-writes, ou investir dans des approches synchrones/quorum (en connaissant le coût en latence).

Task 8 (Postgres): Check WAL receiver status and any network symptoms

cr0x@server:~$ psql -x -c "SELECT status, receive_start_lsn, received_lsn, latest_end_lsn, latest_end_time, conninfo FROM pg_stat_wal_receiver;"
-[ RECORD 1 ]---+---------------------------------------------
status          | streaming
receive_start_lsn | 3A/9B000000
received_lsn    | 3A/9F1200A0
latest_end_lsn  | 3A/9F1200A0
latest_end_time | 2025-12-31 12:00:18.882+00
conninfo        | host=10.0.1.10 port=5432 user=replicator ...

Signification : Le streaming est sain ; latest_end_time est à jour. Le transport n’est pas le goulot.

Décision : Arrêtez de blâmer « le réseau ». Regardez les contraintes de replay : stockage, CPU, conflits et checkpoints.

Task 9 (Postgres): Detect hot standby conflicts and cancellations

cr0x@server:~$ psql -c "SELECT datname, confl_snapshot, confl_lock, confl_bufferpin, confl_deadlock FROM pg_stat_database_conflicts;"
  datname  | confl_snapshot | confl_lock | confl_bufferpin | confl_deadlock
-----------+----------------+------------+-----------------+----------------
 postgres  |              0 |          0 |               0 |              0
 prod      |            144 |          3 |              12 |              0

Signification : Des conflits sont réels sur prod ; les conflits de snapshot sont fréquents lorsque des requêtes longues tournent pendant que le replay doit supprimer de vieilles versions de lignes.

Décision : Si vous privilégiez la fraîcheur des données plutôt que les lectures longues, configurez l’annulation plus agressive des requêtes. Si vous privilégiez la stabilité des lectures longues, acceptez la latence et adaptez la logique applicative. Vouloir les deux mène souvent au bruit des pagers.

Task 10 (Postgres): Identify long-running queries on the replica causing conflicts or bloat

cr0x@server:~$ psql -x -c "SELECT pid, usename, state, now() - xact_start AS xact_age, wait_event_type, wait_event, query FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_start ASC LIMIT 5;"
-[ RECORD 1 ]----+---------------------------------------------
pid             | 21844
usename         | app_ro
state           | active
xact_age        | 00:18:41.772
wait_event_type | Client
wait_event      | ClientRead
query           | SELECT ... FROM big_table JOIN ...

Signification : Une transaction de 18 minutes sur un hot standby est un aimant à conflits et un risque de ralentissement du replay.

Décision : La tuer pendant la réponse à l’incident, puis corriger le pattern : imposer statement timeouts, déplacer les requêtes analytiques hors du hot standby, ou utiliser un entrepôt.

Task 11 (System): Check disk latency that makes apply/replay crawl

cr0x@server:~$ iostat -x 1 3
Linux 6.1.0 (db-replica-1) 	12/31/2025 	_x86_64_	(8 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          12.11    0.00    7.88   28.42    0.00   51.59

Device            r/s     w/s   rkB/s   wkB/s  avgrq-sz avgqu-sz await  svctm  %util
nvme0n1         120.0  980.0  6400.0 51200.0     97.4     22.8  21.8   0.9   99.2

Signification : 99% d’utilisation et 20ms d’attente n’est pas « correct ». Votre réplique est liée au stockage ; le replay/l’application va prendre du retard.

Décision : Scalez le stockage (meilleurs disques, plus d’IOPS), réduisez l’amplification d’I/O (index, batching), ou déplacez la réplique sur des volumes séparés pour WAL/relay logs vs données lorsque c’est possible.

Task 12 (System): Validate network health (because sometimes it actually is the network)

cr0x@server:~$ mtr -r -c 20 10.0.1.10
Start: 2025-12-31T12:02:00+0000
HOST: db-replica-1               Loss%   Snt   Last   Avg  Best  Wrst StDev
  1.|-- 10.0.2.1                  0.0%    20    0.4   0.5   0.3   1.2   0.2
  2.|-- 10.0.10.5                 0.0%    20    1.1   1.3   1.0   2.4   0.3
  3.|-- 10.0.1.10                 2.0%    20    2.2   2.4   2.0   6.8   1.1

Signification : 2% de perte et des pics de jitter peuvent absolument créer de la latence de transport et des retransmissions, surtout sous des flux soutenus.

Décision : Si Postgres/MySQL montre des gaps de réception, corrigez le chemin (erreurs NIC, lien surchargé, voisin bruyant). Ne tunez pas la base pour compenser la perte de paquets. C’est comme baisser vos standards d’embauche parce que vos chaises grincent.

Task 13 (Postgres): Check replication slots and WAL retention risk

cr0x@server:~$ psql -x -c "SELECT slot_name, slot_type, active, restart_lsn, pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_bytes FROM pg_replication_slots;"
-[ RECORD 1 ]---+------------------------------
slot_name      | replica_1
slot_type      | physical
active         | f
restart_lsn    | 3A/12000000
retained_bytes | 107374182400

Signification : Le slot est inactif et retient ~100Go de WAL. Le disque du primaire est maintenant un compte à rebours.

Décision : Décidez si cette réplique revient bientôt. Sinon, supprimez le slot (après vérification qu’il n’est pas nécessaire) ou ramenez la réplique et laissez-la rattraper.

Task 14 (MySQL): Check relay log growth rate and disk usage (avoid “replica died” as a side quest)

cr0x@server:~$ du -sh /var/lib/mysql/*relay* 2>/dev/null; df -h /var/lib/mysql
4.2G	/var/lib/mysql/mysql-relay-bin.000123
Filesystem      Size  Used Avail Use% Mounted on
/dev/nvme0n1p1  400G  362G   18G  96% /var/lib/mysql

Signification : Les relay logs sont volumineux et le disque est presque plein. Si cela atteint 100%, MySQL aura une très mauvaise journée et vous l’accompagnerez.

Décision : Libérez de l’espace immédiatement (ajoutez un volume, purgez en toute sécurité si possible, ou arrêtez temporairement les consommateurs intensifs de réplication). Puis corrigez la cause racine : débit d’application et dimensionnement du disque.

Task 15 (Postgres): See if checkpoints are too frequent (WAL pressure and I/O spikes)

cr0x@server:~$ 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
------------------+-----------------+-----------------------+----------------------+--------------------
              112 |             389 |               9823412 |              4412231 |           18442201

Signification : De nombreux checkpoints demandés suggèrent un volume WAL ou des paramètres forçant des checkpoints fréquents ; cela peut asservir le replay avec des rafales I/O.

Décision : Ajustez les réglages de checkpoint et le stockage ; réduisez les sources de rafales WAL (mises à jour massives, cadence de rebuild d’index). Validez avec des mesures I/O et le comportement de replay, pas avec des impressions.

Cela fait plus d’une douzaine de tâches. Utilisez-les comme checklist, pas comme buffet.

Causes de latence qui comptent vraiment (par sous-système)

1) Forme des transactions : gros commits, beaucoup de commits, et la tyrannie de la « ligne chaude »

La réplication est fondamentalement sérielle à des endroits clés. Même avec l’application parallèle, l’ordre des commits et le suivi des dépendances peuvent forcer la sérialisation. Votre charge décide si le parallélisme est réel ou cosmétique.

• Les grosses transactions créent une latence en rafales : la réplique doit appliquer un gros bloc avant que la « latence » ne s’améliore.
• Beaucoup de petites transactions engendrent un overhead constant : fsync, acquisition de verrous, mises à jour B-tree, et bookkeeping de commit dominent.
• Lignes chaudes (compteurs, last_seen, compte unique) créent des chaînes de dépendances que l’application parallèle ne peut pas casser.

Que faire : groupez les écritures intentionnellement, évitez les mises à jour « bavardes » par ligne, et redesign des points chauds (événements append-only, compteurs sharded, rollups périodiques).

2) Amplification d’écriture : index, clés étrangères et contraintes « utiles »

Chaque écriture n’est pas une écriture. C’est un petit cortège : mise à jour de heap/page, mises à jour d’index, WAL/binlog, flush de pages, métadonnées, et parfois lectures supplémentaires pour vérifier des contraintes.

Si les réplicas prennent du retard lors de fortes périodes d’écriture, votre premier suspect devrait être l’amplification d’écriture, pas les réglages de réplication.

3) Physique du stockage : la latence bat la bande passante

L’application/replay de réplication est une charge lourde en écritures avec des points de synchronisation désagréables. La faible latence compte plus que de gros chiffres séquentiels.

• Un await élevé dans iostat corrèle souvent directement avec la croissance de la latence.
• Les volumes cloud avec crédits de rafale peuvent « sembler corrects » jusqu’à ce qu’ils ne le soient plus, puis la latence monte et ne redescend jamais.
• Les charges mixtes (réplication + lectures analytiques) peuvent détraquer les caches et provoquer une amplification d’I/O aléatoire.

4) Trafic de lecture sur les réplicas : le faux « déjeuner gratuit »

Servir des lectures depuis des réplicas semble être de la capacité gratuite. En pratique c’est un problème de compétition pour les ressources :

• Les lectures intensives provoquent du churn de cache, forçant plus de lectures disque pour l’application/replay.
• Les longues lectures en hot standby Postgres peuvent provoquer des conflits avec le replay.
• Dans MySQL, les lectures peuvent concurrencer pour le buffer pool et l’I/O ; les verrous de métadonnées et interactions DDL peuvent devenir épicés.

Règle : une réplique destinée à la fraîcheur ne devrait pas être aussi votre terrain de jeu analytique improvisé.

5) Réseau : latence, perte et fantasmes cross-région

La réplication cross-région n’est pas un exercice de tuning ; c’est de la physique et de la probabilité. La perte provoque des retransmissions ; la latence étire les boucles de feedback ; le jitter crée des micro-rafales.

Si vous répliquez sur Internet public (ou un VPN surchargé), vous ne gérez pas une base de données ; vous gérez une expérience réseau.

Correctifs spécifiques à MySQL et compromis

Réplication parallèle : utile, pas magique

MySQL moderne supporte l’application parallèle, mais le degré dépend de la version et des paramètres. La charge doit aussi permettre le parallélisme (plusieurs schémas, transactions indépendantes).

À faire : activer et valider les workers parallèles ; surveiller l’utilisation des workers.

Éviter : « Monter les workers à 64 » sans mesurer les waits de verrous et les chaînes de dépendance de commit. Vous obtiendrez de l’overhead et la même latence.

Format binlog et row image : contrôler le volume avec soin

Le logging basé sur les lignes est plus sûr opérationnellement pour la plupart des charges en production, mais il peut générer beaucoup de binlog — surtout avec des lignes larges et une image FULL.

À faire : réduire les mises à jour inutiles, éviter de mettre à jour des colonnes inchangées, et garder les lignes étroites quand c’est important.

Considérer : binlog_row_image=MINIMAL seulement après vérifications de compatibilité (outils en aval, audit, CDC, et besoins de debug).

Schéma et patterns de requêtes favorables aux réplicas

• Gardez les index secondaires intentionnels. Chaque index supplémentaire est du travail d’application en plus sur chaque réplique.
• Privilégiez des écritures idempotentes et évitez les boucles read-modify-write qui créent des hotspots.
• Regroupez les jobs en arrière-plan et évitez les « petits commits à l’infini ».

Les réglages de durabilité ne sont pas un plan de performance

Vous pouvez réduire la pression fsync avec les réglages de durabilité, mais vous achetez la performance avec un risque de perte de données. Parfois c’est acceptable pour un store cache. Pour les données cœur ? Non.

Première blague (et seule autre) : désactiver la durabilité pour corriger la latence, c’est comme enlever le détecteur de fumée parce qu’il continue de vous réveiller.

Correctifs spécifiques à PostgreSQL et compromis

Conflits de hot standby : choisissez un camp

Sur un hot standby, le replay doit appliquer des changements qui peuvent supprimer des versions de lignes ou verrouiller des ressources. Les requêtes longues peuvent entrer en conflit avec ce replay.

Postgres vous donne des choix, pas des miracles :

• Prioriser la fraîcheur du replay : annuler les requêtes conflictuelles plus rapidement. Les utilisateurs voient des échecs de requêtes, mais les données restent plus fraîches.
• Prioriser la stabilité des lectures : autoriser les longues requêtes, accepter les retards de replay. Les utilisateurs obtiennent des résultats, mais les données deviennent obsolètes.

Que faire : définir clairement le rôle de chaque réplique. « Cette réplique est pour les tableaux de bord, jusqu’à 5 minutes de retard » est une décision produit valide. Prétendre qu’elle est temps réel mène aux incidents.

Gestion du volume WAL : la victoire peu glamoureuse

Le WAL est la facture de votre comportement d’écriture. Réduisez la facture :

• Évitez les réécritures de table complètes pendant les heures de pointe.
• Méfiez-vous des mises à jour massives qui touchent beaucoup de lignes ; souvent un INSERT dans une nouvelle table + swap est opérationnellement meilleur, mais change aussi les patterns WAL.
• Considérez le tuning d’autovacuum et la maintenance des tables pour réduire la bloat (qui sinon augmente l’I/O lors du replay).

Replication slots : garde-fous avec des dents

Les slots empêchent la suppression du WAL tant qu’un consommateur en a besoin. Parfait pour la réplication logique et les standbys robustes. Parfait aussi pour remplir les disques quand un consommateur disparaît.

Règle opérationnelle : si vous utilisez des slots, vous devez avoir des alertes sur la taille de WAL retenue et l’activité des slots, ainsi qu’un runbook pour « est-il sûr de supprimer ce slot ? »

Réplication synchrone : quand l’utiliser et ce que ça coûte

La réplication synchrone peut borner la latence en faisant attendre les commits l’accusé de réception d’une réplique. Ce n’est pas une « correction gratuite » ; c’est un impôt en latence. Utilisez-la pour un petit ensemble de chemins de données critiques, ou pour une HA régionale où la cohérence est primordiale.

Erreurs courantes : symptôme → cause racine → correction

1) « Seconds behind master » est bas, mais les utilisateurs voient quand même des lectures obsolètes

Symptôme : MySQL rapporte peu de latence, mais l’app lit des données anciennes.

Cause racine : Vous mesurez la mauvaise chose. La latence basée sur un timestamp peut être trompeuse pendant les périodes d’inactivité, ou votre appli fait des lectures après écriture sans stickiness.

Correction : Implémentez read-your-writes : router vers le primaire après une écriture, ou utiliser la stickiness de session, ou suivre GTID/LSN et attendre la rattrapage de la réplique.

2) La latence augmente régulièrement chaque jour, puis se « réinitialise » après une maintenance

Symptôme : Les réplicas ralentissent avec le temps ; un redémarrage ou une pause « règle » le problème.

Cause racine : Churn de cache, bloat, indexes qui grossissent, ou amplification d’écriture croissante. Parfois c’est aussi des crédits de rafale de stockage épuisés.

Correction : Mesurez la latence I/O et le comportement des buffers, suivez la croissance table/index, et planifiez le stockage avec marge. Traitez la bloat et l’indexation comme du travail de capacité, pas comme un échec personnel.

3) La réplique Postgres annule des requêtes, et les tableaux de bord hurlent

Symptôme : « canceling statement due to conflict with recovery » apparaît ; les utilisateurs se plaignent.

Cause racine : Conflit hot standby : des lectures snapshot longues entrent en conflit avec le cleanup/replay.

Correction : Déplacez les requêtes longues ailleurs ; ajoutez statement_timeout sur la réplique ; ajustez les paramètres de conflit hot standby selon le rôle. Ne mettez pas de workloads BI sur une standby axée fraîcheur.

4) Le disque primaire Postgres se remplit de WAL de façon inattendue

Symptôme : Le répertoire WAL grossit rapidement ; pression disque ; un slot montre des bytes retenus énormes.

Cause racine : Consommateur inactif/lent avec un replication slot ; le WAL ne peut pas être recyclé.

Correction : Restaurez le consommateur ou supprimez le slot après vérification. Ajoutez de la surveillance et une politique pour le cycle de vie des slots.

5) La réplique MySQL est « running » mais l’application ne rattrape jamais après la pointe

Symptôme : Thread I/O ok, SQL thread ok, la latence monte et reste élevée.

Cause racine : Débit d’application en-dessous du taux d’écritures soutenu : latence stockage, CPU insuffisant, trop d’index, application mono-thread, ou contention.

Correction : Augmentez les ressources de la réplique (IOPS, CPU), tuner la réplication parallèle quand pertinent, réduire l’amplification d’écriture, et répartir les charges sur des réplicas avec des rôles clairs.

6) Vous scalez les réplicas et la latence empire

Symptôme : Ajouter des réplicas augmente la charge du primaire et la latence des réplicas.

Cause racine : Chaque réplique ajoute de l’overhead pour l’envoi de réplication (réseau, WAL sender, binlog dump threads) et peut augmenter la pression fsync/log selon la configuration.

Correction : Utilisez la réplication en cascade (là où c’est approprié), assurez-vous que le réseau est provisionné, et validez la marge CPU/I/O du primaire avant d’ajouter des réplicas.

Trois mini-histoires d’entreprise issues du front de la latence

Mini-histoire n°1 : L’incident causé par une fausse hypothèse (les réplicas sont « sûrs »)

Une société SaaS de taille moyenne avait une belle slide d’architecture : écritures sur le primaire, lectures sur les réplicas. Ils avaient aussi une fonctionnalité « export instantané » que les utilisateurs pouvaient lancer juste après avoir changé des paramètres.

Quelqu’un a supposé que la lecture des paramètres pouvait venir d’une réplique parce que « c’est juste de la configuration ». Le job d’export écrivait un enregistrement « started » sur le primaire, puis lisait les paramètres sur une réplique, puis produisait un résultat basé sur ce qu’il trouvait.

Pendant une campagne marketing, les écritures ont explosé. La latence de réplication est passée de secondes à minutes. Les exports ont commencé à utiliser d’anciens paramètres — mauvais filtres, mauvais formats, mauvaises destinations. Rien n’a planté. Le système a simplement produit des résultats confiants et incorrects, qui sont l’erreur la plus coûteuse.

La correction n’a pas été un réglage. Ils ont implémenté read-your-writes : après avoir mis à jour des paramètres, la session utilisateur était épinglée au primaire pour une courte fenêtre. Ils ont aussi tagué les réplicas par rôle : « fraîcheur » vs « analytique ». La fonctionnalité d’export a cessé de prétendre pouvoir vivre sur des lectures « bon marché ».

Mini-histoire n°2 : L’optimisation qui a mal tourné (plus gros batchs, plus grosse douleur)

Une plateforme de paiements utilisait MySQL avec des réplicas. Un job nocturne mettait à jour des statuts par lots. La latence était gênante mais gérable. Quelqu’un a remarqué que le job passait trop de temps à committer et a décidé « d’optimiser » en augmentant drastiquement la taille des batchs.

Le primaire a adoré au début : moins de commits, moins d’overhead. Puis les réplicas ont commencé à prendre du retard en rafales. Les threads d’application heurtaient une énorme transaction, mettaient longtemps à l’appliquer, et laissaient les relay logs grossir. Pendant cette fenêtre, les réplicas servaient des lectures de plus en plus obsolètes.

Pire : quand la grosse transaction a finalement été appliquée sur la réplique, elle a déclenché une vague de flushs. La latence stockage a grimpé. L’application a ralenti encore. Les graphiques de l’équipe sont devenus une exposition d’art moderne.

La vérité ennuyeuse : ils ont optimisé l’overhead de commit du primaire et ignoré la mécanique d’application de la réplique. La correction a été d’ajuster la taille des batchs vers un sweet spot mesuré : assez petit pour que l’application reste fluide, assez grand pour éviter la mort par commit. Ils ont aussi ajouté des garde-fous : taille maximale de transaction et un coupe-circuit qui pause le job quand la latence des réplicas dépasse un seuil.

Mini-histoire n°3 : La pratique ennuyeuse mais correcte qui a sauvé la mise (réplicas par rôle et runbooks)

Une entreprise de retail utilisait Postgres avec deux standbys. L’un était désigné « fresh standby » avec annulation agressive des requêtes conflictuelles ; l’autre était « reporting standby » où les longues lectures étaient autorisées et la latence attendue.

Pendant la saison haute, un développeur a lancé un rapport coûteux. Il est tombé sur le reporting standby comme prévu. Ce standby a pris quelques minutes de retard, mais personne n’a paniqué parce que son SLO le permettait. Le fresh standby est resté proche du temps réel car il n’hébergeait pas la charge du rapport.

Puis un déploiement a introduit une migration lourde en écritures. Le replay sur le fresh standby a commencé à dériver. L’on-call a suivi un runbook : vérifier receive vs replay LSN, regarder iostat, confirmer l’absence de conflits de requêtes, puis rediriger temporairement les lectures « sensibles à la fraîcheur » vers le primaire. Ils n’en ont pas débattu 45 minutes dans un canal. Ils ont agi.

L’impact business a été minime. Pas parce qu’ils avaient une réplication parfaite, mais parce qu’ils avaient un but défini pour chaque réplique, des attentes mesurées, et un plan pratiqué. L’ennuyeux a sauvé la mise. Encore.

Listes de contrôle / plan pas-à-pas

Checklist A: When lag spikes right now (incident response)

1. Confirmer l’étendue : une réplique ou toutes ? MySQL ou Postgres ? une seule région ou cross-région ?
2. Classer la latence : transport vs application vs visibilité (utiliser les tâches ci-dessus).
3. Protéger la correction : router les lectures critiques vers le primaire ; activer read-your-writes.
4. Réduire la charge sur la réplique : arrêter les requêtes analytiques, mettre en pause les jobs background, limiter les écrivains par lot.
5. Vérifier la latence stockage : si await est élevé et %util saturé, corriger l’infra ou réduire les écritures.
6. Chercher l’unique coupable : grosse transaction, rebuild d’index, migration ou vacuum perturbateur.
7. Faire le plus petit changement sûr : redirection temporaire, pause de job, kill de requête, ajout de capacité.
8. Documenter la timeline : quand la latence a commencé, corrélations (déploiement, job, trafic, événements stockage).

Checklist B: Prevent lag from becoming your personality (engineering plan)

1. Définir les rôles des réplicas : fraîcheur vs reporting vs DR.
2. Définir des budgets de latence : secondes/minutes acceptables par rôle ; communiquer au produit.
3. Instrumenter le pipeline :
   • MySQL : croissance des relay logs, états des workers, taux d’application.
   • Postgres : LSN reçu vs rejoué, délai de timestamp de replay, compteurs de conflit, rétention de slots.
4. Construire la sécurité applicative : read-your-writes, tokens de cohérence (GTID/LSN), fallback vers le primaire.
5. Contrôler l’amplification d’écriture : hygiène des index, éviter les lignes larges, éviter les mises à jour inutiles.
6. Planifier les fenêtres de maintenance : opérations massives hors-pointe ; rate-limit des migrations et backfills.
7. Dimensionner avec marge : IOPS et CPU dimensionnés pour le peak + rattrapage, pas pour la moyenne.
8. Pratiquer le basculement : la latence interagit avec le failover de façon désagréable ; répétez.

Checklist C: Schema and workload patterns that reduce lag by design

• Remplacer « mettre à jour la même ligne constamment » par des événements append-only + compactage périodique.
• Grouper les écritures à une taille stable ; éviter les deux extrêmes (commits minuscules, commits monstres).
• Garder les index secondaires intentionnels ; supprimer ceux qui sont « peut-être utiles un jour ».
• Sur Postgres, éviter les transactions longues sur les hot standbys ; elles coûtent en fraîcheur ou en fiabilité.

FAQ

1) Les mesures de latence MySQL sont-elles fiables ?

Elles sont utiles, pas sacrées. Seconds_Behind_Master est une estimation basée sur des timestamps et peut induire en erreur pendant les périodes d’inactivité ou avec certains workloads. Mesurez aussi la croissance des relay logs et les états d’application.

2) La latence de réplication Postgres est-elle plus facile à raisonner ?

Généralement oui. Les métriques basées sur les LSN vous permettent de séparer réception vs replay. La latence temporelle via pg_last_xact_replay_timestamp() est aussi pratique, bien qu’elle soit NULL si aucune transaction n’a encore été rejouée.

3) Dois-je exécuter des analyses sur des réplicas ?

Oui, mais pas sur la même réplique dont vous dépendez pour la fraîcheur. Donnez à l’analytique sa propre réplique (ou son propre système). Sinon vous échangez « moins de requêtes sur le primaire » contre « la réplication prend du retard et la cohérence devient étrange ».

4) Ajouter plus de réplicas réduit-il la latence ?

Non. Les réplicas réduisent la charge de lecture sur le primaire (parfois). La latence dépend de la capacité de la réplique à recevoir et appliquer. Plus de réplicas peut augmenter l’overhead du primaire et l’utilisation réseau.

5) Quelle est la manière la plus rapide et sûre de réduire l’impact utilisateur pendant la latence ?

Rediriger les lectures sensibles à la fraîcheur vers le primaire et garder les réplicas pour les lectures non critiques. Ajouter le read-your-writes après les écritures. C’est un filet de sécurité applicatif qui fonctionne quelle que soit la base.

6) Le parallélisme de réplication peut-il « résoudre » la latence en MySQL ?

Il peut beaucoup aider lorsque les transactions sont indépendantes. Il n’aidera pas beaucoup avec des chaînes de dépendance (lignes chaudes, contention sur une table, ordre strict des commits). Traitez-le comme un multiplicateur sur une charge déjà parallélisable.

7) Pourquoi Postgres annule-t-il parfois des requêtes sur une réplique ?

Conflits hot standby : le replay doit appliquer des changements qui entrent en conflit avec le snapshot d’une requête ou des verrous nécessaires. Selon les paramètres, Postgres retarde le replay (plus de latence) ou annule les requêtes (plus d’échecs). Choisissez en connaissance de cause.

8) Les replication slots dans Postgres sont-ils obligatoires ?

Ils sont nécessaires pour la réplication logique et utiles pour empêcher la suppression du WAL avant que les consommateurs l’aient reçu. Ils sont aussi capables de remplir les disques si les consommateurs disparaissent. Utilisez-les avec surveillance et une politique de cycle de vie.

9) Dois-je utiliser la réplication synchrone pour éliminer la latence ?

La réplication synchrone peut borner la latence en faisant attendre les commits. Vous payez en latence d’écriture et en disponibilité réduite si la standby synchrone est malade. Utilisez-la pour les chemins de données où la staleness bornée vaut le coût.

10) Quelle est la cause racine la plus fréquente en production ?

La latence stockage sous charge d’écriture, amplifiée par des choix de schéma (trop d’index) et des workloads mixtes sur les réplicas (analytique + fraîcheur). On blâme souvent les bases ; les graphes I/O disent la vérité en silence.

Conclusion : prochaines étapes qui ne vous feront pas honte

Si vous retenez une chose : la latence de réplication est une file d’attente. Vous ne la « tunez » pas loin avec de l’optimisme. Vous augmentez la capacité d’application/replay, réduisez le volume d’écriture, ou changez vos attentes sur ce que sont les réplicas.

Prochaines étapes pratiques :

1. Implémenter read-your-writes pour la correction côté utilisateur. Arrêtez de faire semblant que les réplicas sont instantanément cohérents.
2. Séparer les rôles des réplicas : un pour la fraîcheur, un pour le reporting, un pour le DR. Donnez à chacun un budget de latence et faites-le respecter.
3. Instrumenter le pipeline de réplication avec des métriques réception vs application/replay, pas seulement un seul nombre de latence.
4. Corriger la marge stockage : si vos disques de réplique sont saturés, rien d’autre n’a d’importance.
5. Auditer l’amplification d’écriture : index, jobs par lot, et lignes chaudes. La plupart des « problèmes de réplication » commencent dans le schéma et la charge, pas dans la réplication.
6. Rédiger le runbook et le répéter. Le pire moment pour découvrir quelle métrique ment est 3h du matin un jour férié.

MySQL et PostgreSQL répliquent bien — quand vous traitez la réplication comme un système de production à part entière, et non comme une case à cocher. La latence que vous tolérez est une décision produit. La latence que vous ne mesurez pas est une décision de carrière.