PostgreSQL vs Percona Server : mise à l’échelle des lectures — réplicas utiles vs réplicas nuisibles

Un modèle mental opérationnel : que signifie vraiment « mettre à l’échelle les lectures »

Mettre à l’échelle les lectures avec des réplicas n’est pas un seul problème. C’en est trois :

1. Capacité : Les réplicas peuvent-ils exécuter votre charge de lecture assez rapidement sans priver le primaire ou se gêner entre eux ?
2. Fraîcheur : À quel point les résultats peuvent-ils être obsolètes avant que votre produit ne devienne « créativement faux » ?
3. Exactitude lors du basculement : Pouvez-vous promouvoir un réplica sans perdre d’écritures ou corrompre les hypothèses de l’application ?

PostgreSQL et Percona Server abordent ces compromis différemment.

La réplication physique de PostgreSQL (streaming WAL) est conservatrice : priorité à l’exactitude et à la simplicité, et elle peut annuler des requêtes de lecture sur les réplicas pour laisser la récupération avancer. Percona Server hérite de l’ADN de réplication de MySQL : polyvalent, largement compris et très capable — mais avec une histoire d’incidents « ça marchait en staging » causés par des détails subtils de cohérence.

La mise à l’échelle des lectures vit aussi au-dessus de la base de données. Si vous n’avez pas de discipline de routage — séparation lecture/écriture, affinité de session, gestion du « lire ses propres écritures » — vous ne faites que répartir la confusion sur plus d’hôtes.

Une vérité opérationnelle qui revient sans cesse : si vos réplicas ne sont pas mesurablement plus rapides pour les requêtes ciblées que le primaire, les réplicas sont une taxe, pas une fonctionnalité. Ils coûtent du matériel, de l’attention opérationnelle et introduisent des modes de défaillance. Faites-les mériter leur place.

Faits et contexte historique intéressants (qui expliquent les aspérités actuelles)

• Le « hot standby » de PostgreSQL est apparu dans la version 9.0 (2010) : avant cela, les réplicas physiques servaient surtout au basculement, pas aux lectures en direct.
• La réplication MySQL a commencé par l’expédition asynchrone de binlogs : le défaut culturel est devenu « les réplicas peuvent prendre du retard », et de nombreux modèles applicatifs partent silencieusement de cette hypothèse.
• La réplication semi-synchrone MySQL a été introduite pour réduire l’écart : elle améliore les garanties de durabilité mais peut limiter le débit sous latence.
• La réplication PostgreSQL est basée sur WAL et orientée pages : elle est efficace et déterministe, mais elle lie la progression du réplica au replay et aux interactions avec vacuum.
• GTID MySQL/Percona a rendu le basculement plus sain : il réduit le chaos « à quelle position de binlog sommes-nous ? » qui ruinait les week-ends.
• Percona Server a ajouté tôt une instrumentation opérationnelle : des fonctionnalités comme des métriques supplémentaires et une meilleure intégration avec performance schema l’ont rendu populaire auprès des équipes SRE qui veulent des preuves.
• La réplication logique PostgreSQL a mûri plus tard (10+) : elle a ouvert la réplication sélective et les mises à niveau sans interruption, mais ce n’est pas une solution miracle pour la mise à l’échelle des lectures.
• MySQL supporte depuis longtemps les topologies multi-source et avancées : on peut faire des choses astucieuses, et les choses astucieuses génèrent des pannes astucieuses.

Réplicas PostgreSQL : pourquoi ils se comportent généralement bien, et quand ils mordent

Comment fonctionnent en pratique les réplicas de lecture PostgreSQL

Dans la réplication en streaming classique de PostgreSQL, le primaire génère du WAL (write-ahead log). Les réplicas reçoivent le WAL et le rejouent. Les lectures sur un réplica s’exécutent contre un snapshot cohérent de la base de données au niveau du WAL rejoué.

Cette étape de rejeu est importante. Si le rejeu est lent — IO, CPU, verrous sur le standby, ou gestion des conflits — le retard de réplication augmente. Le réplica est vivant et répond aux requêtes, mais il répond depuis le passé.

Le mode d’échec « réplicas qui font mal » : conflits de récupération

PostgreSQL a une opinion : la récupération doit avancer. Si une requête longue sur le standby entre en conflit avec le rejeu du WAL (souvent à cause du vacuum sur le primaire), PostgreSQL peut annuler la requête du standby pour laisser la récupération progresser. Vous verrez des erreurs comme canceling statement due to conflict with recovery.

Ce n’est pas PostgreSQL qui est méchant ; c’est PostgreSQL qui est honnête. Vous demandez à un réplica d’être à la fois une machine à remonter le temps et un entrepôt de données. Choisissez l’un ou l’autre, ou concevez des contournements :

• Utilisez hot_standby_feedback avec précaution pour réduire les annulations (mais acceptez le risque de bloat sur le primaire).
• Réglez max_standby_streaming_delay pour troquer retard de rejeu contre terminaison des requêtes.
• Dirigez les longues analyses vers une réplica logique ou un système dédié, pas vers votre standby HA.

Contrôles de fraîcheur qui comptent

