MySQL : tuer les processus bloqués et monitorer les connexions

Credit : Logo officiel

MySQL : tuer les processus bloqués et monitorer les connexions

Dylan D. — Agent Support Technique Serveur Depannage 2457 mots 13 min de lecture

MySQL : tuer les processus bloqués et monitorer les connexions

3h12 du matin, mon téléphone vibre. Alerte Netdata : load average à 38 sur un VPS à 4 vCPU, mysqld qui mange 380% de CPU, site WooCommerce d'un client en pleine campagne de soldes complètement gelé. Je me connecte en SSH, top confirme, et je sais déjà ce que je vais trouver dans SHOW FULL PROCESSLIST : une centaine de connexions empilées, des Waiting for table metadata lock partout, et une requête analytics non indexée qui tourne depuis 22 minutes. Sur les bases que je gère au quotidien, c'est le scénario le plus fréquent — bien plus fréquent qu'un vrai crash MySQL. Cet article rassemble la méthode que j'applique à chaque incident de ce type, des commandes que je tape par réflexe au monitoring qui m'évite d'être réveillé la prochaine fois.

Le réflexe avant tout : ne pas redémarrer

La première tentation quand MySQL part en vrille, c'est systemctl restart mysql. Mauvaise idée dans 90% des cas. Un redémarrage va flusher le buffer pool InnoDB (qui peut faire plusieurs Go), invalider le cache des plans d'exécution, et au redémarrage tu vas te retrouver avec un crash recovery qui peut durer 30 secondes à plusieurs minutes selon le volume de transactions non commitées. Pendant ce temps, le site reste down et les clients qui étaient en train de payer perdent leur panier.

À la place, je commence toujours par identifier qui bloque quoi. Une connexion MySQL en root via socket Unix, et go :

mysql -u root -p

Si le serveur refuse les nouvelles connexions parce que max_connections est saturé, MySQL réserve traditionnellement une connexion supplémentaire pour un user avec le privilège SUPER (ou CONNECTION_ADMIN sur MySQL 8). C'est exactement pour ce genre de situation qu'on garde un compte admin à part. Si même ça ne passe pas, on peut se rabattre sur mysqladmin :

mysqladmin -u root -p processlist
mysqladmin -u root -p status

mysqladmin status me donne en une ligne : Uptime, Threads, Questions, Slow queries, Opens, Flush tables, Open tables, Queries per second. C'est mon premier coup d'œil quand je débarque sur un incident.

Lire SHOW PROCESSLIST comme un médecin lit un ECG

SHOW FULL PROCESSLIST;

Le FULL est important : sans lui, la colonne Info est tronquée à 100 caractères et tu ne vois pas la requête complète. Sortie typique sur un site WordPress qui rame :

+------+--------+-----------+----------+---------+------+-------------------------+----------------------------------------------+
| Id   | User   | Host      | db       | Command | Time | State                   | Info                                         |
+------+--------+-----------+----------+---------+------+-------------------------+----------------------------------------------+
| 4231 | wp_usr | localhost | wp_prod  | Query   | 1247 | Sending data            | SELECT * FROM wp_postmeta WHERE meta_key ... |
| 4258 | wp_usr | localhost | wp_prod  | Query   |   89 | Waiting for table lock  | UPDATE wp_options SET option_value = ...     |
| 4259 | wp_usr | localhost | wp_prod  | Query   |   89 | Waiting for table lock  | UPDATE wp_options SET option_value = ...     |
| 4260 | wp_usr | localhost | wp_prod  | Query   |   88 | Waiting for table lock  | UPDATE wp_options SET option_value = ...     |
| 4267 | wp_usr | localhost | wp_prod  | Sleep   | 4521 |                         | NULL                                         |
| 4268 | wp_usr | localhost | wp_prod  | Sleep   | 3890 |                         | NULL                                         |
+------+--------+-----------+----------+---------+------+-------------------------+----------------------------------------------+

