Performance tuning MySQL

Cette page est une documentation originale PmaControl. Elle reprend une structure d'index pratique pour travailler sur l'optimisation MySQL, sans copier de contenu tiers ni dépendre d'un produit externe.

Objectif

Le tuning MySQL consiste à réduire la latence, stabiliser le débit et éviter les saturations répétées. Le travail doit partir de mesures réelles plutôt que de valeurs génériques copiées d'un serveur à l'autre.

Ordre de travail recommandé

1. Capturer la charge réelle: QPS, latence, erreurs, connexions, I/O disque, mémoire et CPU.
2. Identifier les requêtes dominantes avec performance_schema, le slow log ou un outil de digest.
3. Vérifier le schéma: index manquants, index redondants, types trop larges, tables sans clef primaire.
4. Ajuster InnoDB: buffer pool, redo log, flush, threads I/O et stratégie de durabilité.
5. Revoir les limites serveur: connexions, fichiers ouverts, caches de tables, tailles des tables temporaires.
6. Tester sur une fenêtre maîtrisée, avec un rollback documenté.

Commandes utiles

SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

A retenir

Un bon tuning MySQL est incrémental. Une variable modifiée sans mesure avant/après devient une source de dette opérationnelle.

Construire une référence comparable

Consignez la version exacte du serveur, ses ressources, la taille des données, la version applicative, le profil du trafic et la fenêtre observée. Distinguez le temps de réponse client du temps SQL : attente du pool de connexions, réseau et traitement applicatif peuvent dominer une requête pourtant rapide côté base. Comparez la même charge et la même concurrence avant et après.

SELECT VERSION() AS server_version, @@version_comment AS distribution;
SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Uptime', 'Questions', 'Threads_connected', 'Threads_running',
  'Innodb_buffer_pool_read_requests', 'Innodb_buffer_pool_reads',
  'Innodb_log_waits', 'Created_tmp_tables', 'Created_tmp_disk_tables'
);

Prenez deux instantanés sur un intervalle représentatif. Questions est cumulatif : débit des instructions clientes = ΔQuestions / durée en secondes, et non compteur brut. Un redémarrage ou une remise à zéro invalide l’intervalle. Relevez les latences p50, p95 et p99 avec le travail terminé et les erreurs ; une moyenne plus basse peut masquer une dégradation des extrêmes.

Identifier les requêtes qui consomment le budget

SELECT SCHEMA_NAME, LEFT(DIGEST_TEXT, 140) AS digest,
       COUNT_STAR AS executions,
       ROUND(SUM_TIMER_WAIT / 1000000000000, 3) AS total_seconds,
       ROUND(AVG_TIMER_WAIT / 1000000000, 3) AS average_ms,
       SUM_ROWS_EXAMINED, SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

Cette requête Performance Schema MySQL classe le temps cumulé, pas le p95 d’une fenêtre récente. Les timers sont exprimés en picosecondes. Vérifiez l’instrumentation et les remises à zéro ; un résultat vide ne prouve pas l’absence de charge. Des requêtes courtes et fréquentes peuvent coûter davantage qu’une requête lente. Comparez lignes examinées et renvoyées, puis étudiez des paramètres représentatifs avec EXPLAIN FORMAT=JSON. EXPLAIN ANALYZE exécute l’instruction : utilisez un environnement contrôlé et une version compatible.

Adapter l’intervention au goulot

Valider un changement à la fois

Notez l’hypothèse, la valeur ou le schéma précédent, le gain attendu et le seuil de retour arrière. Réchauffez les caches de façon comparable, répétez les essais représentatifs et conservez les résultats bruts. Comparez ensemble latence, débit, erreurs, CPU, mémoire, I/O et retard de réplication. Refusez un gain qui dégrade les résultats, la durabilité ou une autre charge importante. Revérifiez au pic et sur un cycle métier complet.

Poursuivre le diagnostic

• Les contrôles de santé établissent disponibilité et capacité.
• Les contrôles du schéma et les paramètres de performance détaillent les interventions ciblées.

Références officielles

• MySQL Performance Schema
• MySQL EXPLAIN