PostgreSQL vous donne des leviers pour borner l’obsolescence. Le meilleur, opérationnellement, est synchronous_commit associé aux réglages de réplication synchrone (synchronous_standby_names). Il peut garantir qu’au moins un standby confirme la réception (ou l’application) avant que le commit ne retourne.

La note cachée : la latence réseau devient partie intégrante de votre chemin d’écriture. Si votre produit est sensible à la latence et que votre standby est interrégional, vous payez effectivement un timbre international pour chaque commit.

Performance des réplicas sous Postgres : n’ignorez pas les bases

Les nœuds standby ont besoin des mêmes soins que les primaires : shared_buffers dimensionné raisonnablement, effective_cache_size réaliste, et stockage capable de suivre les lectures aléatoires plus les écritures de rejeu WAL. Un standby avec des disques lents peut accumuler du retard même s’il sert à peine des requêtes.

De plus, les réplicas Postgres ne sont pas des « lectures gratuites ». Chaque requête nécessite CPU, mémoire et IO. Si vous pointez une flotte de tableaux de bord vers un seul standby sans pooling de connexions, vous pouvez arriver à DDoS votre propre base de données poliment.

Blague n°1 : Un réplica de lecture, c’est comme un stagiaire qui prend des notes — utile jusqu’à ce que vous lui demandiez de prédire l’avenir, puis il panique et supprime votre feuille de calcul.

Réplicas Percona Server : rapides, familiers, et pleins de pièges

Ce que vous exécutez vraiment quand vous lancez Percona Server

Percona Server est compatible MySQL, souvent utilisé comme remplacement direct avec des fonctionnalités d’instrumentation et de performance supplémentaires. Pour la mise à l’échelle des lectures, les comportements fondamentaux viennent de la réplication MySQL : le primaire écrit des binlogs, les réplicas les récupèrent et les appliquent. Historiquement, l’application était mono-thread ; les versions récentes offrent une réplication parallèle, mais seulement si vous la configurez et si votre charge s’y prête.

La douleur classique : le retard de réplication n’est pas un bug, c’est le comportement par défaut

Beaucoup de configurations MySQL/Percona traitent les réplicas comme des caches éventuellement cohérents. C’est acceptable jusqu’à ce que quelqu’un route des lectures critiques métier vers des réplicas sans contrat de fraîcheur. Si votre application fait « écrire, puis lire, puis facturer », le retard se transforme en tickets de support et en financiers en colère.

Semisync, GTID et l’illusion de sécurité

La réplication semi-synchrone aide en faisant attendre le primaire qu’au moins un réplica accuse réception de la transaction avant de commit (selon la config). Elle réduit la fenêtre de perte en cas de panne du primaire, mais n’assure pas magiquement que les réplicas sont rattrapés pour les lectures. La réception n’est pas l’application.

La réplication basée sur GTID rend le basculement et la gestion de topologie moins sujets aux erreurs, mais elle ne vous protège pas d’un mauvais routage. GTID résout « quelle transaction avons-nous appliquée », pas « quelles hypothèses l’appli voit-elle ? ».

La performance des réplicas est souvent un problème d’application, pas de requête

Quand un réplica Percona prend du retard, les équipes ajoutent souvent du CPU ou augmentent le buffer pool en espérant. Parfois ça marche. Souvent le réplica est bloqué sur l’application du thread SQL : une grosse transaction, un changement de schéma, une table chaude unique, ou des réglages de parallélisme insuffisants.

La réplication parallèle peut être bénéfique, mais elle a des prérequis. Si votre charge consiste principalement en contention sur une seule table ou de larges transactions sérialisées, les workers parallèles resteront oisifs en attendant leur tour — comme une réunion où tout le monde est « aligné » mais rien n’avance.

Mines antipersonnel de la correction des réplicas : statement vs row, et comportement des DDL

La meilleure pratique moderne est la réplication basée sur les lignes pour la sécurité. La réplication basée sur les instructions peut diverger avec des fonctions non déterministes ou des différences de fuseau horaire. Le mode mixte est un compromis, et les compromis sont des lieux où naissent les incidents.

Les DDL peuvent aussi bloquer l’application de la réplication ou créer de longues pauses. Les changements de schéma en ligne aident, mais introduisent leur propre complexité opérationnelle et peuvent se retourner contre vous si vos outils ne sont pas adaptés à votre charge.

Blague n°2 : La réplication parallèle, c’est comme embaucher plus de déménageurs — super jusqu’à ce que vous réalisiez que le canapé ne passe que d’une seule façon par la porte.

Séparation lecture/écriture : schémas de routage qui ne vous brûlent pas

Schéma 1 : « Lire ses propres écritures » avec affinité de session

Si un utilisateur vient d’écrire des données et s’attend à les lire immédiatement, vous avez deux options sensées :

• Diriger cette session vers le primaire pendant une fenêtre (affinité primaire après écriture), puis autoriser les lectures sur les réplicas ensuite.
• Utiliser une clôture de fraîcheur : ne lire que depuis des réplicas ayant confirmé l’application d’au moins le LSN/GTID de l’écriture (plus complexe, mais précis).

Tout le reste, c’est de l’ingénierie basée sur l’espoir.

Schéma 2 : Séparer par type de charge, pas par endpoint

