May 13, 2025 • Written by ROMAN AGABEKOV
Basic Details
The innodb_file_per_table setting determines whether InnoDB stores table and index data in shared tablespace files or in their own individual tablespace files.
innodb_file_per_table – Usage
Historically, InnoDB stored all data and indexes in a single large file: ibdata1. This approach made it difficult to reclaim disk space, backup individual tables, or optimize large tables independently. The innodb_file_per_table variable solves these problems by enabling file-per-table tablespaces.
When innodb_file_per_table is enabled:
When disabled:
In general, keeping innodb_file_per_table enabled is recommended for most modern MySQL workloads.
When innodb_file_per_table is enabled:
- Each new table gets its own .ibd file.
- Disk space can be reclaimed using OPTIMIZE TABLE or DROP TABLE.
- Features like table compression and transportable tablespaces are available.
When disabled:
- All InnoDB data goes into the shared system tablespace.
- Dropping or truncating a table does not reduce disk size.
- Some features like compression and per-table encryption may not be available.
In general, keeping innodb_file_per_table enabled is recommended for most modern MySQL workloads.
innodb_file_per_table – Configuration
The innodb_file_per_table variable must be set in the MySQL configuration file before the server starts. It cannot be changed dynamically.
Configuration File:
Configuration File:
[mysqld]
innodb_file_per_table = = XXSet to 0 to disable, 1 to enable. Restart MySQL to apply changes.
innodb_file_per_table – Considerations
Enabling innodb_file_per_table introduces several benefits, especially for disk space management and per-table control. However, there are some tradeoffs and caveats to be aware of.
Increased Filesystem Overhead
Every InnoDB table (and index) creates an individual .ibd file. On systems with tens or hundreds of thousands of tables, this can result in filesystem strain — longer file access times, inode exhaustion, or backup scripts taking longer due to file count.
Fragmentation and Inefficiency
Frequent DELETE or UPDATE operations without regular OPTIMIZE TABLE may lead to internal fragmentation. This happens regardless of innodb_file_per_table, but with file-per-table enabled, fragmentation is now distributed across many files instead of being contained in a single shared space.
More Complex Backups
Logical backups (e.g., with mysqldump) remain the same, but physical backups may require handling many more files. Some backup tools must walk the full directory tree, and performance may degrade slightly in large schemas with file-per-table enabled.
Leverage Automation
For most production deployments, especially with modern storage, backup systems, and monitoring in place, the benefits of enabling innodb_file_per_table outweigh the drawbacks.
But whether it’s the right setting for your environment depends on more than best practices. It depends on how your database actually behaves: How many tables you have. How often they’re updated or dropped. How much disk I/O they generate. Whether backups are done per-table or whole-instance.
Explore automated tuning solutions such as Releem. Reelem continuously monitors your MySQL server’s performance metrics and workload patterns, then recommends configuration changes, including whether innodb_file_per_table should be enabled or not. It factors in table counts, data size, query volume, disk behavior, and more, so you get settings tuned specifically for your system.
Work smarter, not harder, with Releem.
Increased Filesystem Overhead
Every InnoDB table (and index) creates an individual .ibd file. On systems with tens or hundreds of thousands of tables, this can result in filesystem strain — longer file access times, inode exhaustion, or backup scripts taking longer due to file count.
Fragmentation and Inefficiency
Frequent DELETE or UPDATE operations without regular OPTIMIZE TABLE may lead to internal fragmentation. This happens regardless of innodb_file_per_table, but with file-per-table enabled, fragmentation is now distributed across many files instead of being contained in a single shared space.
More Complex Backups
Logical backups (e.g., with mysqldump) remain the same, but physical backups may require handling many more files. Some backup tools must walk the full directory tree, and performance may degrade slightly in large schemas with file-per-table enabled.
Leverage Automation
For most production deployments, especially with modern storage, backup systems, and monitoring in place, the benefits of enabling innodb_file_per_table outweigh the drawbacks.
But whether it’s the right setting for your environment depends on more than best practices. It depends on how your database actually behaves: How many tables you have. How often they’re updated or dropped. How much disk I/O they generate. Whether backups are done per-table or whole-instance.
Explore automated tuning solutions such as Releem. Reelem continuously monitors your MySQL server’s performance metrics and workload patterns, then recommends configuration changes, including whether innodb_file_per_table should be enabled or not. It factors in table counts, data size, query volume, disk behavior, and more, so you get settings tuned specifically for your system.
Work smarter, not harder, with Releem.
FAQ:
MySQL innodb_file_per_table
Variable
MySQL innodb_file_per_table
Variable
What is the purpose of innodb_file_per_table?
It determines whether each InnoDB table gets its own .ibd file or if all tables are stored together in a shared tablespace.
Is innodb_file_per_table enabled by default?
Yes, for MySQL versions 5.6.6 and later, as well as modern versions of Percona and MariaDB, this setting is ON by default.
Can I enable this without affecting existing tables?
Yes. Existing tables won’t be affected until they’re rebuilt. Use ALTER TABLE to move them to individual tablespaces.
How do I shrink disk space for an InnoDB table?
Run OPTIMIZE TABLE your_table; to reclaim unused space if innodb_file_per_table is enabled.
Can I change innodb_file_per_table at runtime?
No. This variable must be set before the MySQL server starts.
How does Releem handle innodb_file_per_table?
Releem detects the current setting and provides configuration guidance based on database structure, disk usage, and performance metrics.
It determines whether each InnoDB table gets its own .ibd file or if all tables are stored together in a shared tablespace.
Is innodb_file_per_table enabled by default?
Yes, for MySQL versions 5.6.6 and later, as well as modern versions of Percona and MariaDB, this setting is ON by default.
Can I enable this without affecting existing tables?
Yes. Existing tables won’t be affected until they’re rebuilt. Use ALTER TABLE to move them to individual tablespaces.
How do I shrink disk space for an InnoDB table?
Run OPTIMIZE TABLE your_table; to reclaim unused space if innodb_file_per_table is enabled.
Can I change innodb_file_per_table at runtime?
No. This variable must be set before the MySQL server starts.
How does Releem handle innodb_file_per_table?
Releem detects the current setting and provides configuration guidance based on database structure, disk usage, and performance metrics.