Comment résoudre les problèmes liés à l'utilisation par mon instance de base de données Amazon RDS MySQL d'un espace de stockage plus grand que prévu ?

Date de la dernière mise à jour : 14/11/2019

Mon instance de base de données Amazon Relational Database Service (Amazon RDS) sur MySQL utilise un espace de stockage plus grand que prévu. Quelle en est la raison et comment optimiser l'espace de stockage sur disque ?

Solution

Vous pouvez utiliser la métrique FreeStorageSpace Amazon CloudWatch pour surveiller l'espace de stockage disponible d'une instance de base de données RDS, mais cette métrique n'indique pas la façon dont l'instance consomme le stockage.

Utilisez les stratégies suivantes pour récupérer de l'espace de stockage :

Exécuter OPTIMIZE TABLE

L'espace consommé par les tables n'est pas nécessairement utilisé, mais il est malgré tout alloué aux tables. Si le paramètre innodb_file_per_table est activé (il l'est par défaut), vous pouvez récupérer cet espace en utilisant OPTIMIZE TABLE. OPTIMIZE TABLE fonctionne pour les tables InnoDB, MyISAM et ARCHIVE, et bien qu'Amazon RDS accepte la commande OPTIMIZE TABLE, Amazon RDS exécute ALTER TABLE...FORCE. Dans ce cas, vous pouvez recevoir un message d'avertissement similaire à « Table does not support optimize ,recate+analyze instead » (La table ne pend pas en charge l'optimisation. Exécution de la recréation et de l'analyse à la place). Pour plus d'informations, consultez OPTIMIZE TABLE dans la documentation MySQL.

Pour recherchez la fragmentation, exécutez une requête similaire à celle-ci :

SELECT
	table_name,
	data_length,
	max_data_length,
	index_length,
	data_free
FROM
	information_schema.tables 
WHERE table_schema='schema_name'
;

La colonne data_free met en évidence la quantité d'espace disponible qui est alloué à une table, mais n'est pas utilisé. Il peut être possible de récupérer cet espace avec OPTIMIZE TABLE si la table est créée dans un tablespace distinct conformément au paramètre de configuration par défaut innodb_file_per_table d'Amazon RDS. Pour plus d'informations, reportez-vous à Tablespaces File-Per-Table dans la documentation MySQL.

Réduire l'espace de stockage des tables d'application

Pour identifier l'espace de stockage utilisé par les tables d'application sur l'instance de base de données, exécutez une requête similaire à celle-ci :

SELECT 
	table_schema,
	SUM(data_length + index_length + data_free)/1024/1024 AS total_mb,
	SUM(data_length)/1024/1024 AS data_mb,
	SUM(index_length)/1024/1024 AS index_mb,
	SUM(data_free)/1024/1024 AS free_mb,
	COUNT(*) AS tables,
	CURDATE() AS today 
FROM 
	information_schema.tables
	GROUP BY table_schema
	ORDER BY 2 DESC
;

Pour localiser la plus grande table d'application sur l'instance de base de données, exécutez une requête similaire à celle-ci :

SELECT 
	table_schema,
	table_name,
	(data_length + index_length + data_free)/1024/1024 AS total_mb,
	(data_length)/1024/1024 AS data_mb,
	(index_length)/1024/1024 AS index_mb,
	(data_free)/1024/1024 AS free_mb,
	CURDATE() AS today
FROM 
	information_schema.tables
	ORDER BY 3 DESC
;

Remarque : Le stockage total utilisé par une base de données et une table individuelles ne peut pas être calculé si la base de données comprend des tables avec des colonnes de longueur variable de plus de 768 octets (par exemple, BLOB, TEXT, VARCHAR ou VARBINARY).

Réduire le stockage des journaux binaires

L'ajout d'un réplica en lecture conduit le journal binaire de l'instance source à utiliser de l'espace de stockage supplémentaire. Pour déterminer la quantité de stockage utilisée par le journal binaire de l'instance source, vérifiez la métrique CloudWatch BinLogDiskUsage. Une augmentation élevée peut indiquer qu'un ou plusieurs réplicas en lecture ne sont pas synchronisés. Pour plus d'informations, consultez Accès aux journaux binaires MySQL.

Réduire ou désactiver le stockage du journal général et le journal des requêtes lentes

L'activation des paramètres de journal général et de journal des requêtes lentes entraîne le stockage de ces journaux et des sauvegardes de ces journaux par votre instance de base de données. Pour faire tourner ces fichiers et contrôler l'utilisation du disque, consultez mysql.rds_rotate_general_log et mysql.rds_rotate_slow_log.

Remarque : Pour éviter les problèmes potentiels de performances et d'utilisation du disque, désactivez le journal général et le journal des requêtes lentes lorsque vous ne les utilisez pas activement à des fins de dépannage.

Gérer ou réduire la taille de l'espace de table système InnoDB

Le tablespace système contient le dictionnaire de données InnoDB et l'espace d'annulation ; sa taille minimale est de 10 Mo. Une fois l'espace alloué, le fichier aura au minimum cette taille, même si les transactions de longue durée peuvent utiliser plus d'espace de stockage disponible.

Par défaut, Amazon RDS définit innodb_file_per_table sur 1, ce qui signifie que les données de chaque tablespace sont stockées dans leur propre fichier .ibd. Pour récupérer de l'espace marqué comme étant réutilisable pour les tables connexes, utilisez la commande OPTIMIZE TABLE pour redimensionner les fichiers du tablespace par tables ou supprimez une table.

Si innodb_file_per_table est défini à 0, toutes les tables sont également allouées au tablespace du système. La suppression de tables ou d'index et la suppression ou la troncation de données des tables allouées dans le tablespace système marque l'espace précédemment occupé comme réutilisable. Cependant, cette commande ne libère pas d'espace dans le système de fichiers.

Comme il n'est pas possible de réduire le tablespace système sur place, exportez les données de la base de données en cours, puis importez-les dans une nouvelle instance. Pour réduire les interruptions, configurez la nouvelle instance MySQL comme réplica de l'instance Amazon RDS source. Lorsque le réplica est synchronisé avec l'instance Amazon RDS source, basculez vers la nouvelle instance. Pour plus d'informations sur la réplication manuelle, consultez Réplication avec une instance MySQL ou MariaDB s'exécutant à l'extérieur d'Amazon RDS.

Remarque : La restauration à partir d'un instantané ou la création d'un réplica en lecture ne vous aidera pas à récupérer de l'espace à partir de l'espace de table système. Cela est dû au fait que les deux méthodes utilisent un instantané du volume de stockage de l'instance source qui contient le tablespace système.