Tuning MySQL / MariaDB pour la production

Introduction

MySQL/MariaDB tournent par défaut avec une configuration minimaliste, prévue pour démarrer sur n'importe quelle machine. Sur un VPS avec 4+ Go de RAM, vous pouvez décupler les performances en quelques minutes.

Ce tuto couvre :

1. Le InnoDB buffer pool (le single most important tuning)
2. La détection des slow queries
3. L'ajout d'indexes sur les colonnes filtrées
4. La gestion des connexions simultanées

Prérequis

• VPS Linux avec MySQL 8 ou MariaDB 10.6+
• Accès root MySQL
• Au moins 2 Go de RAM disponibles
• Backup de votre base avant tout tuning

Étape 1 : Backup obligatoire

mysqldump -u root -p --all-databases --single-transaction > /root/all-databases-$(date +%F).sql

Étape 2 : Identifier votre situation

# Version
mysql --version

# Config actuelle
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

# RAM dispo
free -h

Étape 3 : Configurer le buffer pool InnoDB

C'est le paramètre le plus important. Le buffer pool cache les données et index InnoDB en RAM, évitant les lectures disque.

Règle : 70-80% de la RAM disponible sur un serveur dédié à MySQL. Sur un VPS partagé (Nginx + PHP + MySQL), comptez 40-50%.

sudo nano /etc/mysql/mariadb.conf.d/99-tuning.cnf

(Ou /etc/mysql/mysql.conf.d/99-tuning.cnf pour MySQL)

[mysqld]
# === InnoDB Buffer Pool ===
# Adapter à votre RAM : 4G pour 8 Go de RAM, 8G pour 16 Go, etc.
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4  # 1 instance par Go (max 8)

# === Logs InnoDB ===
innodb_log_file_size = 512M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2  # 1 pour bancaire, 2 pour web
innodb_flush_method = O_DIRECT

# === I/O ===
innodb_io_capacity = 2000           # SSD : 2000, NVMe : 5000+
innodb_io_capacity_max = 4000
innodb_read_io_threads = 8
innodb_write_io_threads = 8

# === Connexions ===
max_connections = 500
thread_cache_size = 50
table_open_cache = 4000

# === Query cache (uniquement MariaDB < 10.6, MySQL 5.x) ===
# query_cache_size = 0
# query_cache_type = 0

# === Buffers par connexion ===
sort_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
join_buffer_size = 4M
tmp_table_size = 64M
max_heap_table_size = 64M

# === Tables temporaires ===
tmpdir = /tmp

Redémarrez :

sudo systemctl restart mariadb  # ou mysql
sudo systemctl status mariadb

Étape 4 : Mesurer l'impact

Avant/après, exécutez :

SHOW ENGINE INNODB STATUS\G

Cherchez la section BUFFER POOL AND MEMORY :

Buffer pool size   262144  -- (en pages de 16K = 4 GB)
Buffer pool hit rate 999 / 1000  -- doit être proche de 1000/1000

Si le hit rate est < 990/1000, augmentez innodb_buffer_pool_size.

Étape 5 : Activer les slow queries

Pour identifier les requêtes lentes à optimiser :

# Dans 99-tuning.cnf
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1                  # En secondes : log toute requête > 1s
log_queries_not_using_indexes = 1    # Log aussi les requêtes sans index

Préparez le fichier :

sudo touch /var/log/mysql/mysql-slow.log
sudo chown mysql:mysql /var/log/mysql/mysql-slow.log
sudo systemctl restart mariadb

Après quelques heures de prod :

sudo less /var/log/mysql/mysql-slow.log

Analysez avec pt-query-digest (Percona Toolkit) :

sudo apt install -y percona-toolkit
sudo pt-query-digest /var/log/mysql/mysql-slow.log | head -50

Affiche le top des requêtes les plus consommatrices.

Étape 6 : Ajouter des indexes

L'optimisation #1 après le buffer pool. Une requête lente est presque toujours une requête sans index.

Identifier les requêtes mal indexées

EXPLAIN SELECT * FROM utilisateurs WHERE email = '[email protected]';

Lignes à surveiller dans le résultat :

• type = ALL : full table scan (mauvais)
• rows = 1000000 : scan énorme
• key = NULL : aucun index utilisé

Créer un index

CREATE INDEX idx_email ON utilisateurs(email);

Puis re-EXPLAIN :

type = ref
rows = 1
key = idx_email

Parfait.

Indexes composés

Pour WHERE statut = 'actif' AND date_creation > '2024-01-01' :

CREATE INDEX idx_statut_date ON utilisateurs(statut, date_creation);

L'ordre des colonnes compte : mettez d'abord la colonne avec le moins de valeurs distinctes.