Ne routez pas « tous les SELECT » vers des réplicas. Routez des classes de requêtes spécifiques. L’analytics, les rapports et les requêtes de tableaux de bord sont de bons candidats — si elles tolèrent l’obsolescence et que vous protégez les réplicas d’une concurrence débridée.

Inversement, un « SELECT … FOR UPDATE » appartient au primaire. Idem pour les lectures qui doivent voir un état immédiatement cohérent (vérifications d’inventaire, clés d’idempotence, logique anti-fraude).

Schéma 3 : Désigner des réplicas par usage

Dans PostgreSQL, un hot standby pour la haute disponibilité n’est pas la même chose qu’un pool de lecture pour la BI. Dans Percona, un réplica utilisé pour les sauvegardes et l’ETL n’est pas celui que vous promouvriez lors d’un incident. Mixer les usages crée des inversions de priorité : vos sauvegardes brident votre candidat de failover, ou vos tableaux de bord provoquent des annulations de requêtes qui gonflent le bloat du primaire.

Schéma 4 : Mettre un gouverneur devant les réplicas

Des poolers de connexions, des timeouts de requêtes et des limites de ressources par rôle vous rapportent leur coût. Le réplica est une ressource partagée. Traitez-le comme tel. « Connexions illimitées depuis les dashboards » est la manière d’apprendre ce que ressent l’épuisement des descripteurs de fichiers.

Idée paraphrasée (attribuée) : John Allspaw soutient que la fiabilité vient de l’apprentissage et de l’amélioration des systèmes, pas du blâme des individus après une panne.

Tâches pratiques : commandes, sorties et décisions (à exécuter en production)

Voici les tâches que j’exécute réellement quand les réplicas se comportent mal. Chacune inclut : la commande, ce que signifie la sortie, et la décision suivante.

PostgreSQL : santé du réplica et retard

Tâche 1 : Vérifier l’état de la réplication depuis le primaire

cr0x@server:~$ psql -X -c "SELECT application_name, client_addr, state, sync_state, write_lag, flush_lag, replay_lag FROM pg_stat_replication;"
 application_name | client_addr  |   state   | sync_state | write_lag | flush_lag | replay_lag
-----------------+--------------+-----------+------------+-----------+-----------+-----------
 standby-a        | 10.0.2.11    | streaming | async      | 00:00:00  | 00:00:00  | 00:00:02
 standby-b        | 10.0.2.12    | streaming | async      | 00:00:00  | 00:00:01  | 00:00:15
(2 rows)

Signification : Le retard est détaillé par étape. Le replay lag est ce que ressentent vos lectures. Standby-b a 15s de retard sur le rejeu.

Décision : Si le replay lag corrèle avec des incidents de fraîcheur, routez les lectures sensibles loin de standby-b ou corrigez les goulets de rejeu.

Tâche 2 : Mesurer précisément le retard en octets (différence LSN)

cr0x@server:~$ psql -X -c "SELECT application_name, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS bytes_behind FROM pg_stat_replication;"
 application_name | bytes_behind
-----------------+--------------
 standby-a        |       524288
 standby-b        |     73400320
(2 rows)

Signification : Standby-b a ~70MB de retard. Le retard en temps peut paraître petit jusqu’à ce qu’une rafale survienne ; les octets montrent l’arriéré.

Décision : Si les octets en retard augmentent régulièrement, vous avez une sous-capacité soutenue (réseau, disque, CPU ou conflits d’application).

Tâche 3 : Sur le standby, vérifier le retard par horodatage de rejeu

cr0x@server:~$ psql -X -c "SELECT now() - pg_last_xact_replay_timestamp() AS replay_delay;"
 replay_delay
--------------
 00:00:14.832
(1 row)

Signification : « À quel point la dernière transaction appliquée est-elle dans le passé ? » Très proche de l’obsolescence perçue par l’utilisateur.

Décision : Si replay_delay dépasse la tolérance produit, cessez d’utiliser ce standby pour les lectures sensibles à la fraîcheur.

Tâche 4 : Voir si des requêtes sur le standby sont annulées

cr0x@server:~$ sudo journalctl -u postgresql -n 50 --no-pager
Dec 30 10:12:01 standby-a postgresql[1832]: ERROR:  canceling statement due to conflict with recovery
Dec 30 10:12:01 standby-a postgresql[1832]: DETAIL:  User query might have needed to see row versions that must be removed.
Dec 30 10:12:01 standby-a postgresql[1832]: STATEMENT:  SELECT ... FROM events WHERE ...

Signification : Conflits hot standby. Le rejeu WAL nettoie des versions de lignes ; votre requête en avait besoin.

Décision : Déplacez les requêtes longues hors du standby, ou acceptez le compromis de hot_standby_feedback=on (avec un plan pour le bloat), ou ajustez les réglages de délai du standby.

Tâche 5 : Vérifier si hot_standby_feedback est activé

cr0x@server:~$ psql -X -c "SHOW hot_standby_feedback;"
 hot_standby_feedback
---------------------
 off
(1 row)

Signification : Le standby n’indique pas au primaire de conserver les anciennes versions de lignes plus longtemps.

