Understanding MySQL Tablespaces: System, Single, and General

Choosing the right tablespace is closely related to routine table management and access performance. In MySQL, a tablespace is a way to manage relational tables using dedicated disk files.

MySQL tablespaces are divided into shared tablespaces (system and general) and single tablespaces.

1. System tablespace

The file ibdata1 in the MySQL data directory stores one or more tables. Its default definition can be set in my.cnf :

innodb_data_file_path=ibdata1:200M;ibdata2:200M:autoextend:max:800M

The system tablespace can also be placed on raw devices, e.g.:

innodb_data_file_path=/dev/nvme0n1p1:3Gnewraw;/dev/nvme0n1p2:2Gnewraw

It contains the double‑writer buffer, change buffer, data dictionary (pre‑MySQL 8.0), table data and indexes.

Three major drawbacks of the system tablespace are:

It cannot shrink automatically, leading to wasted space even after tables are dropped.

Expanding it requires service downtime and manual re‑import of data.

Writes to multiple tables are sequential, limiting concurrency.

To reclaim space in ibdata1 you must:

Export all tables with mysqldump .

Stop the MySQL service.

Reset ibdata1 to its default size.

Import the data back.

Example of space not being released after dropping a table:

# create table t1
mysql> create table t1(id int, r1 char(36)) tablespace innodb_system;
# insert data, ibdata1 grows
# drop table t1
# ibdata1 size remains unchanged

2. Single tablespace

Each table has its own .ibd file, e.g. t1.ibd . You can enable it globally in my.cnf :

innodb_file_per_table=1

Or specify it per table when creating:

create table t1 (id int, r1 char(36)) tablespace innodb_file_per_table;

Advantages include:

Faster TRUNCATE TABLE operations.

Ability to place tables on different disks (e.g., logs on slower storage, hot tables on SSD).

Use OPTIMIZE TABLE to rebuild and shrink tables.

Easy migration of individual tables between instances.

Support for new InnoDB features such as compression and large objects.

Better per‑table monitoring (file size visible at OS level).

Each table can grow up to 64 TB, independent of the instance limit.

Typical rebuild process:

# Show current size of t1.ibd (324M)
# Run: optimize table t1;
# Temporary files '#sql-*.ibd' are created, original file is replaced.
# After rebuild, t1.ibd size reduces to 84M.

Table migration example:

create database ytt2;
use ytt2;
create table t1 like ytt.t1;
alter table t1 discard tablespace;
cp /tmp/t1.ibd ../ytt2/;
alter table t1 import tablespace;

Drawbacks of single tablespaces are potential file‑descriptor increase and possible space fragmentation after heavy DML.

3. General tablespace

Introduced in MySQL 5.7, a general tablespace is shared like the system tablespace but can contain multiple tables. Creation example:

create tablespace ts1 add datafile '/var/lib/mysql-files/ts1.ibd' engine innodb;
create table t1(id int, r1 datetime) tablespace ts1;
create table t2(id int, r1 datetime) tablespace ts1;

Characteristics (a compromise between system and single tablespaces):

Does not shrink automatically.

Can be renamed.

Files can be placed outside the data directory.

Uses fewer file descriptors than single tablespaces but cannot be migrated as easily.

Tables can be moved between tablespaces with ALTER TABLE … TABLESPACE :

alter table t1 tablespace innodb_file_per_table;
alter table t1 tablespace innodb_system;
alter table t1 tablespace ts1;

Destruction rules:

System tablespace cannot be dropped unless all contents are removed.

Single tablespace is removed automatically when its table is dropped or migrated.

General tablespace can be dropped only after all referencing tables are removed or moved.

Overall, the article introduces MySQL's three tablespace types, compares their pros and cons, and provides practical commands for managing them.