Tablespace Bloat

Scenario

Tablespace bloat often occurs in GaussDB(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 full migration, DRS uses row-level parallel migration to ensure migration performance and transmission stability. If the source database data is compact, table bloat may occur after data is migrated to the GaussDB(for MySQL) database. As a result, the disk space required is much greater than that of the source database.

Cause 2: Table Fragmentation After a Large Number of Deletions Are Performed

When data is deleted, GaussDB(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 any. If there is no data to fill, tablespace bloat occurs, causing table fragmentation.

You can run the following SQL statement to query detailed information 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;

The optimize table command locks tables for a short period of time. Therefore, you are advised to optimize tablespaces during off-peak hours.