MySQL 5.5 Reference Manual Including MySQL NDB Cluster 7.2 Reference Guide File-Per-Table Tablespaces

A file-per-table tablespace contains data and indexes for a single InnoDB table, and is stored on the file system in its own data file.

File-per-table tablespace characteristics are described under the following topics in this section:

File-Per-Table Tablespace Configuration

InnoDB creates tables in the shared system tablespace by default. To have InnoDB create tables in file-per-table tablespaces instead, you can enable the innodb_file_per_table variable.

An innodb_file_per_table setting can be specified in an option file or configured at runtime using a SET GLOBAL statement. Changing the setting at runtime requires privileges sufficient to set global system variables. See Section, “System Variable Privileges”.

Option file:


Using SET GLOBAL at runtime:

mysql> SET GLOBAL innodb_file_per_table=ON;

Enabling innodb_file_per_table causes a table-copying ALTER TABLE operation to implicitly move a table that resides in the system tablespace to a file-per-table tablespace. A table-copying ALTER TABLE operation recreates the table using the current innodb_file_per_table setting. This behavior does not apply when adding or dropping secondary indexes. To perform a table-copying ALTER TABLE operation on a table that resides in the system tablespace without moving the table to a file-per-table tablespace, ensure that innodb_file_per_table is disabled before executing the operation.

File-Per-Table Tablespace Data Files

A file-per-table tablespace is created in an .idb data file in a schema directory under the MySQL data directory. The .ibd file is named for the table (table_name.ibd). For example, the data file for table test.t1 is created in the test directory under the MySQL data directory:

mysql> USE test;  
mysql> CREATE TABLE t1 (
   name VARCHAR(100) 
 ) ENGINE = InnoDB; 
shell> cd /path/to/mysql/data/test
shell> ls 
File-Per-Table Tablespace Advantages

File-per-table tablespaces have the following advantages over the shared system tablespace.

  • Disk space is returned to the operating system after truncating or dropping a table created in a file-per-table tablespace. Truncating or dropping a table stored in the system tablespace creates free space within the system tablespace, which can only be used for InnoDB data. In other words, a system tablespace does not shrink in size after a table is truncated or dropped.

  • A table-copying ALTER TABLE operation on a table that resides in the system tablespace can increase the amount of disk space occupied by the tablespace. Such operations may require as much additional space as the data in the table plus indexes. This space is not released back to the operating system as it is for file-per-table tablespaces.

  • TRUNCATE TABLE performance is better when executed on tables that reside in file-per-table tablespaces.

  • Tables created in file-per-table tablespaces use the Barracuda file format. See Section 14.13, “InnoDB File-Format Management”. The Barracuda file format enables features associated with DYNAMIC and COMPRESSED row formats. See Section 14.14, “InnoDB Row Formats”.

  • Tables stored in individual tablespace data files can save time and improve chances for a successful recovery when data corruption occurs, when backups or binary logs are unavailable, or when the MySQL server instance cannot be restarted.

  • You can backup or restore tables created in file-per-table tablespaces quickly using MySQL Enterprise Backup, without interrupting the use of other InnoDB tables. This is beneficial for tables on varying backup schedules or that require backup less frequently. See Making a Partial Backup for details.

  • File-per-table tablespaces permit monitoring table size on the file system by monitoring the size of the tablespace data file.

  • Common Linux file systems do not permit concurrent writes to a single file such as a system tablespace data file when innodb_flush_method is set to O_DIRECT. As a result, there are possible performance improvements when using file-per-table tablespaces in conjunction with this setting.

  • Tables in the shared system tablespace, which contains other structures such as the InnoDB data dictionary and undo logs, are limited in size by the 64TB tablespace size limit. By comparison, each file-per-table tablespace has a 64TB size limit, which provides plenty of room for individual tables to grow in size.

File-Per-Table Tablespace Disadvantages

File-per-table tablespaces have the following disadvantages compared to the shared system tablespace.

  • With file-per-table tablespaces, each table may have unused space that can only be utilized by rows of the same table, which can lead to wasted space if not properly managed.

  • fsync operations are performed on multiple file-per-table data files instead of a shared system tablespace data file. Because fsync operations are per file, write operations for multiple tables cannot be combined, which can result in a higher total number of fsync operations.

  • mysqld must keep an open file handle for each file-per-table tablespace, which may impact performance if you have numerous tables in file-per-table tablespaces.

  • More file descriptors are required when each table has its own data file.

  • There is potential for more fragmentation, which can impede DROP TABLE and table scan performance. However, if fragmentation is managed, file-per-table tablespaces can improve performance for these operations.

  • The buffer pool is scanned when dropping a table that resides in a file-per-table tablespace, which can take several seconds for large buffer pools. The scan is performed with a broad internal lock, which may delay other operations.

  • The innodb_autoextend_increment variable, which defines the increment size for extending the size of an auto-extending system tablespace file when it becomes full, does not apply to file-per-table tablespace files, which are auto-extending regardless of the innodb_autoextend_increment setting. Initial file-per-table tablespace extensions are by small amounts, after which extensions occur in increments of 4MB.