Comment résoudre les problèmes liés à une instance RDS for MySQL ou MariaDB qui indique un état de stockage plein ?

Brève description

Pour dépanner une instance Amazon RDS for MySQL ou MariaDB dont l'espace de stockage est saturé, vérifiez l'espace total utilisé sur votre instance de base de données afin d'identifier ce qui utilise l’espace. Vous pouvez utiliser l'espace de votre instance de base de données pour les objets suivants :

• Les bases de données créées par les utilisateurs
• Les tables temporaires
• Les journaux binaires ou les journaux relais de l'instance de secours MySQL (si vous utilisez un réplica en lecture)
• Le tablespace InnoDB
• Les journaux généraux, les journaux de requêtes lentes et les journaux d'erreurs

Après avoir vérifié votre espace de stockage et identifié les personnes qui l'utilisent, vous pouvez récupérer de l'espace. Puis, vous pouvez surveiller la métrique FreeStorageSpace pour éviter d'autres problèmes d'espace de stockage.

Remarque : Si vous constatez une diminution soudaine de l'espace de stockage disponible, exécutez la commande SHOW FULL PROCESSLIST pour vérifier les requêtes au niveau de l'instance de base de données. La commande SHOW FULL PROCESSLIST fournit des informations sur toutes les connexions actives et sur les requêtes exécutées par chaque connexion. Pour examiner les transactions qui sont restées actives pendant longtemps, exécutez d’abord la commande INFORMATION_SCHEMA.INNODB_TRX ou SHOW ENGINE INNODB STATUS. Puis, vérifiez la sortie.

Résolution

Pour dépanner une instance Amazon RDS for MySQL ou MariaDB qui indique une capacité de stockage saturée, procédez comme suit :

Vérifier l'espace total utilisé sur votre instance de base de données MySQL

Identifier la taille de chaque base de données créée par l'utilisateur

SELECT SUBSTRING_INDEX(TABLESPACE_NAME,"/",1) AS DATABASE_NAME, ROUND((DATA_FREE/1024/1024/1024),3) AS 'REUSABLE (GB)', ROUND(SUM((TOTAL_EXTENTS * EXTENT_SIZE)/1024/1024/1024),3) AS 'TOTAL (GB)' FROM INFORMATION_SCHEMA.FILES GROUP BY DATABASE_NAME ORDER BY 'TOTAL (GB)'  DESC;

Vérifiez la taille de chaque table pour une base de données utilisateur que vous spécifiez :
Remarque : Remplacez example-database-name par le nom de votre base de données.

SELECT SUBSTRING_INDEX(TABLESPACE_NAME,"/",-1) as 'TABLE_NAME', ROUND((total_extents * extent_size)/1024/1024/1024,3) AS "TableSizeinGB" from information_schema.files WHERE FILE_NAME LIKE 'example-database-name';

Vérifier l'espace total utilisé sur votre instance MariaDB

Identifiez la taille de chaque base de données créée par l'utilisateur :

mysql> SELECT table_schema, ROUND(SUM(data_length+index_length)/1024/1024/1024,2) "size in GB" FROM information_schema.tables GROUP BY 1 ORDER BY 2 DESC;

Vérifiez la taille de chaque table pour une base de données utilisateur que vous spécifiez :
Remarque : Remplacez example-database par le nom de la base de données et example-table par le nom de la table.

mysql> SELECT table_schema "example-database", example-table,(data_length + index_length)/1024/1024/1024 AS "TableSizeinGB" from information_schema.tables where table_schema='database_name';

Vérifier vos tables temporaires

Les tables temporaires créées par l'utilisateur InnoDB et les tables temporaires internes sur disque sont créées dans un fichier tablespace temporaire nommé ibtmp1. Les fichiers tablespace temporaires peuvent s'étendre jusqu'à ibtmp2 dans le répertoire de données MySQL. Si la table temporaire ibtmp1 utilise une quantité d’espace de stockage excessive, redémarrez l'instance de base de données pour libérer de l'espace.

Remarque : Vous pouvez utiliser uniquement les versions 5.7 et ultérieures de MySQL ou les versions 8.0 et ultérieures de MySQL pour interroger la taille des fichiers du tablespace InnoDB.

Identifier le tablespace temporaire InnoDB :

mysql> SELECT file_name, tablespace_name, table_name, engine, index_length, total_extents, extent_size from information_schema.files WHERE file_name LIKE '%ibtmp%';

Pour récupérer l'espace disque occupé par un fichier de données de tablespace temporaire global, redémarrez le serveur MySQL ou l’instance de base de données. Pour plus d'informations, consultez la page Le tablespace temporaire sur le site Web de MySQL.

Vérifier votre tablespace InnoDB

MySQL peut créer des tables temporaires internes qui ne peuvent pas être supprimées en raison d'une requête. Ces tables temporaires ne font pas partie de la table nommée tables dans information_schema. Pour plus d'informations, consultez la page Utilisation de tables temporaires internes dans MySQL sur le site Web de MySQL.

Identifiez les tables temporaires internes :

mysql> SELECT * FROM information_schema.innodb_sys_tables WHERE name LIKE '%#%';

Identifiez le tablespace du système InnoDB :

