Comment réduire/supprimer le fichier ibdata1 dans MySQL ?

Question

J'utilise MySQL en localhost comme "outil de requête" pour effectuer des statistiques dans R, c'est-à-dire que chaque fois que j'exécute un script de R, je crée une nouvelle base de données (A), je crée une nouvelle table (B), j'importe les données dans B, je soumets une requête pour obtenir ce dont j'ai besoin, puis je laisse tomber B et laisse tomber A.

Cela fonctionne bien pour moi, mais je me rends compte que la taille du fichier ibdata augmente rapidement, je n'ai rien stocké dans MySQL, mais le fichier ibdata1 a déjà dépassé 100 Mo.

J'utilise plus ou moins les paramètres par défaut de MySQL pour la configuration, y a-t-il un moyen pour que je puisse automatiquement réduire/supprimer le fichier ibdata1 après une période de temps fixe ?

Answer 1

Ce ibdata1 n'est pas rétréci est une fonctionnalité particulièrement ennuyeuse de MySQL. Le site ibdata1 ne peut pas être réduit à moins de supprimer toutes les bases de données, de supprimer les fichiers et de recharger un dump.

Mais vous pouvez configurer MySQL pour que chaque table, y compris ses index, soit stockée dans un fichier séparé. De cette manière ibdata1 ne grandira pas autant. Selon Commentaire de Bill Karwin ceci est activé par défaut à partir de la version 5.6.6 de MySQL.

Ça fait un moment que j'ai fait ça. Cependant, pour configurer votre serveur afin d'utiliser des fichiers distincts pour chaque table, vous devez modifier les éléments suivants my.cnf afin de le permettre :

[mysqld]
innodb_file_per_table=1

https://dev.mysql.com/doc/refman/5.6/en/innodb-file-per-table-tablespaces.html

Comme vous voulez récupérer l'espace de ibdata1 vous devez en fait supprimer le fichier :

1. Faites un mysqldump de toutes les bases de données, procédures, déclencheurs, etc. sauf le mysql et performance_schema bases de données
2. Abandonner toutes les bases de données sauf les 2 bases de données ci-dessus
3. Arrêtez mysql
4. Supprimer ibdata1 et ib_log fichiers
5. Démarrer mysql
6. Restaurer à partir d'une décharge

Lorsque vous démarrez MySQL à l'étape 5, le ibdata1 et ib_log Les fichiers seront recréés.

Maintenant tu es prêt à partir. Lorsque vous créez une nouvelle base de données à des fins d'analyse, les tables se trouvent dans des fichiers séparés. ibd* et non dans ibdata1 . Comme vous abandonnez généralement la base de données peu de temps après, les ibd* les fichiers seront supprimés.

http://dev.mysql.com/doc/refman/5.1/en/drop-database.html

Vous avez probablement vu ceci :
http://bugs.mysql.com/bug.php?id=1341

En utilisant la commande ALTER TABLE <tablename> ENGINE=innodb ou OPTIMIZE TABLE <tablename> on peut extraire les pages de données et d'index de ibdata1 dans des fichiers séparés. Cependant, ibdata1 ne sera pas réduit si vous ne faites pas les étapes ci-dessus.

En ce qui concerne le information_schema qu'il n'est ni nécessaire ni possible de laisser tomber. Il s'agit en fait d'un ensemble de vues en lecture seule, et non de tables. Et il n'y a aucun fichier associé à ces vues, pas même un répertoire de base de données. Le site informations_schema utilise la mémoire db-engine et est abandonné et régénéré lors de l'arrêt/redémarrage de mysqld. Voir https://dev.mysql.com/doc/refman/5.7/en/information-schema.html .

Answer 2

Lorsque vous supprimez des tables innodb, MySQL ne libère pas l'espace dans le fichier ibdata, c'est pourquoi il continue de croître. Ces fichiers ne rétrécissent presque jamais.

Comment réduire un fichier ibdata existant :

https://dev.mysql.com/doc/refman/5.6/en/innodb-system-tablespace.html#innodb-resize-system-tablespace

Vous pouvez script ceci et programmer le script pour qu'il s'exécute après une période de temps fixe, mais pour la configuration décrite ci-dessus, il semble que plusieurs tablespaces soient une solution plus facile.

Si vous utilisez l'option de configuration innodb_file_per_table vous créez plusieurs tablespaces. C'est-à-dire que MySQL crée des fichiers séparés pour chaque table au lieu d'un seul fichier partagé. Ces fichiers séparés sont stockés dans le répertoire de la base de données, et ils sont supprimés lorsque vous supprimez cette base de données. Cela devrait supprimer la nécessité de réduire/supprimer les fichiers ibdata dans votre cas.

Plus d'informations sur les tablespaces multiples :

https://dev.mysql.com/doc/refman/5.6/en/innodb-file-per-table-tablespaces.html

Answer 3

Si vous utilisez le moteur de stockage InnoDB pour (certaines) de vos tables MySQL, vous avez probablement déjà rencontré un problème avec sa configuration par défaut. Comme vous l'avez peut-être remarqué, dans le répertoire de données de votre MySQL (sous Debian/Ubuntu - /var/lib/mysql) se trouve un fichier appelé "ibdata1". Il contient presque toutes les données InnoDB (ce n'est pas un journal des transactions) de l'instance MySQL et peut devenir assez gros. Par défaut, ce fichier a une taille initiale de 10Mo et il s'étend automatiquement. Malheureusement, par conception, les fichiers de données InnoDB ne peuvent pas être réduits. C'est pourquoi les DELETEs, TRUNCATEs, DROPs, etc. ne récupéreront pas l'espace utilisé par le fichier.

Je pense que vous pouvez y trouver une bonne explication et une bonne solution :

http://vdachev.net/2007/02/22/mysql-reducing-ibdata1/

Answer 4

Si votre objectif est de surveiller l'espace libre de MySQL et que vous ne pouvez pas empêcher MySQL de réduire votre fichier ibdata, alors obtenez-le grâce aux commandes d'état des tables. Exemple :

MySQL > 5.1.24 :

mysqlshow --status myInnodbDatabase myTable | awk '{print $20}'

MySQL < 5.1.24 :

mysqlshow --status myInnodbDatabase myTable | awk '{print $35}'

Comparez ensuite cette valeur à votre fichier ibdata :

du -b ibdata1

Source : http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html

Answer 5

Comme nous l'avons déjà noté, vous ne pouvez pas réduire ibdata1 (pour le faire, vous devez vider et reconstruire), mais il n'y a souvent aucun besoin réel de le faire.

En utilisant autoextend (probablement le paramètre de taille le plus courant) ibdata1 préalloue le stockage, augmentant chaque fois qu'il est presque plein. Cela rend les écritures plus rapides car l'espace est déjà alloué.

Lorsque vous supprimez des données, elles ne rétrécissent pas mais l'espace à l'intérieur du fichier est marqué comme inutilisé. Maintenant, lorsque vous insérez de nouvelles données, l'espace vide est réutilisé dans le fichier avant de le faire croître davantage.

Donc, il ne continuera à se développer que si vous avez réellement besoin de ces données. À moins que vous n'ayez besoin de cet espace pour une autre application, il n'y a probablement aucune raison de le réduire.