MySQL vs PostgreSQL : « C’est devenu lent » — plan de diagnostic de 15 minutes pour les deux

Playbook de diagnostic rapide (15 minutes)

Voici l’ordre qui trouve le goulot d’étranglement de façon la plus fiable sans empirer la situation. Il est conçu pour l’incident « c’est soudainement devenu lent » où vous avez besoin d’un signal rapidement.

Minute 0–2 : Confirmer que c’est la base de données (et pas votre application qui ment)

1. Comparer la latence de l’app et celle de la BD. Si votre application mesure le temps par requête, utilisez-le. Sinon, exécutez une requête DB triviale depuis l’hôte de l’app (pas votre portable) pour éliminer les problèmes réseau et DNS.
2. Vérifier les métriques du pool de connexions. Un pool saturé ressemble à une BD lente. Souvent c’est simplement « trop de threads en attente d’une connexion ».

Décision : Si les requêtes triviales sont rapides et que l’attente du pool est élevée, votre BD peut être correcte et le feu est dans la taille du pool / la concurrence de l’app.

Minute 2–5 : Chercher le mur de ressources évident (CPU, mémoire, E/S)

1. CPU saturé ? Pensez : mauvais plan, index manquant, requête de rapport soudaine, ou un troupeau grondant.
2. Pression mémoire ? Le swap rend les bases de données comme si elles tournaient sous l’eau.
3. Latence disque ? Si le stockage est lent, tout est lent. Ne débattez pas SQL pendant que vos disques fondent.

Décision : Si la latence de stockage est élevée, priorisez le chemin de stockage et réduisez la pression d’écriture. Si le CPU est saturé, identifiez les requêtes principales et stoppez le pire coupable.

Minute 5–9 : Vérifier la contention (verrous, waits, famine de pool)

1. Verrous : trouver les sessions bloquantes et l’instruction qui tient le verrou. Tuez le bloqueur seulement quand vous comprenez ce que c’est (changement de schéma ? transaction longue ? job batch ?).
2. Événements d’attente : MySQL : attentes InnoDB, verrous de métadonnées. PostgreSQL : waits de lock, LWLocks, attentes I/O.

Décision : Si vous voyez un seul bloqueur provoquant un entassement, résolvez-le d’abord. La contention multiplie la douleur.

Minute 9–13 : Identifier les requêtes « coût » dominantes en ce moment

1. Trouver les empreintes de requêtes consommant du temps, pas seulement le nombre.
2. Vérifier une régression de plan (index non utilisé, stats obsolètes, changement de paramètre, valeurs de bind différentes).

Décision : Si une requête domine, atténuez (limiter le débit, tuer, ajouter un index, basculer un flag fonctionnalité) avant de faire quelque chose de « brillant ».

Minute 13–15 : Choisir une atténuation sûre et capturer des preuves

• Atténuer : arrêter l’hémorragie (annuler/tuer les sessions hors de contrôle, réduire la concurrence, désactiver temporairement les chemins fonctionnels coûteux, ajouter un index ciblé si sûr, augmenter les timeouts du pool pour éviter les stampedes).
• Capturer : snapshot du processlist / pg_stat_activity, des waits, des top queries, iostat, et de l’état de réplication. Vous en aurez besoin après que l’adrénaline baisse.

Idée paraphrasée de Gene Kim : « Améliorer la fiabilité, c’est en grande partie améliorer les boucles de feedback et raccourcir le temps de diagnostic. »

La réalité partagée : la lenteur est généralement l’une de cinq choses

MySQL et PostgreSQL ont des tripes différentes, mais la lenteur en production a tendance à rimer. La plupart des incidents se résument à une de ces catégories :

1. Saturation des ressources : CPU, mémoire, E/S disque, réseau ou descripteurs de fichiers.
2. Contention : verrous, contention de latch, hotspots de lignes, ou trop d’écrivains concurrents.
3. Mauvais plans : index manquants, statistiques obsolètes, changements de paramètres, bizarreries du cache de plan, ou changements de distribution des données.
4. Amplification des écritures : checkpoints, vagues de flush, dette d’autovacuum/vacuum, ou pression sur redo/WAL.
5. Dépendances externes : pics de latence du stockage, voisins bruyants, cache RAID défaillant, problèmes d’hyperviseur, ou un job de sauvegarde qui fait quelque chose de « innocent ».

C’est pourquoi le playbook commence par l’hôte et les waits. Vous ne pouvez pas résoudre en SQL un contrôleur de stockage qui fait une crise.

Blague #1 : La façon la plus rapide de rendre une base de données lente est de déclarer « Ça va probablement » et ensuite lancer une requête de rapport full-table pendant le pic de trafic.

Faits & histoire intéressants qui comptent en incident

Ce ne sont pas des anecdotes de quiz. Ce sont celles qui expliquent pourquoi le moteur se comporte ainsi quand vous êtes de garde.

