Release storage by deleting data files - PolarDB - Alibaba Cloud Documentation Center

Data files generated by a PolarDB for MySQL cluster accumulate over time and can exhaust the available storage capacity. This topic explains how to identify and delete redundant data files to free up storage.

Symptoms

The cluster storage is full or growing unexpectedly due to accumulated data files. To check data space usage, go to the Storage Analysis page in the PolarDB console and review the Storage Trend section.

The following figure shows an example of data space usage in the Storage Trend section.

Storage Trend section on the Storage Analysis page

Solution

Delete redundant tables to reclaim storage by following these steps.

Important

Back up the data of any table before deleting it.
Use DROP TABLE or TRUNCATE TABLE to delete tables and release storage. The DELETE statement does not release storage.
The OPTIMIZE TABLE statement locks the table during execution. Run it during off-peak hours.
After data files are deleted, the cluster takes some time to release the freed storage capacity.
To release more storage beyond data files, delete other file types such as log files. For details, see Release storage by deleting log files.

Procedure

Connect to the PolarDB for MySQL cluster.
On the SQL Console page, run the following query to list data files by size and identify historical or unnecessary data that can be deleted:
```
   SELECT file_name, concat(TOTAL_EXTENTS,'M') as 'FIle_size' FROM INFORMATION_SCHEMA.FILES order by TOTAL_EXTENTS DESC
```
Run DROP TABLE or TRUNCATE TABLE to delete the identified tables. Replace <database name> and <table name> with actual values.
```
   DROP TABLE <database name>.<table name>
   TRUNCATE TABLE <database name>.<table name>
```

Next steps

Scale up storage. PolarDB for MySQL uses a storage-compute decoupled architecture, so storage can be scaled independently. For details, see Manually scale up storage capacity.
Reclaim tablespace fragments. If you frequently run DELETE statements on a table, tablespace fragments accumulate over time. Run the OPTIMIZE TABLE statement during off-peak hours to reclaim tablespace: For details, see Execute the OPTIMIZE TABLE statement to reclaim tablespace.
```
  OPTIMIZE TABLE <database name>.<table name>
```