Oui, gérer des bases de données surchargées peut être un vrai casse-tête, surtout lorsqu’elles prolifèrent de manière exponentielle. Si votre instance MySQL ou MariaDB est lente ou si votre espace disque disparaît plus vite que prévu, il est peut-être temps d’envisager la compression et la défragmentation des tables. Ces méthodes peuvent récupérer une quantité surprenante d’espace et améliorer les performances, mais il faut toujours faire preuve de prudence : sauvegarder les données au préalable, vérifier la configuration et espérer que personne ne se risque à une suppression totale sans précautions. Voici quelques solutions qui ont fait leurs preuves en conditions réelles, moyennant quelques essais et erreurs.
Comment résoudre les problèmes d’espace et de performances de MySQL/MariaDB
Lutter contre les fichiers ibdata1 et ib_log — Séparez vos données InnoDB
Si vos fichiers ibdata1 et ib_log occupent un espace considérable, c’est probablement parce que toutes vos tables InnoDB sont regroupées dans un seul fichier monolithique. Ce comportement est normal, mais il est peu efficace et complique énormément le nettoyage. Sur certaines configurations, ces fichiers ne se réduisent pas automatiquement, ce qui est agaçant. La solution consiste donc à configurer MySQL pour qu’il stocke chaque table séparément ; vous pourrez ainsi libérer de l’espace plus facilement par la suite.
Voici la marche à suivre :
- Commencez par sauvegarder toutes vos bases de données, surtout si elles sont importantes ; mysqldump est votre allié :
# mysqldump -u [username] -p [database_name] > backup.sql - Arrêtez le serveur MySQL ou MariaDB :
# sudo systemctl stop mysqlou# sudo systemctl stop mariadb - Modifiez votre fichier de configuration, généralement situé à l’emplacement /etc/mysql/my.cnf ou /etc/mysql/mariadb.conf.d/50-server.cnf. Recherchez ou ajoutez la ligne suivante sous [mysqld] :
innodb_file_per_table=1 - Supprimez les fichiers volumineux ibdata1 et ib_log. MySQL ne le fera pas automatiquement ; vous devrez les supprimer manuellement (assurez-vous d’en avoir une sauvegarde !).
# sudo rm /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile1 - Redémarrez le serveur :
# sudo systemctl start mysql - Restaurez vos sauvegardes si nécessaire, puis vérifiez que les nouvelles tables sont stockées dans leurs propres fichiers .ibd — si vous avez de la chance, les anciens fichiers ne grossiront plus.
Cette méthode est un peu radicale, mais elle permet de bien gérer les fichiers ibdata. Sur certains systèmes, il peut être nécessaire de sauvegarder, recréer et recharger la base de données pour que la transition se fasse correctement ; c’est pourquoi les sauvegardes sont essentielles.
Compression des tables dans InnoDB — Économisez de l’espace avec ROW_FORMAT=COMPRESSED
Si vous avez des tables contenant beaucoup de texte ou des données BLOB, leur compression peut libérer beaucoup d’espace disque. Le hic ? Cela peut augmenter considérablement la charge du processeur. Cette solution n’est donc pertinente que si le processeur de votre serveur n’est pas déjà saturé ou si l’espace disque est une préoccupation majeure.
Accédez à votre shell MySQL :
# mysql -u root -p
Passez à la base de données que vous souhaitez nettoyer :
# use your_database_name;
Vérifiez quelles tables prennent beaucoup de place :
SELECT table_name AS "Table", ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size in MB" FROM information_schema. TABLES WHERE table_schema = "your_database_name" ORDER BY (data_length + index_length) DESC;Cela vous indique quelles tables sont les plus volumineuses. Choisissez-en une ou plusieurs à compresser. Par exemple, si la table b_crm_event_relationssemble trop grande, exécutez :
ALTER TABLE b_crm_event_relations ROW_FORMAT=COMPRESSED;
Ensuite, la taille de la table devrait diminuer ; j’ai constaté une réduction d’environ 26 Mo à 11 Mo, ce qui est très appréciable. Attention : la compression peut ralentir temporairement les opérations de lecture/écriture. Il est donc conseillé de ne pas l’utiliser de manière excessive en période de forte activité.
Tables MyISAM — Compresser et enregistrer
Si vous travaillez avec d’anciennes tables MyISAM, vous pouvez les compresser avec l’outil myisampack. Il s’agit plutôt d’une compression du fichier table que d’une compression au sein de la base de données.
Il vous suffit d’exécuter cette commande dans votre terminal — remplacez le chemin par l’emplacement de votre table :
# myisampack -b /var/lib/mysql/test/modx_session
Cela crée une version compressée de votre fichier. MYD, comme cecimodx_session. MYI :.Vous pouvez vérifier la taille avant et après en utilisant du -sh.
Assurez-vous d’exécuter myisampack sur le fichier. MYD, puis myisamchk -rq sur la table. C’est un peu étrange, mais cela permet vraiment de réduire la taille des petites tables lorsque l’espace est limité.
Maintenance régulière : défragmentation et optimisation des tables
Au fil du temps, les tables peuvent se fragmenter et se remplir d’espace inutilisé. L’exécution de la commande OPTIMIZE TABLE permet souvent de récupérer cet espace et d’accélérer l’accès aux données.
Connectez-vous à votre interface MySQL, sélectionnez votre base de données :
# mysql -u root -p
Choisissez votre base de données :
# USE your_database;
Et courir :
OPTIMIZE TABLE b_crm_timeline_bind, b_disk_deleted_log_v2, b_disk_object_path;
Cette opération reconstruira les tables et réduira leur taille en cas de fragmentation. Vous devriez voir un message confirmant la réussite de l’opération, avec la valeur de `data_free` ramenée à zéro. Sur certaines configurations, le message « La table ne prend pas en charge l’optimisation » peut s’afficher ; dans ce cas, une recréation suivie d’une analyse sera effectuée. Quoi qu’il en soit, cette opération tend à réduire considérablement la taille des tables et à améliorer leurs performances.
Si vous voulez une solution rapide et tout-en-un, il vous suffit de courir :
# mysqlcheck -o --all-databases -u root -p
C’est une bonne habitude à prendre de temps en temps. N’oubliez pas de sauvegarder vos données au préalable, car, comme on dit, la loi de Murphy est implacable.
Résumé
- Sauvegardez vos bases de données avant de modifier les configurations ou la compression.
- Modifiez le fichier /etc/mysql/my.cnf pour activer
innodb_file_per_table=1. - Supprimez les anciens fichiers ibdata1 et ib_log après l’arrêt du serveur ; la recréation de nouveaux fichiers permet de contrôler leur croissance.
- À utiliser
ALTER TABLE...ROW_FORMAT=COMPRESSEDavec parcimonie sur les grands textes ou les tableaux BLOB pour économiser de l’espace. - Compressez les tables MyISAM avec myisampack et exécutez myisamchk -rq.
- Exécutez OPTIMIZE TABLE ou mysqlcheck -o périodiquement pour défragmenter.
Conclure
Essayer ces astuces peut améliorer le fonctionnement de votre base de données et libérer un espace disque précieux, surtout pour les tables anciennes ou les projets en pleine expansion. Certes, cela demande un peu de travail, mais le gain en vitesse et en espace en vaut la peine. Pensez simplement à sauvegarder vos données, à revérifier vos configurations et à programmer une tâche cron pour assurer un fonctionnement optimal. Espérons que cela vous fera gagner quelques heures, car une base de données plus performante, c’est la garantie d’un meilleur service.