MySQL :: MySQL 5.7 Reference Manual :: 15.7.9 InnoDB General Tablespaces

General Tablespace Capabilities

The general tablespace feature provides the following capabilities:

Similar to the system tablespace, general tablespaces are shared tablespaces that can store data for multiple tables.
General tablespaces have a potential memory advantage over file-per-table tablespaces. The server keeps tablespace metadata in memory for the lifetime of a tablespace. Multiple tables in fewer general tablespaces consume less memory for tablespace metadata than the same number of tables in separate file-per-table tablespaces.
General tablespace data files may be placed in a directory relative to or independent of the MySQL data directory, which provides you with many of the data file and storage management capabilities of file-per-table tablespaces. As with file-per-table tablespaces, the ability to place data files outside of the MySQL data directory allows you to manage performance of critical tables separately, setup RAID or DRBD for specific tables, or bind tables to particular disks, for example.
General tablespaces support both Antelope and Barracuda file formats, and therefore support all table row formats and associated features. With support for both file formats, general tablespaces have no dependence on innodb_file_format or innodb_file_per_table settings, nor do these variables have any effect on general tablespaces.
The TABLESPACE option can be used with CREATE TABLE to create tables in a general tablespaces, file-per-table tablespace, or in the system tablespace.
The TABLESPACE option can be used with ALTER TABLE to move tables between general tablespaces, file-per-table tablespaces, and the system tablespace. Previously, it was not possible to move a table from a file-per-table tablespace to the system tablespace. With the general tablespace feature, you can now do so.

Creating a General Tablespace

General tablespaces are created using CREATE TABLESPACE syntax.

CREATE TABLESPACE tablespace_name
    ADD DATAFILE 'file_name'
    [FILE_BLOCK_SIZE = value]
        [ENGINE [=] engine_name]

A general tablespace may be created in the MySQL data directory or in a directory outside of the MySQL data directory. To avoid conflicts with implicitly created file-per-table tablespaces, creating a general tablespace in a subdirectory under the MySQL data directory is not supported. Also, when creating a general tablespace outside of the MySQL data directory, the directory must exist prior to creating the tablespace.

An isl file is created in the MySQL data directory when a general tablespace is created outside of the MySQL data directory.

Examples:

Creating a general tablespace in the MySQL data directory:

mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;

Creating a general tablespace in a directory outside of the MySQL data directory:

mysql> CREATE TABLESPACE `ts1` ADD DATAFILE '/my/tablespace/directory/ts1.ibd' Engine=InnoDB;

You can specify a path that is relative to the MySQL data directory as long as the tablespace directory is not under the MySQL data directory. In this example, the my_tablespace directory is at the same level as the MySQL data directory:

mysql> CREATE TABLESPACE `ts1` ADD DATAFILE '../my_tablespace/ts1.ibd' Engine=InnoDB;

Note

The ENGINE = InnoDB clause must be defined as part of the CREATE TABLESPACE statement or InnoDB must be defined as the default storage engine (default_storage_engine=InnoDB).

Adding Tables to a General Tablespace

After creating an InnoDB general tablespace, you can use CREATE TABLE tbl_name ... TABLESPACE [=] tablespace_name or ALTER TABLE tbl_name TABLESPACE [=] tablespace_name to add tables to the tablespace, as shown in the following examples:

CREATE TABLE:

mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1 ROW_FORMAT=COMPACT;

ALTER TABLE:

mysql> ALTER TABLE t2 TABLESPACE ts1;

For detailed syntax information, see CREATE TABLE and ALTER TABLE.

General Tablespace Row Format Support

General tablespaces support all table row formats (REDUNDANT, COMPACT, DYNAMIC, COMPRESSED) with the caveat that compressed and uncompressed tables cannot coexist in the same general tablespace due to different physical page sizes.

For a general tablespace to contain compressed tables (ROW_FORMAT=COMPRESSED), FILE_BLOCK_SIZE must be specified, and the FILE_BLOCK_SIZE value must be a valid compressed page size in relation to the innodb_page_size value. Also, the physical page size of the compressed table (KEY_BLOCK_SIZE) must be equal to FILE_BLOCK_SIZE/1024. For example, if innodb_page_size=16K and FILE_BLOCK_SIZE=8K, the KEY_BLOCK_SIZE of the table must be 8.

