Analyser et optimiser l'espace disque MySQL

Credit : Logo officiel

Analyser et optimiser l'espace disque MySQL

Dylan D. — Agent Support Technique Serveur Depannage 1848 mots 10 min de lecture

Analyser et optimiser l'espace disque MySQL

Vendredi 16h, alerte quota disque chez un client : son hebergement IONOS Pro est plein a 95%. Le client jure qu'il a pas mis de gros fichiers, son site contient juste un WooCommerce avec 500 produits. Verification SSH : les fichiers du site font 2 Go, normal. Mais sa base MySQL fait 4.2 Go. Quatre point deux gigas pour 500 produits. Investigation : la table wp_actionscheduler_logs contient 8.7 millions de lignes, jamais purgees depuis 3 ans. Apres nettoyage et ALTER TABLE FORCE, on recupere 3.8 Go d'espace. Le client est sauve, le site repart.

C'est un cas que je traite plusieurs fois par mois. Les bases MySQL grossissent silencieusement, surtout sur WordPress et WooCommerce ou des plugins logguent tout sans jamais purger. Je te montre les requetes que j'utilise pour diagnostiquer, optimiser, et automatiser sur MySQL 8 et MariaDB 10.11.

Voir la taille globale par base de donnees

Premiere requete a connaitre par coeur :

SELECT
  table_schema AS 'Base',
  ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Taille (Mo)',
  ROUND(SUM(data_free) / 1024 / 1024, 2) AS 'Espace recuperable (Mo)',
  COUNT(*) AS 'Nb tables'
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;

La colonne data_free c'est la cle. C'est l'espace alloue physiquement sur le disque mais marque comme reutilisable apres des suppressions. MySQL InnoDB ne libere jamais cet espace automatiquement, faut le forcer.

Si tu vois une ligne avec Espace recuperable superieur a 500 Mo, t'as du gain potentiel.

Analyse table par table

Pour zoomer sur une base specifique :

SELECT
  table_name AS 'Table',
  ROUND(data_length / 1024 / 1024, 2) AS 'Donnees (Mo)',
  ROUND(index_length / 1024 / 1024, 2) AS 'Index (Mo)',
  ROUND(data_free / 1024 / 1024, 2) AS 'Espace perdu (Mo)',
  table_rows AS 'Lignes',
  ENGINE AS 'Moteur'
FROM information_schema.TABLES
WHERE table_schema = 'dbs12345'
ORDER BY (data_length + index_length) DESC
LIMIT 20;

Remplace dbs12345 par le nom de ta base. Sur les serveurs IONOS le nom commence souvent par dbs suivi d'un identifiant numerique.

Les tables qui explosent le plus souvent en pratique :

Compter les lignes precisement

Le table_rows d'information_schema est un estimation pour InnoDB, pas un comptage reel. Pour un compte exact :

SELECT COUNT(*) FROM wp_postmeta;
SELECT COUNT(*) FROM wp_options WHERE autoload = 'yes';

La deuxieme requete est cruciale pour WordPress : les options autoload sont chargees a chaque requete HTTP. Si tu en as 10 000, ton site est lent. Le seuil sain c'est moins de 1000 options autoload, ideal moins de 500.

Identifier les options autoload qui posent probleme

SELECT
  option_name,
  ROUND(LENGTH(option_value)/1024, 2) AS 'Taille (Ko)'
FROM wp_options
WHERE autoload = 'yes'
ORDER BY LENGTH(option_value) DESC
LIMIT 30;

