MySQL vs PostgreSQL pour forte concurrence : qui s’arrête le premier et pourquoi

Ce que « forte concurrence » signifie réellement en production

Les gens disent « forte concurrence » quand ils veulent dire l’une des trois choses suivantes :

• Beaucoup de clients simultanés (des centaines à des dizaines de milliers de connexions actives).
• Beaucoup de transactions simultanées (un nombre plus restreint de connexions effectuant beaucoup de travail, fortement chevauchées).
• Beaucoup de conflits simultanés (tout le monde veut les mêmes quelques lignes, les mêmes pages d’index, les mêmes compteurs, la même partition, la même ligne de cache).

Le troisième est l’endroit où les systèmes vont pleurer. Vous pouvez acheter plus de coeurs pour « beaucoup de transactions ». Vous pouvez ajouter un pooler pour « beaucoup de clients ». Mais « beaucoup de conflits » est un problème de conception qui se fait passer pour un problème de capacité.

De plus : concurrence n’est pas débit. Vous pouvez avoir un faible débit et être pourtant limité par la concurrence si vous êtes coincé dans des attentes de verrous, des changements de contexte, des tempêtes d’fsync ou une file de threads se battant pour la même structure de données partagée.

Positionnement opiné : où chaque moteur casse en premier

Si vous me forcez à généraliser (et vous le faites), voici comment cela se déroule habituellement en production réelle :

PostgreSQL : vous atteignez rapidement le mur des connexions/processus, puis le nettoyage MVCC si vous êtes négligent

• Des milliers de connexions directes peuvent nuire rapidement car chaque backend est un processus avec overhead mémoire et coût d’ordonnancement. Postgres peut gérer de nombreux comptes de connexions, mais le faire sans mise en pool vous fait dépenser du CPU en overhead plutôt qu’en requêtes.
• Les transactions longues créent du poids mort MVCC : d’anciennes versions de lignes s’accumulent, les index gonflent, autovacuum prend du retard, et finalement des requêtes « simples » ralentissent car elles scannent un tas de déchets.
• Les mises à jour chaudes peuvent créer du bloat et de la contention au niveau des pages. « C’est MVCC donc les lectures ne bloquent pas les écritures » est vrai jusqu’à ce que le système soit occupé à nettoyer après vous.

MySQL (InnoDB) : vous atteignez les murs de contention de verrous et d’I/O/amplification d’écriture, surtout sur les lignes chaudes

• Lignes chaudes et index chauds apparaissent sous forme d’attentes de verrous, de deadlocks et d’effondrement du débit. InnoDB est robuste, mais ce n’est pas magique : « mettre à jour une ligne de compteur » à forte concurrence est essentiellement une attaque par déni de service auto-infligée.
• La vidange des pages sales et la pression redo/log peuvent dominer sous fortes écritures. Un flushing mal réglé ressemble à des pics de latence aléatoires et à « pourquoi le disque est occupé alors que le CPU est inactif ? »
• La réplication sous charge d’écriture (selon la topologie) devient le mur suivant : le lag grandit, le basculement devient dangereux, et vous découvrez que votre appli lisait des données « majoritairement cohérentes » et appelait cela une fonctionnalité.

Qui atteint le mur en premier ? Le moteur que vous exploitez comme si c’était l’autre moteur. Si vous exécutez Postgres sans mise en pool et avec des transactions longues, il craquera tôt. Si vous exécutez MySQL avec des mises à jour de lignes chaudes, d’énormes index secondaires et un optimisme « le buffer pool nous sauvera », il craquera tôt.

Deux choses sont toujours vraies : la base est d’abord blâmée, et la base est plus souvent coupable que personne ne veut l’admettre.

Blague n°1 : Une base de données en forte concurrence est comme un restaurant avec un seul serveur et des menus infinis — techniquement tout le monde peut commander, pratiquement personne ne mange.

Faits et contexte historique qui comptent encore

Un peu d’histoire « ancienne » reste opérationnellement pertinente parce qu’elle a façonné les valeurs par défaut, les modèles mentaux et les outils.