1. Le MVCC de PostgreSQL est plus ancien que beaucoup de runbooks de production. Son contrôle de concurrence multi-version signifie que les lecteurs ne bloquent pas les écrivains, mais il crée des tuples morts que vacuum doit nettoyer — sinon bloat et lenteur suivent.
2. InnoDB n’a pas toujours été « MySQL ». Historiquement MySQL avait plusieurs moteurs de stockage ; InnoDB est devenu le défaut car l’exactitude transactionnelle et la récupération après crash sont devenues non négociables en production.
3. Les verrous de métadonnées MySQL peuvent arrêter le monde. Certains DDL et même certaines instructions « innocentes » prennent des verrous de métadonnées, qui peuvent bloquer d’autres requêtes de manière surprenante durant des changements de schéma.
4. PostgreSQL considère « VACUUM » comme une préoccupation opérationnelle de première classe. Autovacuum n’est pas une décoration optionnelle ; c’est la façon dont Postgres paie sa dette MVCC et empêche le wraparound d’ID de transaction.
5. La réplication fait partie de la performance. Le lag de réplication MySQL peut transformer l’« élasticité en lecture » en incident d’intégrité des données, et le lag Postgres peut croître quand la génération de WAL submerge l’envoi ou la lecture.
6. Les deux moteurs se sont nettement améliorés en introspection. Performance Schema de MySQL et les vues de statistiques de Postgres (plus des extensions comme pg_stat_statements) ont transformé le « deviner » en « mesurer », si vous les activez réellement.
7. Les paramètres par défaut reflètent des compromis, pas votre charge de travail. Les deux systèmes sont livrés avec des réglages conçus pour démarrer sur des machines modestes. La production nécessite des décisions explicites sur la mémoire, les checkpoints et les connexions.
8. Le planificateur de requêtes de PostgreSQL est basé sur le coût et sensible aux statistiques. Quand les stats sont obsolètes ou la distribution des données change, les plans peuvent basculer d’« index scan » à « sequential scan » et vous jurerez que quelqu’un a saboté la DB.
9. L’histoire de MySQL inclut des arêtes vives autour de comportements implicites. Des choses comme la troncature silencieuse (selon SQL mode) et des valeurs par défaut d’isolation différentes ont historiquement causé des histoires « ça marchait en staging » — souvent performance et exactitude combinées.

Tâches pratiques : commandes, sorties et décisions (MySQL + PostgreSQL + Linux)

Vous voulez des commandes à exécuter pendant un incident, plus ce que la sortie signifie et quelle décision prendre ensuite. Voici celles que j’utilise en premier.

Tâche 1 : Vérifier la charge hôte, le steal CPU et la file d’exécution

cr0x@server:~$ uptime
 10:21:33 up 187 days,  3:12,  2 users,  load average: 28.14, 26.90, 19.77

Sens : Une charge moyenne bien au-dessus des cœurs CPU suggère contention (CPU, E/S ou file d’exécution). Si vous êtes en virtualisé/cloud, une charge élevée avec une utilisation CPU modeste signifie souvent des waits d’E/S ou du throttling.

Décision : Si la charge est énorme, vérifiez immédiatement iowait et la latence disque (Tâche 3). Ne commencez pas l’« optimisation » SQL à l’aveugle.

Tâche 2 : Vérifier la pression mémoire et le swapping

cr0x@server:~$ free -h
               total        used        free      shared  buff/cache   available
Mem:            62Gi        55Gi       1.2Gi       512Mi       5.8Gi       2.3Gi
Swap:           8.0Gi       6.4Gi       1.6Gi

Sens : Une utilisation intensive du swap sur un hôte DB est généralement une spirale de mort pour la performance. « Available » mémoire faible et le swap impliquent que l’OS évince les working sets.

Décision : Arrêter la nouvelle charge (limiter le débit), réduire les pics de mémoire DB (ex. trop de connexions), et envisager un redémarrage seulement si vous pouvez le faire en sécurité après avoir trouvé la fuite. Vérifiez aussi les réglages mémoire MySQL/Postgres par rapport à la RAM totale.

Tâche 3 : Mesurer la latence et la saturation du disque