Étape 7 : Outils d'audit automatique

MySQLTuner

sudo apt install -y mysqltuner
sudo mysqltuner

Donne un rapport priorisé sur ce qu'il manque/est mal réglé.

tuning-primer

wget https://launchpadlibrarian.net/78745738/tuning-primer.sh
chmod +x tuning-primer.sh
./tuning-primer.sh

Recommendations basées sur l'historique d'usage.

⚠️ Ne lancez pas ces outils sur un serveur fraîchement redémarré : ils n'ont pas assez de données pour donner des recos pertinentes. Attendez au moins 24h d'usage.

Étape 8 : Optimiser pour beaucoup de connexions courtes (PHP, API)

Si votre app fait beaucoup de connexions courtes (typique PHP avec PDO sans persistance) :

# Réutilisation des connexions
wait_timeout = 60
interactive_timeout = 60
max_connect_errors = 10000

# Pool de threads (MariaDB)
thread_handling = pool-of-threads
thread_pool_size = 16

Étape 9 : Optimiser pour de grosses requêtes (ETL, BI)

Si vous faites des grosses requêtes analytiques :

# Buffer pour les jointures lourdes
join_buffer_size = 32M
sort_buffer_size = 16M
read_rnd_buffer_size = 16M

# Augmenter le packet size pour gros INSERT/UPDATE
max_allowed_packet = 256M

Étape 10 : Activer les performance schemas

MySQL 5.7+ et MariaDB 10.2+ ont des schemas de stats détaillés :

performance_schema = ON
performance_schema_max_sql_text_length = 4096

Permet d'inspecter en temps réel :

SELECT * FROM performance_schema.events_statements_summary_by_digest 
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

Étape 11 : Backups InnoDB sans bloquer

Pour des backups hot (sans verrou) avec mariabackup :

sudo apt install -y mariadb-backup

# Backup
sudo mariabackup --backup --target-dir=/backup/$(date +%F) \
    --user=root --password='votre_password'

# Prepare (rejoue les logs InnoDB pour avoir un état cohérent)
sudo mariabackup --prepare --target-dir=/backup/2026-05-16

Beaucoup plus rapide qu'un mysqldump sur des bases > 10 Go.

Étape 12 : Monitoring avec Grafana

Pour visualiser les perfs MySQL en continu, voir le tuto Prometheus + Grafana et installez mysqld_exporter.

Dépannage

"InnoDB: Could not allocate memory"

Vous avez alloué trop de buffer pool. Réduisez innodb_buffer_pool_size.

"Too many connections"

Augmentez max_connections. Si vraiment beaucoup d'apps :

max_connections = 1000

⚠️ Chaque connexion consomme ~10-20 Mo. 1000 connexions = ~15 Go RAM en plus du buffer pool.

Requêtes restent lentes même avec buffer pool augmenté

Le buffer pool ne sert à rien si les requêtes scannent tout le disque (pas d'index). Allez voir slow query log.

"Out of sort memory"

Augmentez sort_buffer_size. Mais c'est aussi le signe d'une requête mal écrite (ORDER BY sur une colonne non indexée).

Commandes utiles

-- Statut général
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Threads_running';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_free';

-- Requêtes en cours
SHOW FULL PROCESSLIST;

-- Tuer une requête bloquée
KILL <id>;

-- Stats par table
SELECT table_name, table_rows, data_length, index_length 
FROM information_schema.TABLES 
WHERE table_schema = 'votre_db' 
ORDER BY data_length DESC;

-- Taille totale d'une DB
SELECT table_schema, 
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "MB"
FROM information_schema.TABLES 
GROUP BY table_schema;

-- Locks en cours
SELECT * FROM information_schema.INNODB_LOCKS;

-- Reset slow query log
mysql -e "SET GLOBAL slow_query_log = 0; SET GLOBAL slow_query_log = 1;"

Conclusion

Avec ces optimisations :

• Le buffer pool absorbe 90%+ des lectures (vs disque) → +500% perfs
• Les indexes sur les bonnes colonnes → requêtes 10-1000x plus rapides
• Le slow query log vous montre quoi optimiser ensuite

Pour aller plus loin :

• Mettez en place un read replica (réplication master/slave) pour distribuer les lectures
• Utilisez ProxySQL pour load balancer entre instances
• Migrez vers TiDB ou Vitess si vous dépassez les capacités d'un seul nœud

Ressources

• Documentation MySQL : https://dev.mysql.com/doc/refman/8.0/en/
• Documentation MariaDB : https://mariadb.com/kb/en/
• Percona Toolkit : https://www.percona.com/software/database-tools/percona-toolkit