Décision : Si vous annulez des lectures importantes, envisagez de l’activer — mais seulement après avoir vérifié le bloat du primaire et la marge d’autovacuum.

Tâche 6 : Vérifier les réglages de réplication synchrone (contrat de fraîcheur)

cr0x@server:~$ psql -X -c "SHOW synchronous_commit; SHOW synchronous_standby_names;"
 synchronous_commit
-------------------
 on
(1 row)

 synchronous_standby_names
--------------------------
 standby-a
(1 row)

Signification : Les commits attendent standby-a (selon le mode sync). Cela peut limiter la latence d’écriture mais resserre le RPO.

Décision : Si votre budget de latence d’écriture ne le permet pas, ne prétendez pas avoir une durabilité synchrone. Passez en asynchrone et concevez en conséquence, ou gardez le sync mais assurez-vous que standby-a est à faible latence et bien provisionné.

Tâche 7 : Trouver les principales requêtes de lecture sur le standby

cr0x@server:~$ psql -X -c "SELECT query, calls, mean_exec_time, rows FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 5;"
                     query                      | calls | mean_exec_time | rows
------------------------------------------------+-------+----------------+------
 SELECT * FROM report_rollups WHERE ...          |   120 |        842.123 |  1200
 SELECT ... JOIN ... WHERE ...                   |  5400 |        120.512 |    40
(2 rows)

Signification : Votre réplica est lent parce qu’on lui demande de faire des choses coûteuses, de manière répétée.

Décision : Ajoutez des index, réduisez les jeux de résultats, mettez en cache, ou déplacez cette charge vers un store analytique dédié. « Réplica » n’est pas un mot magique qui transforme de mauvaises requêtes en bonnes requêtes.

Percona Server : santé du réplica et retard d’application

Tâche 8 : Sur un réplica, vérifier les threads de réplication et le retard

cr0x@server:~$ mysql -e "SHOW REPLICA STATUS\G"
*************************** 1. row ***************************
             Replica_IO_Running: Yes
            Replica_SQL_Running: Yes
                   Seconds_Behind_Source: 27
                  Retrieved_Gtid_Set: 1-100-984433
                   Executed_Gtid_Set: 1-100-984120
                Replica_SQL_Running_State: Waiting for dependent transaction to commit

Signification : L’IO est OK, l’application SQL est en retard. Les sets GTID montrent l’écart. L’état SQL indique une dépendance/sérialisation.

Décision : Concentrez-vous sur le débit d’application : réglages parallèles, taille des transactions, tables/rows chauds, et performance disque pour redo/undo.

Tâche 9 : Vérifier la configuration de la réplication parallèle

cr0x@server:~$ mysql -e "SHOW VARIABLES LIKE 'replica_parallel%'; SHOW VARIABLES LIKE 'slave_parallel%';"
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| replica_parallel_workers          | 8     |
| replica_parallel_type             | LOGICAL_CLOCK |
+-----------------------------------+-------+
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| slave_parallel_workers            | 8     |
| slave_parallel_type               | LOGICAL_CLOCK |
+-----------------------------------+-------+

Signification : Workers parallèles activés. Si le retard persiste, votre charge peut ne pas se paralléliser bien ou vous êtes limité ailleurs.

Décision : Si les workers sont à 0, activez-les prudemment ; s’ils sont déjà activés, cherchez de grosses transactions ou des blocages disque plutôt que « ajouter des workers ».

Tâche 10 : Trouver les plus grandes transactions récentes (douleur d’application de binlog)

cr0x@server:~$ mysql -e "SELECT THREAD_ID, EVENT_NAME, TIMER_WAIT/1000000000000 AS seconds, SQL_TEXT FROM performance_schema.events_statements_history_long ORDER BY TIMER_WAIT DESC LIMIT 3;"
+-----------+--------------------------+---------+--------------------------------------+
| THREAD_ID | EVENT_NAME               | seconds | SQL_TEXT                              |
+-----------+--------------------------+---------+--------------------------------------+
|      2213 | statement/sql/insert     |  12.421 | INSERT INTO audit_log SELECT ...      |
|      1987 | statement/sql/update     |   9.812 | UPDATE orders SET status='...' WHERE...|
+-----------+--------------------------+---------+--------------------------------------+

Signification : De grosses instructions peuvent sérialiser l’application et créer des pics de retard.

Décision : Fractionnez les transactions monstres, évitez les « INSERT INTO … SELECT » aux heures de pointe, et envisagez des patterns de batch respectueux de la réplication.

Tâche 11 : Vérifier la pression InnoDB sur le réplica (buffer pool et IO)

cr0x@server:~$ mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads'; SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';"
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| Innodb_buffer_pool_reads | 18399231 |
+--------------------------+----------+
+----------------------------------+------------+
| Variable_name                    | Value      |
+----------------------------------+------------+
| Innodb_buffer_pool_read_requests | 9912837712 |
+----------------------------------+------------+

Signification : Si les lectures du buffer pool augmentent rapidement par rapport aux requêtes, vous manquez de cache et touchez le disque. Les réplicas faisant de l’analytics ont tendance à cela.

Décision : Augmentez le buffer pool (si la mémoire le permet), réduisez le working set, ajoutez des indexes, ou déplacez l’analytics hors du réplica.

