Where/how does innodb store tables in files?

Question

I read somewhere that InnoDB can store all the tables in a single file or in separate files. Is this a configuration option? What is the default setting? A file for all tables or a file per table? What is the recommended setting?

Answer 1

The setting is innodb_file_per_table, and it's default 0 (ie. use one big file) up to MySQL 5.1, and then in either 5.5 or 5.6 the default changed. Changing this value will not affect tables which already exist.

Even with innodb_file_per_table set to ON you still need the shared tablespace file (ibdata1 file by default).

There is no one recommended setting, it depends on your databases. Some advantages:
- It's easier to reclaim space from the file (you only need to reimport one table)
- It can be faster on ext* filesystems as they use inode level locking, and so splitting the accesses over multiple files will be alleviate this a little

Some disadvantages:
- It takes more space, as reclaimed space can only be used in the same table
- It takes longer to recover from an unclean restart
- It can be a big problem if you have a large number of tables (as you can hit an open files limit)
- I might be wrong with this one, but if the temporary tables aren't in the system tablespace a drop table can take a long time

What problem are you trying to solve?

Answer 2

• Innodb_file_per table controls if a single tablespace is used by InnoDB. If set to "1" a tablespace will be created for each table. It has effect from the time it was applied - existing tables are not changed. Using multiple tablespaces is in particular useful if you often drop large objects (tables or databases) as files will be deleted and the disk space reclaimed by the OS. With the setting of "0" dropping an object will not return diskspace to the OS.
• A set of files with names such as ibdata1, ibdata2, and so on, that make up the InnoDB system tablespace.
• Each InnoDB table created using the file-per-table mode goes into its own tablespace file, with a .ibd extension, inside the database directory. This file contains the table data and any indexes for the table. File-per-table mode, controlled by the innodb_file_per_table option, affects many aspects of InnoDB storage usage and performance, and is enabled by default in MySQL 5.6.7 and higher.

Conclusion:

So, it's always better to use InnoDB_file_per_table option and if are turning on this option in my.cnf make sure that you have taken a backup of InnoDB tables and restore those tables after service restart.Now, you will have all InnoDB tables with separate .ibd files