The following table shows permitted FILE_BLOCK_SIZE and KEY_BLOCK_SIZE values for each innodb_page_size value. FILE_BLOCK_SIZE values may also be specified in bytes. To determine a valid KEY_BLOCK_SIZE value for a given FILE_BLOCK_SIZE, divide the FILE_BLOCK_SIZE value by 1024. Table compression is not support for 32K and 64K InnoDB page sizes. For more information about KEY_BLOCK_SIZE, see CREATE TABLE, and Section 15.9.1.2, “Creating Compressed Tables”.

Table 15.5 FILE_BLOCK_SIZE and KEY_BLOCK_SIZE Values for CREATE TABLESPACE

InnoDB Page Size (innodb_page_size)	Permitted FILE_BLOCK_SIZE Values	Permitted KEY_BLOCK_SIZE Values
64K	64K (65536)	Compression is not supported
32K	32K (32768)	Compression is not supported
16K	16K (16384)	N/A: If `innodb_page_size` is equal to `FILE_BLOCK_SIZE`, the tablespace cannot contain a compressed table.
	8K (8192)	8
	4K (4096)	4
	2K (2048)	2
	1K (1024)	1
8K	8K (8192)	N/A: If `innodb_page_size` is equal to `FILE_BLOCK_SIZE`, the tablespace cannot contain a compressed table.
	4K (4096)	4
	2K (2048)	2
	1K (1024)	1
4K	4K (4096)	N/A: If `innodb_page_size` is equal to `FILE_BLOCK_SIZE`, the tablespace cannot contain a compressed table.
	2K (2048)	2
	1K (1024)	1

This example demonstrates creating a general tablespace and adding a compressed table. The example assumes a default innodb_page_size of 16K. The FILE_BLOCK_SIZE of 8192 requires that the compressed table have a KEY_BLOCK_SIZE of 8.

mysql> CREATE TABLESPACE `ts2` ADD DATAFILE 'ts2.ibd' FILE_BLOCK_SIZE = 8192 Engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE t4 (c1 INT PRIMARY KEY) TABLESPACE ts2 ROW_FORMAT=COMPRESSED
KEY_BLOCK_SIZE=8;
Query OK, 0 rows affected (0.00 sec)

If you do not specify FILE_BLOCK_SIZE when creating a general tablespace, FILE_BLOCK_SIZE defaults to innodb_page_size. When FILE_BLOCK_SIZE is equal to innodb_page_size, the tablespace may only contain tables with an uncompressed row format (COMPACT, REDUNDANT, and DYNAMIC row formats).

Moving Non-Partitioned Tables Between Tablespaces Using ALTER TABLE

You can use ALTER TABLE with the TABLESPACE option to move a non-partitioned InnoDB table to an existing general tablespace, to a new file-per-table tablespace, or to the system tablespace.

Note

Running an ALTER TABLE tbl_name TABLESPACE [=] tablespace_name operation on a partitioned table only modifies the table's default tablespace. It does not move the table's partitions.

To move a non-partitioned table from a file-per-table tablespace or from the system tablespace to a general tablespace, specify the name of the general tablespace. The general tablespace must exist. See CREATE TABLESPACE for more information.

ALTER TABLE tbl_name TABLESPACE [=] tablespace_name

To move a non-partitioned table from a general tablespace or file-per-table tablespace to the system tablespace, specify innodb_system as the tablespace name.

ALTER TABLE tbl_name ... TABLESPACE [=] innodb_system

To move a non-partitioned table from the system tablespace or a general tablespace to a file-per-table tablespace, specify innodb_file_per_table as the tablespace name.

ALTER TABLE tbl_name ... TABLESPACE [=] innodb_file_per_table

ALTER TABLE ... TABLESPACE operations always cause a full table rebuild, even if the TABLESPACE attribute has not changed from its previous value.

ALTER TABLE ... TABLESPACE syntax does not support moving a table from a temporary tablespace to a persistent tablespace.

The DATA DIRECTORY clause is permitted with CREATE TABLE ... TABLESPACE=innodb_file_per_table but is otherwise not supported for use in combination with the TABLESPACE option.

General Tablespace Table Partition Support

The TABLESPACE option may be used to assign individual table partitions or subpartitions to a general tablespace, a separate file-per-table tablespace, or the system tablespace. All partitions must belong to the same storage engine. Usage is demonstrated in the following examples.

mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;
mysql> CREATE TABLESPACE `ts2` ADD DATAFILE 'ts2.ibd' Engine=InnoDB;

