What is a tablespace and why is it used?

Question

While investigating an issue, I came across this error:

30503 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace mysql/innodb_index_stats uses space ID: 2 at filepath: ./mysql/innodb_index_stats.ibd. Cannot open tablespace Mydb/be_command_log which uses space ID: 2 at filepath: ./Mydb/be_command_log.ibd

After reading a bit about this issue, I came to know that this is a known issue of MySQL.

But my problem is that I don't know much about how tablespaces really work. How are they useful? I read this definition, but it does not give all the information.

Can someone share some detailed information about what are tablespaces and how do they work?

Answer 1

A data file that can hold data for one or more InnoDB tables and associated indexes.

There are many types of tablespaces based on the configuration w.r.t the information clubbing per table. These are,

a. System tablespace b. File per tablespace c. General tablespace

System tablespace contains,

1. InnoDB data dictionary.
2. DoubleWrite Buffer.
3. Change buffer
4. Undo Logs.

Apart from this it also contains,

1. Tables &
2. Index data

Associated file is .idbdata1

The innodb_file_per_table option, which is enabled by default in MySQL 5.6 and higher, allows tables to be created in file-per-table tablespaces, with a separate data file for each table. Enabling the innodb_file_per_table option makes available other MySQL features such as table compression and transportable tablespaces.

Associated file is .idbd

InnoDB introduced general tablespaces in MySQL 5.7.6. General tablespaces are shared tablespaces created using CREATE TABLESPACE syntax. They can be created outside of the MySQL data directory, are capable of holding multiple tables, and support tables of all row formats.