Tuning MySQL/MariaDB

Un serveur MySQL ou MariaDB mal dimensionné consomme trop de RAM ou laisse des requêtes lentes passer inaperçues. Ce guide cible un VPS de production HolyCloud : réglage InnoDB buffer pool, journal des slow queries et lecture des métriques.

Prérequis

• MySQL 8.x ou MariaDB 10.6+ installé
• Accès root MySQL et sudo sur le système
• Snapshot avant modification de la configuration
• Connaissance de la charge (OLTP, WordPress, etc.)

Évaluer la RAM disponible

free -h
mysql --version

Règle empirique pour VPS dédié à la base : innodb_buffer_pool_size ≈ 50–70 % de la RAM si MySQL est le service principal ; sinon 25–40 % si Nginx, PHP et Redis partagent la machine.

Exemple pour 4 Go RAM, MySQL principal :

innodb_buffer_pool_size = 2G

Configuration InnoDB

Fichier typique :

• Debian/Ubuntu : /etc/mysql/mysql.conf.d/mysqld.cnf ou /etc/mysql/mariadb.conf.d/50-server.cnf

Sauvegarde :

sudo cp -a /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf.bak

Ajoutez ou modifiez sous [mysqld] :

[mysqld]
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 2
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
max_connections = 150

Attention : innodb_flush_log_at_trx_commit = 2 améliore les perfs mais réduit la durabilité en cas de coupure de courant. Gardez = 1 pour données financières critiques.

Appliquez :

sudo systemctl restart mysql
# ou : sudo systemctl restart mariadb

Vérifiez les variables actives :

mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool%';"
mysql -e "SHOW ENGINE INNODB STATUS\G" | less

Activer le slow query log

Dans le même fichier [mysqld] :

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

Créez le répertoire si besoin et redémarrez :

sudo mkdir -p /var/log/mysql
sudo chown mysql:mysql /var/log/mysql
sudo systemctl restart mysql

Activation à chaud (temporaire) :

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

Analyser les requêtes lentes

sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

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

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

Dans MySQL, inspectez les requêtes en cours :

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

Index et cache de requêtes

Vérifiez les tables sans clé primaire :

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema','sys')
AND table_type = 'BASE TABLE';

MySQL 8+ n'utilise plus le query cache ; concentrez-vous sur les index et le buffer pool.

Monitoring rapide

mysqladmin status
mysqladmin extended-status | grep -i innodb

Dépannage

| Problème | Piste |

|----------|-------|

| OOM killer tue mysqld | Réduire innodb_buffer_pool_size |

| Redémarrage long | Taille des redo logs ; patience au premier boot après changement |

| Slow log vide | Baisser long_query_time, générer du trafic test |

Besoin d'aide ?

Pour un cluster ou une réplication, ouvrez un ticket HolyCloud avec votre charge (QPS, taille base).