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

This topic describes the issue of exhausted storage caused by excessive temporary files. This topic also provides a solution on how to release the storage and perform subsequent maintenance.

Problem description

Temporary table files may be generated in a PolarDB for MySQL cluster when a query statement is executed to sort, group, or associate tables in the cluster. Alternatively, cached binary log files are generated before large transactions are submitted. The generated files occupy a large number of storage resources and may exhaust the storage resources of the cluster. In this case, the error code [1114]; The table '/home/mysql/log/tmp/#sqlxxxxx' is full; error is reported.

Solutions

Terminate sessions that contain messages such as Copy to tmp table and Sending data.
1. Log on to the PolarDB console. Click Clusters in the left-side navigation pane. Select a region in the upper-left corner and click the ID of the cluster in the list to go to the Basic Information page.
2. In the upper-right corner of the Basic Information page, click Log on to Database.
3. In the database that you want to manage, execute the following SQL statement to view a database session:
```
SHOW PROCESSLIST;
```
4. Click the State column in the returned query result to sort the data in the column and check whether the column contains a large number of messages such as "Copy to tmp table" and "Sending data". If the column contains a large number of messages, record the session ID.
5. Execute the following SQL statement to terminate the session:
```
kill [$ID];
```
  In the preceding statement, [$ID] indicates the session ID mentioned in the preceding step.
  Important
  Before you terminate the session, make sure that the session does not affect your business.
If storage is not released after the preceding steps, you can restart all nodes in the cluster to release the storage occupied by temporary files. For more information about how to restart nodes, see Manage nodes.
Modify parameters.
1. Log on to the PolarDB console. Click Clusters in the left-side navigation pane. Select a region in the upper-left corner and click the ID of the cluster in the list to go to the Basic Information page.
2. Choose Settings and Management > Parameters in the left-side navigation pane. Modify the values of tmp_table_size and max_heap_table_size to increase the size of the temporary tablespace.

What to do next

To prevent temporary files generated in queries from occupying a large amount of storage, you can optimize query statements. For example, you can avoid the frequent usage of the Order By and Group By clauses in query statements. You can use EXPLAIN together with an SQL statement to check whether an internal temporary table is used. Example:
```
EXPLAIN SELECT * FROM alarm GROUP BY created_on ORDER BY default;
```
The following output is returned.
If the "Using temporary" message is displayed in the Extra column, an internal temporary table is used.
To prevent cached binary log files from occupying a large amount of storage, you can reduce the number of large transactions, especially large transactions that are run in parallel over multiple connections. If you want to run a large number of large transactions, we recommend that you use short-lived connections. This reduces the storage occupied by cached binary log files.
We recommend that you monitor the storage usage of your cluster on a regular basis, and promptly clear or split data to ensure that storage usage does not exceed 80%.