1. La lignée de PostgreSQL remonte à POSTGRES à l’UC Berkeley (années 1980). La culture « d’abord la correction » se voit dans des fonctionnalités comme MVCC, SQL robuste et un planificateur sérieux.
2. La domination initiale de MySQL (fin des années 1990–2000) venait du fait d’être rapide et facile pour les applis web, souvent avec MyISAM. Beaucoup d’histoires d’horreur sur la concurrence viennent d’avant InnoDB ou de changements d’engine mal compris.
3. InnoDB est devenu le défaut dans MySQL 5.5 (2010). Si quelqu’un parle encore de « verrous de table dans MySQL » comme d’une vérité universelle, il voyage dans le temps ou gère quelque chose de maudit.
4. L’autovacuum de Postgres est arrivé pour rendre MVCC viable à l’échelle. Ce n’est pas du ménage optionnel ; c’est le ramasse-miettes pour votre modèle de visibilité des données.
5. L’historique de réplication MySQL inclut des pièges de réplication basée sur des instructions, puis la réplication en ligne, puis mixte. Les charges d’écritures avec non-déterminisme ont appris beaucoup d’humilité à des équipes.
6. L’instrumentation des événements d’attente de Postgres (notamment étendue au fil des années) a changé la façon dont on débogue la contention : vous pouvez souvent nommer précisément le goulet au lieu de deviner.
7. Le Performance Schema de MySQL est devenu un véritable outil d’observabilité. Si vous ne l’utilisez pas pour diagnostiquer la contention, vous déboguez en vibes.
8. Les deux communautés ont tiré des leçons sur le fait que « plus de threads » est un piège : pression du planificateur, contention sur les mutex et saturation I/O ne négocient pas.
9. Les poolers de connexions sont devenus une pratique standard pour Postgres dans les environnements web à forte concurrence, façonnant le déploiement moderne (pgBouncer est pratiquement du mobilier).

Les murs : goulets d’étranglement courants sous concurrence extrême

Playbook de diagnostic rapide

L’objectif est d’identifier la ressource limitante en moins de 15 minutes. Pas de perfectionner le système. Pas de gagner une dispute. Arrêter l’hémorragie.

Première étape : confirmer si vous êtes lié par le CPU, l’I/O ou les verrous

• CPU-bound : forte utilisation CPU, file runnable qui grandit, temps de requête qui augmentent uniformément.
• I/O-bound : util disque élevée, pics de latence d’fsync ou d’écriture, checkpoints/flushing corrélés aux blocages.
• Lock-bound : de nombreuses sessions « actives » mais ne consommant pas de CPU ; elles attendent.
• Connection-bound : grand nombre de connexions, changements de contexte fréquents, pression mémoire, épuisement du pool.

Deuxième étape : trouver la raison d’attente principale, pas la requête la plus lente

En forte concurrence, la liste des « requêtes lentes » ment souvent. La chose lente est la file. Identifiez ce que tout le monde attend : un verrou, un buffer pin, flush WAL, espace redo, une page d’index spécifique, une synchronisation du système de fichiers.

Troisième étape : identifier le point chaud (table, index, ligne ou chemin de code)

Une fois que vous connaissez la classe d’attente, trouvez le point chaud. La correction est généralement l’une des suivantes :

• réduire la contention (sharder le point chaud, changer l’algorithme, éviter les mises à jour de lignes chaudes)
• réduire le travail (supprimer des index, grouper les écritures, mettre en cache les lectures, éviter des transactions inutiles)
• augmenter la capacité (stockage plus rapide, plus de mémoire, plus de CPU) — seulement après savoir ce que vous alimentez
• ajouter de l’isolation (pooler, file d’attente, backpressure)

Quatrième étape : faire un changement qui réduit la pression de concurrence

Les changements de stabilisation les plus rapides sont souvent ennuyeux : réduire le nombre maximum de transactions actives, activer la mise en pool, réduire les timeouts applicatifs, découper les gros jobs batch, et arrêter les transactions longues.

Blague n°2 : Ajouter plus de serveurs applicatifs à une base de données liée par des verrous, c’est comme crier sur un embouteillage — plus fort ne veut pas dire plus vite.

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

Ce sont les tâches que j’exécute réellement pendant les incidents. Chacune inclut ce que la sortie signifie et la décision que vous prenez à partir de celle-ci. Mixez et adaptez pour MySQL ou PostgreSQL selon ce qui brûle.

Tâche 1 : Vérifier la charge système et la saturation CPU (Linux)

cr0x@server:~$ uptime
 14:07:21 up 32 days,  6:12,  2 users,  load average: 22.44, 19.10, 13.02

Sens : Une moyenne de charge bien supérieure au nombre de coeurs CPU suggère une file runnable ou des attentes I/O non interruptibles.

Décision : Si la charge est élevée et le CPU est élevé, cherchez des requêtes coûteuses. Si la charge est élevée et le CPU modeste, suspectez des attentes de verrous ou des attentes I/O.

Tâche 2 : Identifier attente I/O et pression de swap

cr0x@server:~$ vmstat 1 5
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
12  3      0  52124  43120 918420    0    0   112  9840 9400 21000 35 10 35 20  0
18  5      0  49812  42980 915220    0    0   120 11240 9900 24000 30 12 28 30  0

