Tablespace Bloat

Scenario

Tablespace bloat often occurs in RDS for MySQL instances. For example, a table contains only 11,774 rows of data but occupies 49.9 GB of storage space. After the table is exported to a local directory, it occupies only 800 MB.

Possible Causes

Cause 1: Parallel Migration During DRS Full Migration

During a full migration, DRS uses row-level parallel migration to ensure migration performance and transmission stability. If the source database data is compact, there may be table bloat after data is migrated to RDS for MySQL. As a result, storage usage is much greater than that of the source database.

Cause 2: Table Fragmentation After a Large Number of Deletions

When data is deleted, RDS for MySQL does not reclaim the storage occupied by the deleted data. Instead, it only marks the deletion and fills the space with new data if there is any. If there is no data to fill up the space, tablespace bloat is the result, along with table fragmentation.

You can run the following SQL statement to query details about a table. The DATA_FREE field indicates the size of tablespace fragmentation.

select * from information_schema.tables where table_schema='db_name' and table_name = 'table_name'\G 

Solution

Run the following SQL command to optimize the tablespace:

optimize table table_name;

Note: The optimize table command briefly locks tables, so you should optimize tablespaces during off-peak hours.