Enable Innodb File Per Table

Issue Type

Operational

Problem

You are running MySQL without the innodb_file_per_table variable enabled (set to ON).

Not setting this can mean, especially for larger databases, that you could end up with a tables file exceeding what the operating system can manage. A file per table can also allow you to split it across devices if storage constraints become an issue. Additionally there are various write performance related disadvantaged of using a single file (depending on operating system/configurations).

There are a number of documented advantages and disadvantages for enabling this feature.

The recommendation is to enable it and it is enabled by default since MySQL 5.6.

Remediation

You can either change the variable at runtime

SET GLOBAL innodb_file_per_table=ON

Or you can change the variable at startup by modifying your my.cnf configuration

[mysqld]
innodb_file_per_table=ON

External Resources

• File-Per-Table Tablespaces
• MySQL Variables
• Option files
• Using system variables