Sens : Un wa élevé indique que le CPU attend le disque. Un cs élevé peut aussi suggérer trop de threads/processus actifs.

Décision : Si wa est constamment élevé, passez rapidement au diagnostic des checkpoints/flushs de la base et à la latence du stockage.

Tâche 3 : Mesurer rapidement la latence de stockage (Linux)

cr0x@server:~$ iostat -x 1 3
Device            r/s     w/s   r_await   w_await  aqu-sz  %util
nvme0n1         120.0   980.0     1.20    18.50    9.40   97.0

Sens : Un w_await élevé et un %util proche de 100% indiquent que le périphérique est saturé en écritures.

Décision : Traitez cela comme un goulet de stockage jusqu’à preuve du contraire. Réduisez le taux d’écriture (regroupement, moins d’index, moins d’événements fsync) ou améliorez le stockage.

Tâche 4 : Compter les connexions et trouver les principaux communicants (PostgreSQL)

cr0x@server:~$ psql -XAt -c "select state, count(*) from pg_stat_activity group by 1 order by 2 desc;"
active|412
idle|1870
idle in transaction|37

Sens : Beaucoup de connexions inactives suggèrent qu’il faut du pooling. Tout idle in transaction non trivial est un signal d’alerte.

Décision : Si idle est énorme, mettez pgBouncer (pooling par transaction pour de nombreuses applis OLTP) et limitez les connexions serveur. Si des idle-in-transaction existent sous charge, chassez ces clients en priorité.

Tâche 5 : Trouver ce sur quoi les sessions Postgres attendent

cr0x@server:~$ psql -X -c "select wait_event_type, wait_event, count(*) from pg_stat_activity where state='active' group by 1,2 order by 3 desc limit 10;"
 wait_event_type |  wait_event  | count
-----------------+--------------+-------
 Lock            | transactionid |   120
 IO              | DataFileRead  |    64
 LWLock          | buffer_content|    38

Sens : Votre concurrence est limitée par des classes d’attente (transactionid locks, I/O reads, LWLocks sur le contenu des buffers).

Décision : Des accumulations de locks transactionid signifient souvent des mises à jour de lignes chaudes ou des vérifications de FK lourdes. DataFileRead indique des manques de cache ou des scans gonflés. buffer_content suggère une contention sur les shared buffers/pages.

Tâche 6 : Identifier la chaîne de blocage dans Postgres

cr0x@server:~$ psql -X -c "select blocked.pid as blocked_pid, blocker.pid as blocker_pid, blocked.query as blocked_query, blocker.query as blocker_query from pg_stat_activity blocked join pg_stat_activity blocker on blocker.pid = any(pg_blocking_pids(blocked.pid)) where blocked.state='active' limit 5;"
 blocked_pid | blocker_pid | blocked_query              | blocker_query
------------+-------------+---------------------------+-------------------------------
      18421 |       17210 | update counters set v=v+1 | update counters set v=v+1

Sens : Un empilement classique de mises à jour sur une ligne chaude.

Décision : Arrêtez les schémas « update même ligne ». Utilisez des compteurs sharded, des logs d’événements append-only avec agrégation, ou déplacez vers un cache suivi de flushs périodiques.

Tâche 7 : Vérifier la santé d’autovacuum et les tuples morts (PostgreSQL)

cr0x@server:~$ psql -X -c "select relname, n_live_tup, n_dead_tup, last_autovacuum from pg_stat_user_tables order by n_dead_tup desc limit 5;"
 relname  | n_live_tup | n_dead_tup |     last_autovacuum
----------+------------+------------+---------------------------
 events   |   52000000 |   18000000 | 2025-12-29 12:11:03+00
 orders   |    9000000 |    2100000 | 2025-12-29 13:40:21+00

Sens : Beaucoup de tuples morts implique bloat et pression de vacuum ; les lectures ralentissent et les caches deviennent moins efficaces.

Décision : Si autovacuum n’arrive pas à suivre, ajustez les réglages autovacuum par table, réduisez le churn d’UPDATE, et éliminez les transactions longues qui bloquent le vacuum.

Tâche 8 : Trouver les transactions longues (PostgreSQL)

cr0x@server:~$ psql -X -c "select pid, now()-xact_start as xact_age, state, left(query,80) from pg_stat_activity where xact_start is not null order by xact_age desc limit 5;"
  pid  | xact_age |        state        | left
-------+----------+---------------------+----------------------------------------
 20311 | 01:12:09 | idle in transaction | SELECT * FROM customer WHERE id=$1

