MySQL General Tablespaces: A Powerful Storage Option

1 What is a General Tablespace?

Unlike the default system tablespace that stores system tables, a general tablespace is a user‑defined container that can hold multiple InnoDB tables, offering greater flexibility for data organization and performance optimization.

Main Features

Multiple‑table storage: A single general tablespace can contain many tables, improving storage efficiency compared with separate file‑per‑table spaces.

Flexible location: Data files may reside in MySQL’s data directory or any configured directory, giving better control over storage management and performance tuning.

Supports all table formats: General tablespaces accept all InnoDB row formats (redundant, compact, dynamic, compressed), providing flexibility for different workloads.

Memory optimisation: Shared metadata reduces memory consumption versus many independent tablespaces.

Benefits of Using General Tablespaces

Improved performance: Placing data files on faster disks or spreading tables across multiple disks can significantly boost performance.

RAID and DRBD integration: Data files can be placed on RAID or DRBD volumes for higher redundancy and disaster recovery.

Encryption support: MySQL can encrypt general tablespaces, enhancing data security.

Convenient table management: Grouping related tables in the same tablespace simplifies administration and organization.

Creating and Managing General Tablespaces

You can create a general tablespace with the CREATE TABLESPACE statement, specifying the data‑file location and engine options.

Example that creates a tablespace named my_general_tablespace using the data file general_tablespace.ibd , enables encryption, and sets the file‑block size:

mysql> CREATE TABLESPACE my_general_tablespace 
    -> ADD DATAFILE 'general_tablespace.ibd' 
    -> ENCRYPTION='Y' 
    -> FILE_BLOCK_SIZE = 16384;
ERROR 3185 (HY000): Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully.

mysql> pager grep -i keyring_file;
PAGER set to 'grep -i keyring_file'

mysql> SHOW PLUGINS;
... (output omitted) ...

mysql> INSTALL PLUGIN keyring_file SONAME 'keyring_file.so';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW PLUGINS;
| keyring_file | ACTIVE | KEYRING | keyring_file.so | GPL |
... (output omitted) ...

mysql> CREATE TABLESPACE my_general_tablespace 
    -> ADD DATAFILE 'general_tablespace.ibd' 
    -> ENCRYPTION='Y' 
    -> FILE_BLOCK_SIZE = 16384;
Query OK, 0 rows affected (0.01 sec);

Creating a tablespace outside the data directory requires adding the directory to innodb_directories and restarting MySQL:

root@mysql8:/var/lib# mkdir mysql_user_defined
root@mysql8:/var/lib# chown -R mysql.mysql mysql_user_defined

mysql> CREATE TABLESPACE user_defined_general_tablespace 
    -> ADD DATAFILE '/var/lib/mysql_user_defined/user_defined_general_tablespace.ibd' 
    -> ENGINE=InnoDB;
ERROR 3121 (HY000): The DATAFILE location must be in a known directory.

-- Check configured directories
mysql> SHOW VARIABLES LIKE 'innodb_directories';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_directories |       |
+--------------------+-------+

-- Add the directory to my.cnf and restart
innodb_directories=/var/lib/mysql_user_defined
service mysql restart;

mysql> CREATE TABLESPACE user_defined_general_tablespace 
    -> ADD DATAFILE '/var/lib/mysql_user_defined/user_defined_general_tablespace.ibd' 
    -> ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec);

2 Assigning Tables to a General Tablespace

After creating a general tablespace, you can assign tables to it during creation or by altering existing tables. Example of creating a table in my_general_tablespace with encryption:

mysql> CREATE TABLE my_table (
    ->   id INT PRIMARY KEY,
    ->   name VARCHAR(50)
    -> ) TABLESPACE = my_general_tablespace ENCRYPTION='Y';
Query OK, 0 rows affected (0.02 sec);

Creating an unencrypted table in the unencrypted user_defined_general_tablespace :

mysql> CREATE TABLE my_unencrypted_table (
    ->   id INT PRIMARY KEY,
    ->   name VARCHAR(50)
    -> ) TABLESPACE = user_defined_general_tablespace;
Query OK, 0 rows affected (0.01 sec);

3 Moving Tables to a General Tablespace

To move an existing table, use ALTER TABLE ... TABLESPACE = my_general_tablespace . If the target tablespace is encrypted, the table must also be encrypted or you must first enable encryption on the table.

mysql> ALTER TABLE authors TABLESPACE = my_general_tablespace;
ERROR 3825 (HY000): Request to create 'unencrypted' table while using an 'encrypted' tablespace.

mysql> ALTER TABLE authors ENCRYPTION='Y';
Query OK, 0 rows affected (0.05 sec);

mysql> ALTER TABLE authors TABLESPACE = my_general_tablespace;
Query OK, 0 rows affected (0.03 sec);

To move a table back to a file‑per‑table tablespace, specify innodb_file_per_table as the target:

mysql> ALTER TABLE authors TABLESPACE = innodb_file_per_table ENCRYPTION='Y';
Query OK, 0 rows affected (0.04 sec);

4 Monitoring

The following query retrieves information about a specific MySQL tablespace, including name, file, engine, status, and free space:

SELECT TABLESPACE_NAME, FILE_NAME, ENGINE, STATUS, DATA_FREE
FROM INFORMATION_SCHEMA.FILES
WHERE TABLESPACE_NAME IN ('my_general_tablespace','user_defined_general_tablespace');

To list InnoDB tables that belong to a given tablespace:

SELECT NAME, SPACE_TYPE, TABLESPACE_NAME
FROM INFORMATION_SCHEMA.INNODB_TABLES
JOIN INFORMATION_SCHEMA.FILES ON FILE_ID=SPACE
WHERE TABLESPACE_NAME='my_general_tablespace';

To retrieve tablespace information for a specific table:

SELECT NAME, SPACE_TYPE, TABLESPACE_NAME
FROM INFORMATION_SCHEMA.INNODB_TABLES
JOIN INFORMATION_SCHEMA.FILES ON FILE_ID=SPACE
WHERE NAME='mytestdb/my_table';

Practical Use Cases

Separate frequently accessed tables onto an SSD‑based general tablespace for high performance, while placing rarely used tables on an HDD‑based tablespace to reduce cost.

Balance I/O load by distributing tables across multiple general tablespaces located on different disks, avoiding bottlenecks.

Dedicated storage for critical data: create a RAID‑backed general tablespace for important tables to ensure redundancy and fault tolerance.

5 Conclusion

MySQL general tablespaces provide a powerful and flexible storage solution that can improve data organization and performance. Understanding their capabilities and deploying them thoughtfully can significantly enhance database management, but you should evaluate your workload and requirements before implementation.