Ce que je lis là-dedans, dans l'ordre :

Les états (State) que je rencontre le plus souvent :

State Ce que ça veut dire
Sending data MySQL lit les lignes et applique le WHERE. Si Time est élevé, soit volume énorme, soit absence d'index.
Sorting result ORDER BY sans index utilisable. Filesort en cours.
Copying to tmp table GROUP BY ou DISTINCT qui crée une table temporaire. Souvent sur disque si grosse.
Waiting for table metadata lock Un ALTER TABLE ou un long SELECT bloque les DDL ou inversement.
Waiting for table lock MyISAM principalement, ou LOCK TABLES explicite.
Statistics MySQL choisit le plan d'exécution. Si ça traîne ici, stats à jour ?
Sleep Connexion ouverte mais inactive. Normal sauf si Time > wait_timeout.

Tuer un processus proprement

Une fois le coupable identifié, deux variantes :

KILL 4231;
KILL QUERY 4231;

KILL 4231 ferme la connexion entièrement : la requête s'arrête et le client se prend un MySQL server has gone away. C'est ce que je fais sur une connexion Sleep zombie ou quand l'appli derrière est de toute façon plantée.

KILL QUERY 4231 ne tue que la requête en cours, la connexion reste ouverte. Le client peut enchaîner avec une autre requête. C'est plus propre quand l'appli est saine et que je veux juste interrompre une analyse mal partie.

Attention : sur une transaction en cours qui a déjà modifié des lignes, KILL déclenche un rollback. Sur une grosse transaction (millions de lignes UPDATE), le rollback peut prendre plus longtemps que la requête initiale. C'est InnoDB qui réécrit les undo logs. J'ai vu un rollback durer 40 minutes sur une base mal taillée — pendant ce temps, impossible d'arrêter le serveur proprement.

Tuer en masse les connexions Sleep

Quand max_connections est saturé à cause de connexions zombies, on génère le batch de KILL via INFORMATION_SCHEMA :

SELECT CONCAT('KILL ', id, ';') AS kill_stmt
FROM information_schema.PROCESSLIST
WHERE COMMAND = 'Sleep'
  AND TIME > 300
  AND USER = 'wp_usr'
  AND DB = 'wp_prod'
INTO OUTFILE '/tmp/kill_sleep.sql';

Puis :

mysql -u root -p < /tmp/kill_sleep.sql

Variante plus rapide en une ligne shell, sans fichier intermédiaire :

mysql -u root -p -Nse "SELECT id FROM information_schema.PROCESSLIST WHERE COMMAND='Sleep' AND TIME>300" \
  | while read pid; do mysql -u root -p -e "KILL $pid"; done

Je filtre toujours par USER et DB pour ne pas tuer des connexions de monitoring ou de réplication. Sur un serveur mutualisé qui héberge plusieurs sites, oublier ce filtre peut couper un site qui tournait très bien.

Diagnostiquer les locks InnoDB

Quand le State est Waiting for table metadata lock ou que des deadlocks apparaissent, SHOW PROCESSLIST ne suffit plus. Il faut creuser dans les vues InnoDB.

SELECT trx_id, trx_state, trx_started, trx_wait_started,
       trx_mysql_thread_id, trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started;

Ça me liste toutes les transactions InnoDB actives, leur état (RUNNING, LOCK WAIT), depuis quand elles attendent et leur ID de thread (qui correspond à Id dans PROCESSLIST). Pour voir explicitement qui bloque qui sur MySQL 8 / MariaDB 10.5+ :

SELECT r.trx_id AS waiting_trx,
       r.trx_mysql_thread_id AS waiting_thread,
       r.trx_query AS waiting_query,
       b.trx_id AS blocking_trx,
       b.trx_mysql_thread_id AS blocking_thread,
       b.trx_query AS blocking_query
FROM performance_schema.data_lock_waits w
JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_engine_transaction_id
JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_engine_transaction_id;

