By default, all InnoDB tables and indexes are stored in the
As an alternative, you can store each
table and its indexes in its own file. This feature is called
“file-per-table mode” because each table that is
created when this setting is in effect has its own
.ibd file. Each such
represents a separate
tablespace. This mode is
controlled by the
You can reclaim operating system disk space when truncating or
dropping a table. For tables created when file-per-table mode
is turned off, truncating or dropping the tables creates free
space internally in the
ibdata files but the
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, running
OPTIMIZE TABLE allows you to
reclaim the unused space.
You can move individual
rather than entire databases.
Tables created when
enabled can use the
Barracuda file format.
The Barracuda file format enables features such as
and dynamic row
formats. Tables created when
innodb_file_per_table is off
cannot use these features. To take advantage of these features
for an existing table, you can turn on the file-per-table
setting and run
existing table. Before converting tables, refer to
Section 14.9.4, “Converting Tables from MyISAM to InnoDB”.
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
tables, using the MySQL Enterprise Backup product. See
Backing Up and Restoring a Single .ibd File for the procedure
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 to
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.10.3, “Limits on Table Size” for related
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 of
mysqld must keep 1 open file handle per table, which may impact performance if you have numerous tables.
More file descriptors are used.
If backward compatibility with MySQL 5.1 is a concern, be
aware that enabling
ALTER TABLE operation
will move an
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 settings 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
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 file-per-table tablespace
files. File-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
--innodb_file_per_table option. For
example, add a line to the
[mysqld] section of
With multiple tablespaces enabled,
stores each newly created table in its own
in the appropriate database directory. Unlike the
MyISAM storage engine, with its separate
for indexes and data,
InnoDB stores the data
and the indexes together in a single
is still created as usual.
If you remove the
innodb_file_per_table line from
my.cnf and restart the server,
InnoDB creates any new tables inside the shared
You can always access both tables in the system tablespace and
tables in their own tablespaces, regardless of the file-per-table
setting. To move a table from the system tablespace to its own
tablespace, or vice versa, you can change the
innodb_file_per_table setting and
issue the command:
-- Move table from system tablespace to its own tablespace. SET GLOBAL innodb_file_per_table=1; ALTER TABLE
table_nameENGINE=InnoDB; -- Move table from its own tablespace to system tablespace. SET GLOBAL innodb_file_per_table=0; ALTER TABLE
InnoDB always needs the shared tablespace
because it puts its internal data dictionary and undo logs
.ibd files are not sufficient
InnoDB to operate.
When a table is moved out of the system tablespace into its own
.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 might prefer to turn on
innodb_file_per_table and then recreate the
entire instance using the mysqldump command.
You cannot freely move
.ibd files between
database directories as you can with
table files. The table definition stored in the
InnoDB shared tablespace includes the database
name. The transaction IDs and log sequence numbers stored in the
tablespace files also differ between databases.
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:
The table must not have been dropped or truncated since you
.ibd file, because doing so
changes the table ID stored inside the tablespace.
statement to delete the current
Copy the backup
.ibd file to the proper
statement to tell
InnoDB to use the new
.ibd file for the table:
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
.ibd file from the buffer pool to the
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 MySQL Enterprise
Use MySQL Enterprise Backup to back up the
Start a second mysqld server on the backup
and let it clean up the
.ibd files in the