Comment régler un serveur MySQL : buffer pool, slow query log et connexions ?

Question

IT Interview Practice · Accepted Answer

Le réglage d'un serveur suit une boucle : **mesurer, trouver le goulot d'étranglement, corriger une seule chose, remesurer.** Les trois domaines à plus fort levier sont le buffer pool d'InnoDB, le slow query log et la gestion des connexions.

## Buffer pool d'InnoDB — le réglage n°1

Le buffer pool met en cache les pages de données et d'index en RAM ; quand votre working set y tient, les lectures sont à la vitesse de la mémoire et l'I/O disque disparaît presque. Dimensionnez-le à ~50–75 % de la RAM sur un serveur de base de données dédié.

```sql
SET GLOBAL innodb_buffer_pool_size = 12 * 1024 * 1024 * 1024;  -- 12 Go

-- Contrôle de santé : le taux de hit devrait être ~99 %+
SHOW ENGINE INNODB STATUS\G
--   Buffer pool hit rate 999 / 1000     <- bien ; proche de 1000/1000 = surtout en RAM
```

## Slow query log — trouver quoi corriger

```sql
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;   -- journalise tout ce qui dépasse 500 ms
-- Agrégez ensuite avec pt-query-digest / mysqldumpslow pour classer les pires requêtes,
-- et faites un EXPLAIN sur chacune. La plupart des gains viennent d'un index manquant, pas d'un réglage serveur.
```

## Connexions

Chaque connexion coûte de la mémoire et des threads. Ne réglez pas `max_connections` à des valeurs astronomiques pour masquer une fuite — une déferlante de milliers de connexions met le serveur à genoux. Utilisez un **connection pool** dans l'application (ou un proxy comme ProxySQL) pour garder un petit ensemble réutilisé, et surveillez `Threads_connected` par rapport à `Threads_running`.

```sql
SHOW GLOBAL STATUS LIKE 'Threads_running';   -- combien travaillent réellement
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
```

## Ordre d'attaque

Corrigez d'abord les requêtes les plus lentes (index) — cela l'emporte généralement sur tout changement de configuration. Dimensionnez ensuite correctement le buffer pool. Ce n'est qu'après que vous réglez `max_connections`, `innodb_log_file_size` et les paramètres de flush. Changez une variable à la fois et remesurez.

## Pourquoi c'est important

La plupart des incidents "MySQL est lent" se résument à une poignée de requêtes non indexées ou à un buffer pool trop petit pour contenir le working set — pas à un réglage exotique. Un ingénieur senior se tourne d'abord vers le slow log et le taux de hit du buffer pool, corrigeant le vrai goulot d'étranglement au lieu de tripoter aveuglément les boutons de configuration.

Buffer pool d'InnoDB — le réglage n°1

Slow query log — trouver quoi corriger

Connexions

Ordre d'attaque

Pourquoi c'est important

Questions connexes