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 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 statements to query details about a table. The DATA_FREE field indicates the size of tablespace fragmentation.

1. Updating statistics

   analyze table db_name.table_name;

2. Checking the fragmentation size

   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 briefly locks tables, so you should optimize tablespaces during off-peak hours.