Full Storage Caused by Complex Queries

Scenario

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

Possible Causes

When you run complex queries on data of a GaussDB(for MySQL) database, GaussDB(for MySQL) 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 other read replicas. If the storage usage of such read replicas is normal, the high storage usage of the primary node or read replica is related to SQL queries running on it.
2. Check the instance slow query logs to find whether any slow-running queries occurred when the storage usage was high.
3. If there is a slow-running 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: Scale up storage space temporarily. Optimizing complex query statements cannot reduce the storage usage right away.