Optimiser le stockage Aurora MySQL en supprimant les données inutiles

Une table de notre base de données Aurora MySQL consommait environ 80 % (environ 400 Go) du stockage total. Puisque nous avons pu archiver des données plus anciennes sous forme de fichiers CSV, nous avons décidé de supprimer les anciens enregistrements et de libérer de l'espace de stockage.

Au départ, je pensais que supprimer les enregistrements libérerait de l'espace de stockage, mais cela s'est avéré plus compliqué que prévu. Je documente donc les étapes détaillées pour référence future.

Vérification de l'utilisation du stockage de table

Vous pouvez vérifier la taille de chaque fichier .ibd en utilisant la requête suivante :

SELECT file_name, ROUND(SUM(total_extents * extent_size)/1024/1024/1024,2) AS "TableSizeinGB" 
FROM information_schema.files 
GROUP BY file_name 
ORDER BY total_extents DESC;

Référence : Documentation MySQL

Remarque importante

AWS re:Post a recommandé la requête suivante pour vérifier la taille des tables, mais les résultats de la table cible étaient environ 150 Go plus petits que ceux de la première requête.

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';

Lorsque j'ai consulté AWS Support, ils ont confirmé que information_schema.tables ne fournit que des valeurs statistiques, souvent inexactes. Ils ont conseillé d'utiliser information_schema.files pour obtenir des données précises.

Les informations concernant la taille de la table (390 Go) ont été extraites de information_schema.tables, et comme il s'agit de données statistiques, elles sont probablement inexactes. À l'avenir, nous vous recommandons d'utiliser information_schema.files pour récupérer les informations sur la taille de la table.

Référence : AWS re:Post

Vérification de l'utilisation du stockage de base de données

La requête suivante vérifie l'utilisation globale de la base de données. Cela utilise également information_schema.files pour plus de précision.

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/%';

Étapes pour libérer le stockage de la base de données

Voici les étapes à suivre pour libérer de l'espace de stockage :

1. Supprimer les anciens enregistrements.
2. Changez la classe d'instance si nécessaire.
3. Exécutez OPTIMIZE TABLE ;.

La simple suppression d'enregistrements ne libère pas d'espace de stockage ; vous devez exécuter OPTIMIZE TABLE pour libérer de l'espace.

De plus, lors de l'opération OPTIMIZE TABLE (ou ALTER TABLE ... FORCE), des fichiers de table intermédiaires temporaires sont créés. Dans Aurora, ces fichiers temporaires sont stockés sur le stockage local. La quantité de stockage local dépend de la classe d'instance. Dans mon cas, l'instance db.r6g.xlarge ne dispose que de 80 Go de stockage local, ce qui n'était pas suffisant pour la taille des enregistrements supprimés. J'ai donc temporairement évolué vers db.r6g.8xlarge (640 Go).

Référence : Optimiser le tableau

Référence : Modifier la table

Référence : Exigences d'espace DDL en ligne InnoDB

Référence : stockage temporaire Aurora MySQL

Attention lors de l'utilisation d'OPTIMIZE TABLE

Après avoir supprimé environ 250 Go d'enregistrements, l'exécution d'OPTIMIZE TABLE a pris environ 130 minutes (environ 2 heures). Étant donné qu'OPTIMIZE TABLE verrouille la table, vous devrez peut-être planifier des temps d'arrêt ou effectuer cette opération pendant les heures creuses. Pour référence, il a fallu environ 15 heures au total pour supprimer tous les enregistrements, que j'ai répartis sur plusieurs jours.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!