MySQL: The innodb_file_per_table misconception

Mistakes in the conception phase of a project often pay off in a far far later point in time. This also applies for the setting innodb_file_per_table in MySQL.

Prio to MySQL 5.6 this option is disabled by default which leads to one huge ibdata1 file being created on your machine for all your databases. The problem with this is that the space allocated on your disk can never be freed again. So if you often import and delete databases (e.g. on your local development machine) the file gets bigger and bigger and there is no way to reduce the size again!
The only way is to export all data, remove all databases and import it again with the innodb_file_per_table option activated (see also http://stackoverflow.com/questions/3456159/how-to-shrink-purge-ibdata1-file-in-mysql)

If innodb_file_per_table setting is active (as in MySQL 5.6 by default) there is not one huge file but single .ibd files per table. Therewith if you delete one table/database the .ibd files will also be removed and the space is yours again.

So perhaps you should keep that in mind if you set up a MySQL server prio to 5.6.

Leave a Reply

Your email address will not be published. Required fields are marked *