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 ?

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

De l'espace consommé par les tables n'est pas activement utilisé, mais alloué quand même à la table. 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 indique la quantité d'espace libre allouée à une table, qui n'est pas activement 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 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 : l'espace de stockage total utilisé par une base de données et une table ne peut pas être calculé si la base de données contient des tables avec des colonnes de longueur variable de plus de 768 octets (par exemple, BLOB, TEXT, VARCHAR ou VARBINARY).

Réduire le stockage du journal binaire

L'ajout d'un réplica en lecture amène le journal binaire de l'instance principale à utiliser un espace de stockage supplémentaire. Pour déterminer l'espace de stockage utilisé par le journal binaire sur l'instance principale, vérifiez la métrique CloudWatch BinLogDiskUsage. Une forte augmentation 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

Lorsque vous activez les paramètres du journal général et du journal des requêtes lentes, l'instance de base de données stocke ces journaux et leurs sauvegardes. Pour effectuer une rotation de 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 pour résoudre des problèmes.

Gérer ou réduire la taille du tablespace système InnoDB

Le tablespace système contient le dictionnaire des données InnoDB et l'espace d'annulation. Il démarre à 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 l'espace marqué comme étant réutilisable pour les tables associées, utilisez la commande OPTIMIZE TABLE pour redimensionner les fichiers tablespace de chaque table ou supprimez une table.

Si innodb_file_per_tableest définit sur 0, toutes les tables sont également allouées au tablespace système. La suppression de tables ou d'index ou la suppression ou la troncature des données des tables allouées dans le tablespace système marque l'espace précédemment occupé comme étant 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 principale Amazon RDS source. Lorsque le réplica est synchronisé avec l'instance principale 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 permet pas de récupérer l'espace du tablespace 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.