mysql> CREATE TABLE t1 (a INT, b INT) ENGINE = InnoDB
    ->  PARTITION BY RANGE(a) SUBPARTITION BY KEY(b) (
    ->    PARTITION p1 VALUES LESS THAN (100) TABLESPACE=`ts1`,
    ->    PARTITION p2 VALUES LESS THAN (1000) TABLESPACE=`ts2`,
    ->    PARTITION p3 VALUES LESS THAN (10000) TABLESPACE `innodb_file_per_table`,
    ->    PARTITION p4 VALUES LESS THAN (100000) TABLESPACE `innodb_system`);

mysql> CREATE TABLE t2 (a INT, b INT) ENGINE = InnoDB
    ->  PARTITION BY RANGE(a) SUBPARTITION BY KEY(b) (
    ->    PARTITION p1 VALUES LESS THAN (100) TABLESPACE=`ts1`
    ->      (SUBPARTITION sp1,
    ->       SUBPARTITION sp2),
    ->    PARTITION p2 VALUES LESS THAN (1000)
    ->      (SUBPARTITION sp3,
    ->       SUBPARTITION sp4 TABLESPACE=`ts2`),
    ->    PARTITION p3 VALUES LESS THAN (10000)
    ->      (SUBPARTITION sp5 TABLESPACE `innodb_system`,
    ->       SUBPARTITION sp6 TABLESPACE `innodb_file_per_table`));

The TABLESPACE option is also supported with ALTER TABLE.

mysql> ALTER TABLE t1 ADD PARTITION (PARTITION p5 VALUES LESS THAN (1000000) TABLESPACE = `ts1`);

Note

If the TABLESPACE = tablespace_name option is not defined, the ALTER TABLE ... ADD PARTITION operation adds the partition to the table's default tablespace, which can be specified at the table level during CREATE TABLE or ALTER TABLE.

To verify that partitions were placed in the specified tablespaces, you can query INFORMATION_SCHEMA.INNODB_SYS_TABLES:

mysql> SELECT NAME, SPACE, SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
    -> WHERE NAME LIKE '%t1%';
+-----------------------+-------+------------+
| NAME                  | SPACE | SPACE_TYPE |
+-----------------------+-------+------------+
| test/t1#P#p1#SP#p1sp0 |    57 | General    |
| test/t1#P#p2#SP#p2sp0 |    58 | General    |
| test/t1#P#p3#SP#p3sp0 |    59 | Single     |
| test/t1#P#p4#SP#p4sp0 |     0 | System     |
| test/t1#P#p5#SP#p5sp0 |    57 | General    |
+-----------------------+-------+------------+

mysql> SELECT NAME, SPACE, SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
    -> WHERE NAME LIKE '%t2%';
+---------------------+-------+------------+
| NAME                | SPACE | SPACE_TYPE |
+---------------------+-------+------------+
| test/t2#P#p1#SP#sp1 |    57 | General    |
| test/t2#P#p1#SP#sp2 |    57 | General    |
| test/t2#P#p2#SP#sp3 |    60 | Single     |
| test/t2#P#p2#SP#sp4 |    58 | General    |
| test/t2#P#p3#SP#sp5 |     0 | System     |
| test/t2#P#p3#SP#sp6 |    61 | Single     |
+---------------------+-------+------------+

Moving Table Partitions Between Tablespaces Using ALTER TABLE

To move table partitions to a different tablespace, you must move each partition using an ALTER TABLE tbl_name REORGANIZE PARTITION statement.

The following example demonstrates how to move table partitions to a different tablespace. INFORMATION_SCHEMA.INNODB_SYS_TABLES and INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES are queried to verify that partitions are placed in the expected tablespace.

Note

If the TABLESPACE = tablespace_name option is not defined in the REORGANIZE PARTITION statement, InnoDB moves the partition to the table's default tablespace. In this example, tablespace ts1, which is defined at the table level, is the default tablespace for table t1. Partition P3 is moved from the system tablespace to tablespace ts1 since no TABLESPACE option is specified in the ALTER TABLE t1 REORGANIZE PARTITION statement for partition P3.

To change a partitioned table's default tablespace, you can run ALTER TABLE tbl_name TABLESPACE [=] tablespace_name on the partitioned table.

mysql> CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd';
mysql> CREATE TABLESPACE ts2 ADD DATAFILE 'ts2.ibd';

mysql> CREATE TABLE t1 ( a INT NOT NULL, PRIMARY KEY (a))
    ->  ENGINE=InnoDB TABLESPACE ts1                          
    ->  PARTITION BY RANGE (a) PARTITIONS 3 (
    ->    PARTITION P1 VALUES LESS THAN (2),
    ->    PARTITION P2 VALUES LESS THAN (4) TABLESPACE `innodb_file_per_table`,
    ->    PARTITION P3 VALUES LESS THAN (6) TABLESPACE `innodb_system`);


