You can store each
InnoDB table and its indexes
in its own data file. This feature is called “file-per-table
tablespaces” because in effect each table has its own
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 TABLEoperation 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.
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 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 a single table quickly, without interrupting the use of other
InnoDBtables, using the MySQL Enterprise Backup product. See Partial Backup and Restore Options for details.
You can exclude tables stored in file-per-table tablespaces from a backup. This is beneficial if you have tables that require backup less frequently or on a different schedule.
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
innodb_file_per_tablein 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 C.7.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.
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.
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.
To enable file-per-table tablespaces, start the server with the
--innodb_file_per_table option. For
example, add a line to the
[mysqld] section of
InnoDB stores each newly created table
into its own
in the database directory where the table belongs. This is similar
to what the
MyISAM storage engine does, but
MyISAM divides the table into a
file and an
InnoDB, the data and the indexes are
stored together in the
.ibd file. The
is still created as usual.
You cannot freely move
.ibd files between
database directories as you can with
table files. This is because the table definition that is stored
InnoDB shared tablespace includes the
database name, and because
InnoDB must preserve
the consistency of transaction IDs and log sequence numbers.
If you remove the
innodb_file_per_table line from
my.cnf and restart the server, newly created
InnoDB tables are created inside the shared
tablespace files again.
affects only table creation, not access to existing tables. If you
start the server with this option, new tables are created using
.ibd files, but you can still access tables
that exist in the shared tablespace. If you start the server
without this option, new tables are created in the shared
tablespace, but you can still access tables created in
InnoDB requires the shared tablespace to
store its internal data dictionary and undo logs. The
.ibd files alone are not sufficient for
InnoDB to operate.
To move an
.ibd file and the associated table
from one database to another, use a
If you have a “clean” backup of an
.ibd file, you can restore it to the MySQL
installation from which it originated as follows:
In this context, a “clean”
file backup is one for which the following requirements are
There are no uncommitted modifications by transactions in the
There are no unmerged insert buffer entries in the
Purge has removed all delete-marked index records from the
mysqld has flushed all modified pages of the
.ibdfile from the buffer pool to the file.
You can make a clean backup
.ibd file using
the following method:
Another method for making a clean copy of an
.ibd file is to use the commercial MySQL Enterprise Backup
Use MySQL Enterprise Backup to back up the
Start a second mysqld server on the backup and let it clean up the
.ibdfiles in the backup.