Pourquoi mon instance Amazon RDS for MySQL ou MariaDB indique-t-elle une saturation de l'espace de stockage ?

Brève description

Pour résoudre un problème de saturation de l’espace de stockage, vous devez d'abord analyser l'espace total utilisé sur l’instance de base de données. L'espace de l’instance de base de données est utilisé pour les éléments 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 identifié ce qui utilise de l'espace de stockage, vous pouvez récupérer de l’espace de stockage. Vous devrez ensuite surveiller la métrique FreeStorageSpace pour éviter de manquer à nouveau d'espace.

Remarque : si vous constatez une diminution soudaine de l'espace de stockage disponible, vérifiez les requêtes en cours au niveau de l'instance de base de données en exécutant la commande SHOW FULL PROCESSLIST. 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 la commande INFORMATION_SCHEMA.INNODB_TRX ou SHOW ENGINE INNODB STATUS. Vérifiez ensuite la sortie.

Résolution

Analysez la quantité totale d’espace utilisé sur l'instance de base de données (bases de données créées par l'utilisateur)

Pour connaître la taille de chaque base de données créée par l'utilisateur, exécutez la requête suivante :

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;

Pour vérifier la taille de chaque table d’une base de données particulière (dans l’instance de base de données), exécutez la requête suivante :

mysql> SELECT table_schema "DB Name", table_name,(data_length + index_length)/1024/1024/1024 AS "TableSizeinGB" from information_schema.tables where table_schema='database_name';

Pour obtenir des tailles de tables plus précises dans MySQL version 5.7 et ultérieure ou MySQL version 8.0 et ultérieure, utilisez la requête suivante :
Remarque : la requête information_schema.files n'est pas applicable aux moteurs MariaDB.

mysql> SELECT file_name, ROUND(SUM(total_extents * extent_size)/1024/1024/1024,2) AS "TableSizeinGB" from information_schema.files where file_name like '%/database_name/%';

Pour obtenir des informations complètes sur le stockage et identifier l’espace fragmenté approximatif au niveau de la base de données et au niveau de la table, exécutez la requête suivante :
Remarque : Cette requête ne s'applique pas aux tables se trouvant dans un tablespace partagé.

mysql> SELECT table_schema AS "DB_NAME", SUM(size) "DB_SIZE", SUM(fragmented_space) APPROXIMATED_FRAGMENTED_SPACE_GB FROM (SELECT table_schema, table_name, ROUND((data_length+index_length+data_free)/1024/1024/1024,2) AS size, ROUND((data_length - (AVG_ROW_LENGTH*TABLE_ROWS))/1024/1024/1024,2)
    AS fragmented_space FROM information_schema.tables WHERE table_type='BASE TABLE' AND table_schema NOT IN ('performance_schema', 'mysql', 'information_schema') ) AS TEMP GROUP BY DB_NAME ORDER BY APPROXIMATED_FRAGMENTED_SPACE_GB DESC;

mysql> SELECT table_schema DB_NAME, table_name TABLE_NAME, ROUND((data_length+index_length+data_free)/1024/1024/1024,2) SIZE_GB, ROUND((data_length - (AVG_ROW_LENGTH*TABLE_ROWS))/1024/1024/1024,2) APPROXIMATED_FRAGMENTED_SPACE_GB from information_schema.tables
    WHERE table_type='BASE TABLE' AND table_schema NOT IN ('performance_schema', 'mysql', 'information_schema') ORDER BY APPROXIMATED_FRAGMENTED_SPACE_GB DESC;

Enregistrez les tailles des base de données acquises à partir de ces deux requêtes et comparez-les aux métriques Amazon CloudWatch dans Amazon RDS. Vous pouvez alors déterminer si l’espace de stockage est plein en raison de l'utilisation des données.

Les 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. Parfois, le fichier tablespace temporaire peut même s'étendre jusqu'à ibtmp2 dans le répertoire de données MySQL.

Astuce : si la table temporaire (ibtmp1) utilise une trop grande quantité d’espace de stockage, redémarrez l'instance de base de données pour libérer de l'espace.

Les opérations DDL en ligne utilisent des fichiers journaux temporaires pour les opérations suivantes :

