You can store each
InnoDB table and its
indexes in its own file. This feature is called “multiple
tablespaces” because in effect each table has its own
You can reclaim disk space when truncating or dropping a
table. For tables created when file-per-table mode is turned
off, truncating or dropping them creates free space
internally in the ibdata
files. That free space can only be used for new
TRUNCATE TABLE operation
is faster when run on individual
You can store specific tables on separate storage devices, for I/O optimization, space management, or backup purposes.
You can run
OPTIMIZE TABLE to
compact or recreate a tablespace. When you run an
InnoDB will create 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
.ibd file. If the previous
.ibd file had grown significantly but
actual data only accounted for a portion of its size,
OPTIMIZE TABLE allows
you to reclaim the unused space.
You can move individual
rather than entire databases.
You can enable more efficient storage for tables with large BLOB or text columns using the dynamic row format.
may improve chances for a successful recovery and save time
if a corruption occurs, a server cannot be restarted, or
backup and binary logs are unavailable.
You can back up or restore a single table quickly, without
interrupting the use of other
File-per-table mode allows you to excluded tables from a backup. This is beneficial if you have tables that require backup less frequently or on a different schedule.
File-per-table mode is convenient for per-table status reporting when copying or backing up tables.
File-per-table mode allows you to 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
O_DIRECT. As a result, there are
possible performance improvements when using
disabled, there is one shared tablespace (the system
tablespace) for tables, the data dictionary, and undo logs.
This single tablespace has a 64TB size limit. If
enabled, each table has its own tablespace, each with a 64TB
size limit. See Section D.7.3, “Limits on Table Size” for
each table may have unused table space, which can only be
utilized by rows of the same table. This could lead to more
rather than less wasted table 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
mysqld must keep 1 open file handle per table, which may impact performance if you have numerous tables.
More file descriptors are used.
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 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 shared 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 per-table tablespace files.
Per-table tablespace files are auto-extending regardless of
the value of
The initial extensions are by small amounts, after which
extensions occur in increments of 4MB.
To enable multiple tablespaces, start the server with the
For example, add a line to the
With multiple tablespaces enabled,
stores each newly created table into its own
file in the database directory where the table belongs. This is
similar to what the
MyISAM storage engine
MyISAM divides the table into a
data file and an
index file. For
InnoDB, the data and the
indexes are stored together in the
file 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
preserve the consistency of transaction IDs and log sequence
If you remove the
innodb_file_per_table line from
my.cnf and restart the server,
InnoDB creates tables inside the shared
tablespace files again.
option affects only table creation, not access to existing
tables. If you start the server with this option, new tables are
.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 any tables that were
created using multiple tablespaces.
InnoDB always needs the shared tablespace
because it puts its internal data dictionary and undo logs
.ibd files are not sufficient
InnoDB to operate.
To move an
.ibd file and the associated
table from one database to another, use a
RENAME TABLE statement:
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”
.ibd file backup is one for which the
following requirements are satisfied:
There are no uncommitted modifications by transactions in
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
.ibd file from the buffer pool to
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
InnoDB Hot Backup tool:
Use InnoDB Hot Backup to back up the
Start a second mysqld server on the
backup and let it clean up the
files in the backup.