Sur les versions plus anciennes (MySQL 5.7), c'était INNODB_LOCK_WAITS et INNODB_LOCKS dans information_schema. Ces vues ont été déprécées au profit de performance_schema.data_locks / data_lock_waits.

Pour le dernier deadlock détecté par InnoDB :

SHOW ENGINE INNODB STATUS\G

La section LATEST DETECTED DEADLOCK te donne les deux transactions en conflit, les locks qu'elles tenaient, et celle qu'InnoDB a choisi de rollback. C'est précieux pour identifier des patterns récurrents (toujours les deux mêmes tables, toujours dans le même ordre inversé).

Mettre en place le slow query log

Les processus bloqués ne sont qu'un symptôme. La cause, c'est presque toujours une ou deux requêtes mal écrites qui reviennent en boucle. Le slow query log les attrape.

Dans /etc/mysql/mariadb.conf.d/50-server.cnf (Debian/MariaDB) ou /etc/my.cnf.d/server.cnf (RHEL), section [mysqld] :

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1

Reload sans redémarrer :

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

Après quelques heures de prod, j'analyse avec pt-query-digest (Percona Toolkit) :

apt install percona-toolkit
pt-query-digest /var/log/mysql/slow.log > /tmp/digest.txt

pt-query-digest regroupe les requêtes par fingerprint (la même requête avec des valeurs différentes compte comme une seule), classe par temps cumulé et te sort un top 20 des coupables. Sur la quasi-totalité des sites WordPress que j'audite, le top 3 contient une requête wp_options (autoload mal utilisé), une requête wp_postmeta sans index sur meta_value, et un JOIN de plugin SEO mal écrit.

mysqltuner pour le tuning de base

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
perl mysqltuner.pl --user root --pass 'xxx'