• L'enregistrement simultané de fichiers DML
• La création de fichiers de tri temporaires lors de la création d'un index
• La création de fichiers de tables intermédiaires temporaires lors de la reconstruction des tables (afin que les tables temporaires puissent occuper de l'espace de stockage)

Remarque : les tailles de fichiers du tablespace InnoDB ne peuvent être demandées qu'à l'aide de MySQL version 5.7 et ultérieure ou MySQL version 8.0 et ultérieure.

Pour trouver le tablespace temporaire InnoDB, exécutez la requête suivante :

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, reportez-vous à Le tablespace temporaire sur le site Web de MySQL.

Espace de table InnoDB

MySQL crée parfois des tables temporaires internes qui ne peuvent pas être supprimées en parce qu’une requête est en cours d’exécution. Ces tables temporaires ne font pas partie de la table nommée « tables » dans information_schema. Pour plus d'informations, reportez-vous à Utilisation des tables temporaires internes dans MySQL sur le site Web de MySQL.

Exécutez la requête suivante pour rechercher ces tables temporaires internes :

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

Le tablespace système InnoDB est la zone de stockage du dictionnaire de données InnoDB. Outre le dictionnaire de données, le tampon de double écriture, le tampon de modifications et les journaux d'annulation sont également présents dans l'espace de table système InnoDB. De plus, le tablespace peut contenir des données d'index et de table si des tables sont créées dans le tablespace système (au lieu de tablespaces de fichier par table ou de tablespaces généraux).

Exécutez la requête suivante pour trouver le tablespace 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 : cette requête s'exécute sur MySQL version 5.7 et ultérieure ou MySQL version 8.0 et ultérieure.

Une fois la taille de votre tablespace système augmentée, vous ne pouvez plus la réduire. Cependant, vous pouvez vider toutes les tables InnoDB et les importer dans une nouvelle instance de base de données MySQL. Pour éviter les tablespaces système volumineux, pensez à utiliser des tablespaces fichier par table. Pour plus d'informations, reportez-vous à Tablespaces fichier par table sur le site Web de MySQL.

Si vous activez Innodb_file_per_table, chaque table stockera les données et les indexera dans son propre fichier tablespace. Vous pouvez récupérer de l'espace (après fragmentation des bases de données et des tables) en exécutant OPTIMIZE TABLE sur la table en question. La commande OPTIMIZE TABLE crée une copie vide de votre table. Les données de l'ancienne table sont ensuite copiées ligne par ligne dans la nouvelle table. Au cours de ce processus, un nouveau tablespace .ibd est créé et de l'espace est récupéré. Pour plus d'informations sur ce processus, reportez-vous à Instruction OPTIMIZE TABLE sur le site Web de MySQL.

Important : la commande OPTIMIZE TABLE utilise l'algorithme COPY pour créer des tables temporaires de la même taille que la table d'origine. Avant d’exécuter cette commande, vérifiez que vous disposez d'un espace disque suffisant.

Pour optimiser votre table, exécutez la syntaxe de commande suivante :

mysql> OPTIMIZE TABLE <tablename>;

Vous pouvez également reconstruire la table en exécutant la commande suivante :

mysql> ALTER TABLE <table_name> ENGINE=INNODB;

Journaux binaires

Si vous activez les sauvegardes automatiques sur votre instance Amazon RDS, les journaux binaires sont également activés automatiquement sur votre instance de base de données. Ces journaux binaires sont stockés sur le disque et consomment de l'espace de stockage, mais ils sont purgés à chaque configuration de la conservation des journaux binaires. La valeur de conservation des fichiers binaires par défaut pour votre instance est également définie sur « Null », ce qui signifie que 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 pour MySQL. Vous pouvez vérifier le nombre d'heures pendant lesquelles un journal binaire est conservé à l'aide de la syntaxe de la commande mysql.rds_show_configuration :

CALL mysql.rds_show_configuration;

Vous pouvez également réduire cette valeur pour conserver les journaux pendant une période plus courte afin de réduire la quantité d'espace qu’ils utilisent. La valeur NULL signifie que les journaux sont purgés dès que possible. 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 le traitement des journaux binaires sur l'instance active ou des journaux de relais sur l'instance de secours.

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 la purge des journaux binaires sur l'instance active et des journaux de relais sur l'instance de secours. En cas de problèmes de purge ou de réplication, ces 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, reportez-vous à Instruction SHOW BINARY LOGS sur le site web de MySQL.

Si l'instance de base de données fait office d'instance de secours pour la réplication, vérifiez la taille des journaux relais (valeur de Relay_Log_Space) à l'aide de la commande suivante :

SHOW SLAVE STATUS\G

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

Amazon RDS for MySQL fournit des journaux (journaux généraux, journaux des requêtes lentes, journaux d'erreurs) dont vous pouvez vous servir pour contrôler votre base de données. Les journaux d'erreurs sont activés par défaut. Toutefois, les journaux généraux et les journaux de requêtes lentes peuvent être activés via un groupe de paramètres personnalisé sur l'instance RDS. Une fois les journaux de requêtes lentes et les journaux généraux activés, ils sont automatiquement stockés dans les tables slow_log et general_log de la base de données MySQL. Pour vérifier la taille des journaux de requêtes lentes, des journaux généraux (de type « FICHIER ») 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, gérez les journaux MySQL basés sur des tables en exécutant une rotation manuellement les tables de journaux. Pour supprimer complètement les anciennes données et récupérer l'espace disque correspondant, appelez les commandes suivantes deux fois à la 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 le paramètre de sorte que la valeur de log_output pour slow_log et general_log soit « Fichier » au lieu de « Table ».

Il est également recommandé de surveiller l'instance de base de données Amazon RDS à l'aide d'Amazon CloudWatch. Vous pouvez configurer des alarmes CloudWatch sur la métrique FreeStorageSpace pour recevoir des alertes chaque fois que votre espace de stockage descend en dessous d'un certain seuil. Enfin, surveillez la métrique FreeStorageSpace en configurant une alarme CloudWatch afin de recevoir des notifications chaque fois que l’instance de base de données manque d'espace libre. Pour plus d'informations, reportez-vous à 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 ?

Vous pouvez également utiliser la fonctionnalité de mise à l'échelle automatique du stockage Amazon RDS pour gérer automatiquement la capacité. Grâce à la mise à l'échelle automatique du stockage, vous n'avez pas besoin d’augmenter manuellement la capacité de stockage de la base de données. Pour plus d'informations sur la mise à l'échelle automatique du stockage Amazon RDS, reportez-vous à Utilisation du stockage pour les instances de base de données Amazon RDS.

---

Informations connexes

Comment puis-je résoudre les problèmes d’utilisation liés à mon instance de base de données Amazon RDS pour MySQL qui utilise plus de stockage que prévu ?