Home

Convert global InnoDB tablespace into per-table files

Mon, 2009.08.24 - 09:57 — müzso
By default MySQL stores InnoDB tables and indexes in a single file called "ibdata1" (which is on Debian in the /var/lib/mysql directory). Unfortunately this file is never "recycled", thus once space is allocated to it, it's never released (see this feature request). Not even after dropping the database that held the given table. However there's an InnoDB option called innodb_file_per_table that allows you to store each InnoDB table in a separate file (just as MyISAM does) and after dropping the table the file is removed and thus the occupied space is recycled.

So the solution is to use this option. But it affects only newly created tables, there's no automatic table conversion involved. However executing an ALTER TABLE ... ENGINE=InnoDB on the given table acts just as if you did drop the table and loaded it back from a backup. You can use the following query to create the ALTER TABLE statements for all of your InnoDB tables:
SELECT CONCAT('ALTER TABLE `', t.`TABLE_SCHEMA`, '`.`', t.`TABLE_NAME`, '` ENGINE=', t.`ENGINE`, ';') AS stmt
FROM `information_schema`.`TABLES` t
WHERE t.`ENGINE` = 'InnoDB'
ORDER BY t.`TABLE_SCHEMA`, t.`TABLE_NAME`

One thing to note is that the above procedure will not decrease the size of your current ibdata1 file. To achieve that you've only one option. Instead of executing "alter table" commands, you've to dump/backup _all_ your InnoDB tables, drop all these tables, add the extra innodb option to my.cnf and import the innodb tables back into the database. A more complete checklist:
  1. Backup/dump all InnoDB tables.
  2. Drop all InnoDB tables.
  3. Stop MySQL.
  4. Add the innodb_file_per_table option to my.cnf (to the [mysqld] section).
  5. Delete the ibdata1 file and any ib_logfile* files in the MySQL database directory.
  6. Start MySQL.
  7. Import tables from the backup that you created in the first step.

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
Thu, 2010.04.29 - 17:55 — bpelhos (not verified)

Why not this way?

(0. Backup/dump all InnoDB tables for safety reasons only.)
1. Stop MySQL.
2. Add the innodb_file_per_table option to my.cnf (to the [mysqld] section).
3. Start MySQL.
4. ALTER all InnoDB tables.
5. Delete the ibdata1 file and any ib_logfile* files in the MySQL database directory.

Thu, 2010.04.29 - 18:34 — müzso

Re: Why not this way?

Because despite the innodb_file_per_table option, the InnoDB engine will still store some (not much) table related data in the global tablespace file (ibdata1). If you do delete ibdata1, MySQL will automatically generate a new one and you'll loose access to all your InnoDB tables (MySQL won't find/list them if you try to reference them in a query, insert, update, delete, ...). Unfortunately I had to learn this the hard way. To move your InnoDB tables into a per-file storage and get rid of the old (large) ibdata1 file at the same time, you've to export/dump your table definition+data, add the innodb_file_per_table option, drop the global tablespace file (ibdata1) and re-create the tables and load their data. There's simply no other way (AFAIK).

And from this there're a few facts that follow:
  • You cannot transport an InnoDB table from one server to the other by just moving the tablespace file (*.ibd) and the table descriptor (*.frm) of the given table. If you do try this, you'll find that on the destination server MySQL doesn't see the new InnoDB table. If you try to hack yourself through by first creating the table with the same CREATE TABLE statement as was used in the source database, and then shutting down MySQL and replacing the ibd+frm files with the ones that you copied over from the other server, you'll get an error message during startup of the database. The only problem preventing this from working is that InnoDB records some kind of a table "index" both in the per-table ibd file and in the global tablespace file (ibdata1). There's a counter and upon the creation of a new InnoDB table the value of this counter is incremented and the current value is assigned to the new table (and this is saved both in the global tablespace and the per-table file). And if these identifiers do not match, MySQL reports an error. There're a few blog posts that describe methods of hacking the global tablespace file to allow this kind of InnoDB table "import", but they're certainly not for the faint of heart and require hours of work/digging (in the easier case creating new "dummy" InnoDB tables to forcefully increase the counter in the global tablespace to the value in the imported table or hexediting the global tablespace file).
  • If you back up your InnoDB tables by backing up the table files, you must make a copy of the global tablespace file too. The InnoDB table files (*.idb) are pretty much worthless without the respective ibdata1 file.
Wed, 2013.03.13 - 17:22 — BUGHUNTER (not verified)

Please be specific about versions when writing about software

Thanks for your hints, quite on top for google search on this issue - however, I would like to ask you for completing this post with the relevant VERSION NUMBERS that you are writing about. Only writing about "MySQL" is not very precise.

Also it would be good, if you would take care for your blog posting, once released into the world - please keep it up-to-date - is this still valid for newer versions of MySQL? What about MariaDB?

Thanks!

Wed, 2013.03.13 - 18:27 — müzso

Re: Please be specific about versions when writing about ...

You're mostly right. I did not add any version numbers to my post. Afaik all MySQL versions (that have support for InnoDB) released up to the date of my post behaved quite the same way regarding the aspects of tablespace management that I described in my post. As far as I can remember I used something like 5.0.*, but I could not be sure after (almost) four years. I did not add a specific version number, because I thought it was not relevant (at least at that time). I admit that meanwhile things might have changed, newer MySQL versions might work differently.

As for keeping my posts up-to-date, you must surely not be entirely serious. This is a personal (mostly technical) blog and reflects stuff that I encounter during my daytime job or my free time hobby projects. I do have a quite fulfilling life (outside my virtual or professional activities), I spend only as much time blogging as it fits my needs and schedule. If later on I revisit a problem that I've blogged about, I used to update the respective post. Here're a few examples where you'll find "update"s (sometimes added years after the original publishing of the post):
I most definitely won't spend time on updating posts about problems that I've not faced ever again after I wrote about them.