Differences Between innodb_data_file_path and innodb_file_per_table

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.