Lesson 9 of the SQL Optimization Course: How to Choose the Right MySQL Tablespace

Introduction

For relational databases, the design of tables and SQL is written are particularly crucial. It wouldn’t be an exaggeration to say that they account for 90% of performance. So this time, specifically targeting these two major knowledge areas, we’ll conduct a detailed analysis for you, peeling back the layers.

This Series uses plain and understandable language and selects a large number of examples to elaborate on the subtleties for you.

🧑‍💻 Target audience:

DBA
Database developers
Students

We will use MySQL as the demonstration database.

1. Understanding Tablespace Types

In MySQL, tablespace management significantly impacts table performance and maintenance. Choosing the right tablespace is crucial for optimizing database efficiency.

1.1 System Tablespace

The system tablespace, represented by the ibdata1 file, is the default storage area for InnoDB tables. It contains various components like the double write buffer and data dictionary.

1
923275  12M -rw-r----- 1 mysql mysql  12M March  18 10:42 ibdata1

However, the system tablespace has notable drawbacks:

Inability to Automatically Reclaim Disk Space: Even after deleting tables, the space isn’t automatically released.
Slow Table Separation During Expansion: Requires service downtime and complex procedures for table separation.
Sequential Writing for Multiple Tables: Leads to performance issues when handling multiple tables.

1.2 File-Per-Table Tablespace

The file-per-table tablespace addresses many system tablespace limitations by providing individual .ibd files for each table.

1
917107  96K -rw-r----- 1 mysql mysql  96K March  18 16:13 t1.ibd

Advantages:

Faster truncate operations
Ability to place tables on different disks based on usage patterns
Efficient table optimization for frequent DML operations
Easy table portability between instances
Support for new MySQL features like table compression

Disadvantages:

Potential tablespace bloat with frequent DML operations
Increased file descriptors and memory usage

1.3 General Tablespace

Introduced in MySQL 5.7, general tablespace offers a middle ground between system and file-per-table tablespaces.

1
mysql> create tablespace ts1 add datafile '/var/lib/mysql-files/ts1.ibd' engine innodb;

Characteristics:

Shared tablespace like the system tablespace
Can define tablespace files outside the MySQL data directory
Occupies fewer file descriptors than file-per-table

2. Switching Between Tablespaces

MySQL allows flexible switching between different tablespaces.

1
2
3
mysql> alter table t1 tablespace innodb_file_per_table;
mysql> alter table t1 tablespace innodb_system;
mysql> alter table t1 tablespace ts1;

3. Destroying Tablespaces

System Tablespace: Cannot be destroyed unless all contents are removed.
File-Per-Table Tablespace: Automatically destroyed when the table is deleted or moved.
General Tablespace: Requires all referencing tables to be deleted or moved before destruction.

1
2
mysql> drop tablespace ts2;
ERROR 3120 (HY000): Tablespace `ts2` is not empty.

To successfully drop a general tablespace:

Identify referencing tables
Move tables to another tablespace
Drop the empty tablespace

1
2
mysql> alter table t4 tablespace innodb_file_per_table;
mysql> drop tablespace ts2;

4.Conclusion

Understanding and selecting the appropriate tablespace type is essential for effective MySQL database management. Each tablespace type has its own advantages and limitations, and the choice should be based on specific application requirements and usage patterns.

👋 See you in the next lesson.

What is SQLFlash?

SQLFlash is your AI-powered SQL Optimization Partner.

Based on AI models, we accurately identify SQL performance bottlenecks and optimize query performance, freeing you from the cumbersome SQL tuning process so you can fully focus on developing and implementing business logic.