Sens : Une transaction immobile d’une heure bloque le vacuum et augmente le risque de bloat.

Décision : Tuez-la si c’est sûr, puis corrigez l’appli. Ajoutez des timeouts (idle_in_transaction_session_timeout) et assurez-vous que les clients ne maintiennent pas de transaction pendant des appels réseau.

Tâche 9 : Vérifier le comportement des checkpoints Postgres

cr0x@server:~$ psql -X -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
------------------+-----------------+-----------------------+----------------------+-------------------
              128 |             412 |               983210  |               210992 |           8420012

Sens : Beaucoup de checkpoints demandés (checkpoints_req) suggèrent une pression WAL forçant des checkpoints, souvent corrélée à des pics d’écriture et des falaises de latence.

Décision : Envisagez d’ajuster les paramètres de checkpoint et la taille du WAL, et réduisez l’amplification d’écriture (index, patterns d’update). Vérifiez aussi que le stockage peut gérer des écritures soutenues.

Tâche 10 : Voir threads/connexions MySQL et requêtes en cours

cr0x@server:~$ mysql -e "SHOW GLOBAL STATUS LIKE 'Threads_%';"
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 120   |
| Threads_connected | 1850  |
| Threads_running   | 210   |
+-------------------+-------+

Sens : Threads_running est la vraie pression de concurrence. Des centaines de threads en cours peuvent signifier contention CPU, attentes de verrous, ou les deux.

Décision : Si les threads en cours sont nombreux et la latence élevée, trouvez ce sur quoi ils attendent (verrous, I/O, redo). Si les connexions sont énormes, corrigez le pooling et les timeouts.

Tâche 11 : Vérifier les attentes et deadlocks InnoDB

cr0x@server:~$ mysql -e "SHOW ENGINE INNODB STATUS\G" | sed -n '1,80p'
=====================================
2025-12-29 14:09:56 INNODB MONITOR OUTPUT
=====================================
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION 8912231, ACTIVE 0 sec updating or deleting
...

Sens : Des deadlocks sous charge sont relativement normaux ; les deadlocks fréquents sont un signe de conception (lignes chaudes, ordre de verrous incohérent, index manquants).

Décision : Si les deadlocks augmentent, priorisez la refonte des requêtes/chemins et l’ordre cohérent des transactions. Ajoutez/ajustez des index pour éviter des verrous de plage issus de scans complets.

Tâche 12 : Identifier les attentes de verrous MySQL via Performance Schema

cr0x@server:~$ mysql -e "select object_schema, object_name, count_star, sum_timer_wait/1000000000000 as seconds_waited from performance_schema.table_lock_waits_summary_by_table order by sum_timer_wait desc limit 5;"
+--------------+-------------+-----------+----------------+
| object_schema| object_name  | count_star| seconds_waited |
+--------------+-------------+-----------+----------------+
| appdb        | counters     |  1203321  |  842.21        |
| appdb        | orders       |   214220  |  190.44        |
+--------------+-------------+-----------+----------------+

Sens : Quelles tables causent des attentes de verrous, quantifiées. « counters » est pratiquement une confession.

Décision : Corrigez le point chaud en premier. Aucun réglage du buffer pool n’aide un compteur sur une seule ligne à monter en charge.

Tâche 13 : Vérifier les signaux de pression du redo log MySQL

cr0x@server:~$ mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';"
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_log_waits | 1842  |
+------------------+-------+

Sens : Une valeur non nulle et en augmentation de Innodb_log_waits signifie que des transactions attendent de l’espace/de la vidange du redo log.

Décision : Étudiez la taille du redo log et les paramètres de flush ; réduisez le volume d’écriture ; confirmez la latence stockage. C’est un mur classique d’écritures à forte concurrence.

Tâche 14 : Voir les principales instructions MySQL par latence totale (Performance Schema)

cr0x@server:~$ mysql -e "select digest_text, count_star, round(sum_timer_wait/1000000000000,2) as total_s, round(avg_timer_wait/1000000000000,6) as avg_s from performance_schema.events_statements_summary_by_digest order by sum_timer_wait desc limit 3;"
+-------------------------------------------+------------+---------+---------+
| digest_text                               | count_star | total_s | avg_s   |
+-------------------------------------------+------------+---------+---------+
| UPDATE counters SET v = v + ? WHERE id=?  |  9202211   |  912.11 | 0.000099|
| SELECT * FROM orders WHERE user_id = ?    |   821220   |  410.44 | 0.000500|
+-------------------------------------------+------------+---------+---------+

Sens : Le petit UPDATE domine la latence totale parce qu’il s’exécute des millions de fois et crée de la contention.

