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.
tablespace feature provides a more flexible alternative,
InnoDB table and its indexes are
stored in a separate
file. Each such
file represents an individual
tablespace. This feature is
controlled by the
configuration option, which is enabled by default in MySQL 5.6.6
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
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 188.8.131.52, “Using Symbolic Links”.
In MySQL 5.6.6 and higher, you can specify the location of
each table using the syntax
CREATE TABLE ... DATA
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
InnoDB creates a new
.ibd file with a temporary name, using
only the space required to store actual data. When the
optimization is complete,
.ibd file and replaces it with
the new one. If the previous
grew significantly but the actual data only accounted for a
portion of its size, running
TABLE can reclaim the unused space.
You can move individual
rather than entire databases.
You can copy individual
InnoDB tables from
one MySQL instance to another (known as the
You can enable more efficient storage for tables with large
using the dynamic row
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
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 D.10.3, “Limits on Table Size” for related information.
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
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
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.
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
ALTER TABLE from
InnoDB table from the system
tablespace to an individual
.ibd file in
recreates the table (
For example, when restructuring the clustered index for an
InnoDB table, the table is re-created using
the current setting for
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
setting. This behaviour also does not apply to tables added to
the system tablespace using
CREATE TABLE ...
ALTER TABLE ...
TABLESPACE syntax. These tables are not affected by
If many tables are growing there is potential for more
fragmentation which can impede
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.
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
setting. The initial extensions are by small amounts, after
which extensions occur in increments of 4MB.