MySQL :: MySQL 5.7 Reference Manual :: 14.6.3.3 General Tablespaces (original) (raw)

14.6.3.3 General Tablespaces

A general tablespace is a shared InnoDB tablespace that is created using CREATE TABLESPACE syntax. General tablespace capabilities and features are described under the following topics in this section:

• General Tablespace Capabilities
• Creating a General Tablespace
• Adding Tables to a General Tablespace
• General Tablespace Row Format Support
• Moving Tables Between Tablespaces Using ALTER TABLE
• Dropping a General Tablespace
• General Tablespace Limitations

General Tablespace Capabilities

General tablespaces provide the following capabilities:

• Similar to the system tablespace, general tablespaces are shared tablespaces capable of storing data for multiple tables.
• General tablespaces have a potential memory advantage overfile-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 can 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 offile-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 oninnodb_file_format orinnodb_file_per_table settings, nor do these variables have any effect on general tablespaces.
• The TABLESPACE option can be used withCREATE TABLE to create tables in a general tablespaces, file-per-table tablespace, or in the system tablespace.
• The TABLESPACE option can be used withALTER TABLE to move tables between general tablespaces, file-per-table tablespaces, and the system tablespace.

Creating a General Tablespace

General tablespaces are created usingCREATE TABLESPACE syntax.

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

A general tablespace can be created in the data directory or outside of it. To avoid conflicts with implicitly created file-per-table tablespaces, creating a general tablespace in a subdirectory under the data directory is not supported. When creating a general tablespace outside of the 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 data directory:

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

Creating a general tablespace in a directory outside of the 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 data directory as long as the tablespace directory is not under the data directory. In this example, themy_tablespace directory is at the same level as the data directory:

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

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), theFILE_BLOCK_SIZE option must be specified, and the FILE_BLOCK_SIZE value must be a valid compressed page size in relation to theinnodb_page_size value. Also, the physical page size of the compressed table (KEY_BLOCK_SIZE) must be equal toFILE_BLOCK_SIZE/1024. For example, ifinnodb_page_size=16KB andFILE_BLOCK_SIZE=8K, theKEY_BLOCK_SIZE of the table must be 8.

The following table shows permittedinnodb_page_size,FILE_BLOCK_SIZE, andKEY_BLOCK_SIZE combinations.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 theFILE_BLOCK_SIZE value by 1024. Table compression is not support for 32K and 64KInnoDB page sizes. For more information aboutKEY_BLOCK_SIZE, seeCREATE TABLE, andSection 14.9.1.2, “Creating Compressed Tables”.

Table 14.3 Permitted Page Size, FILE_BLOCK_SIZE, and KEY_BLOCK_SIZE Combinations for Compressed Tables

This example demonstrates creating a general tablespace and adding a compressed table. The example assumes a defaultinnodb_page_size of 16KB. TheFILE_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;

mysql> CREATE TABLE t4 (c1 INT PRIMARY KEY) TABLESPACE ts2 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

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

Moving Tables Between Tablespaces Using ALTER TABLE

ALTER TABLE with theTABLESPACE option can be used to move a table to an existing general tablespace, to a new file-per-table tablespace, or to the system tablespace.

Note

Support for placing table partitions in shared tablespaces was deprecated in MySQL 5.7.24; expect it to be removed in a future version of MySQL. Shared tablespaces include theInnoDB system tablespace and general tablespaces.

To move a 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. SeeALTER TABLESPACE for more information.

ALTER TABLE tbl_name TABLESPACE [=] tablespace_name;

To move a table from a general tablespace or file-per-table tablespace to the system tablespace, specifyinnodb_system as the tablespace name.

ALTER TABLE tbl_name TABLESPACE [=] innodb_system;

To move a table from the system tablespace or a general tablespace to a file-per-table tablespace, specifyinnodb_file_per_table as the tablespace name.

ALTER TABLE tbl_name TABLESPACE [=] innodb_file_per_table;

ALTER TABLE ... TABLESPACE operations 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 withCREATE TABLE ... TABLESPACE=innodb_file_per_table but is otherwise not supported for use in combination with theTABLESPACE option.

Restrictions apply when moving tables from encrypted tablespaces. SeeEncryption Limitations.

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 aDROP TABLESPACE operation. If the tablespace is not empty, DROP TABLESPACE returns an error.

Use a query similar to the following to identify tables in a general tablespace.

mysql> SELECT a.NAME AS space_name, b.NAME AS table_name FROM INFORMATION_SCHEMA.INNODB_TABLESPACES a,
       INFORMATION_SCHEMA.INNODB_TABLES b WHERE a.SPACE=b.SPACE AND a.NAME LIKE 'ts1';
+------------+------------+
| space_name | table_name |
+------------+------------+
| ts1        | test/t1    |
| ts1        | test/t2    |
| ts1        | test/t3    |
+------------+------------+

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 anALTER TABLE operation that was interrupted by a server exit. For more information, seeSection 14.22.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 usingDROP 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.

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 newInnoDB 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 anInnoDB 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;

mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1 Engine=InnoDB;

mysql> DROP TABLE t1;

mysql> DROP TABLESPACE ts1;

Note

_`tablespacename`_ 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 forfile-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 andALTER TABLE ...IMPORT TABLESPACE are not supported for tables that belong to a general tablespace.
• Support for placing table partitions in general tablespaces was deprecated in MySQL 5.7.24; expect it to be removed in a future version of MySQL.
• The ADD DATAFILE clause is not supported in a replication environment where the source and replica reside on the same host, as it would cause the source and replica to create a tablespace of the same name in the same location.