MySQL innodb ne libère pas d'espace disque après avoir supprimé des lignes de données de la table

Question

J'ai une table de données mysql de type INNODB, qui contient environ 2 millions de lignes de données. Lorsque j'ai supprimé des lignes de données de la table, cela n'a pas libéré l'espace disque alloué. La taille du fichier ibdata1 n'a pas non plus diminué après l'exécution de la commande optimize table commandement.

Existe-t-il un moyen de récupérer l'espace disque de mySQL.

Je suis dans une mauvaise situation, cette application fonctionne sur une cinquantaine de sites différents et le problème du manque d'espace disque apparaît maintenant sur presque tous les sites.

Answer 1

Une autre façon de résoudre le problème de la récupération de l'espace est de créer plusieurs partitions dans la table - des partitions basées sur l'étendue, des partitions basées sur la valeur et il suffit de déposer/trouver la partition pour récupérer l'espace, ce qui libérera l'espace utilisé par l'ensemble des données stockées dans la partition particulière.

Il y aura quelques changements nécessaires dans le schéma de la table lorsque vous introduisez le partitionnement pour votre table comme - les clés uniques, les index pour inclure la colonne de partition, etc.

Answer 2

Si l'OPTIMIZE ne résout pas votre problème, essayez :

ALTER TABLE tbl_name ENGINE=INNODB, ALGORITHM=INPLACE, LOCK=NONE;

ou

ALTER TABLE tbl_name FORCE, ALGORITHM=INPLACE, LOCK=NONE;

Answer 3

Il y a un an, j'ai également rencontré le même problème sur la version mysql5.7 et ibdata1 occupait 150 Go. J'ai donc ajouté des tablespaces d'annulation

Faire une sauvegarde de Mysqldump
Arrêtez le service mysql
Supprimer toutes les données du répertoire de données
Ajoutez le paramètre undo tablespace ci-dessous dans le fichier my.cnf actuel.

 #undo tablespace
  innodb_undo_directory =  /var/lib/mysql/
  innodb_rollback_segments = 128 
  innodb_undo_tablespaces = 3
  innodb_undo_logs = 128  
  innodb_max_undo_log_size=1G
  innodb_undo_log_truncate = ON

Démarrez le service mysql
stocker la sauvegarde mysqldump

Problème résolu !

Answer 4

Il existe plusieurs façons de récupérer de l'espace disque après avoir supprimé des données d'une table pour le moteur MySQL Inodb.

Si vous n'utilisez pas innodb_file_per_table dès le début, vider toutes les données, supprimer tous les fichiers, recréer la base de données et importer à nouveau les données est la seule solution (voir les réponses de FlipMcF ci-dessus).

Si vous utilisez innodb_file_per_table, vous pouvez essayer

1. Si vous pouvez supprimer toutes les données, la commande truncate supprimera les données et récupérera l'espace disque pour vous.
2. La commande Alter table va supprimer et recréer la table afin qu'elle puisse récupérer de l'espace disque. Par conséquent, après avoir supprimé les données, exécutez la commande ALTER TABLE TBL_A qui ne change rien pour libérer le disque dur (par exemple, la table TBL_A a un jeu de caractères uf8, après avoir supprimé les données, exécutez la commande ALTER TABLE TBL_A charset utf8 -> cette commande ne change rien à votre table mais elle permet à mysql de recréer votre table et de regagner de l'espace disque).
3. Créez TBL_B comme TBL_A . Insérez les données que vous souhaitez conserver de la TBL_A dans la TBL_B. Déposez TBL_A, et renommez TBL_B en TBL_A. Cette méthode est très efficace si TBL_A et les données à supprimer sont importantes (la commande de suppression dans MySQL innodb est très peu performante).