How do I release storage by deleting data files? - PolarDB

This topic describes the issue of a large amount of data files exhausting the storage capacity, how to resolve this issue and next steps.

Problem description

If you store the data files that are generated by a PolarDB for MySQL cluster for a long period of time, the storage may be exhausted. You can view the data space usage in the Storage Trend section on the Storage Analysis page of the PolarDB console. The following figure shows the difference between the routing algorithms.

Solution

Perform the following steps to delete redundant data:

Note

To prevent data loss, backup the data of the table that you want to delete.
Execute the DROP or TRUNCATE statement to delete tables and release storage. The DELETE statement cannot be used to release storage.
If you execute the OPTIMIZE statement on a table, the table becomes locked. Execute the OPTIMIZE statement during off-peak hours.
After data files are deleted, a period of time is required to release the storage capacity of your cluster.
If you need to release more storage after performing the following steps and deleting data files, you can delete other types of files to reduce the storage usage. For more information, see How do I release storage capacity by deleting log files.

Connect to a database cluster.
On the SQL Console page, run the following command to view the file size of the database and check whether historical data or unnecessary data can be deleted.
```
SELECT file_name, concat(TOTAL_EXTENTS,'M') as 'FIle_size' FROM INFORMATION_SCHEMA.FILES order by TOTAL_EXTENTS DESC
```
Execute the DROP or TRUNCATE statement to delete data. Replace the database name and table name parameters in the statement with the real database name and table name.
```
DROP TABLE <database name>.<table name>
TRUNCATE TABLE <database name>.<table name> 
```

What to do next

Scale up the storage of your cluster. PolarDB for MySQL uses an architecture that separates storage resources from computing resources. You can manually scale up the storage capacity of your cluster. For more information, see Manually scale up storage capacity.
If you frequently execute the DELETE statement on a table, tablespace fragments are generated. We recommend that you execute the OPTIMIZE TABLE <database name>.<table name> statement during off-peak hours to reclaim tablespace. For more information, see Execute the OPTIMIZE TABLE statement to reclaim tablespace.