Differences Between innodb_data_file_path and innodb_file_per_table

Differences Between innodb_data_file_path and innodb_file_per_table
By: Keith Murphy

Recently, a customer wondered if they should start using the innodb_file_per_table option, or if they should continue to use the large InnoDB tablespace files created by the innodb_data_file_path option in the my.cnf option file.

Many people still use the older innodb_data_file_path option because it is the default for MySQL server. So, what are the benefits of using innodb_file_per_table instead?

The innodb_file_per_table makes for easier-to-manage files. With this option each InnoDB table has its own data and index file under the database directory. As an example, if you had table foo located in database xyz the InnoDB data file for table foo would be /var/lib/mysql/data/xyz/foo.idb. Each table would have its own idb table in the appropriate database directory. This is in contrast to using the innodb_data_file_path option with (typically) one large file in the root of your data directory. For example, it might be /var/lib/mysql/data/ibdata1.idb. All table data and indexes would be stored in this one file, and it can be very large and unwieldy. I don’t recall the largest ibdata file I have seen, but what do you do if you have a 100 gig InnoDB tablespace file? It can, and does, happen. The file contains what amounts to all the data of all your databases on the server.

Also, with the innodb_file_per_table option, you know what table is in each file. You know that table foo is in the foo.idb file. No other table’s data or index information is going to be in there. There is still a shared tablespace but it is going to be much smaller and more manageable. It contains the shared meta-data of all the databases, not individual table data. With the shared tablespace option, all data is typically going to be in one file, and you can’t differentiate between databases or tables.

Are there any advantages to using innodb_data_file_path? Glad you asked! There is one. You can specify multiple file paths using multiple innodb_file_per_table options in my.cnf, and “spread the load” over multiple partitions or hard drives. That is the theory. In practice, it is very rarely worth it.

Another question that often comes up is, if we are already using the single tablespace format can we convert to the innodb_file_per_table option? You can convert from one to the other but it will require that you stop writes to the server, perform a complete logical backup (i.e. mysqldump) of the server, shut down the server, change the my.cnf configuration to file per table, and then restart the server. You will then need to drop all the current database and then reimport all your data. Fun!

One last benefit of using innodb_file_per_table is that defragmenting the table (using OPTIMIZE TABLE) will actually free up space in the data file, whereas right now, if you defragment an InnoDB table, the ibdata file will not shrink.

Using Per-Table Tablespaces


Jun 20, 2008
Category: MySQL
Tags: innodb, innodb_data_file_path, innodb_file_per_table
9 Responses

1.
Morgan Tocker says:
June 20, 2008 at 2:06 pm

There’s situations when either one can be a better choice.

There are more disadvantages of using file per table - you end up consuming significantly more file descriptors (which is a problem particularly for MySQL on Windows). The main InnoDB thread that wakes up every few seconds will also have to flush a lot more files when it checkpoints.

You don’t need to mysqldump the data to convert from the shared global tablespace to the file per table model (but you don’t reclaim space unless you do). A CREATE TABLE new_table LIKE old_table; INSERT INTO new_table SELECT * FROM old_table; DROP TABLE old_table; RENAME TABLE new_table to old_table; is the alternative.
2.
Gavin Towey says:
June 20, 2008 at 4:14 pm

You don’t even have to do that much to convert tables from the single tablespace to the individual files. After enabling “innodb_file_per_table” and restarting mysql just do “ALTER TABLE foo ENGINE=InnoDB” and it will rebuild the table and pull it out of the shared tablespace.

However if you ever want to reclaim all that shared tablespace then you’ll have to dump the db, shutdown mysql & delete the tablespace / log & .frm files then edit the my.cnf to re-adjust the shared tablespace file sizes and then restart and let mysql build empty files, then import your data back in.

It’s also been shown that the shared tablespace has a bit faster read/write performance.
3.
Xaprb says:
June 22, 2008 at 7:34 am

With file-per-table, the shared tablespace is still configurable as usual. It just doesn’t have as much data in it, so you can configure it to be smaller. It does have more than meta-data, though. For example: undo logs, insert buffer, doublewrite buffer, etc. Lots of “real” data — the kind of stuff that will cause lost data if you delete it.
4.
phasnox says:
July 2, 2008 at 12:15 pm

Hi, great article..
you said
“You can specify multiple file paths using multiple innodb_file_per_table options in my.cnf, and ?spread the load? over multiple partitions or hard drives. ”

how do you do that?
5.
guzik » Blog Archive » MySQL: multiple tablespaces says:
August 1, 2008 at 10:09 pm

[...] Differences Between innodb_data_file_path and innodb_file_per_table [...]
6.
Dieter says:
August 2, 2008 at 5:31 am

With MyISAM one could easily copy a database directory from one server to another and there it could be used immidialety.

Will this work with a InnoDB database that has been splitted to files too?
7.
ICD says:
August 6, 2008 at 3:27 pm

ON a version 5.0.36sp1-enterprise-gpl MySQL Enterprise Server (GPL)

I have the innodb_file_per_table set on without the innodb_data_file_path set and the
following is true when I check the global variables:
have_innodb has a value of YES
innodb_data_file_path has a value of ibdata1:10M:autoextend
innodb_file_per_table has a value of ON

this to me seems to be a bug. If there will be individual file ibd files why would I have an ibdata1 created? Secondly, anytime I try setting the innodb_data_file_path it turns off the innodb engine and have_innodb winds up having a value of NO. The error when I do set the innodb_data_file_path is
080805 7:16:57 [ERROR] InnoDB: syntax error in innodb_data_file_path

Here is the entry in the my.cnf:
datadir = /data/mysql
innodb_data_home_dir = /data/mysql
innodb_data_file_path = ibdata1:50M;ibdata2:50M:MAX:100M
innodb_file_per_table

Any ideas. I want to have the .ibd per table and not have an ibdata1 which autoextends.
8.
ILD says:
August 6, 2008 at 3:33 pm

Here is my.cnf
datadir = /data/mysql
innodb_data_home_dir = /data/mysql
innodb_data_file_path = ibdata1:50M;ibdata2:50M:MAX:100M
innodb_file_per_table

The above disables innodb.
When I have the following in my.cnf
datadir = /data/mysql
innodb_file_per_table

It correctly enables innodb and it produces individual ibd tables but it winds up with the following default attribute:

innodb_data_file_path = ibdata1:10M:autoextend

How can I turn this off and still have innodb enabled with the innodb_file_per_table turned on?

This seems to be a bug.

Here is the version I am working with
mysql Ver 14.12 Distrib 5.0.36sp1, for unknown-linux-gnu (x86_64) using readline 5.0
9.
herodiade says:
November 29, 2008 at 7:14 am

Actually, you can spread the ibdata files on different places (ie. several raid 10 arrays) just as with innodb_file_per_table.

This is just a matter of setting :
innodb_data_home_dir =
innodb_data_file_path = /d1/ibdata1:1024M;/d2/ibdata2:1024M;/d3/ibdata3:1024M:autoextend

In my opinion, the real case for innodb_file_per_table is the infamous “innodb ibdata files do not shrink” bug.