cr0x@server:~$ iostat -xz 1 3
Linux 6.2.0 (db01)  12/29/2025  _x86_64_  (16 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          12.41    0.00    6.23   38.77    0.00   42.59

Device            r/s     w/s   rkB/s   wkB/s  await  aqu-sz  %util
nvme0n1         120.0  1800.0   4096  98304   24.8   12.4    98.9

Sens : %util proche de 100% avec await élevé indique que le device est saturé et que les requêtes font la queue. iowait est aussi élevé. C’est une lenteur liée au stockage.

Décision : Trouver ce qui écrit (Tâche 4), réduire la pression d’écriture (mettre en pause jobs batch, limiter l’intensité de l’autovacuum avec prudence, ralentir l’ingestion) et vérifier les pics de checkpoint/flush dans la BD.

Tâche 4 : Identifier les principaux écrivains disque (rapide et sale)

cr0x@server:~$ pidstat -d 1 5
Linux 6.2.0 (db01)  12/29/2025  _x86_64_  (16 CPU)

12:22:11      UID       PID   kB_rd/s   kB_wr/s kB_ccwr/s  Command
12:22:12      106      2143      0.00  84210.00      0.00  mysqld
12:22:12      106      1987      0.00   5200.00      0.00  postgres
12:22:12        0      8891      0.00   1200.00      0.00  rsync

Sens : Vous pouvez voir quel processus martèle les écritures disque. Si rsync/sauvegarde écrit fort pendant le pic, félicitations : vous avez trouvé le coupable avec un badge « maintenance ».

Décision : Si des processus non-DB sont lourds, arrêter/replanifier. Si c’est la BD qui écrit, vérifier les checkpoints, les flushs et les transactions volumineuses.

Tâche 5 : Confirmer l’espace disque et la pression d’inodes

cr0x@server:~$ df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/nvme0n1p2  900G  842G   13G  99% /var/lib

Sens : 99% d’occupation peut anéantir la performance (fragmentation, délais d’allocation) et risque une panne complète. Beaucoup de bases se comportent mal près des disques pleins.

Décision : Libérer de l’espace maintenant (rotation des logs, déplacer des sauvegardes), puis planifier la capacité. Vérifier aussi les binary logs/WAL qui dérapent.

Tâche 6 : Vérifier les erreurs réseau et retransmissions (souvent « DB lent » = perte de paquets)

cr0x@server:~$ ss -s
Total: 1892 (kernel 0)
TCP:   1583 (estab 1242, closed 251, orphaned 0, timewait 251)

Transport Total     IP        IPv6
RAW	  0         0         0
UDP	  17        13        4
TCP	  1332      1210      122
INET	  1349      1223      126
FRAG	  0         0         0

Sens : Un grand nombre de connexions établies peut impliquer un mauvais comportement du pool ou des pics de trafic. Associez cela aux comptes de connexions DB.

Décision : Si les connexions ont explosé, cherchez un déploiement qui a changé le pooling ou les timeouts. Si les connexions sont stables mais que la latence est élevée, concentrez-vous sur les waits et l’E/S.

Tâche 7 (MySQL) : Voir ce que font les threads maintenant

cr0x@server:~$ mysql -e "SHOW FULL PROCESSLIST\G" | sed -n '1,80p'
*************************** 1. row ***************************
     Id: 93218
   User: app
   Host: 10.0.4.21:51344
     db: shop
Command: Query
   Time: 42
  State: Waiting for table metadata lock
   Info: ALTER TABLE orders ADD COLUMN promo_code varchar(32)
*************************** 2. row ***************************
     Id: 93244
   User: app
   Host: 10.0.3.19:51002
     db: shop
Command: Query
   Time: 38
  State: Sending data
   Info: SELECT * FROM orders WHERE user_id=... ORDER BY created_at DESC LIMIT 20

Sens : « Waiting for table metadata lock » est une grosse flèche rouge pointant un DDL bloquant des requêtes normales. « Sending data » longtemps peut indiquer des scans complets, des jeux de résultats énormes ou des waits I/O.

Décision : Si un verrou de métadonnées bloque, mettre en pause/tuer la session DDL (prudemment) ou attendre qu’elle se termine si elle est presque finie. Ensuite planifier le changement de schéma correctement (DDL en ligne quand possible).

Tâche 8 (MySQL) : Identifier les waits et bloqueurs InnoDB

cr0x@server:~$ mysql -e "SHOW ENGINE INNODB STATUS\G" | sed -n '1,120p'
------------
TRANSACTIONS
------------
Trx id counter 89423311
Purge done for trx's n:o < 89423000 undo n:o < 0 state: running
History list length 19876
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 89423288, ACTIVE 61 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 93244, OS thread handle 140228, query id 5129312 10.0.3.19 app Sending data
UPDATE inventory SET qty=qty-1 WHERE sku='A-1841'
------- TRX HAS BEEN WAITING 61 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 421 page no 183 n bits 72 index PRIMARY of table `shop`.`inventory` trx id 89423288 lock_mode X locks rec but not gap waiting

Sens : InnoDB vous indique qu’une transaction attend un verrou d’enregistrement. La « History list length » élevée peut aussi suggérer un retard de purge, corrélant avec des transactions longues.

Décision : Trouver la transaction bloquante (plus bas dans la sortie, ou via performance_schema) et décider : tuer le bloqueur, tuer l’attendant, ou réduire la concurrence sur cette clé chaude (SKU, ligne utilisateur, etc.). Si la longueur de la history list reste élevée, chasser les transactions longues.

Tâche 9 (MySQL) : Vérifier le lag de réplication et l’état du thread SQL

cr0x@server:~$ mysql -e "SHOW SLAVE STATUS\G" | egrep "Seconds_Behind_Master|Slave_IO_Running|Slave_SQL_Running|Last_SQL_Error|Relay_Master_Log_File|Exec_Master_Log_Pos"
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 487
Relay_Master_Log_File: binlog.003214
Exec_Master_Log_Pos: 98122314

Sens : Le replica a ~8 minutes de retard. Les lectures depuis des replicas peuvent être obsolètes ; promouvoir un replica en échec peut faire perdre des données.

Décision : Si l’app utilise des replicas pour les lectures, envisagez de router les lectures critiques vers le primaire temporairement. Investiguer pourquoi le thread SQL ne suit pas (écritures lourdes, apply single-thread, transactions longues).

Tâche 10 (PostgreSQL) : Vérifier les sessions actives et qui attend

cr0x@server:~$ psql -c "SELECT pid, usename, state, wait_event_type, wait_event, now()-query_start AS runtime, left(query,120) AS q FROM pg_stat_activity WHERE state <> 'idle' ORDER BY runtime DESC LIMIT 10;"
  pid  | usename | state  | wait_event_type |  wait_event  |  runtime  |                          q
-------+---------+--------+-----------------+--------------+-----------+----------------------------------------------------------
 18421 | app     | active | Lock            | relation     | 00:01:12  | ALTER TABLE orders ADD COLUMN promo_code text
 18502 | app     | active | Lock            | tuple        | 00:00:55  | UPDATE inventory SET qty=qty-1 WHERE sku='A-1841'
 18611 | app     | active | IO              | DataFileRead | 00:00:49  | SELECT * FROM orders WHERE user_id=$1 ORDER BY created_at
(3 rows)

Sens : Postgres vous dit si des sessions attendent des verrous ou de l’I/O. Les waits de type « relation » pointent souvent vers du DDL ou des transactions longues tenant des verrous.

Décision : Si les waits de verrou dominent, trouver le bloqueur (Tâche 11). Si les waits I/O dominent, revenir à iostat et vérifier le hit cache et les checkpoints.

Tâche 11 (PostgreSQL) : Trouver les PID bloqueurs (qui tient la porte fermée)

cr0x@server:~$ psql -c "SELECT blocked.pid AS blocked_pid, blocked.query AS blocked_query, blocker.pid AS blocker_pid, blocker.query AS blocker_query FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked ON blocked.pid = blocked_locks.pid JOIN pg_catalog.pg_locks blocker_locks ON blocker_locks.locktype = blocked_locks.locktype AND blocker_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE AND blocker_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocker_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocker_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocker_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocker_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocker_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocker_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocker_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocker_locks.pid != blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocker ON blocker.pid = blocker_locks.pid WHERE NOT blocked_locks.granted;"
 blocked_pid |              blocked_query              | blocker_pid |              blocker_query
------------+-----------------------------------------+------------+-----------------------------------------
      18502 | UPDATE inventory SET qty=qty-1 ...      |      18111 | BEGIN; SELECT * FROM inventory WHERE ...
(1 row)

Sens : Vous avez un PID bloqueur précis. Souvent c’est une transaction laissée ouverte par un job, une migration, ou une session de debug « temporaire ».

Décision : Si le bloqueur est clairement accidentel et n’exécute pas de travail critique, terminez-le. Si c’est une migration, coordonnez et choisissez l’option la moins dommageable.

Tâche 12 (PostgreSQL) : Vérifier la pression vacuum/autovacuum et les tuples morts

cr0x@server:~$ psql -c "SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_vacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;"
   relname   | n_live_tup | n_dead_tup |    last_autovacuum     |      last_vacuum
-------------+------------+------------+------------------------+------------------------
 orders      |    5821934 |    2149921 | 2025-12-29 09:41:12+00 | 2025-12-27 02:10:04+00
 inventory   |     402113 |     210993 | 2025-12-29 10:02:01+00 |
(2 rows)

Sens : Un nombre élevé de tuples morts suggère du bloat et/ou que le vacuum ne suit pas, ce qui peut augmenter l’I/O et dégrader l’efficacité des index.

Décision : Si les tuples morts sont nombreux et que l’autovacuum est en retard, ajustez autovacuum pour les tables chaudes (paramètres par table) et recherchez les transactions longues empêchant le nettoyage.

Tâche 13 (PostgreSQL) : Vérifier le lag de réplication via LSN

cr0x@server:~$ psql -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.15    | streaming | 00:00:01  | 00:00:03  | 00:00:07
(1 row)

Sens : Le lag est faible. Si replay_lag est élevé, votre replica peut être lent à appliquer le WAL (CPU ou I/O limité, ou conflits hot standby).

Décision : Si le lag est élevé et que vous utilisez des replicas pour les lectures, envisagez de router les lectures, ou réduisez le volume d’écritures. Investiguer les pics de génération WAL et les ressources du replica.

Tâche 14 (MySQL) : Vérifier la santé du buffer pool et la pression de lecture

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| 982134221 |
+-------------------------+-----------+
+---------------------------------+------------+
| Variable_name                   | Value      |
+---------------------------------+------------+
| Innodb_buffer_pool_read_requests| 3812234910 |
+---------------------------------+------------+

Sens : Si buffer_pool_reads augmente rapidement par rapport à read_requests, vous manquez de cache et touchez le disque. Les nombres absolus importent moins que le taux pendant l’incident.

Décision : Si les misses du cache montent en flèche, trouvez la requête qui a changé le pattern d’accès, ou envisagez d’augmenter le buffer pool (long terme). À court terme, réduire la charge ou ajouter l’index qui évite les scans larges.

Tâche 15 (PostgreSQL) : Vérifier le ratio de cache hit et les fichiers temporaires

cr0x@server:~$ psql -c "SELECT datname, blks_hit, blks_read, temp_files, temp_bytes FROM pg_stat_database WHERE datname = current_database();"
 datname | blks_hit  | blks_read | temp_files | temp_bytes
---------+-----------+-----------+------------+------------
 shop    | 991233221 | 11233211  |      12933 |  987654321
(1 row)

Sens : temp_files/temp_bytes qui augmentent signifient que des requêtes déversent sur disque (sorts, hashes). C’est souvent un problème de mémoire par requête (work_mem) ou un mauvais plan produisant de gros résultats intermédiaires.

Décision : Identifier les requêtes qui déversent (pg_stat_statements aide) et les corriger. Évitez d’augmenter work_mem en masse sur des systèmes chargés — cela se multiplie par connexion.

Tâche 16 : Capturer rapidement un profil des threads DB (perspective Linux)

cr0x@server:~$ top -b -n 1 | sed -n '1,25p'
top - 10:23:02 up 187 days,  3:14,  2 users,  load average: 27.92, 26.80, 20.01
Tasks: 612 total,   3 running, 609 sleeping,   0 stopped,   0 zombie
%Cpu(s): 14.1 us,  5.9 sy,  0.0 ni, 41.2 id, 38.1 wa,  0.0 hi,  0.7 si,  0.0 st
MiB Mem :  63488.0 total,   1320.2 free,  56502.4 used,   5665.4 buff/cache
MiB Swap:   8192.0 total,   6553.7 used,   1638.3 free.  2401.9 avail Mem

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
 2143 mysql      20   0 24.1g   36.2g   154m S  182.4  58.3  884:12.11 mysqld
 1987 postgres   20   0 15.2g    9.1g    88m S   42.1  14.7  311:09.22 postgres

Sens : Un iowait élevé indique que le stockage limite. Un mysqld avec beaucoup de CPU suggère une tempête de requêtes CPU-bound. Une grosse mémoire RES avec swap indique surcommit.

Décision : Si iowait domine, arrêter les écritures. Si CPU domine, isoler les requêtes principales et les tuer ou atténuer. Si le swap domine, réduire la concurrence et l’empreinte mémoire.

Spécificités MySQL : InnoDB, verrous de métadonnées, réplication et pièges habituels

À quoi ressemble « soudainement lent » en MySQL

Les incidents MySQL se présentent souvent comme l’un de ces patterns :

• Amoncellement de verrous de métadonnées : un DDL ou LOCK TABLES bloque une table chaude ; tout se met en file d’attente derrière.
• Contention sur une seule ligne/clé chaude : compteurs, lignes d’inventaire, mises à jour « last seen » — classique « une ligne, beaucoup d’écrivains ».
• Misses du buffer pool : le dataset a grandi ou le pattern de requête a changé, transformant des hits mémoire en lectures disque.
• Pression de flush/checkpoint InnoDB : pages dirty atteignent un seuil, des vagues de flush apparaissent, les écritures stallent.
• Lag de réplication : les replicas sont en retard ; les lectures sont obsolètes ; la sécurité de failover diminue.

Deux priorités de diagnostic MySQL que l’on oublie (et qu’on regrette)

1) Verrous de métadonnées. Si vous ne vérifiez que les verrous de ligne, vous passerez à côté de « l’ALTER TABLE qui régit tout ». L’état du processlist l’expose clairement. Quand vous le voyez, n’argumentez pas avec ça.

