InnoDB General Tablespaces – Preview

The new InnoDB Labs release contains the ability to create and use independent multi-table general tablespaces.

This feature will provide a way to group tables together into tablespaces at a location and filename of your choosing.  Tables using row formats of Redundant, Compact, and Dynamic can be combined together into the same general tablespace. Compressed tables with the same key_block_size can also be combined together.

The SQL syntax for creating an empty general tablespaces is:
CREATE TABLESPACE `tblspace_name` ADD DATAFILE 'tablespace.ibd' [FILE_BLOCK_SIZE=n];

The filename can contain an absolute path or a path relative to the data directory where the InnoDB system tablespace (ibdata1) is found. The file in the example above would be created in the same directory as the InnoDB system tablespace. The file name extension ‘.ibd’ is required on all datafile names.

The FILE_BLOCK_SIZE parameter is only necessary if you want to create a general tablespace that holds compressed tables. The value can be specified in bytes or using the “k” shorthand notation, for example: 4096 or 4k.

A table can be added to a general table space with:
CREATE TABLE tbl_name TABLESPACE=`tblspace_name`;

Or it can be moved to a general tablespace  with:
ALTER TABLE tbl_name TABLESPACE=`tblspace_name`;

And finally, an empty tablespace can be deleted with:
DROP TABLESPACE `tblspace_name`;

This labs release also demonstrates the ability to move tables between the three types of InnoDB tablespaces — the system tablespace, file-per-table tablespaces, and general tablespaces — using the ALTER TABLE tbl_name TABLESPACE='tblspace_name'; statement.  If the specified tblspace_name value is “innodb_system” then the table will be re-created within the system tablespace.  If the specified tblspace_name value is “innodb_file_per_table”, then the table will be re-created as a single-table tablespace.  Thus you have full flexibility to move tables around within your database.

Note that a general tablespace is not associated with any particular database/schema like a table is.  So it is possible to create tables in multiple database schemas using the same general tablespace, just as it has always been with the system tablespace. And while DROP DATABASE might drop all of the individual tables found within a general tablespace, it will not drop the tablespace itself.

We look forward to getting your input on this new feature! Please let us know if you encounter any issues or have any general feedback. Thank you for using MySQL!

13 thoughts on “InnoDB General Tablespaces – Preview

  1. Great!
    About resize and downsize tablespaces, like redimension and shrink with using “alter table engine=innodb” to clean up the unused space.

    How does it work in this new functionality?


    1. InnoDB does not currently shrink either file-per-table or general tablespaces. There has been some discussion about a possible ‘OPTIMIZE TABLESPACE’. The workaround is to create a new tablespace and move each table in the old tablespace to the new one using ALTER TABLE name TABLESPACE=new_tablespace;

          1. It is not an online operation because it moves all data for a table from one tablespace to another. So the normal ALTER TABLE locks will apply.

  2. Ok, thanks!

    Guys, two more questions!

    1 Is possible “ALTER TABLESPACE ADD DATAFILE XXX.ibd ….”, and there are commands for moving data files or change tablespace?

    2 – How it works for partitioned tables? The mysql created more files in the partition table, or simply creates a .frm hosts file and the data in the tablespace?

    1. 1- “ALTER TABLESPACE ADD DATAFILE XXX.ibd ….” is not supported. Much of the reason for splitting a tablespace into multiple files is if a file system size limit is too small. But that issue no longer exists practically. The other reason is to separate parts of the data. But that is better done using partitioning or just separating tables into separate tablespaces. What other reason is there for multi-file tablespaces, considering that there is no control whatsoever as to which file any particular row goes into within a tablespace. (The system tablespace can be multiple files, but you cannot control which file contains what data).

      2- The InnoDB labs release does not support assigning each partition to its own tablespace, but that will be supported.

Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter * Time limit is exhausted. Please reload CAPTCHA.