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 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.