Il arrive parfois de devoir ajouter ou supprimer une grande quantité de données dans une table, ce qui a pour effet de libéré de l’espace disque, mais, dans le cas de mysql l’espace réservé dans la base de donnée n’est pas libéré pour le système d’exploitation.
La situation est d’ailleurs identique pour une base SQL Server, il faudra utiliser de la commande DBCCSHRINK afin de libérer l’espace d’une BDD vers l’OS.
Il y a peu de temps un des serveurs mysql de ma société s’est retrouvé à cours d’espace disque dans la nuit (gros import de données surprise) et donc service mysql HS, je vais présenter ici les quelques étapes qui m’ont permis de résoudre le problème.
Identification
Dans un premier temps j’ai recherché la cause du problème, après connexion sur le serveur j’ai vite pu constater du manque de place avec un simple df
df -h
Cette commande vous permet de voir l’espace disque utilisé sur votre serveur et surtout s’il reste de l’espace libre. Pour affiner la recherche il faudra utiliser le paquet ncdu que j’ai tendance à installer sur l’ensemble de mes serveurs, c’est une sorte de treesize.
sudo apt install ncdu cd / sudo ncdu
Pour naviguer dans le menu de ncdu il faut utiliser les flèches de votre clavier, Enter pour entrer dans un répertoire, il est possible de supprimer des fichiers/répertoires directement dans ncdu avec la touche d. Une liste des raccourcis disponible ici https://dev.yorhel.nl/ncdu/man
Dans un premier temps j’ai pu supprimer quelques backups que je fait sur la machine afin de regagner un minimum de place et ensuite faire repartir le service mysql. Mais j’ai surtout identifier un .idb (innodb) qui était énorme.
il s’agissait d’une table qui stock l’historique des tâches passées dans un outil ETL Talend nous n’avons certainement pas besoin de garder autant de données dans cette table, j’ai donc demandé a l’équipe en charge de l’ETL de faire du ménage dans le logs 🙂
Une fois le problème identifié nous allons aller voir ce que contient cette table
Vérification du status d’une table
Il faut se connecter à votre serveur mysql, j’utilise le mysql-client présent sur mon serveur mais vous pouvez utiliser des outils comme HeidiSQL ou mysql Workbench pour vous aider dans cette tâche.
mysql -u root -p
Fournir le mot de passe de votre utilisateur mysql. Une fois connecté vous pouvez afficher l’ensemble des DBs sur le serveur avec la commande
SHOW DATABASES;
Nous pouvons aussi exécuter la requête suivante pour voir la taille des databases :
SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;
Nous allons ensuite afficher la valeur de Data_free (la valeur est exprimée en Octets)
SHOW TABLE STATUS FROM talend_administration WHERE name='taskexecutionhistory';
Cette commande affiche l’ensemble des status pour la table talend_administration, d’ici vous pouvez donc visualiser la colonne Data Free si sa valeur est très élevée (c’était mon cas mais pas dans l’image ci-dessous le problème est corrigé depuis) il faudra alors l’optimiser.
Optimisation de la Table
Selon la documentation mysql, il faut optimiser vos tables mysql dans le cas d’insert, update ou delete conséquents dans vos bases de données https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html
Le fonctionnement d’innoDB pour l’optimisation des tables fonctionne de cette manière :
- Création d’une nouvelle table (recreate)
- Index de l’ancienne table (analyze)
- Transfert des données à la nouvelle table
- Suppression de l’ancienne table et libération du Data_free pour l’OS
Pour lancer l’optimisation d’une table il faudra exécuter la requête suivante :
USE talend_administration;
OPTIMIZE TABLE taskexecutionhistory;
Cette commande devrait vous renvoyer l’ensemble des informations suivantes
Table is full …
Dans mon cas j’ai eu un souci qui m’affichait qu’il ne pouvait pas procéder à l’optimisation, après quelques recherches il s’est avérer que l’espace disque nécessaire au recreate n’était pas suffisant !
Il faut bien faire attention à avoir le minimum d’espace libre nécessaire pour la re-création de la table et donc à avoir au minimum la capacité d’accueil de l’index_length et du data_length de la table. Pour résoudre ce problème j’ai donc dû rajouter de l’espace disque à la VM qui soutient de mysql comme expliquer dans un ancien billet.
Pixxlisation 