L'utilisation du disque d'une base de données peut augmenter continuellement, particulièrement avec le fichier ibdata1 dans MySQL. Ce fichier peut atteindre des tailles considérables (plusieurs centaines de gigaoctets) et continuer de croître sur une longue période.

Analyse des causes de l'augmentation de ibdata1

Le moteur InnoDB utilise le contrôle de concurrence multi-versions (MVCC). Pour ce faire, il conserve les informations d'annulation (Undo logs) nécessaires aux requêtes dans le fichier système ibdata1. Si une requête s'exécute pendant une longue période sur une table InnoDB et que cette table subit des modifications importantes pendant cette requête, une grande quantité d'informations d'annulation est générée, entraînant une augmentation de la taille de ibdata1. Il est important de noter que le fichier ibdata1 ne supporte pas la réduction en ligne des données dans les versions actuelles de MySQL.

Pour vérifier la configuration relative aux fichiers par table, utilisez la commande suivante :

SHOW VARIABLES LIKE 'innodb_file_per_table';

Vous pouvez également examiner les variables liées aux Undo logs :

SHOW VARIABLES LIKE '%undo%';

Stratégies de résolution

La solution principale consiste à surveiller et à clore les sessions ou transactions qui s'exécutent trop longtemps. Les approches alternatives incluent :

• Basculement maître-esclave (Master-Slave Switch)
• Migration des données
• Augmentation de l'espace de stockage disponible

Optimisation de l'espace disque

Dans les cas où les fichiers de données occupent trop d'espace, le nettoyage des données inutiles peut réduire leur taille. Les commandes DROP TABLE et TRUNCATE TABLE peuvent être utilisées pour supprimer les tables qui ne sont plus requises.

Estimation de la taille logique des tables

La table information_schema.tables fournit des statistiques basées sur des échantillons, ce qui peut entraîner des différences entre la taille des tables et des bases de données rapportée et l'espace réel occupé par les fichiers de données. Généralement, la taille rapportée est inférieure à l'espace réel occupé.

SELECT
 table_name,
 CONCAT(
   ROUND(
     (data_length + index_length) / 1024 / 1024,
     2
   ),
   'MB'
 ) AS taille_logique
FROM
 information_schema.tables
WHERE
 table_schema = 'VOTRE_SCHEMA' AND table_name = 'VOTRE_TABLE';
   

Il est possible que même après avoir réexécuté ANALYZE TABLE, les valeurs obtenues soient inférieures à l'espace réel occupé par les fichiers de données.

Pour une estimation plus proche de l'espace réel occupé par les fichiers de données, en tenant compte des espaces libres (data_free), utilisez la requête suivante :

SELECT
 SUM(data_length + index_length + data_free) / 1024 / 1024 AS taille_estimée_mb
FROM
 information_schema.tables;
   

Il faut savoir que l'opération DELETE ne libère pas immédiatement l'espace disque occupé par les données supprimées. Cet espace devient du "vide" dans les fichiers de données. De plus, DELETE génère des fichiers de journalisation (binlogs) qui consomment de l'espace disque supplémentaire. Pour récupérer l'espace après des opérations de suppression, il est nécessaire d'utiliser OPTIMIZE TABLE nom_de_la_table ou ALTER TABLE nom_de_la_table ENGINE=InnoDB.

Les fichiers temporaires sont automatiquement libérés à la fin d'une requête ou à la terminaison d'une session. Si un manque d'espace disque est causé par des fichiers temporaires, la terminaison des sessions concernées peut libérer l'espace nécessaire.