Décision : Réduisez la fréquence, regroupez, shardez ou refactorez. Ne poursuivez pas des micro-optimisations sur le SELECT alors que l’UPDATE est le couteau.

Tâche 15 : Vérifier rapidement les signes de bloat d’index Postgres (approx.)

cr0x@server:~$ psql -X -c "select relname, pg_size_pretty(pg_relation_size(relid)) as table_sz, pg_size_pretty(pg_total_relation_size(relid)) as total_sz from pg_catalog.pg_statio_user_tables order by pg_total_relation_size(relid) desc limit 5;"
 relname | table_sz | total_sz
---------+----------+----------
 events  | 58 GB    | 110 GB
 orders  | 12 GB    | 28 GB

Sens : Une taille totale bien supérieure à la taille de la table suggère des index lourds et/ou du bloat. Ce n’est pas une preuve mais une forte odeur.

Décision : Enquêter sur les patterns d’update, autovacuum et la nécessité des index. Envisagez un reindex / fenêtres de maintenance si le bloat est confirmé et nuit aux hits cache.

Tâche 16 : Vérifier les bases du retard de réplication (Postgres)

cr0x@server:~$ psql -X -c "select client_addr, state, write_lag, flush_lag, replay_lag from pg_stat_replication;"
 client_addr |   state   | write_lag | flush_lag | replay_lag
-------------+-----------+-----------+-----------+------------
 10.0.2.18    | streaming | 00:00:00.2| 00:00:01.1| 00:00:03.8

Sens : Un replay_lag qui croît sous concurrence indique que les réplicas n’appliquent pas le WAL assez vite, souvent à cause d’I/O ou de CPU contraints.

Décision : Si les réplicas sont en retard et servent des lectures, vous servez une vérité obsolète. Soit corrigez la capacité des réplicas, réduisez le churn, ou orientez les lectures critiques vers le primaire.

Trois mini-récits d’entreprise des tranchées de la concurrence

Mini-récit 1 : L’incident causé par une mauvaise hypothèse

Une entreprise SaaS de taille moyenne a migré un service d’analytics client de MySQL vers PostgreSQL. L’équipe avait de bonnes raisons : meilleures fonctionnalités SQL, support JSON plus agréable pour leurs payloads, et un planificateur qui gérait mieux leurs rapports ad hoc. Ils ont fait des tests de charge. Ça semblait OK.

Puis est venu le premier « vrai » événement de trafic : une campagne marketing plus une intégration partenaire. La concurrence a grimpé, et la base a commencé à expirer. Le CPU n’était pas terrible, et le stockage ne fondait pas. Mais l’appli était coincée dans une panique lente.

La mauvaise hypothèse était simple : « Postgres peut gérer beaucoup de connexions, donc on ouvrira une par requête comme avant. » Ils avaient des milliers de connexions de courte durée qui se heurtaient. L’overhead d’authentification, le churn de processus, la fragmentation mémoire et les changements de contexte sont devenus une taxe sur chaque requête.

Pire, leurs bornes transactionnelles étaient négligées. Certaines requêtes ouvraient une transaction, faisaient un SELECT, appelaient un service externe, puis faisaient un UPDATE. Sous charge, ces sessions idle-in-transaction se sont empilées et le vacuum a pris du retard. Le système n’était pas seulement lent ; il s’aggravait lentement.

La correction fut peu glamour : pgBouncer en mode pooling par transaction, un plafond raisonnable de connexions côté serveur, et des timeouts stricts sur les transactions inactives. Ensuite, ils ont refactorisé le chemin « appeler un service externe en tenant une transaction ». La concurrence s’est stabilisée, et le même matériel a géré plus de travail. La base ne devenait pas « plus rapide ». Le système devenait moins stupide.

Mini-récit 2 : L’optimisation qui a mal tourné

Une plateforme e‑commerce sous MySQL (InnoDB) avait pour objectif de réduire les lectures sur la page produit durant les pics. Un ingénieur bien intentionné a ajouté une colonne « view_count » dans la table products et la mettait à jour à chaque vue de page. Une ligne par produit. Qu’est-ce qui pourrait mal tourner ?

Tout, en ralenti au début. Les jours normaux, ça fonctionnait. Lors des ventes, les produits populaires étaient martelés. La base a commencé à afficher des attentes de verrous et des deadlocks croissants. Les retries applicatifs se sont multipliés. La charge d’écriture a augmenté à cause des retries. La pression de flushing a augmenté. La latence a augmenté. Les timeouts ont augmenté. Les retries ont augmenté. Vous voyez la suite.