Les recommandations à prendre au sérieux : innodb_buffer_pool_size trop petit (idéalement 60-70% de la RAM si la machine est dédiée à MySQL), tmp_table_size / max_heap_table_size trop bas (déclenche des temp tables sur disque), table_open_cache saturé. Les recommandations à ignorer : tout ce qui touche au query_cache sur MySQL 8 (le cache n'existe plus) et les suggestions de key_buffer_size énormes alors que tu n'utilises pas MyISAM.

Pour vérifier rapidement les variables clés :

SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'wait_timeout';
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Threads_running';
SHOW STATUS LIKE 'Max_used_connections';

Threads_running est plus parlant que Threads_connected : c'est le nombre de threads qui font effectivement quelque chose. Sur un serveur sain, même chargé, tu devrais rester sous 20-30. Si tu vois 80+, tu as un problème de concurrence ou un thundering herd.

Monitoring continu sans usine à gaz

Pour ne plus être réveillé à 3h du mat, j'installe un script de surveillance basique en cron toutes les minutes. Il alerte quand Threads_running dépasse un seuil ou quand une requête tourne depuis plus de N secondes.

#!/bin/bash
# /usr/local/bin/mysql-watchdog.sh
MAX_RUNNING=40
MAX_QUERY_TIME=120
MAIL="alerts@example.com"

RUNNING=$(mysql -Nse "SHOW STATUS LIKE 'Threads_running'" | awk '{print $2}')
LONG=$(mysql -Nse "SELECT COUNT(*) FROM information_schema.PROCESSLIST WHERE COMMAND='Query' AND TIME>$MAX_QUERY_TIME")

if [ "$RUNNING" -gt "$MAX_RUNNING" ] || [ "$LONG" -gt 0 ]; then
  {
    echo "Threads_running=$RUNNING long_queries=$LONG"
    echo "---"
    mysql -e "SHOW FULL PROCESSLIST" | head -50
  } | mail -s "[MySQL] Alerte $(hostname)" "$MAIL"
fi

Dans crontab :

* * * * * /usr/local/bin/mysql-watchdog.sh

Les credentials MySQL passent par ~/.my.cnf du user qui exécute le cron, en mode 0600. C'est volontairement minimaliste — pas besoin de Prometheus pour ça. Pour quelque chose de plus visuel, je branche Netdata (qui détecte MySQL automatiquement) ou un dashboard Grafana avec mysqld_exporter pour les bases plus critiques.

Une autre commande utile pour un snapshot complet à un instant T, à mettre dans un script de post-mortem :

mysqladmin -u root -p debug

Ça dump dans le error log MySQL un état détaillé : threads, locks, événements, status I/O. Très pratique pour analyser après coup ce qui s'est passé.

Erreurs courantes et leur fix

Lock wait timeout exceeded; try restarting transaction

Une transaction a attendu un lock pendant innodb_lock_wait_timeout secondes (50 par défaut). Le fix court terme : identifier la transaction longue qui tient le lock via INNODB_TRX et la KILL. Le fix long terme : raccourcir les transactions, ne jamais laisser une transaction ouverte pendant un appel HTTP externe ou un sleep applicatif. Si vraiment 50s ne suffit pas pour un batch légitime, augmenter innodb_lock_wait_timeout ponctuellement à la session.

Deadlock found when trying to get lock; try restarting transaction

Deux transactions se bloquent mutuellement, InnoDB en sacrifie une. Le fix : SHOW ENGINE INNODB STATUS pour identifier les tables et l'ordre d'acquisition des locks. La règle d'or, toujours acquérir les locks dans le même ordre dans toute l'application. Et côté code, prévoir un retry avec backoff sur cette erreur précise — c'est attendu, pas catastrophique.

Too many connections

max_connections atteint. Court terme : augmenter à chaud avec SET GLOBAL max_connections = 300; (effet immédiat, persiste jusqu'au redémarrage), puis tuer les Sleep. Long terme : pourquoi tant de connexions ? Souvent un PHP-FPM avec pm.max_children mal calibré, ou un pool d'application qui ne libère pas. Vérifier wait_timeout (par défaut 28800 = 8h, c'est énorme), je le passe à 600 sur les serveurs web.

MySQL server has gone away

La connexion a été coupée côté serveur. Causes fréquentes : wait_timeout dépassé sur une connexion persistante, requête dont la taille dépasse max_allowed_packet (par défaut 64M sur MySQL 8, parfois 16M sur d'anciennes installs), ou OOM killer qui a buté mysqld. Vérifier dmesg | grep -i oom et le error log dans /var/log/mysql/error.log.

Waiting for table metadata lock qui ne se résout jamais

Un ALTER TABLE lancé pendant qu'un long SELECT tient un metadata lock partagé. L'ALTER attend, et toutes les requêtes suivantes (même de simples SELECT) attendent derrière l'ALTER. Symptôme classique : tout le site se gèle d'un coup alors qu'une seule requête tournait depuis longtemps. Fix : KILL la requête longue pour libérer le metadata lock, puis relancer l'ALTER pendant une fenêtre de maintenance avec pt-online-schema-change qui fait l'ALTER sans lock long.

Pour aller plus loin

Ce qui change quand tu connais ces commandes

La différence entre un sysadmin qui panique et un sysadmin qui résout un incident MySQL en 5 minutes, ce n'est pas l'expérience : c'est d'avoir ces quatre ou cinq commandes en mémoire musculaire. SHOW FULL PROCESSLIST, INNODB_TRX, KILL QUERY, SHOW ENGINE INNODB STATUS. Le reste — pt-query-digest, mysqltuner, monitoring — c'est du préventif qui te permet d'agir avant que l'alerte ne tombe. Le vendredi soir e-commerce dont je parlais en intro, on a fini par identifier un plugin de stats qui lançait un GROUP BY sur 4 millions de lignes à chaque chargement de page admin. Une fois la requête ajoutée à pt-query-digest, fix en 10 minutes. Sans le slow log, on aurait pu chercher pendant des heures.

# Articles similaires

Sur les memes sujets et plus loin