Full Storage Caused by Complex Queries

Scenario

The storage usage of a primary instance or read replica is occasionally high or reaches 100%, while the storage usage of the standby instance or other read replicas is within a normal range.

Possible Causes

When you run complex queries on the data of an RDS for MySQL database, RDS creates temporary tables to store the data and operations such as GROUP BY, ORDER BY, DISTINCT, and UNION are executed on the data in the temporary tables. When memory is insufficient, storage space is consumed.

Troubleshooting:

1. Check the storage usage of the standby instance and other read replicas. If the storage usage of such instances is normal, the high storage usage of the primary instance or read replica is related to SQL queries running on it.
2. Check the instance slow query logs to find whether there were any slow queries when the storage usage was high.
3. If there is a slow query, run the explain [slow SQL statement] command to analyze the SQL statement.
4. Check whether the extra column in the command output contains using temporary or using filesort. If yes, a temporary table or file is used during the statement execution. If a large amount of data is queried, the storage usage is high.

Solution

1. Optimize the query statement by adopting the following measures:
   • Add a proper index.
   • Use the WHERE condition.
   • Rewrite the SQL statement to optimize the execution plan.
   • If temporary tables are necessary, reduce the number of concurrent requests.
2. Workaround: Temporarily scale up storage space. Optimizing complex query statements cannot reduce the storage usage right away.