Tu vas voir des trucs aberrants comme une option wpseo_premium_orphaned_content_support_log qui fait 8 Mo, ou des _transient_* en autoload (alors que les transients sont censes pas l'etre). Pour passer une option en non-autoload :

UPDATE wp_options SET autoload = 'no' WHERE option_name = 'nom_option_pourrie';

Ne supprime jamais directement une option, certains plugins explosent si leurs options manquent. Passe-les juste en non-autoload.

Recuperer l'espace perdu avec ALTER TABLE FORCE

Quand tu supprimes des lignes dans MySQL InnoDB, l'espace sur disque n'est pas libere. Il est marque comme reutilisable mais le fichier .ibd ne retrecit pas. C'est pour ca que data_free augmente. Pour forcer la recuperation physique :

ALTER TABLE wp_postmeta FORCE;

Ca reconstruit la table physiquement, en compactant les donnees et liberant l'espace inutilise. C'est l'equivalent moderne d'OPTIMIZE TABLE qui marche systematiquement avec InnoDB (alors qu'OPTIMIZE TABLE peut faire un simple analyze sur certaines versions).

Attention critique : sur une grosse table (plusieurs Go), ca peut prendre plusieurs minutes a une heure et verrouiller la table en lecture/ecriture. Fais-le en heures creuses ou pendant une fenetre de maintenance. Pour minimiser le lock :

ALTER TABLE wp_postmeta ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;

L'option ALGORITHM=INPLACE reconstruit sans copier la table, et LOCK=NONE permet les lectures/ecritures pendant l'operation. Pas dispo sur toutes les versions, mais sur MySQL 8.0+ ca marche bien.

Optimiser toutes les tables d'un coup

Via mysqlcheck en ligne de commande :

mysqlcheck -o -u dbuser -p dbs12345

L'option -o execute OPTIMIZE TABLE sur toutes les tables de la base. Pour toutes les bases en une commande :

mysqlcheck -o --all-databases -u root -p

Variante avec ALTER FORCE explicite (script bash) :

#!/bin/bash
DB="dbs12345"
for TABLE in $(mysql -u dbuser -p -B -N -e "SHOW TABLES" $DB); do
    echo "Optimizing $TABLE..."
    mysql -u dbuser -p -e "ALTER TABLE $TABLE FORCE;" $DB
done

Nettoyer les donnees inutiles d'abord

Avant d'optimiser physiquement, vire le superflu logique :

-- Les revisions WordPress (en stocke des centaines par article)
DELETE FROM wp_posts WHERE post_type = 'revision';

-- Les meta orphelines (post supprime mais sa meta reste)
DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts p ON pm.post_id = p.ID
WHERE p.ID IS NULL;

-- Les commentaires spam et trash
DELETE FROM wp_comments WHERE comment_approved = 'spam';
DELETE FROM wp_comments WHERE comment_approved = 'trash';

-- Les meta de commentaires orphelines
DELETE cm FROM wp_commentmeta cm
LEFT JOIN wp_comments c ON cm.comment_id = c.comment_ID
WHERE c.comment_ID IS NULL;

-- Les transients expires (beaucoup de plugins en laissent trainer)
DELETE FROM wp_options WHERE option_name LIKE '\_transient\_%';
DELETE FROM wp_options WHERE option_name LIKE '\_site\_transient\_%';

-- Les sessions WooCommerce expirees
DELETE FROM wp_woocommerce_sessions WHERE session_expiry < UNIX_TIMESTAMP();

Apres ces deletes, lance les ALTER TABLE FORCE pour recuperer physiquement l'espace.

On voit souvent des bases WordPress avec 500 000 revisions d'articles. Limite ca dans wp-config.php :

define('WP_POST_REVISIONS', 5);
define('AUTOSAVE_INTERVAL', 300);
define('EMPTY_TRASH_DAYS', 7);

Le cas WooCommerce Action Scheduler

Action Scheduler stocke les actions planifiees dans wp_actionscheduler_actions et leurs logs dans wp_actionscheduler_logs. Sur un site WooCommerce un peu ancien, ces tables peuvent depasser le Go.

Nettoyer les actions terminees plus vieilles que 30 jours :

DELETE FROM wp_actionscheduler_actions 
WHERE status IN ('complete', 'failed', 'canceled') 
AND scheduled_date_gmt < DATE_SUB(NOW(), INTERVAL 30 DAY);

DELETE FROM wp_actionscheduler_logs 
WHERE log_date_gmt < DATE_SUB(NOW(), INTERVAL 30 DAY);

Apres ca, ALTER TABLE FORCE sur les deux tables. Dans WP-CLI :

wp action-scheduler clean --batch-size=1000 --before='30 days ago'

Surveiller les connexions actives

Un autre aspect souvent neglige : les connexions MySQL en cours qui peuvent saturer le serveur :

SHOW PROCESSLIST;

Version plus lisible avec INFORMATION_SCHEMA :

SELECT
  ID, USER, HOST, DB, COMMAND, TIME, STATE,
  LEFT(INFO, 100) AS QUERY
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE COMMAND != 'Sleep'
ORDER BY TIME DESC;

Les requetes qui durent plus de 30 secondes sont a investiguer. Pour tuer une requete bloquee :

KILL QUERY 12345;

Ou pour killer toute la connexion :

KILL 12345;

Ou 12345 est l'ID de la connexion vu dans PROCESSLIST.

Verifier la taille des fichiers ibdata1 et binlogs

MySQL peut aussi consommer de l'espace en dehors des bases proprement dites. Sur les VPS IONOS, verifie :

ls -lh /var/lib/mysql/ibdata1
ls -lh /var/lib/mysql/ib_logfile*
du -sh /var/lib/mysql/binlog*

ibdata1 est le tablespace InnoDB. Si tu n'as pas active innodb_file_per_table (defaut depuis MySQL 5.6), toutes les donnees y sont stockees et le fichier ne reduit jamais. Pour confirmer :

SHOW VARIABLES LIKE 'innodb_file_per_table';

Si OFF, active dans /etc/mysql/my.cnf :

[mysqld]
innodb_file_per_table = ON

Les binlogs sont les logs binaires utilises pour la replication et le point-in-time recovery. Ils peuvent prendre des dizaines de Go. Pour purger les anciens :

PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);

Ou configure une retention automatique dans my.cnf :

binlog_expire_logs_seconds = 604800

Soit 7 jours en secondes.

Mettre en place un monitoring

Script bash qui surveille la taille des bases et alerte par mail si une base depasse 2 Go :

#!/bin/bash
THRESHOLD=2048
QUERY="SELECT table_schema, ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb FROM information_schema.TABLES GROUP BY table_schema HAVING size_mb > $THRESHOLD"
RESULT=$(mysql -u root -p"$MYSQL_PASS" -B -N -e "$QUERY")
if [ -n "$RESULT" ]; then
    echo "Bases depassant ${THRESHOLD}Mo:\n$RESULT" | mail -s "MySQL Size Alert" admin@mondomaine.fr
fi

A mettre en cron quotidien.

Erreurs courantes et leur fix

"Specified key was too long" pendant ALTER

Probleme de charset utf8mb4 avec des index trop longs. Verifie ta version MySQL et passe en innodb_large_prefix :

SET GLOBAL innodb_large_prefix = ON;
SET GLOBAL innodb_file_format = Barracuda;
SET GLOBAL innodb_file_per_table = ON;

Disk full pendant l'ALTER TABLE FORCE

MySQL InnoDB a besoin d'autant d'espace libre que la taille de la table pour la reconstruire. Si tu as 90% de disque utilise et tu lances un FORCE sur une table de 5 Go, tu vas planter le serveur. Verifie avant :

df -h /var/lib/mysql/

Lock timeout sur grosse table

Si LOCK=NONE n'est pas dispo et que la table est tres sollicitee, fais le FORCE pendant la fenetre la moins chargee. Sinon utilise pt-online-schema-change de Percona qui fait du copy-then-swap.

data_free reste eleve apres ALTER

Possible si tu as innodb_file_per_table = OFF. Dans ce cas tous les .ibd sont dans ibdata1 qui ne se reduit jamais. La seule solution c'est un dump/restore complet. Sur les bases recentes c'est rare, le file_per_table est par defaut depuis MySQL 5.6.

Pas les droits pour ALTER

Ton user MySQL doit avoir le droit ALTER :

SHOW GRANTS FOR 'dbuser'@'localhost';

Sur les bases IONOS mutualisees, les droits sont generalement complets sur tes propres bases.

Pour aller plus loin

Une base saine, un site rapide

Une base MySQL bien entretenue, c'est plus que de l'espace disque gagne. C'est des requetes plus rapides (moins de pages a parcourir), un cache plus efficace, des sauvegardes plus rapides, et globalement un site plus reactif. Negliger sa BDD c'est comme jamais nettoyer son disque dur : ca finit par te ralentir et te bouffer toute la place sans que tu comprennes pourquoi.

Prends l'habitude de checker tes plus grosses bases une fois par mois. Une vingtaine de minutes pour identifier les tables qui gonflent, nettoyer le superflu, et optimiser. Tu eviteras les alertes quota a 16h le vendredi soir, et tes clients seront contents de la rapidite de leur site. Et fais des backups avant chaque operation -- meme si tu es sur de toi. Surtout si tu es sur de toi.

# Articles similaires

Sur les memes sujets et plus loin