Comment diagnostiquer un goulot d’étranglement (bottleneck) sur une base de données MySQL / PostgreSQL

Un goulot d’étranglement sur une base de données ralentit l’ensemble de votre pile applicative. Pour diagnostiquer efficacement l’origine de la lenteur (disque, CPU, verrouillage), suivez cette méthodologie d’analyse.

Étape 1 : Analyser l’utilisation des ressources système

Avant de plonger dans le moteur SQL, utilisez des outils système pour identifier la ressource saturée :

• htop ou top : Vérifiez si le processus mysqld ou postgres s’accapare l’intégralité du CPU.

• iotop ou iostat -xz 1 : Surveillez la valeur %util (utilisation du disque) et le temps d’attente d’écriture (await). Un disque saturé est le bottleneck le plus fréquent en base de données.

Étape 2 : Traquer les requêtes lentes (Slow Queries)

Identifiez les requêtes SQL mal indexées qui lisent des millions de lignes inutilement.

• Pour MySQL : Activez le Slow Query Log dans votre fichier /etc/mysql/my.cnf :

  Ini, TOML

  slow_query_log = 1
  slow_query_log_file = /var/log/mysql/mysql-slow.log
  long_query_time = 1.0 # Logge les requêtes de plus d'une seconde
  log_queries_not_using_indexes = 1
  

  Analysez ensuite ce fichier avec mysqldumpslow ou l’outil pt-query-digest (Percona Toolkit).

• Pour PostgreSQL : Utilisez l’extension incontournable pg_stat_statements. Ajoutez ceci dans postgresql.conf :

  Ini, TOML

  shared_preload_libraries = 'pg_stat_statements'
  

  Puis exécutez cette requête pour lister les requêtes les plus gourmandes en temps cumulé :

  SQL

  SELECT query, calls, total_exec_time, rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;
  

Étape 3 : Diagnostiquer les verrous (Locks)

Deux requêtes concurrentes qui s’attendent mutuellement peuvent paralyser votre base.

• MySQL : Visualisez l’état des verrous avec SHOW ENGINE INNODB STATUS; ou examinez la table information_schema.innodb_trx.

• PostgreSQL : Détectez les blocages actifs grâce à la table système pg_locks jointe à pg_stat_activity pour repérer quelle transaction bloque les autres.