Comment puis-je déterminer ce qui utilise l'espace de stockage d'une instance de base de données Amazon RDS exécutant MySQL ou MariaDB ?

Date de la dernière mise à jour : 13/03/2020

Comment puis-je déterminer ce qui utilise l'espace de stockage d'une instance de base de données Amazon Relational Database Service (Amazon RDS) exécutant MySQL ou MariaDB ?

Courte description

Analysez la quantité totale d'espace utilisé sur l'instance de base de données. L'espace sur l'instance de base de données est utilisé pour les éléments suivants :

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

Après avoir identifié ce qui utilise l'espace de stockage disponible, vous pouvez en récupérer. Vous devrez cependant, ensuite, surveiller la métrique FreeStorageSpace pour éviter d'en manquer à l'avenir.

Solution

Analyser la quantité totale d'espace utilisé sur l'instance de base de données

Exécutez la requête suivante pour trouver 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;

Exécutez les requêtes suivantes pour identifier l'espace fragmenté approximatif au niveau de la base de données et de la table :

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;

Remarque : si vous utilisez une version de moteur de base de données supérieure à MySQL 5.7 ou MariaDB 10.2.1, vous pouvez extraire une taille de base de données ou de table plus précise en utilisant les requêtes suivantes avec la commande information_schema :

mysql> SELECT SUBSTRING_INDEX(it.name, '/', 1) AS table_schema,
ROUND(SUM(its.allocated_size)/1024/1024/1024, 2) "size in GB" FROM
information_schema.innodb_sys_tables it INNER JOIN information_schema.innodb_sys_tablespaces its ON it.space =
its.space GROUP BY 1 ORDER BY 2 DESC;
mysql> SELECT SUBSTRING_INDEX(it.name, '/', 1) AS table_schema, ROUND(SUM(its.allocated_size)/1024/1024/1024,2) "size in GB",
ROUND(SUM(t.data_free)/1024/1024/1024,2) "fragmented size in GB"  FROM
information_schema.innodb_sys_tables it INNER
JOIN  information_schema.innodb_sys_tablespaces
its ON it.space = its.space INNER JOIN information_schema.innodb_sys_tablestats istat ON istat.table_id =
it.table_id INNER JOIN information_schema.tables t ON t.table_schema = SUBSTRING_INDEX(it.name,
'/', 1) AND t.table_name = SUBSTRING_INDEX(it.name, '/', -1) GROUP BY 1 ORDER BY 2 DESC;
mysql> SELECT SUBSTRING_INDEX(it.name, '/', 1) AS
table_schema, t.table_name, ROUND(its.allocated_size/1024/1024/1024,2)
"size in GB", ROUND(t.data_free/1024/1024/1024,2) "fragmented
size in GB"  FROM information_schema.innodb_sys_tables
it INNER JOIN information_schema.innodb_sys_tablespaces its ON it.space = its.space INNER JOIN
information_schema.innodb_sys_tablestats istat ON istat.table_id = it.table_id INNER JOIN information_schema.tables t
ON t.table_schema = SUBSTRING_INDEX(it.name, '/', 1) AND t.table_name =
SUBSTRING_INDEX(it.name, '/', -1)  WHERE
t.table_schema NOT IN ('performance_schema', 'mysql', 'information_schema')
ORDER BY 4 DESC;

Exécutez la commande suivante pour récupérer le nom et la taille de chaque fichier journal binaire disponible sur l'instance de base de données :

SHOW MASTER LOGS;

Vérifiez la taille des journaux des requêtes lentes, généraux et d'erreurs à l'aide de la console Amazon RDS ou de l'interface de ligne de commande AWS (CLI AWS).

Remarque : par défaut, les journaux des requêtes lentes et les journaux généraux sont respectivement stockés dans les tables slow_log et general_log au sein de la base de données MySQL. Les tables n'indiquent pas une taille de fichier exacte pour les journaux. Modifiez le paramètre de sorte que la valeur log_output associée à slow_log et general_log corresponde à File, et non à Table.

Dans certains cas, 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 la commande information_schema. Pour obtenir des informations supplémentaires, consultez la documentation MySQL relative à l'utilisation d'une table temporaire interne dans MySQL. Exécutez la requête suivante pour identifier ces tables temporaires internes :

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

La taille de fichier des espaces de table InnoDB ne peut pas être interrogée dans les versions 5.5 et 5.6 de MySQL, mais la version 5.7 et les versions ultérieures de MySQL le permettent. 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%';

Exécutez la requête suivante pour identifier l'espace de table 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%';

Si l'instance de base de données fait office d'instance de secours pour la réplication, vous pouvez vérifier la taille des journaux relais à l'aide de la valeur associée à Relay_Log_Space dans la commande suivante :

SHOW SLAVE STATUS\G

Récupération d'espace de stockage

Pour récupérer de l'espace par fragmentation au niveau des bases de données utilisateur et des tables, optimisez la table InnoDB. Avec les tables InnoDB, l'instruction OPTIMIZE TABLE est mappée avec l'instruction ALTER TABLE... La commande FORCE permet de recréer la table afin de mettre à jour les statistiques d'index, ainsi que de libérer de l'espace inutilisé dans l'index se trouvant au sein du cluster. Pour obtenir des informations supplémentaires, consultez la documentation MySQL relative à l'instruction OPTIMIZE TABLE. La commande suivante inclut l'instruction OPTIMIZE TABLE :

mysql> OPTIMIZE TABLE foo;

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

mysql> ALTER TABLE <table_name> ENGINE=INNODB;

Important : cette commande inclut l'instruction COPY ALGORITHM permettant de créer des tables temporaires qui sont de la même taille que la table d'origine. Vérifiez que l'espace disque disponible est suffisant avant d'exécuter cette commande.

Dans MySQL 5.7 et ses versions ultérieures, si la table temporaire (ibtmp1) implique une utilisation trop importante du stockage, redémarrez l'instance de base de données pour libérer de l'espace.

Si les tables se rapportant au journal associé aux requêtes lentes, ainsi qu'au journal général, impliquent une utilisation trop importante du stockage, gérez les journaux MySQL basés sur une table en modifiant manuellement les tables de journaux. Pour supprimer totalement les anciennes données et récupérer l'espace correspondant, exécutez deux fois les commandes suivantes, successivement :

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

Éviter que des problèmes d'espace de stockage faible ne se produisent à l'avenir

La période de rétention des journaux binaires détermine combien de temps les journaux binaires sont stockés sur l'instance de base de données. Pour vérifier la période de conservation des journaux binaires, exécutez la commande suivante :

mysql> CALL mysql.rds_show_configuration;

Vous pouvez également réduire cette valeur pour conserver les journaux pendant une plus courte durée et, par conséquent, 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, surveillez la métrique ReplicaLag au niveau de l'instance de secours. En effet, cette situation retarde, au niveau de l'instance de secours, le traitement des journaux binaires avec l'instance active et le journal relais.

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;. Cette commande permet de fournir les détails de toutes les connexions actives, les requêtes qui sont exécutées par chaque connexion et la durée pendant laquelle la requête a été dans son état actuel. Pour passer en revue les transactions qui ont été actives pendant longtemps, exécutez la commande SHOW ENGINE INNODB STATUS; ou SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX. Vérifiez ensuite la sortie.

Enfin, surveillez la métrique FreeStorageSpace en configurant une alarme Amazon CloudWatch afin de recevoir une notification lorsque l'instance de base de données n'a presque plus d'espace libre.