Tâche 12 : Vérifier si le réplica est limité par fsync/IO

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

Device            r/s     w/s   rMB/s   wMB/s  await  %util
nvme0n1         120.0   410.0     8.1    52.3   18.4  98.7

Signification : Le disque est saturé (%util ~99%). Le temps d’attente est élevé. L’application et les lectures se concurrencent pour l’IO.

Décision : Déplacez redo/binlog vers un stockage plus rapide, réduisez la charge de lecture, ajustez soigneusement les réglages de flush, ou ajoutez des réplicas pour répartir la charge de lecture.

Tâche 13 : Vérifier le mode binlog/GTID depuis la source (préparation au basculement)

cr0x@server:~$ mysql -e "SHOW VARIABLES LIKE 'gtid_mode'; SHOW VARIABLES LIKE 'enforce_gtid_consistency'; SHOW MASTER STATUS\G"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode     | ON    |
+---------------+-------+
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| enforce_gtid_consistency | ON    |
+--------------------------+-------+
*************************** 1. row ***************************
             File: binlog.000812
         Position: 94822133
Executed_Gtid_Set: 1-100-984433

Signification : GTID est activé et cohérent. C’est la base pour des outils de basculement raisonnables.

Décision : Si GTID est désactivé en 2025, prévoyez du temps pour le corriger. Vous pouvez toujours fonctionner sans, mais vous choisissez des opérations manuelles sujettes aux erreurs.

Tâche 14 : Repérer les erreurs de routage des lectures via processlist

cr0x@server:~$ mysql -e "SHOW PROCESSLIST;"
+-----+------+-----------------+------+---------+------+------------------------+------------------------------+
| Id  | User | Host            | db   | Command | Time | State                  | Info                         |
+-----+------+-----------------+------+---------+------+------------------------+------------------------------+
| 101 | app  | 10.9.1.25:42110  | prod | Query   |   12 | Sending data           | SELECT ... FOR UPDATE ...    |
| 142 | app  | 10.9.1.25:42144  | prod | Query   |    8 | Waiting for table lock | UPDATE users SET ...         |
+-----+------+-----------------+------+---------+------+------------------------+------------------------------+

Signification : Des écritures et des lectures verrouillantes sur un réplica indiquent un routage cassé. Certains proxies classifient mal les requêtes ; certaines applis ignorent l’intention lecture/écriture.

Décision : Corrigez les règles de routage et imposez des utilisateurs en lecture seule sur les réplicas. Si le réplica est modifiable, quelqu’un écrira dessus — accidentellement ou « temporairement ».

Tâches transverses : OS, réseau et réalité de capacité

Tâche 15 : Vérifier les retransmissions réseau (qualité du flux de réplication)

cr0x@server:~$ ss -ti dst :5432
ESTAB 0 0 10.0.2.11:50824 10.0.1.10:5432
	 cubic wscale:7,7 rto:214 rtt:3.1/1.2 ato:40 mss:1448 pmtu:1500 rcvmss:1448 advmss:1448 cwnd:10 bytes_sent:248120122 bytes_retrans:81234 bytes_acked:248038888

Signification : Des retransmissions existent ; si elles sont élevées et augmentent, votre « retard de réplication » est en partie un problème réseau.

Décision : Corrigez la perte de paquets avant d’ajuster les paramètres de la base. Les bases de données excellent en beaucoup de choses ; la thérapie anti-perte de paquets n’en fait pas partie.

Tâche 16 : Confirmer qu’un réplica est réellement en lecture seule au niveau base

cr0x@server:~$ psql -X -c "SHOW transaction_read_only;"
 transaction_read_only
-----------------------
 off
(1 row)

Signification : Cette session peut écrire. Sur un standby, les écritures échoueront, mais sur un nœud promu ou mal configuré, cela peut être dangereux.

Décision : Appliquez des rôles en lecture seule et des restrictions côté proxy/application. Compter sur « c’est un standby » n’est pas une politique.

cr0x@server:~$ mysql -e "SHOW VARIABLES LIKE 'read_only'; SHOW VARIABLES LIKE 'super_read_only';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| super_read_only | ON    |
+-----------------+-------+

Signification : Le réplica est protégé même contre les utilisateurs privilégiés (avec réserves).

Décision : Activez super_read_only sur les réplicas qui ne doivent jamais accepter d’écritures.

Méthode de diagnostic rapide

Quand on vous dit « les réplicas sont lents » ou « les lectures sont incohérentes », vous n’avez pas le temps d’un débat philosophique sur le théorème CAP. Faites ceci.

Première étape : décider si c’est un problème de fraîcheur ou de performance

• PostgreSQL : vérifiez now() - pg_last_xact_replay_timestamp() sur le réplica et pg_stat_replication sur le primaire.
• Percona : vérifiez Seconds_Behind_Source et l’état du thread SQL, plus GTID exécuté vs récupéré.

Si le retard est élevé, la plainte « lectures lentes » peut être due à des lectures obsolètes, pas à des requêtes lentes.

Deuxième étape : trouver la catégorie de goulot

