InnoDB tables were stored in the
This monolithic approach was targeted at machines dedicated to
database processing, with carefully planned data growth, where any
disk storage allocated to MySQL would never be needed for other
purposes. The file-per-table
tablespace feature provides a more flexible alternative,
InnoDB table is stored in its own
tablespace data file (
.ibd file). This
feature is controlled by the
configuration option, which is enabled by default.
You can reclaim disk space when truncating or dropping a table stored in a file-per-table tablespace. Truncating or dropping tables stored in the shared system tablespace creates free space internally in the system tablespace data files (ibdata files) which can only be used for new
Similarly, a table-copying
ALTER TABLEoperation on table that resides in a shared tablespace can increase the amount of space used by the tablespace. Such operations may require as much additional space as the data in the table plus indexes. The additional space required for the table-copying
ALTER TABLEoperation is not released back to the operating system as it is for file-per-table tablespaces.
TRUNCATE TABLEoperation is faster when run on tables stored in file-per-table tablespaces.
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.3, “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 =, as explained in Section 220.127.116.11, “Creating a Tablespace Outside of the Data Directory”.
You can run
OPTIMIZE TABLEto compact or recreate a file-per-table tablespace. When you run an
InnoDBcreates a new
.ibdfile with a temporary name, using only the space required to store actual data. When the optimization is complete,
InnoDBremoves the old
.ibdfile and replaces it with the new one. If the previous
.ibdfile grew significantly but the actual data only accounted for a portion of its size, running
OPTIMIZE TABLEcan reclaim the unused space.
You can move individual
InnoDBtables rather than entire databases.
You can copy individual
InnoDBtables 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. Tables created in the system tablespace cannot use these features. To take advantage of these features for an existing table, enable the
innodb_file_per_tablesetting and run
ALTER TABLEto place the table in a file-per-table tablespace. Before converting tables, refer to Section 18.104.22.168, “Converting Tables from MyISAM to InnoDB”.
You can enable more efficient storage for tables with large
TEXTcolumns 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.
You can back up or restore individual tables quickly using the MySQL Enterprise Backup product, without interrupting the use of other
InnoDBtables. This is beneficial if you have tables that require backup less frequently or on a different backup schedule. See Making a Partial Backup for details.
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_methodis set to
O_DIRECT. As a result, there are possible performance improvements when using file-per-table tablespaces in conjunction with
The system tablespace stores the data dictionary and undo logs, and is limited in size by
InnoDBtablespace size limits. See Section 22.214.171.124, “Limits on InnoDB Tables”. With file-per-table tablespaces, each table has its own tablespace, which provides room for growth.
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.
fsyncoperations must run on each open table rather than on a single file. Because there is a separate
fsyncoperation for each file, write operations on multiple tables cannot be combined into a single I/O operation. This may require
InnoDBto perform a higher total number of
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_tableis enabled by default in MySQL 5.6 and higher. You may consider disabling it if backward compatibility with earlier versions of MySQL is a concern. Disabling
ALTER TABLEoperations from implicitly moving a table that resides in the system tablespace to a file-per-table tablespace.
For example, when restructuring the clustered index, the table is re-created using the current setting for
innodb_file_per_table. This behavior does not apply to
ALTER TABLEoperations that use the
If many tables are growing there is potential for more fragmentation which can impede
DROP TABLEand 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.
innodb_autoextend_incrementvariable, 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_incrementsetting. The initial extensions are by small amounts, after which extensions occur in increments of 4MB.
option is enabled by default.
You can also set
dynamically, while the server is running:
mysql> SET GLOBAL innodb_file_per_table=1;
enabled, you can store
InnoDB tables in a
file. Unlike the
MyISAM storage engine, with
files for indexes and data,
InnoDB stores the
data and the indexes together in a single
.ibd file. The
file is still created as usual.
If you disable
innodb_file_per_table in your
startup options and restart the server, or disable it with the
SET GLOBAL command,
creates new tables inside the system tablespace.
You can always read and write any
tables, regardless of the file-per-table setting.
To move a table from the system tablespace to its own
tablespace, change the
and rebuild the table:
mysql> SET GLOBAL innodb_file_per_table=1; mysql> ALTER TABLE table_name ENGINE=InnoDB;
When a table is moved out of the system tablespace into its
.ibd file, the data files that make
up the system tablespace remain the same size. The space
formerly occupied by the table can be reused for new
InnoDB data, but is not reclaimed for use
by the operating system. When moving large
InnoDB tables out of the system tablespace,
where disk space is limited, you may prefer to enable
recreate the entire instance using the