Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  InnoDB File-Per-Table Tablespaces

14.4.4 InnoDB File-Per-Table Tablespaces

Historically, all InnoDB tables and indexes were stored in the system tablespace. This monolithic approach was targeted at machines dedicated entirely to database processing, with carefully planned data growth, where any disk storage allocated to MySQL would never be needed for other purposes. InnoDB's file-per-table tablespace feature provides a more flexible alternative, where each InnoDB table and its indexes are stored in a separate .ibd data file. Each such .ibd data file represents an individual tablespace. This feature is controlled by the innodb_file_per_table configuration option, which is enabled by default in MySQL 5.6.6 and higher.

Advantages of File-Per-Table Tablespaces

  • You can reclaim disk space when truncating or dropping a table stored in a file-per-table tablepace. Truncating or dropping tables stored in the system tablespace creates free space internally in the system tablespace data files (ibdata files) which can only be used for new InnoDB data.

  • The TRUNCATE TABLE operation is faster when run on tables stored in file-per-table tablepaces.

  • You can store specific tables on separate storage devices, for I/O optimization, space management, or backup purposes. In previous releases, you had to move entire database directories to other drives and create symbolic links in the MySQL data directory, as described in Section 8.12.4, “Using Symbolic Links”. In MySQL 5.6.6 and higher, you can specify the location of each table using the syntax CREATE TABLE ... DATA DIRECTORY = absolute_path_to_directory, as explained in Section 14.4.5, “Creating a File-Per-Table Tablespace Outside the Data Directory”.

  • You can run OPTIMIZE TABLE to compact or recreate a file-per-table tablespace. When you run an OPTIMIZE TABLE, InnoDB creates a new .ibd file with a temporary name, using only the space required to store actual data. When the optimization is complete, InnoDB removes the old .ibd file and replaces it with the new one. If the previous .ibd file grew significantly but the actual data only accounted for a portion of its size, running OPTIMIZE TABLE can reclaim the unused space.

  • You can move individual InnoDB tables rather than entire databases.

  • You can copy individual InnoDB tables from one MySQL instance to another (known as the transportable tablespace feature).

  • Tables created in file-per-table tablespaces use the Barracuda file format. The Barracuda file format enables features such as compressed and dynamic row formats.

  • You can enable more efficient storage for tables with large BLOB or TEXT columns using the dynamic row format.

  • File-per-table tablespaces may improve chances for a successful recovery and save time when a corruption occurs, when a server cannot be restarted, or when backup and binary logs are unavailable.

  • File-per-table tablespaces are convenient for per-table status reporting when copying or backing up tables.

  • You can monitor table size at a file system level, without accessing MySQL.

  • Common Linux file systems do not permit concurrent writes to a single 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 innodb_flush_method.

  • The system tablespace stores the data dictionary and undo logs, and has a 64TB size limit. By comparison, each file-per-table tablespace has a 64TB size limit, which provides you with room for growth. See Section C.10.3, “Limits on Table Size” for related information.

Potential Disadvantages of File-Per-Table Tablespaces

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

  • fsync operations must run on each open table rather than on a single file. Because there is a separate fsync operation for each file, write operations on multiple tables cannot be combined into a single I/O operation. This may require InnoDB to perform a higher total number of fsync operations.

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

  • More file descriptors are used.

  • innodb_file_per_table is enabled by default in MySQL 5.6.6 and higher. You may consider disabling it if backward compatibility with MySQL 5.5 or 5.1 is a concern. Disabling innodb_file_per_table prevents ALTER TABLE from moving an InnoDB table from the system tablespace to an individual .ibd file in cases where ALTER TABLE recreates the table (ALGORITHM=COPY).

    For example, when restructuring the clustered index for an InnoDB table, the table is re-created using the current setting for innodb_file_per_table. This behavior does not apply when adding or dropping InnoDB secondary indexes. When a secondary index is created without rebuilding the table, the index is stored in the same file as the table data, regardless of the current innodb_file_per_table setting. This behaviour also does not apply to tables added to the system tablespace using CREATE TABLE ... TABLESPACE or ALTER TABLE ... TABLESPACE syntax. These tables are not affected by the innodb_file_per_table setting.

  • If many tables are growing there is potential for more fragmentation which can impede DROP TABLE and table scan performance. However, when fragmentation is managed, having files in their own tablespace can improve performance.

  • The buffer pool is scanned when dropping a file-per-table tablespace, which can take several seconds for buffer pools that are tens of gigabytes in size. The scan is performed with a broad internal lock, which may delay other operations. Tables in the system tablespace are not affected.

  • The innodb_autoextend_increment variable, which defines increment size (in MB) for extending the size of an auto-extending shared 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. The initial extensions are by small amounts, after which extensions occur in increments of 4MB.

Download this Manual
User Comments
Sign Up Login You must be logged in to post a comment.