A file-per-table tablespace contains data and indexes for a single
InnoDB
table, and is stored on the file system
in a single data file.
File-per-table tablespace characteristics are described under the following topics in this section:
InnoDB
creates tables in file-per-table
tablespaces by default. This behavior is controlled by the
innodb_file_per_table
variable.
Disabling innodb_file_per_table
causes InnoDB
to create tables in the system
tablespace.
An innodb_file_per_table
setting can be specified in an option file or configured at
runtime using a
SET
GLOBAL
statement. Changing the setting at runtime
requires privileges sufficient to set global system variables.
See Section 5.1.8.1, “System Variable Privileges”.
Option file:
[mysqld]
innodb_file_per_table=ON
Using SET
GLOBAL
at runtime:
mysql> SET GLOBAL innodb_file_per_table=ON;
innodb_file_per_table
is
enabled by default in MySQL 5.6 and higher. You might consider
disabling it if backward compatibility with earlier versions of
MySQL is a concern.
Disabling
innodb_file_per_table
prevents table-copying ALTER
TABLE
operations from implicitly moving a table that
resides in the system tablespace to a file-per-table
tablespace. A table-copying ALTER
TABLE
operation recreates the table using the
current innodb_file_per_table
setting. This behavior does not apply when adding or dropping
secondary indexes, nor does it apply to
ALTER TABLE
operations that use
the INPLACE
algorithm, or to tables added
to the system tablespace using
CREATE TABLE ...
TABLESPACE
or
ALTER TABLE ...
TABLESPACE
syntax.
A file-per-table tablespace is created in an
.ibd
data file in a schema directory under
the MySQL data directory. The .ibd
file is
named for the table
(
).
For example, the data file for table table_name
.ibdtest.t1
is created in the test
directory under the
MySQL data directory:
mysql> USE test;
mysql> CREATE TABLE t1 (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100)
) ENGINE = InnoDB;
$> cd /path/to/mysql/data/test
$> ls
t1.ibd
You can use the DATA DIRECTORY
clause of the
CREATE TABLE
statement to
implicitly create a file-per-table tablespace data file outside
of the data directory. For more information, see
Section 14.6.1.2, “Creating Tables Externally”.
File-per-table tablespaces have the following advantages over shared tablespaces such as the system tablespace or general tablespaces.
Disk space is returned to the operating system after truncating or dropping a table created in a file-per-table tablespace. Truncating or dropping a table stored in a shared tablespace creates free space within the shared tablespace data file, which can only be used for
InnoDB
data. In other words, a shared tablespace data file does not shrink in size after a table is truncated or dropped.A table-copying
ALTER TABLE
operation on a table that resides in a shared tablespace can increase the amount of disk space occupied by the tablespace. Such operations may require as much additional space as the data in the table plus indexes. This space is not released back to the operating system as it is for file-per-table tablespaces.TRUNCATE TABLE
performance is better when executed on tables that reside in file-per-table tablespaces.File-per-table tablespace data files can be created on separate storage devices for I/O optimization, space management, or backup purposes. See Section 14.6.1.2, “Creating Tables Externally”.
You can import a table that resides in a file-per-table tablespace from another MySQL instance. See Section 14.6.1.3, “Importing InnoDB Tables”.
Tables created in file-per-table tablespaces use the Barracuda file format. See Section 14.10, “InnoDB File-Format Management”. The Barracuda file format enables features associated with
DYNAMIC
andCOMPRESSED
row formats. See Section 14.11, “InnoDB Row Formats”.Tables stored in individual tablespace data files can save time and improve chances for a successful recovery when data corruption occurs, when backups or binary logs are unavailable, or when the MySQL server instance cannot be restarted.
You can backup or restore tables created in file-per-table tablespaces quickly using MySQL Enterprise Backup, without interrupting the use of other
InnoDB
tables. This is beneficial for tables on varying backup schedules or that require backup less frequently. See Making a Partial Backup for details.File-per-table tablespaces permit monitoring table size on the file system by monitoring the size of the tablespace data file.
Common Linux file systems do not permit concurrent writes to a single file such as a shared tablespace data file when
innodb_flush_method
is set toO_DIRECT
. As a result, there are possible performance improvements when using file-per-table tablespaces in conjunction with this setting.Tables in a shared tablespace are limited in size by the 64TB tablespace size limit. By comparison, each file-per-table tablespace has a 64TB size limit, which provides plenty of room for individual tables to grow in size.
File-per-table tablespaces have the following disadvantages compared to shared tablespaces such as the system tablespace or general tablespaces.
With file-per-table tablespaces, each table may have unused space that can only be utilized by rows of the same table, which can lead to wasted space if not properly managed.
fsync
operations are performed on multiple file-per-table data files instead of a single shared tablespace data file. Becausefsync
operations are per file, write operations for multiple tables cannot be combined, which can result in a higher total number offsync
operations.mysqld must keep an open file handle for each file-per-table tablespace, which may impact performance if you have numerous tables in file-per-table tablespaces.
More file descriptors are required when each table has its own data file.
There is potential for more fragmentation, which can impede
DROP TABLE
and table scan performance. However, if fragmentation is managed, file-per-table tablespaces can improve performance for these operations.The buffer pool is scanned when dropping a table that resides in a file-per-table tablespace, which can take several seconds for large buffer pools. The scan is performed with a broad internal lock, which may delay other operations.
The
innodb_autoextend_increment
variable, which defines the increment size 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 theinnodb_autoextend_increment
setting. Initial file-per-table tablespace extensions are by small amounts, after which extensions occur in increments of 4MB.