mysql> SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as space_name
    -> FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES A
    -> LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES B
    -> ON A.SPACE = B.SPACE WHERE A.NAME LIKE '%t1%' ORDER BY A.NAME;
+----------------+------------+--------------+
| partition_name | space_type | space_name   |
+----------------+------------+--------------+
| test/t1#P#P1   | General    | ts1          |
| test/t1#P#P2   | Single     | test/t1#P#P2 |
| test/t1#P#P3   | System     | NULL         |
+----------------+------------+--------------+

mysql> ALTER TABLE t1 REORGANIZE PARTITION P1
    -> INTO (PARTITION P1 VALUES LESS THAN (2) TABLESPACE = `ts2`);
  
mysql> ALTER TABLE t1 REORGANIZE PARTITION P2
    -> INTO (PARTITION P2 VALUES LESS THAN (4) TABLESPACE = `ts2`);
  
mysql> ALTER TABLE t1 REORGANIZE PARTITION P3
    -> INTO (PARTITION P3 VALUES LESS THAN (6));

mysql> SELECT A.NAME AS partition_name, A.SPACE_TYPE AS space_type, B.NAME AS space_name
    -> FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES A
    -> LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES B
    -> ON A.SPACE = B.SPACE WHERE A.NAME LIKE '%t1%' ORDER BY A.NAME;
+----------------+------------+------------+
| partition_name | space_type | space_name |
+----------------+------------+------------+
| test/t1#P#P1   | General    | ts2        |
| test/t1#P#P2   | General    | ts2        |
| test/t1#P#P3   | General    | ts1        |
+----------------+------------+------------+

Dropping a General Tablespace

The DROP TABLESPACE statement is used to drop an InnoDB general tablespace.

All tables must be dropped from the tablespace prior to a DROP TABLESPACE operation. If the tablespace is not empty, DROP TABLESPACE returns an error.

If a DROP TABLESPACE operation on an empty general tablespace returns an error, the tablespace may contain an orphan temporary or intermediate table that was left by an ALTER TABLE operation that was interrupted by a server exit. For more information, see Section 15.21.3, “Troubleshooting InnoDB Data Dictionary Operations”.

A general InnoDB tablespace is not deleted automatically when the last table in the tablespace is dropped. The tablespace must be dropped explicitly using DROP TABLESPACE tablespace_name.

A general tablespace does not belong to any particular database. A DROP DATABASE operation can drop tables that belong to a general tablespace but it cannot drop the tablespace, even if the DROP DATABASE operation drops all tables that belong to the tablespace. A general tablespace must be dropped explicitly using DROP TABLESPACE tablespace_name.

Similar to the system tablespace, truncating or dropping tables stored in a general tablespace creates free space internally in the general tablespace .ibd data file which can only be used for new InnoDB data. Space is not released back to the operating system as it is when a file-per-table tablespace is deleted during a DROP TABLE operation.

This example demonstrates how to drop an InnoDB general tablespace. The general tablespace ts1 is created with a single table. The table must be dropped before dropping the tablespace.

mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts10 Engine=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> DROP TABLE t1;
Query OK, 0 rows affected (0.01 sec)

mysql> DROP TABLESPACE ts1;
Query OK, 0 rows affected (0.01 sec)

Note

tablespace_name is a case-sensitive identifier in MySQL.

General Tablespace Limitations

A generated or existing tablespace cannot be changed to a general tablespace.
Creation of temporary general tablespaces is not supported.
General tablespaces do not support temporary tables.
Tables stored in a general tablespace may only be opened in MySQL releases that support general tablespaces.
Similar to the system tablespace, truncating or dropping tables stored in a general tablespace creates free space internally in the general tablespace .ibd data file which can only be used for new InnoDB data. Space is not released back to the operating system as it is for file-per-table tablespaces.
Additionally, a table-copying ALTER TABLE operation on table that resides in a shared tablespace (a general tablespace or the system tablespace) can increase the amount of space used by the tablespace. Such operations require as much additional space as the data in the table plus indexes. The additional space required for the table-copying ALTER TABLE operation is not released back to the operating system as it is for file-per-table tablespaces.
ALTER TABLE ... DISCARD TABLESPACE and ALTER TABLE ...IMPORT TABLESPACE are not supported for tables that belong to a general tablespace.

For more information see Section 14.1.19, “CREATE TABLESPACE Syntax”.