1. Goulot d’application/rejeu : IO saturé, thread SQL sérialisé, rejeu WAL bloqué, conflits/annulations.
2. Goulot de requête : requêtes coûteuses, index manquants, trop de concurrence, mauvais plans.
3. Goulot système : vol CPU, pression mémoire, perte réseau, voisin bruyant.

Troisième étape : prendre une décision de routage immédiatement

• Si le retard > tolérance : routez les lectures sensibles vers le primaire ou vers un réplica plus sain.
• Si le réplica est surchargé : limitez la concurrence (pooling, timeouts) et déchargez le trafic de lecture non critique.
• Si l’application est en retard : arrêtez d’envoyer de l’analytics lourd au candidat HA. Laissez-le rattraper son retard.

Quatrième étape : corriger avec le plus petit levier qui change l’issue

Les problèmes de réplica se résolvent souvent par des actions ennuyeuses : un index, une réécriture de requête, une réduction du nombre de connexions, une montée en IO. Résistez à la tentation de repenser la topologie de réplication pendant un incident.

Erreurs courantes : symptômes → cause racine → correctif

Erreur 1 : « On peut envoyer tous les SELECT aux réplicas »

Symptômes : Lectures aléatoires obsolètes, lignes « manquantes », état UI incohérent après des mises à jour.

Cause racine : Aucun contrat de fraîcheur ; le retard asynchrone est normal sous charge.

Correctif : Implémentez le « lire ses propres écritures » (fenêtre d’affinité primaire ou clôtures LSN/GTID). Routez uniquement les classes de requêtes tolérantes à l’obsolescence vers les réplicas.

Erreur 2 : le standby Postgres annule les requêtes BI

Symptômes : Erreurs sur le conflit avec la recovery, tableaux de bord qui expirent pendant les périodes intensives de vacuum.

Cause racine : Conflit hot standby entre le rejeu WAL et les snapshots longue durée.

Correctif : Déplacez la BI sur un réplica dédié/réplication logique ; ou activez hot_standby_feedback avec un plan contre le bloat ; ou imposez des durées de requête bornées.

Erreur 3 : le réplica Percona est « sain » parce que le thread IO tourne

Symptômes : IO thread Yes, SQL thread Yes, mais le retard continue d’augmenter ; les lectures deviennent de plus en plus obsolètes.

Cause racine : Le débit d’application est insuffisant (sérialisation du thread SQL, grosses transactions, saturation disque).

Correctif : Ajustez la réplication parallèle, réduisez la taille des transactions, corrigez les tables points chauds, et provisionnez l’IO pour l’application autant que pour les lectures.

Erreur 4 : réplicas utilisés pour sauvegardes, reporting et basculement

Symptômes : Le candidat de basculement est toujours en retard ; la promotion entraîne une fenêtre de perte d’écritures accrue ; les sauvegardes ralentissent le cluster.

Cause racine : Les réplicas multi-usage créent des priorités concurrentes et des retards imprévisibles.

Correctif : Attribuez des rôles : au moins un réplica HA « propre », des réplicas de pool de lecture, et un nœud séparé pour reporting/ETL si nécessaire.

Erreur 5 : sur-optimisation de la durabilité au mauvais niveau

Symptômes : Les écritures ralentissent après activation du sync/semisync ; p99 latence en hausse ; charge d’incident accrue.

Cause racine : La latence est désormais liée au RTT du réplica et au fsync. Le réplica n’était pas dimensionné pour cette responsabilité.

Correctif : Si vous avez besoin d’une durabilité plus forte, gardez les cibles synchrones proches et rapides. Sinon acceptez l’asynchrone et concevez explicitement pour le RPO/RTO.

Erreur 6 : le pool de lecture fond sous les tempêtes de connexions

Symptômes : Réplicas montrant un fort contexte de commutation CPU, beaucoup de connexions inactives, pression mémoire, pics de latence soudains.

Cause racine : Pas de pooling ni de limites de concurrence ; dashboards et jobs batch ouvrent beaucoup de connexions.

Correctif : Utilisez PgBouncer ou ProxySQL (ou équivalent), définissez des tailles de pool, imposez des timeouts et isolez les jobs batch.

Trois mini-histoires de la vie en entreprise (anonymisées, douloureusement familières)

Incident causé par une mauvaise hypothèse : « Les lectures ne peuvent pas casser les flux monétaires »

Une plateforme d’abonnement de taille moyenne a déplacé « la plupart des lectures » vers des réplicas compatibles MySQL pour protéger le primaire. L’objectif était raisonnable : réduire le CPU du primaire, garder la latence d’écriture stable et monter horizontalement. Un proxy faisait la séparation en se basant sur si la requête commençait par SELECT.

Deux semaines plus tard, le support a remarqué un pattern : des validations qui auraient dû être immédiates semblaient « en attente » pendant des minutes. La finance a noté pire : des doubles facturations occasionnelles quand les utilisateurs relançaient des actions. Personne n’avait modifié la logique de facturation. Les graphiques de la base semblaient normaux — jusqu’à ce qu’on regarde le retard des réplicas pendant le trafic de pointe.

