How Do I Handle a Large Number of Temporary Tables Being Generated for Long Transactions and High Memory Usage?

Scenario

The memory usage of a GaussDB(for MySQL) instance kept increasing from 11:30 to 12:27 and reached the memory limit.

Figure 1 Memory usage

Possible Causes

1. Check the processlist.log file. In this example, shown below, there were two slow SQL statements in that time period.
   Figure 2 Slow SQL statements
   
2. Analyze slow query logs generated in that time period. There was about 90 GB of data and about 1 billion of data rows in the logs, and there were two SQL statements that took 40 to 50 minutes to execute. The execution time basically overlapped when the memory usage went up in the monitoring results, so we know that the high memory usage was caused by temporary tables.

   

Solution

1. Upgrade the instance specifications to maintain the memory usage within a proper range, preventing a sudden increase in traffic from causing an OOM crash. For details, see Changing vCPUs and Memory of a DB Instance.
2. Optimize slow SQL statements as needed.