2) Tempêtes de connexions. MySQL n’apprécie pas qu’on crée des milliers de connexions avec une grosse mémoire par thread. « On va juste augmenter max_connections » est la voie pour transformer un incident lent en incident OOM.

Mouvements d’atténuation MySQL généralement sûrs

• Tuer les SELECTs hors de contrôle qui bouffent CPU et I/O, surtout les requêtes ad hoc.
• Limiter le débit du coupable au niveau application (feature flag, shaping de requêtes) plutôt que de matraquer la BD.
• Arrêter les changements de schéma en pleine journée sauf si vous utilisez la bonne approche en ligne et que vous l’avez répétée.
• Déplacer les lectures lourdes (si les replicas sont saines et assez cohérentes pour le cas d’usage).

Quand « ce sont juste des lectures lentes » est en réalité un problème d’écritures

Sur InnoDB, des écritures lourdes peuvent ralentir les lectures via la saturation d’E/S et le churn du buffer pool. Vous le verrez comme « les lectures sont lentes », mais iostat donne la vérité. Traitez le disque comme la ressource partagée qu’il est.

Spécificités PostgreSQL : vacuum, bloat, WAL et pièges habituels

À quoi ressemble « soudainement lent » en PostgreSQL

La lenteur Postgres tend à se regrouper autour de :