La mauvaise hypothèse était simple : « les lectures de facturation sont simplement des lectures ». Mais le flux de facturation faisait en réalité écrire, puis lire pour vérifier l’état avant d’appeler un fournisseur de paiement externe. Quand cette vérification était dirigée vers un réplica en retard, il voyait l’ancien état et relançait l’opération. Le système se comportait de façon cohérente, simplement pas correctement.

La correction n’a pas été exotique. Ils ont ajouté une fenêtre d’affinité « primaire après écriture » par session utilisateur, et ont marqué certains chemins de requête comme « uniquement primaire ». Le retard est parfois survenu encore, mais ce n’était plus un bug d’exactitude. Ils ont aussi commencé à alerter sur « retard au-dessus de la tolérance métier », pas « retard au-dessus d’un nombre arbitraire ».

Optimisation qui s’est retournée contre eux : « Faisons en sorte que le standby arrête d’annuler les requêtes »

Une autre entreprise faisait tourner PostgreSQL avec un hot standby servant des tableaux de bord. L’équipe BI se plaignait des requêtes annulées pendant les périodes d’écriture intenses. L’équipe DB a activé hot_standby_feedback=on pour que le standby cesse de perdre les longues requêtes.

Les tableaux de bord sont devenus stables. Tout le monde a célébré. Puis l’utilisation disque sur le primaire a commencé à grimper d’une manière qui ne correspondait pas à la croissance des données. L’activité autovacuum augmentait, mais le bloat des tables continuait de croître. Les scans d’index ont ralenti. Le primaire travaillait plus pour faire le même travail.

Ce qui s’est passé : hot_standby_feedback a empêché le vacuum de nettoyer les tuples morts que le standby pourrait encore nécessiter. Les longues requêtes sur le standby disaient effectivement au primaire « garde les anciennes versions », et le primaire a obéi jusqu’à devenir un problème de stockage et de performance.

Ils ont annulé le changement et séparé les charges : un standby HA optimisé pour le rejeu et le basculement, et un réplica de reporting séparé avec des timeouts de requête contrôlés et un calendrier pour les rapports lourds. L’équipe BI n’a pas adoré les contraintes, mais le primaire a cessé de suffoquer lentement.

Pratique ennuyeuse mais correcte qui a sauvé la mise : « Un réplica est sacré »

Une entreprise SaaS avait une règle qui sonnait bureaucratique : un réplica par cluster est le candidat à la promotion, et il n’est pas utilisé pour des lectures ad hoc, l’ETL, les sauvegardes ou des expériences. Les gens se plaignaient que c’était du matériel « gaspillé ». Ce n’était pas le cas.

Pendant un incident de stockage sur un primaire, ils ont dû basculer sous pression. Le réplica candidat à la promotion était régulièrement dans des bornes de retard serrées, car il était protégé des charges bruyantes et son budget IO était réservé pour l’application. La promotion a été rapide et prévisible.

Pendant ce temps, un autre réplica servant le reporting avait plusieurs minutes de retard à cause d’un job batch faisant de gros scans. Si ce nœud avait été promu, la fenêtre de perte d’écritures aurait été sensiblement plus grande, et la récupération aurait nécessité une réconciliation au niveau métier. Au lieu de cela, ce fut un basculement standard et un postmortem standard.

La pratique n’était pas glamour. Elle ne demandait pas de nouveaux outils. Elle a sauvé la mise en réduisant les surprises. Dans le travail de fiabilité, réduire les surprises, c’est essentiellement tout le travail.

Listes de contrôle / plan étape par étape : réplicas qui fonctionnent

Étape 1 : Définir le contrat de fraîcheur en termes métier

• Listez les flux qui doivent « lire leurs propres écritures » (paiements, authentification, inventaire, clés d’idempotence, actions admin).
• Définissez l’obsolescence acceptable maximale pour tout le reste (secondes, pas des impressions).
• Transformez cela en règles de routage et en alertes.

Étape 2 : Séparer les rôles des réplicas

• Réplica HA : trafic de lecture minimal, optimisé pour rejeu/application, monitoré pour la disponibilité à la promotion.
• Réplica pool de lecture : sert les lectures applicatives tolérantes au retard ; protégé par pooling et timeouts.
• Reporting/ETL : nœud séparé si la charge est lourde ou longue.

Étape 3 : Rendre le routage applicable

• Utilisez des utilisateurs/roles distincts pour primaire vs réplica.
• Sur Percona : activez super_read_only.
• Sur Postgres : utilisez default_transaction_read_only pour les rôles réplica (et appliquez aussi au niveau proxy/app).

Étape 4 : Limiter la concurrence et la durée d’exécution

• Installez du pooling de connexions ; fixez des limites par service.
• Définissez des timeouts de statement pour les dashboards et outils ad hoc.
• Surveillez les transactions longues ; elles ruinent les deux systèmes différemment, mais ruinent quand même.

Étape 5 : Assurer que les réplicas disposent d’assez d’IO pour deux jobs

• Les réplicas doivent appliquer les changements et servir des lectures. C’est IO d’écriture + IO de lecture.
• Mesurez la saturation disque pendant le trafic de pointe, pas un mardi à midi.
• Si le stockage est le goulot, tuner le SQL devient un hobby, pas une solution.

Étape 6 : Tester le basculement comme si vous le pensiez vraiment

