Efficient Recovery of standalone tablespaces in MySQL InnoDB

For innodb independent tablespaces, delete cannot recycle the space occupied by the disk. You can use truncate (the principle is to delete or recreate it first;

Directly alter table .... engine = innodb can be used to sort shards and reclaim some tablespaces. When the data volume is small or the buffer pool is small (less than 30 GB), it is quite good;

When there are many shards or a large buffer pool, we need to face risks-when executing DDL statements on the Innodb Storage engine, the entire system will be hang for a short time, the length of hang is related to the Buffer Pool size. The main reason is that when InnoDB drops the table, it will traverse the buf pool LRU linked list two times in a row and lock the traversal process, resulting in the hang of the system. The LRU linked list will be regularly released by the buf pool mutex for the first time, so the impact on the system hang is small. The second time will be held, which has a greater impact on the system hang. -

-- For relevant articles, refer:

MySQL performance of deleting large tables

Explanation of Innodb buffer

Here I will introduce a safe and efficient method for fragment. pt-online-schema-change

Percona is used for non-blocking online ddl, but only alter table... statement to recycle the tablespace, you can use the principle of this tool: create a temporary table, use a trigger to ensure the data consistency with the original table, and finally replace renmame; if you have used this tool, you may have questions. During the final operation of "online ddl", drop old table; drop trigger; however, you can use -- no-drop-old-table to prevent the old table from being deleted. When there is other time, you can use scripts to delete records in batches, finally, drop the remaining "small table"; this avoids hang from living in the system;

I personally tested the table version 2.2.1 and 60 GB, and collected it to 21 GB in about 2 hours;

Recommended reading:

Startup, shutdown, and restoration of the InnoDB Storage Engine

MySQL InnoDB independent tablespace Configuration

Architecture of MySQL Server layer and InnoDB Engine Layer

InnoDB deadlock Case Analysis

MySQL Innodb independent tablespace Configuration