mysql> SELECT file_name, tablespace_name, table_name, engine, index_length, total_extents, extent_size from information_schema.files WHERE file_name LIKE '%ibdata%';

Remarque : La requête précédente est prise en charge sur les versions 5.7 et ultérieures de MySQL ou sur les versions 8.0 et ultérieures de MySQL.

Lorsque la taille du tablespace de votre système augmente, vous ne pouvez pas la diminuer. Pour contourner ce problème, vous pouvez vider vos tables InnoDB et les importer dans une nouvelle instance de base de données MySQL. Pour éviter les tablespaces système volumineux, utilisez des tablespaces fichier par table. Pour plus d'informations, consultez la page Tablespaces fichier par table sur le site Web de MySQL.

Si vous activez Innodb_file_per_table, chaque table stocke les données et les index dans son propre fichier tablespace. Pour récupérer de l'espace, exécutez OPTIMIZE TABLE. Pour plus d’informations, consultez la page Instruction OPTIMIZE TABLE sur le site Web de MySQL.

Remarque : La commande OPTIMIZE TABLE utilise l'algorithme COPY pour créer des tables temporaires de la même taille que la table d'origine. Assurez-vous que vous disposez de l'espace disque disponible avant d'exécuter OPTIMIZE TABLE.

Pour optimiser votre table, exécutez la commande suivante :
Remarque : Remplacez example-table-name par la table que vous souhaitez décrire.

mysql> OPTIMIZE TABLE example-table-name;

(Facultatif) Pour reconstruire la table, exécutez la commande suivante :
Remarque : Remplacez example-table-name par la table que vous souhaitez optimiser.

mysql> ALTER TABLE example-table-name ENGINE=INNODB;

Vérifier vos journaux binaires

Si vous activez les sauvegardes automatiques sur votre instance Amazon RDS, les journaux binaires sont activés automatiquement sur votre instance de base de données. Les journaux binaires sont stockés sur le disque et consomment de l'espace de stockage, mais ils sont supprimés à chaque configuration de la conservation des journaux binaires. La valeur de rétention binlog par défaut pour votre instance est définie sur Null et les fichiers sont immédiatement supprimés.

Pour éviter les problèmes d'espace de stockage insuffisant, définissez la période de conservation des journaux binaires appropriée dans Amazon RDS for MySQL.

Pour vérifier le nombre d'heures pendant lesquelles un journal binaire est retenu, exécutez la commande mysql.rds_show_configuration :

CALL mysql.rds_show_configuration;

Pour réduire la quantité d'espace utilisée par les journaux binaires, réduisez le nombre d'heures pendant lesquelles un journal binaire est retenu. La valeur NULL supprime immédiatement les journaux.

S'il existe une instance de secours pour l'instance active, surveillez la métrique ReplicaLag sur l'instance de secours. La métrique ReplicaLag indique tout retard survenant pendant lorsque le journal binaire traite sur l'instance active ou des journaux de relais sur l'instance de secours.

En cas de problèmes de purge ou de réplication, les journaux binaires peuvent s'accumuler au fil du temps, ce qui consomme de l’espace disque supplémentaire. Pour vérifier le nombre de journaux binaires sur une instance et leur taille de fichier, utilisez la commande SHOW BINARY LOGS. Pour plus d'informations, consultez la page Instruction SHOW BINARY LOGS sur le site web de MySQL.

Si l'instance de base de données sert d’instance de réplication de secours, vérifiez Relay_Log_Space pour connaître la taille des journaux de relais :

SHOW SLAVE STATUS\G

Journaux MySQL (journaux généraux, journaux de requêtes lentes et journaux d'erreurs)

Pour vérifier la taille des journaux de requêtes lentes, des journaux généraux de type FILE et des journaux d'erreurs, consultez et répertoriez les fichiers journaux de la base de données. Si les tables des journaux de requêtes lentes et des journaux généraux utilisent trop d’espace de stockage, effectuez une rotation manuelle des tables de journaux pour gérer les journaux MySQL basés sur des tables.

Pour supprimer complètement les anciennes données et récupérer l'espace disque correspondant, exécutez les commandes suivantes deux fois de suite :

mysql> CALL mysql.rds_rotate_slow_log;mysql> CALL mysql.rds_rotate_general_log;

Remarque : Les tables n’indiquent pas une taille de fichier précise pour les journaux. Modifiez la valeur du paramètre pour log_output sur Fichier pour slow_log et general_log.

Surveiller et mettre à l’échelle votre instance de base de données Amazon RDS

Pour surveiller et mettre à l’échelle votre instance Amazon RDS, procédez comme suit :

• Utilisez Amazon CloudWatch pour surveiller votre instance de base de données Amazon RDS.
• Configurez les alarmes CloudWatch sur la métrique FreeStorageSpace pour recevoir des alertes lorsque votre espace de stockage diminue jusqu'à une valeur seuil spécifiée. Pour plus d'informations, consultez la section Comment puis-je créer des alarmes CloudWatch pour surveiller l’espace de stockage disponible d’Amazon RDS et éviter les problèmes de saturation de l’espace de stockage ?
• Utilisez la fonctionnalité d’autoscaling du stockage Amazon RDS pour gérer automatiquement la capacité.