• Mesurez : temps de promotion, temps de reconnexion client, et fenêtre de perte de données (ou confirmation d’absence, si synchrone).
• Vérifiez les paramètres du nœud promu : flags lecture seule désactivés, server_id correct, config de réplication correcte, sauvegardes correctes.
• Exercez la restauration d’un nouveau réplica depuis une sauvegarde + rattrapage. Si vous ne pouvez pas le faire, vous n’avez pas un système de réplication ; vous avez des animaux de compagnie.

FAQ

1) Les réplicas PostgreSQL sont-ils « plus cohérents » que les réplicas Percona ?

Par défaut, les deux sont typiquement asynchrones, ce qui signifie que les deux peuvent être obsolètes. PostgreSQL a tendance à être plus explicite sur les conflits (annulation des requêtes) plutôt que de rester silencieusement en retard, mais l’obsolescence reste réelle.

2) Quelle est l’approche la plus simple et sûre pour mettre à l’échelle les lectures ?

Routez uniquement les lectures tolérantes à l’obsolescence vers les réplicas, gardez le « lire ses propres écritures » sur le primaire via l’affinité de session, et limitez la concurrence sur les réplicas avec du pooling.

3) Pourquoi mon standby Postgres annule-t-il des requêtes ?

Les conflits hot standby surviennent lorsque le rejeu WAL doit supprimer d’anciennes versions de lignes (souvent à cause du vacuum) mais qu’une requête sur le standby en a encore besoin. Corrigez en déplaçant les longues requêtes ailleurs, en ajustant le délai du standby, ou en utilisant hot_standby_feedback avec précaution.

4) Pourquoi Seconds_Behind_Source ment parfois ?

Dans MySQL/Percona, il peut être NULL ou trompeur dans certains états, et il est basé sur le temps plutôt que sur l’arriéré en octets. Comparez les sets GTID et surveillez l’état du thread d’application pour une image plus claire.

5) Dois-je utiliser la réplication synchrone pour rendre les réplicas sûrs pour les lectures ?

Les réglages synchrones aident la durabilité et réduisent la perte de données au basculement. Ils ne garantissent pas automatiquement des « lectures sûres » à moins d’assurer également que les lectures vont vers des nœuds ayant appliqué les transactions nécessaires. C’est toujours un problème de routage.

6) Puis-je exécuter de l’analytics sur le réplica HA ?

Oui, jusqu’au jour où vous devez basculer et découvrez que votre nœud HA est en retard ou à court de ressources. Si vous tenez à un basculement prévisible, gardez un réplica ennuyeux et protégé.

7) La réplication logique est-elle meilleure pour la mise à l’échelle des lectures ?

Parfois. La réplication logique peut réduire certains problèmes de conflit de rejeu et vous permettre de répliquer des sous-ensembles, mais elle ajoute de la complexité opérationnelle et ne supprime pas le besoin de discipline de routage et de tuning de performance.

8) Combien de réplicas me faut-il ?

Suffisamment pour gérer la concurrence de lecture lors des pics avec une marge, plus au moins un nœud réservé pour la HA (selon votre design de basculement). Si un réplica est toujours saturé, vous n’en avez pas assez ou vous routez mal la charge.

9) Quelle est la meilleure alerte pour la santé d’un réplica ?

Alertez sur le retard relatif à la tolérance métier, plus la saturation disque sur les réplicas, plus les annulations de requêtes (Postgres) ou les blocages d’application (Percona). Les alertes qui ne se rattachent pas à l’impact utilisateur créent de la fatigue d’alerte.

10) Les proxies peuvent-ils automatiser entièrement la séparation lecture/écriture en toute sécurité ?

Ils aident, mais ils ne peuvent pas connaître vos exigences produit. Un proxy peut classifier des requêtes ; il ne peut pas savoir quelles lectures doivent être fraîches. Vous devez encoder cette logique dans le routage applicatif ou la politique de session.

Prochaines étapes pratiques

• Rédigez le contrat de fraîcheur pour vos 10 principaux flux utilisateur. Si vous ne pouvez pas l’exprimer, vous ne pouvez pas l’appliquer.
• Choisissez les rôles des réplicas : un candidat HA maintenu propre, un ou plusieurs réplicas pool de lecture, et (si nécessaire) un nœud reporting/ETL.
• Implémentez des garde-fous de routage : affinité primaire après écriture, utilisateurs dédiés aux réplicas, et application de la lecture seule sur les réplicas.
• Exécutez les commandes ci-dessus chaque semaine et pendant les pics : retard en temps et en octets, état d’application, saturation IO, requêtes principales.
• Corrigez le plus gros goulot en premier : disques saturés, concurrence incontrôlée, ou une requête pathologique. Ne remplacez pas la capacité par des réglages.
• Entraînez-vous au basculement et mesurez-le. Si le basculement est une théorie non testée, il sera testé pour vous plus tard, à un mauvais moment.

Les réplicas peuvent absolument mettre à l’échelle les lectures. Ils peuvent aussi multiplier la confusion. La différence tient à savoir si vous traitez la réplication comme un système d’ingénierie avec des contrats, des budgets et de l’observabilité — ou comme un tour de magie que vous espérez ne pas avoir à expliquer.