• Attentes de verrous dues à des transactions longues : quelqu’un a gardé une transaction ouverte pendant… je ne sais quoi, et maintenant vacuum et DDL ne peuvent pas progresser.
• Dette d’autovacuum et bloat : les requêtes ralentissent sur des jours/semaines, puis basculent en « soudain » quand le cache et l’I/O ne cachent plus.
• Pression Checkpoint/WAL : des rafales d’écriture provoquent des pics WAL ; les checkpoints deviennent coûteux ; la latence I/O augmente.
• Déversements de fichiers temporaires : le tri/hash d’une requête déverse sur disque et traîne le système dans des waits I/O.
• Changements de plan : stats, valeurs de paramètres ou distribution des données qui mènent à des scans séquentiels alors qu’on attendait des index scans.

Deux priorités de diagnostic Postgres que l’on oublie (et qu’on regrette)

1) Transactions longues. Une seule session idle in transaction peut empêcher vacuum de récupérer les tuples morts et tenir des verrous. C’est l’équivalent opérationnel de garer une voiture en travers dans une ruelle étroite.

2) Activité de fichiers temporaires. Si temp_bytes monte vite, vous déversez sur disque et vous le ressentirez. Corriger la requête d’abord ; « augmenter work_mem » est parfois correct mais souvent imprudent en forte concurrence.