L’équipe a d’abord poursuivi le « tuning MySQL » : taille du buffer pool, concurrence des threads, réglages de flushing. Ils ont obtenu des améliorations marginales mais pas de stabilité. Le point chaud était la conception : une mise à jour à haute fréquence, très contended, sur un petit ensemble de lignes. Ils avaient créé un mutex global déguisé en fonctionnalité.

La vraie correction : arrêter de mettre à jour la même ligne pour chaque vue. Ils sont passés à une table d’événements append-only (ou même un système de compteurs externe) et ont agrégé de manière asynchrone. Soudain les deadlocks se sont calmés, le lag de réplication a diminué, et la base a arrêté de faire du cardio.

La leçon n’était pas « ne jamais stocker des compteurs ». C’était : si l’écriture est plus chaude que l’objet compté, vous avez besoin d’un pattern d’écriture qui s’adapte à la concurrence, pas qui s’y oppose.

Mini-récit 3 : La pratique ennuyeuse mais correcte qui a sauvé la situation

Une équipe de services financiers utilisait PostgreSQL pour de l’OLTP et avait un job batch hebdomadaire qui faisait des mises à jour lourdes pour la réconciliation. Le job était lent mais prévisible. Ils avaient l’habitude qui semblait paranoïaque : ils suivaient l’autovacuum par table, fixaient des réglages vacuum/analyze par table pour les relations à fort churn, et avaient une politique stricte qu’une transaction plus vieille qu’un seuil déclenchait une alerte.

Un jour, un nouveau déploiement de microservice a introduit un bug : fuite de connexions plus un oubli de commit dans un chemin d’erreur. Les sessions se sont empilées « idle in transaction » tout en tenant des snapshots. Le job batch a démarré, a généré des tuples morts, et autovacuum ne pouvait pas les récupérer. Les latences des requêtes ont monté, mais pas instantanément — juste assez pour éveiller les soupçons.

L’ingénieur d’astreinte n’a pas commencé par regarder les graphiques CPU. Il a lancé deux requêtes : événements d’attente actifs, et âge des transactions les plus longues. La transaction la plus longue avait des heures. C’était la piste. Ils ont tué les pires coupables, atténué la fuite en annulant, et le système s’est remis avant que le bloat ne devienne un nettoyage de plusieurs jours.

C’est le genre de victoire dont personne ne se vante en conférence. C’est aussi comme on garde ses week‑ends. Des garde‑fous ennuyeux — timeouts, alertes sur l’âge des transactions, vérification régulière du vacuum — empêchent que des problèmes de concurrence ne deviennent existentiels.

Erreurs courantes : symptômes → cause racine → correctif

1) « Le CPU est bas mais tout est lent »

Symptômes : latence élevée des requêtes, faible utilisation CPU, nombreuses sessions actives.

Cause racine : contention de verrous ou stalls I/O ; les threads/processus attendent plutôt qu’ils n’exécutent.

Correctif : identifiez les événements d’attente (Postgres) ou les attentes de verrous/redo (MySQL). Supprimez les schémas de lignes chaudes, corrigez les index manquants provoquant des verrous de plage, réduisez l’amplification d’écriture, et limitez la concurrence via pooling/backpressure.

2) Autovacuum PostgreSQL « n’arrive pas à suivre »

Symptômes : taille table/index qui grandit, taux de cache hit qui se dégrade, temps de requête qui augmentent sur plusieurs jours.

Cause racine : transactions longues tenant des snapshots ; tables à forte écriture sans seuils autovacuum adaptés.

Correctif : tuez/évitez les transactions longues, mettez des timeouts, ajustez autovacuum par table, et réduisez le churn d’UPDATE (préférez INSERT-only avec compaction/agrégation quand possible).

3) Les deadlocks MySQL augmentent après une release

Symptômes : logs de deadlock en hausse, tempêtes de retry, effondrement du débit.

Cause racine : nouveau chemin de code changeant l’ordre des verrous ou introduisant des updates de lignes chaudes ; index manquant provoquant un scan avec verrous larges.

Correctif : rendre l’ordre des verrous cohérent ; ajouter des index appropriés ; réduire la contention en sharding ; limiter les retries avec jitter et plafonnement.

4) « Ajouter des replicas n’a pas aidé »

Symptômes : primaire toujours surchargé, replicas en retard, latence de lecture incohérente.

Cause racine : la charge est liée aux écritures/verrous, pas aux lectures ; les replicas ne peuvent pas appliquer assez vite.

Correctif : corrigez d’abord le chemin d’écriture (index, regroupement, contention). Orientez vers les replicas seulement les lectures tolérantes au retard. Améliorez l’I/O des replicas si l’application est le goulot.

5) L’épuisement du pool de connexions provoque des pannes en cascade

