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é dans votre instance de base de données. L'espace de votre instance de base de données est utilisé pour les éléments suivants :

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

Après avoir identifié ce qui utilise l'espace de stockage, vous pouvez récupérer de l’espace de stockage. Ensuite, surveillez 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 une longue période, exécutez la commande INFORMATION_SCHEMA.INNODB_TRX ou SHOW ENGINE INNODB STATUS. Examinez ensuite la sortie.

Solution

Analysez l'espace total utilisé dans l'instance de base de données (bases de données créées par l'utilisateur)

Pour trouver 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 votre 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 ou postérieure ou MySQL 8.0 et versions postérieures, 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 ainsi qu'une estimation de l'espace fragmenté au niveau de la base de données et de la table, exécutez la requête suivante :
Remarque : cette requête ne s'applique pas aux tables se trouvant dans un espace de table 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 ensuite déterminer si la saturation de l'espace de stockage est liée à 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 d'espace de table temporaire nommé ibtmp1. Parfois, le fichier d'espace de table temporaire peut même s'étendre à ibtmp2 dans le répertoire de données MySQL.

Conseil : Si la table temporaire (ibtmp1) utilise une quantité excessive 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 :

• Enregistrement d'opérations DML simultanées
• Création de fichiers de tri temporaires lors de la création d'un index
• Création de fichiers de tables intermédiaires temporaires lors de la reconstruction de tables (de sorte que les tables temporaires puissent occuper de l'espace de stockage)

Remarque : la taille des fichiers de l'espace de table InnoDB peut être interrogée uniquement à l'aide de MySQL version 5.7 et supérieure ou de MySQL version 8.0 et supérieure.

Pour trouver l'espace de table 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 d'espace de table temporaire global, redémarrez le serveur MySQL ou réamorcez votre instance de base de données. Pour en savoir plus, consultez la rubrique The temporary tablespace sur le site Internet MySQL.

Espace de table InnoDB

Parfois, MySQL crée des tables temporaires internes qui ne peuvent pas être supprimées, car 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 en savoir plus, consultez la rubrique Internal temporary table use in MySQL sur le site Internet MySQL.

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

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

L'espace de table 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. Par ailleurs, l'espace de table peut contenir des données d'index et de tables si des tables sont créées dans l'espace de table système (et non dans des espaces de table généraux ou des espaces à un fichier par table).

Exécutez la requête suivante pour trouver l'espace de table 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 dans MySQL version 5.7 et supérieure ou dans MySQL version 8.0 et supérieure.

Vous ne pouvez pas réduire la taille de votre espace de table système après l'avoir augmentée. Toutefois, vous pouvez vider toutes vos tables InnoDB et les importer dans une nouvelle instance de base de données MySQL. Pour éviter les espaces de table système volumineux, envisagez d'utiliser des espaces de table à un fichier par table. Pour en savoir plus, consultez Espaces de table fichier-par-table sur le site Internet MySQL.

Si vous activez Innodb_file_per_table, chaque table stocke les données et les index dans son propre fichier d'espace de table. Vous pouvez récupérer de l'espace (de la 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 nouvelle copie vide de votre table. Les données de l'ancienne table sont ensuite copiées ligne par ligne dans la nouvelle table. Pendant ce processus, un nouvel espace de table .ibd est créé et de l'espace est récupéré. Pour en savoir plus sur ce processus, consultez Déclaration OPTIMIZE TABLE sur le site Internet 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. Vérifiez que vous disposez d'un espace disque suffisant avant d'exécuter cette commande.

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 automatisées 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 rétention des journaux binaires. La valeur de rétention des journaux binaires par défaut de votre instance est également définie sur « Null » (Zéro), ce qui signifie que le fichier est immédiatement supprimé.

Pour éviter les problèmes liés à un espace de stockage insuffisant, définissez la période de rétention des journaux binaires appropriée dans Amazon RDS for MySQL. Vous pouvez examiner le nombre d'heures de rétention d'un journal binaire avec la syntaxe de commande mysql.rds_show_configuration :

CALL mysql.rds_show_configuration;

Vous pouvez également réduire cette valeur de façon à conserver les journaux sur une période plus courte et ainsi réduire l'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, contrôlez la métrique ReplicaLag sur l'instance de secours. La métrique ReplicaLag indique tout retard survenant pendant le traitement du journal binaire sur l'instance active ou les journaux relais de l'instance de secours.

S'il existe une instance de secours pour l'instance active, contrôlez la métrique ReplicaLag sur l'instance de secours. La métrique ReplicaLag indique tout retard pendant la purge des journaux binaires sur l'instance active et du journal 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 en savoir plus, consultez la rubrique SHOW BINARY LOGS statement sur le site Informations 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 des 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, etc.) dont vous pouvez vous servir pour contrôler votre base de données. Les journaux d'erreurs sont actifs par défaut. Toutefois, les journaux généraux et les journaux des requêtes lentes peuvent être activés via un groupe de paramètres personnalisé sur l'instance RDS. Une fois que les journaux des requêtes lentes et les journaux généraux sont activés, ils sont automatiquement stockés dans les tables slow_log et general_log dans la base de données MySQL. Pour vérifier la taille des requêtes lentes, des journaux généraux (de type « FILE ») et des journaux d'erreurs, affichez et répertoriez les fichiers journaux de base de données.

Si les tables des journaux des requêtes lentes ou des journaux généraux utilisent une trop grande quantité de stockage, gérez les journaux MySQL basés sur une table en assurant une rotation manuelle des 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 de taille de fichier précise pour les journaux. Modifiez le paramètre de sorte que la valeur de log_output associée à slow_log et general_log corresponde à « File », et non à « 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 pour recevoir une notification chaque fois que votre instance de base de données est à court d'espace libre. Pour en savoir plus, consultez Comment créer des alarmes CloudWatch pour surveiller l'espace de stockage Amazon RDS libre 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é. Avec 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 en savoir plus sur la mise à l'échelle automatique du stockage Amazon RDS, consultez Utilisation du stockage pour les instances de base de données Amazon RDS.

---

Informations connexes

Comment résoudre les problèmes liés à mon instance de base de données Amazon RDS for MySQL lorsqu'elle utilise plus d'espace de stockage que prévu ?