Mouvements d’atténuation Postgres généralement sûrs

• Terminer le bloqueur accidentel (idle in transaction, session ad hoc) après vérification qu’il n’exécute pas un batch critique.
• Annuler les requêtes hors contrôle plutôt que de tuer tout le processus de la base.
• Réduire la concurrence au niveau du pool pour arrêter le troupeau. Postgres préfère moins de connexions bien comportées.
• Réparer le vacuum par table ciblée plutôt que de monter globalement des réglages qui peuvent brutaliser les disques.

Blague #2 : L’autovacuum, c’est comme sortir la poubelle : l’ignorer ne fait pas gagner du temps, ça change juste l’odeur de votre incident.

Trois mini-histoires du monde de l’entreprise (anonymisées, plausibles, techniquement exactes)

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

Ils avaient un processus de migration propre : déploiements blue/green, vérifications préalables, et un mantra « les migrations DB sont sûres » qui s’est répété jusqu’à devenir physique. Une équipe a poussé une release avec un changement de schéma ajoutant une colonne nullable. « Ça va », ont-ils dit. « C’est en ligne. »

Sur MySQL, ce n’était pas si simple. L’ALTER a pris un verrou de métadonnées, et la table était chaude. Le verrou n’a pas tout bloqué instantanément ; il a bloqué au pire moment possible : d’autres sessions se sont mises en file, elles ont tenu les connexions plus longtemps, le pool s’est saturé, et l’app a commencé à expirer. L’SRE de garde a vu « DB lente » sur le dashboard, puis a constaté que le CPU n’était pas saturé. Ils ont failli partir sur une chasse aux optimisations de requête.

Le tournant a été d’exécuter SHOW FULL PROCESSLIST et de voir « Waiting for table metadata lock » répété comme une scène de film d’horreur. L’« hypothèse erronée » n’était pas que le DDL en ligne existe. C’était de supposer que « en ligne » signifie « sans impact opérationnel ». Le DDL en ligne conteste encore les ressources et peut bloquer sous charge, selon l’opération et la version.

La correction a été ennuyeuse : tuer la session de migration, rollback du déploiement, et replanifier le changement avec une procédure répétée, des fenêtres de maintenance explicites et des vérifications de charge. Après l’incident, ils ont ajouté un garde-fou : les changements de schéma devaient être prouvés sur une charge proche de la production, et les migrations étaient limitées par feature flags. Pas glamour. Ça a marché.

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

Un cluster Postgres avait un problème classique : trop de connexions. La base gérait ça un temps, jusqu’à ce qu’elle ne le fasse plus. Quelqu’un a proposé l’évidence : « Augmentons max_connections. Le hardware est bon marché. » Ça a été approuvé parce que ça sonnait comme du capacity planning.

Ils l’ont augmenté. Et au prochain pic, la latence était pire. Pas un peu. « Les clients ne peuvent plus se connecter » pire. La machine a commencé à swapper. Le monitoring était absurde : plein de sessions effectuant de petites tâches lentement, comme si la base avait décidé d’être passivement agressive.

Postgres alloue de la mémoire par connexion et encaisse un overhead par backend. Augmenter max_connections sans changer le comportement du pool a transformé la BD en festival de context-switch. Pire : plus de requêtes concurrentes ont augmenté les déversements temporaires parce que la mémoire s’est fragmentée entre plus de backends, et l’I/O a augmenté.

La résolution n’a pas été un réglage GUC futé. Ce fut la mise en place d’un pool de connexions correct, la diminution du nombre de sessions DB actives, et l’alignement de work_mem avec la concurrence réaliste. La leçon : augmenter une limite n’est pas une optimisation ; c’est donner la permission à votre système d’échouer de façons nouvelles et plus coûteuses.

Mini-histoire 3 : La pratique ennuyeuse mais correcte qui a sauvé la journée

Une entreprise exploitait MySQL et PostgreSQL sur des produits différents. La contrainte unificatrice : la couche de stockage était partagée, et « incidents de stockage » étaient des tueurs silencieux. Une équipe demandait depuis des mois d’ajouter une pratique simple : capturer périodiquement des snapshots des métriques clés DB et OS pendant le fonctionnement normal et les stocker avec les marqueurs de déploiement. Tout le monde acquiesçait poliment. Personne ne voulait payer pour du « ennuyeux ».

Finalement ils l’ont fait, parce qu’un SRE en avait marre de déboguer à l’aveugle. Toutes les cinq minutes : résumés iostat, lag de réplication, stats buffer/cache, top waits, empreintes des requêtes principales. Pas d’URLs brutes, pas d’outillage fancy — juste des snapshots cohérents comparables dans le temps. Ils enregistraient aussi quand les jobs de sauvegarde tournaient et quand la compaction avait lieu sur l’array de stockage.

Puis le jour de la « lenteur soudaine » arriva. La latence a grimpé sur plusieurs services. La première question : « Quelle base est cassée ? » Les snapshots ont répondu plus vite que les opinions. L’attente disque (await) a bondi à la même minute sur tous les hôtes DB. Le lag de réplication est monté après, pas avant. Le CPU est resté surtout normal. Le système n’était pas « SQL lent ». Il était « stockage lent ».