Symptômes : timeouts applicatifs, taux d’erreur en hausse, la base montre des tempêtes de connexions.

Cause racine : taille du pool trop petite par rapport à la latence ; ou pire, pool trop grand et qui écrase la base de données ; timeouts manquants laissent des clients coincés tenant des connexions.

Correctif : fixez un plafond dur sur les connexions DB, utilisez du pooling, ajoutez des timeouts de requête et de transaction, et imposez du backpressure au bord.

6) « Nous avons réglé la mémoire mais c’est toujours lent »

Symptômes : caches importants configurés, mais beaucoup de lectures disque et stalls sous concurrence.

Cause racine : bloat, mauvais plans de requête sous paramétrisation, ou jeu de travail plus grand que la mémoire à cause d’index trop nombreux ou de lignes volumineuses.

Correctif : réduisez le bloat et les index inutiles ; mesurez les ratios de cache hit ; corrigez les patterns de requêtes et les statistiques ; envisagez la partition ou l’archivage.

Listes de contrôle / plan étape par étape

Plan étape par étape : choisir entre MySQL et PostgreSQL pour l’OLTP à forte concurrence

1. Définissez le modèle de contention : y a‑t‑il des points chauds (compteurs, inventaire, files), ou majoritairement des lignes indépendantes ?
2. Définissez la forme des transactions : courtes et fréquentes, ou longues et complexes ? Si les transactions longues sont courantes, planifiez des contrôles et des timeouts dès le premier jour.
3. Décidez de la stratégie de connexion : pour Postgres, supposez que le pooling est requis. Pour MySQL, pooler aussi ; ne considérez pas la BD comme un simple serveur de sockets.
4. Mesurez l’amplification d’écriture : comptez les index, la largeur des lignes, et le taux d’updates attendu. Si vous écrivez beaucoup, soyez impitoyable sur les index.
5. Choisissez l’isolation intentionnellement : les niveaux par défaut existent pour des raisons historiques, pas parce qu’ils sont optimaux pour votre charge.
6. Concevez le backpressure : votre appli doit se dégrader gracieusement quand la BD est saturée. Sans cela, n’importe quelle BD échouera « dramatiquement ».
7. Planifiez les sémantiques de réplication : quelles lectures peuvent tolérer du retard ? Si la réponse est « aucune », l’architecture doit en tenir compte.
8. Opérationnalisez la maintenance : vacuum et bloat (Postgres), purge et pression redo (MySQL), sauvegardes, basculements, et changements de schéma sous charge.

Checklist : stabiliser une base liée par des verrous en incident

• Confirmer que les attentes de verrous sont la classe d’attente dominante (wait events Postgres / synthèses d’attente de verrous MySQL).
• Identifier la table/index le plus contesté et le schéma exact des requêtes.
• Réduire temporairement la concurrence : baisser le nombre de workers applicatifs, réduire les consommateurs de queue, resserrer la taille du pool, ou délester la charge.
• Arrêter les retries en masse : plafonner les retries, ajouter du jitter, et échouer rapidement pour les chemins non critiques.
• Appliquer des mitigations ciblées : ajouter un index manquant, changer l’ordre d’une transaction, désactiver une fonctionnalité non essentielle qui écrit.
• Après stabilité : refondre le point chaud (compteurs sharded, redesign de file, événements append-only, partitionnement).

Checklist : prévenir le bloat MVCC de Postgres qui tue la concurrence

• Alerter sur les sessions idle in transaction et l’âge des transactions longues.
• Revoir les tables à fort churn chaque semaine : tuples morts, fréquence de vacuum, croissance des tables.
• Ajuster autovacuum par table où le churn est élevé ; ne comptez pas uniquement sur les valeurs globales par défaut.
• Préférer les patterns INSERT-only + compaction/agrégation périodique pour les flux d’événements à forte écriture.
• Garder les transactions courtes ; ne pas tenir de snapshots pendant des appels externes.

Checklist : prévenir les tempêtes de verrous InnoDB MySQL

• Identifier et éliminer les updates de lignes chaudes et les « compteurs globaux ».
• Assurer que les WHERE critiques sont indexés pour éviter des verrous larges et des scans.
• Garder les transactions courtes ; éviter les gros batches qui tiennent des verrous longtemps.
• Surveiller les attentes du redo log et le comportement de flush sous écritures soutenues.
• Valider le comportement du lag de réplication lors des tests de charge ; ne le découvrez pas en production.

FAQ

1) PostgreSQL est‑il « pire » en forte concurrence parce qu’il utilise des processus ?

