MySQL innodb_file_per_table Disk Space Management

This article explores how the innodb_file_per_table configuration affects disk space management in MySQL. We will examine the operational differences between storing all data in a single shared system tablespace versus utilizing individual table files, focusing on disk space reclamation, fragmentation, storage allocation, and administrative overhead.

Shared Tablespace vs. Individual Table Files

By default in modern MySQL versions, innodb_file_per_table is enabled. This setting dictates where InnoDB stores its data and indexes:

• When Disabled (OFF): All tables and indexes are stored inside the shared system tablespace, typically represented by one or more ibdata files (e.g., ibdata1) in the MySQL data directory.
• When Enabled (ON): MySQL creates a separate .ibd data file for each newly created table inside the respective database directory.

Reclaiming Disk Space

The most significant implication of the innodb_file_per_table setting lies in how the operating system reclaims disk space.

The Shared Tablespace Limitation

When innodb_file_per_table is disabled, dropping a table or deleting large volumes of rows does not reduce the physical size of the ibdata1 file on the operating system’s filesystem.

While InnoDB marks the deleted space as “free” internally and will reuse it for future inserts, the disk space remains allocated to MySQL. The only way to shrink a bloated ibdata1 file is to perform a full logical backup (using mysqldump or mysqlpump), stop the MySQL service, delete the database files, reinitialize the data directory, and restore the backup.

The File-Per-Table Advantage

When innodb_file_per_table is enabled, reclaiming disk space is straightforward:

• Dropping a Table: Dropping a table immediately deletes its corresponding .ibd file, freeing up disk space at the operating system level instantly.
• Truncating a Table: Running TRUNCATE TABLE recreates the table and releases the old storage space back to the OS.
• Optimizing a Table: If a table has undergone heavy write operations resulting in unused allocated space, running OPTIMIZE TABLE (which internally executes ALTER TABLE ... ENGINE=InnoDB) rebuilds the table. This processes defragmentation and shrinks the .ibd file to its actual data size, returning the unused disk space to the OS.

Storage Fragmentation

Database fragmentation behaves differently depending on this setting:

• Shared Tablespace: Internal fragmentation is pooled. If Table A deletes 10GB of data, Table B can utilize that vacant 10GB of space inside ibdata1. This can prevent the physical database size from growing if overall data sizes remain stable.
• File-Per-Table: Fragmentation is isolated to each individual .ibd file. If Table A frees up 10GB of space, that space cannot be utilized by Table B unless Table A is rebuilt to release the space to the operating system first.

OS-Level File Management and Performance

Using individual table files introduces operating system-level implications:

• File Descriptors: With innodb_file_per_table enabled, MySQL must open a separate file handle for every active table. For databases with tens of thousands of tables, this requires adjusting the open_files_limit in MySQL and the file descriptor limits in the operating system.
• Filesystem Fragmentation: Over time, creating and expanding thousands of individual .ibd files can lead to physical disk fragmentation at the filesystem level, potentially impacting spinning hard drive (HDD) performance, though this is less of a concern on modern Solid State Drives (SSDs).
• Administrative Flexibility: Having individual .ibd files allows administrators to move specific tables to different storage volumes using the DATA DIRECTORY clause, or easily copy individual tables between databases using the DISCARD TABLESPACE and IMPORT TABLESPACE commands.