Parce qu’ils avaient des baselines, ils ont escaladé vers l’équipe stockage avec des preuves : await device, profondeur de queue, et corrélation entre hôtes. L’équipe stockage a trouvé une tâche de maintenance malveillante sur l’array et l’a arrêtée. L’incident s’est terminé sans tuer de requêtes au hasard et sans blâme croisé. Pratique ennuyeuse. Pratique correcte. Elle s’est remboursée en un après-midi.

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

Checklist A : Les premières cinq minutes (utilisez-la à chaque fois)

1. Depuis l’hôte DB : exécutez uptime, free -h, iostat -xz 1 3.
2. Décidez : CPU-bound vs I/O-bound vs mémoire/swap-bound. Si vous ne pouvez pas classifier, vous ne regardez pas le bon niveau.
3. Vérifiez les connexions : nombre de connexions DB et attente du pool applicatif.
4. Vérifiez les waits : processlist + InnoDB status pour MySQL ; pg_stat_activity wait_event pour Postgres.
5. Capturez des preuves : collez les sorties dans le canal incident. Votre futur vous remerciera.

Checklist B : Si c’est lié à l’I/O

1. Confirmer la saturation disque : await iostat, aqu-sz, %util.
2. Identifier les écrivains : pidstat -d ; vérifier sauvegarde/rotation logs/rsync.
3. Suspects côté BD :
   • MySQL : pression flushing/checkpoint, transactions volumineuses, pics de binlog.
   • Postgres : checkpoints, pics WAL, activité vacuum, déversements temporaires.
4. Atténuer : arrêter les writers batch, réduire la concurrence, retarder les jobs non critiques.
5. Après incident : évaluer QoS stockage, volumes séparés, et mesurer la latence disque de base.

Checklist C : Si c’est CPU-bound

1. Trouver les requêtes principales par temps :
   • MySQL : performance schema / échantillonnage du processlist.
   • Postgres : pg_stat_statements si activé ; sinon pg_stat_activity + logs.
2. Valider le plan : scanne-t-il ? trie-t-il ? joint-il trop ?
3. Atténuer : tuer les requêtes runaway, ajouter l’index manquant si sûr, réduire la concurrence, appliquer des feature flags.
4. Après : ajouter un test de régression pour le plan de la requête et suivre les empreintes de requêtes après les déploiements.

Checklist D : Si c’est lié aux verrous / contention

1. Trouver les bloqueurs : InnoDB status / performance_schema pour MySQL ; jointures pg_locks pour Postgres.
2. Classifier le bloqueur : DDL, transaction longue, update sur hotspot, ou job batch.
3. Atténuer : terminer le bloqueur si sûr, ou mettre en pause la fonctionnalité générant la contention.
4. Après : repenser les hotspots (sharder les compteurs, utiliser des queues, éviter les updates single-row en forte concurrence).

Erreurs courantes : symptôme → cause racine → correction

1) « La BD est lente » mais les requêtes triviales sont rapides

Symptôme : Les requêtes applicatives expirent ; la BD « a l’air correcte ».

Cause racine : Saturation du pool de connexions ou exhaustion de threads dans l’application ; les requêtes sont en file avant d’atteindre la BD.

Correction : Vérifier le temps d’attente du pool et max connections ; réduire la concurrence applicative ; corriger les fuites (ne pas rendre les connexions) ; ajouter du backpressure.

2) Beaucoup de sessions MySQL bloquées sur « Waiting for table metadata lock »

Symptôme : Soudain tout ce qui touche une table se bloque ; le CPU peut sembler normal.

Cause racine : DDL ou une session tenant un verrou de métadonnées bloque d’autres instructions.

Correction : Identifier la session DDL et l’arrêter/tuer ; replanifier les changements de schéma ; utiliser des techniques de schéma en ligne adaptées à votre version MySQL et à l’opération.

3) Les requêtes Postgres ralentissent avec le temps, puis « tombent » soudainement

Symptôme : Latence croissante, lectures disque accrues, tables/indexs plus gros.

Cause racine : L’autovacuum n’arrive pas à suivre ; le bloat augmente ; des tuples morts persistent à cause de transactions longues ou de réglages vacuum insuffisants.

Correction : Trouver et éliminer les transactions longues ; tuner autovacuum par table chaude ; planifier des vacuum manuels si nécessaire ; s’assurer que maintenance_work_mem et la capacité I/O sont adéquats.

4) Les replicas prennent du retard et l’app obtient des « lectures lentes »

Symptôme : Les endpoints de lecture sont lents ou incohérents ; le failover semble risqué.

Cause racine : L’apply de réplication ne suit pas à cause de pics d’écriture, de goulots I/O ou de limites single-thread (selon moteur/réglage).

Correction : Réduire le volume d’écritures, optimiser les transactions, s’assurer que les replicas ont un I/O au moins égal, et vérifier les réglages de réplication. Router les lectures critiques vers le primaire temporairement.

5) « On a ajouté un index et c’est pire »

Symptôme : Les écritures ralentissent, le lag de réplication augmente, le disque se remplit plus vite.

Cause racine : L’index a augmenté l’amplification d’écritures ; l’overhead de maintenance a monté ; les requêtes ne l’utilisent pas, ou c’est le mauvais index.

Correction : Valider l’utilisation avec les plans et les stats ; supprimer les index inefficaces ; privilégier des index étroits et sélectifs ; envisager des index partiels (Postgres) ou des index composites conçus pour les prédicats exacts.