Non. Il est pire face à des schémas de connexions négligents. Avec du pooling et des nombres de sessions raisonnables, Postgres gère très bien une concurrence transactionnelle élevée. Sans pooling, vous payez un overhead OS inutilement.

2) MySQL est‑il « meilleur » parce qu’il est plus rapide ?

Parfois il est plus rapide pour des patterns OLTP simples et peut être très efficace. Mais « plus rapide » s’effondre sous la contention de verrous et l’amplification d’écriture. Sous des updates de lignes chaudes, MySQL vous punira vite et fort.

3) Lequel touche le mur en premier à 10 000 connexions ?

Sans pooling : généralement Postgres se plaindra plus tôt à cause de l’overhead par processus. Avec pooling : les deux peuvent survivre, et le mur se déplace vers les verrous, l’I/O ou la conception des requêtes.

4) Pourquoi des UPDATE « simples » deviennent‑ils le principal consommateur de latence ?

Parce qu’ils ne sont pas simples sous contention. Un UPDATE de 0,1 ms exécuté dix millions de fois avec des attentes de verrous devient votre coût dominant. Fréquence plus contention bat la finesse à chaque fois.

5) Les replicas de lecture peuvent‑ils résoudre les problèmes de forte concurrence ?

Seulement si vous êtes vraiment lié aux lectures et que vos lectures tolèrent le lag des replicas. Si vous êtes lié par des verrous ou des écritures, les replicas ne résoudront pas le goulet du primaire et peuvent ajouter de la complexité opérationnelle.

6) Quelle est la meilleure pratique unique pour Postgres en forte concurrence ?

Utiliser un pooler de connexions et imposer des transactions courtes avec timeouts. Si vous faites cela, vous évitez les murs précoces les plus courants et maintenez la santé du nettoyage MVCC.

7) Quelle est la meilleure pratique unique pour MySQL en forte concurrence ?

Éliminer les points chauds et s’assurer que les index soutiennent vos requêtes d’écriture. Surveillez aussi les attentes du redo log ; si le travail de durabilité bloque les écritures au premier plan, vous verrez un effondrement de la concurrence.

8) L’isolation SERIALIZABLE est‑elle une mauvaise idée pour la concurrence ?

Pas intrinsèquement, mais elle est coûteuse et peut augmenter les retries/échecs de sérialisation sous contention. Utilisez‑la quand vous en avez besoin, et concevez pour les retries délibérément. Ne l’activez pas par sécurité en espérant le meilleur.

9) Comment savoir si je dois monter en hardware ou refondre les requêtes ?

Si les attentes sont dominées par un point chaud unique ou par des scans induits par le bloat, refondez d’abord. Si vous saturerez le stockage avec des écritures soutenues inévitables et que vous avez réduit l’amplification, alors montez le hardware.

10) Quel est le seul indicateur qui prédit fiablement un incident imminent ?

Les outliers d’âge de transaction. Une transaction longue peut empoisonner la concurrence en bloquant le nettoyage (Postgres) ou la purge (InnoDB) et en retenant des verrous plus longtemps que prévu.

Prochaines étapes (quoi faire lundi matin)

Si vous hésitez entre MySQL et PostgreSQL pour un système à forte concurrence, choisissez en fonction de vos modes de défaillance attendus et de votre discipline opérationnelle :

• Si vous pouvez imposer du pooling et une hygiène transactionnelle, PostgreSQL est un excellent choix par défaut avec une observabilité et une profondeur SQL remarquables.
• Si votre charge est OLTP simple avec une discipline stricte de schéma/index, MySQL/InnoDB peut être brutalement efficace — jusqu’à ce que vous introduisiez des points chauds et prétendiez que c’est acceptable.

Puis faites les tâches ennuyeuses qui évitent les sauvetages héroïques à l’avenir :

1. Implémentez le pooling de connexions et imposez un plafond dur des connexions BD.
2. Ajoutez des timeouts : timeout de requête, timeout de transaction, et timeout idle-in-transaction (ou équivalents côté appli).
3. Construisez des tableaux de bord pour les attentes (wait events Postgres ; verrous/redo waits MySQL), pas seulement le CPU.
4. Réalisez un test de charge qui inclut les points chauds : compteurs, files et jobs batch. Si vous ne testez pas les parties laides, la production le fera.
5. Élaborez un playbook pour les points chauds : compteurs sharded, événements append-only, redesign de file et stratégies de partitionnement.

Un principe opérationnel à garder : paraphrased idea de John Allspaw : la fiabilité vient du fait de concevoir des systèmes qui s’attendent à l’échec et y répondent gracieusement, pas de prétendre que les échecs n’arriveront pas.

Choisissez votre base de données. Puis administrez‑la sérieusement.