MySQL: The Truth about InnoDB File Per Table Tablespaces

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In this post, I am going to discuss MySQL InnoDB File-Per-Table tablespaces.
What are the advantages and disadvantages? How to enable and disable it?

By default, The InnoDB stores tables and indexes into the system tablespace. If we want to create separate .idb and .frm file for each table, we should enable innodb_file_per_table.

How to Enable and Disable Innodb_file_per_table?

To Enable:
Add below line into [mysqld] section of my.cnf file and restart the MySQL Server. It will create two separate file for each table, .idb file for data and .frm file for table definition.

To Disable:
Simply, you can remove innodb_file_per_table line from my.cnf and restart the server. Now newly created table stores into common tablespace (ibdata1).

Advantages to enable innodb_file_per_table:

• The Truncate Table operation runs faster when we have separate.idb file.
• We can reclaim disk space by truncating and dropping a single table. On the other side, with the single table space we can use this free space only for new InnoDB data.
• We can easily run OPTIMIZE TABLE on individual Table files.
• We can easily store and move individual table.
• We can use features like compressed and dynamic row formats.
• We can quickly perform backup and restore of individual tables.

Disadvantages to enable innodb_file_per_table:

• The management of each different file is very difficult and time taking exercise.
• Each different file has unused space so we require to perform OPTIMIZE TABLE on each individual file.
• Mysqld has to open file handler per table, which may reduce the performance of MySQL Server.
• For every action of each file buffer pool is scanned, which reduce the performance of MySQL Server.