6) Déversements temporaires ou temp-sorts en masse dans Postgres

Symptôme : temp_bytes monte ; latence disque augmente ; les tris deviennent lents.

Cause racine : Mauvais plan produisant d’énormes ensembles intermédiaires ; work_mem insuffisant pour la requête ; trop de tris concurrents.

Correction : Corriger la requête (ajouter un index, réécrire join/order), utiliser LIMIT plus tôt, et tuner work_mem prudemment en tenant compte de la concurrence.

7) Le swap commence et la BD devient imprévisible

Symptôme : La variance de latence explose ; le CPU idle peut sembler élevé ; iowait monte.

Cause racine : Trop de connexions, mémoire par session trop grande, ou pression sur le cache OS ; parfois colocalisation/voisin bruyant.

Correction : Réduire la concurrence ; imposer le pooling de connexions ; dimensionner correctement les réglages mémoire ; investiguer la contention au niveau hôte.

FAQ

1) Lequel est plus facile à diagnostiquer sous pression : MySQL ou PostgreSQL ?

Postgres est généralement plus explicite sur les waits et les bloqueurs via les vues système. MySQL peut être tout aussi diagnostiqueable si Performance Schema est activé et que vous savez où regarder, mais beaucoup de shops le laissent sous-utilisé.

2) Quel est le signal le plus rapide pour « le stockage est le problème » ?

iostat -xz montrant un await élevé et un %util élevé, plus un iowait CPU élevé. Si c’est vrai, débattre des index est prématuré.

3) Tuer des requêtes est-ce sûr ?

C’est plus sûr que de laisser tout expirer. Mais faites-le délibérément : identifier la session, confirmer que ce n’est pas une migration/nettoyage critique, et préférer annuler une instruction (Postgres) plutôt que tuer tout le processus sauf si nécessaire.

4) Pourquoi les sessions « idle in transaction » sont-elles si importantes en Postgres ?

Elles peuvent empêcher vacuum de supprimer les tuples morts et tenir des verrous plus longtemps que vous ne le pensez. Elles gonflent aussi l’horizon des transactions, ce qui transforme une maintenance routinière en incident plus tard.

5) Pourquoi augmenter max_connections ralentit souvent Postgres ?

Parce que cela augmente l’overhead par backend et encourage une concurrence non bornée. Plus de sessions actives signifie plus de contention, plus de churn du cache, et plus de déversements temporaires. Le pooling est généralement la bonne solution.

6) Pourquoi MySQL « se fige » parfois pendant des changements de schéma ?

Les verrous de métadonnées et le comportement DDL. Même quand une opération est « en ligne », elle peut encore bloquer à des phases ou sous certaines charges. L’effet de mise en file peut ressembler à un gel de toute la BD.

7) Comment savoir si c’est une régression de plan ?

Symptômes : pics CPU avec une hausse des lectures logiques, ou une requête qui était rapide devient lente après un déploiement ou un changement de stats. Confirmez en regardant le plan (EXPLAIN) et si la requête a cessé d’utiliser un index.

8) Dois-je tuner buffers/caches pendant l’incident ?

Rarement. La plupart des changements de tuning nécessitent un redémarrage (parfois), ont des effets retardés, et sont faciles à rater. Pendant l’incident : atténuez la charge, stoppez les bloqueurs, tuez les runaway, et capturez des preuves. Tuner après, avec une hypothèse claire.

9) Et si les deux bases sont lentes en même temps ?

Supposez d’abord une infrastructure partagée : stockage, réseau, DNS, hôte de virtualisation, ou un job de sauvegarde partagé. Une lenteur corrélée entre moteurs n’est généralement pas une coïncidence de plans de requêtes.

10) Si je ne peux activer qu’une seule fonctionnalité d’introspection, laquelle ?

Pour Postgres : pg_stat_statements (avec une rétention raisonnable). Pour MySQL : Performance Schema avec les instruments statement et wait activés à un niveau acceptable. Le meilleur incident est celui où vous n’avez pas à deviner.

Prochaines étapes à faire cette semaine

Si vous exploitez des bases de données en production, « ça devient soudainement lent » n’est pas une question de si. C’est une question de quand. Voici comment raccourcir et rendre moins dramatique le prochain incident :

1. Écrivez votre séquence de 15 minutes (empruntez le playbook ci-dessus) et rendez-la visible pour la rotation on-call.
2. Activez le fingerprinting de requêtes et les waits dans le moteur que vous utilisez, et vérifiez que vous pouvez répondre : « quelles sont les 5 principales requêtes par temps total en ce moment ? »
3. Mesurez la latence disque de base pendant les heures normales. Sans baseline, tout graphe devient de l’art moderne.
4. Faites respecter la discipline des connexions : pooler les connexions, plafonner la concurrence, et faire de l’attente du pool une alerte de première classe.
5. Rendez les changements de schéma ennuyeux : répétez-les, planifiez-les, et traitez le DDL comme une charge de production avec rayon d’impact.

Quand la prochaine alerte sonnera, vous n’aurez pas besoin d’un débat sur les moteurs. Vous aurez besoin d’une classification rapide — CPU, mémoire, E/S, verrous ou plan — et d’une action sûre à la fois.