Resolving temporary table exhaustion by modifying RDS DB parameters
Introduction
Hello, I'm Yamato Okui from the Customer Success department.
One day suddenly, Aurora MySQL's CPU usage sticks at 99%, and 5XX errors frequently occur from ALB. This is a bottleneck faced by many applications.
In such situations, it often appears to be an issue with ALB or application servers (EC2 instances). However, the root cause is frequently hidden in the database layer. When database (RDS) performance reaches its limit, the impact cascades as application server response delays and timeouts, ultimately causing ALB, which interfaces with users, to return 5XX errors in a chain reaction.
In this article, we will identify such problems through error log analysis and safely resolve them using AWS CloudShell.## Identifying the Cause
First, we'll investigate the cause of the CPU usage increase from Aurora's error logs.
From Aurora and RDS > Databases > Select the target RDS instance details, choose the "Logs and events" section, and select the log with the same last write time as the "CPU usage spike timing."
In the logs, you may find a large number of error messages like the following that coincide with the timing of the CPU usage spike:
YYYY-MM-DDThh:mm:ss.ssssssZ SESSION_ID [ERROR] [MY-013132] [Server] The table '/rdsdbdata/tmp/#sql*****' is full! (handler.cc:1234)
YYYY-MM-DDThh:mm:ss.ssssssZ SESSION_ID [ERROR] [MY-013132] [Server] The table '/rdsdbdata/tmp/#sql*****' is full! (handler.cc:1234)
YYYY-MM-DDThh:mm:ss.ssssssZ SESSION_ID [ERROR] [MY-013132] [Server] The table '/rdsdbdata/tmp/#sql*****' is full! (handler.cc:1234)
YYYY-MM-DDThh:mm:ss.ssssssZ SESSION_ID [ERROR] [MY-013132] [Server] The table '/rdsdbdata/tmp/#sql*****' is full! (handler.cc:1234)
YYYY-MM-DDThh:mm:ss.ssssssZ SESSION_ID [ERROR] [MY-013132] [Server] The table '/rdsdbdata/tmp/#sql*****' is full! (handler.cc:1234)
...
This "The table ~ is full!" error is the cause of the current issue.
This means that there is insufficient space for the "temporary table" that MySQL internally uses to execute complex queries (such as JOIN operations that combine multiple tables or ORDER BY that sorts large amounts of data).
These temporary tables are "workspaces" dedicated to queries, and with default settings, this workspace is small (often 16MB), so it cannot secure the amount needed for complex queries, causing the process to error.
Data that cannot be processed in memory is written to temporary tables on disk. The frequent occurrence of this error suggests that disk write operations (disk I/O) are happening frequently. This increase in disk I/O consumes a large amount of CPU resources and significantly degrades the overall query performance. As a result, subsequent processes stall, and CPU usage reached 99%. With this discovery, we can deduce that the DB parameter controlling the size of these "temporary tables" needs to be addressed.
Let's Fix It
Precautions before starting work
1: Preliminary check - Understanding the current configuration### 1.1 Check the current parameter group
Confirm which parameter group is currently applied to the target DB instance.
- Input
# Check the parameter group name for read replica 1
aws rds describe-db-instances \
--db-instance-identifier my-aurora-instance-1 \
--query 'DBInstances[0].DBParameterGroups[0].DBParameterGroupName' \
--output text
# Check the parameter group name for read replica 2
aws rds describe-db-instances \
--db-instance-identifier my-aurora-instance-2 \
--query 'DBInstances[0].DBParameterGroups[0].DBParameterGroupName' \
--output text
- Output
default.aurora-mysql8.0
```### 1.2 Checking Current Parameter Values
Next, we'll check the current values of `tmp_table_size` (maximum size of temporary tables) and `max_heap_table_size` (maximum size of MEMORY tables) which are relevant to this issue.
- Input
```bash
# Replace <current parameter group name> with the name confirmed in STEP1
aws rds describe-db-parameters \
--db-parameter-group-name <current parameter group name> \
--query 'Parameters[?ParameterName==`tmp_table_size`||ParameterName==`max_heap_table_size`]'
- Output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| DescribeDBParameters |
+---------------------------+-----------------+------------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+----------------------+------------------+
| AllowedValues | ApplyMethod | ApplyType | DataType | Description | IsModifiable | ParameterName | Source |
+---------------------------+-----------------+------------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+----------------------+------------------+
| 16384-1844674407370954752| pending-reboot | dynamic | integer | Maximum size to which MEMORY tables are allowed to grow. | True | max_heap_table_size | engine-default |
| 1024-18446744073709551615| pending-reboot | dynamic | integer | Defines the maximum size of internal in-memory temporary tables created by the MEMORY storage engine and, as of Aurora 3.04, the TempTable storage engine when aurora_tmptable_enable_per_table_limit is ON. | True | tmp_table_size | engine-default |
+---------------------------+-----------------+------------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+----------------------+------------------+
```- Output content
| Allowed Values | Apply Method | Apply Type | Data Type | Description | Is Modifiable | Parameter Name | Source |
| --- | --- | --- | --- | --- | --- | --- | --- |
| 16384 - 1844674407370954752 | pending-reboot | dynamic | integer | The maximum size to which MEMORY tables are allowed to grow. | True | max_heap_table_size | engine-default |
| 1024 - 18446744073709551615 | pending-reboot | dynamic | integer | Defines the maximum size of internal in-memory temporary tables created by the MEMORY storage engine. In Aurora 3.04 and later, also applies to the TempTable storage engine when `aurora_tmptable_enable_per_table_limit` is ON. | True | tmp_table_size | engine-default |
By default, the size is set to around 16MB.
Note that for temporary tables MySQL creates in memory, the upper size limit is determined by the smaller value between tmp_table_size and max_heap_table_size. Therefore, it's common practice to set both parameters to the same value.
> The maximum size of an in-memory temporary table is defined by the value of tmp_table_size or max_heap_table_size, whichever is smaller.
[8.4.4 Internal Temporary Table Use in MySQL](https://dev.mysql.com/doc/refman/8.0/ja/internal-temporary-tables.html)
## 2: Creating and Modifying Custom DB Parameter Groups
In AWS, you cannot directly edit the default parameter groups. Therefore, you first need to create a custom parameter group.
### 2.1 Check the Parameter Group Family
Verify to specify the correct family for your Aurora engine version.
- Input
```bash
aws rds describe-db-engine-versions \
--engine aurora-mysql \
--query 'DBEngineVersions[?EngineVersion==`8.0.mysql_aurora.3.09.0`].DBParameterGroupFamily' \
--output text
- Output
aurora-mysql8.0
```### 2.2 Create a custom DB parameter group
Specify the family name we looked up earlier to create a new group.
- Input
```bash
aws rds create-db-parameter-group \
--db-parameter-group-name "aurora-mysql80-temp-table-optimized" \
--db-parameter-group-family "aurora-mysql8.0" \
--description "Temporary table optimization for production cluster"
- Output
{
"DBParameterGroup": {
"DBParameterGroupName": "aurora-mysql80-temp-table-optimized",
"DBParameterGroupFamily": "aurora-mysql8.0",
"Description": "Temporary table optimization for production cluster",
"DBParameterGroupArn": "arn:aws:rds:ap-northeast-1:123456789012:pg:aurora-mysql80-temp-table-optimized"
}
}
```### 2.3 Changing Parameters
We'll modify the parameters of our custom group to solve the problem. Here, we'll increase the temporary table size limit (workspace) from 16MB to 256MB. You should adjust these settings according to the complexity of queries issued by your application and the memory size of your instance.
```bash
# Set tmp_table_size to 256MB
aws rds modify-db-parameter-group \
--db-parameter-group-name "aurora-mysql80-temp-table-optimized" \
--parameters "ParameterName=tmp_table_size,ParameterValue=268435456,ApplyMethod=pending-reboot"
# Set max_heap_table_size to 256MB
aws rds modify-db-parameter-group \
--db-parameter-group-name "aurora-mysql80-temp-table-optimized" \
--parameters "ParameterName=max_heap_table_size,ParameterValue=268435456,ApplyMethod=pending-reboot"
# (Recommended) Specify in-memory temporary table engine optimized for MySQL 8.0
aws rds modify-db-parameter-group \
--db-parameter-group-name "aurora-mysql80-temp-table-optimized" \
--parameters "ParameterName=internal_tmp_mem_storage_engine,ParameterValue=TempTable,ApplyMethod=pending-reboot"
ApplyMethod=pending-reboot indicates that a restart is required to activate these changes.
You can also combine multiple --parameters options into a single command.
aws rds modify-db-parameter-group \
--db-parameter-group-name "aurora-mysql80-temp-table-optimized" \
--parameters \
"ParameterName=tmp_table_size,ParameterValue=268435456,ApplyMethod=pending-reboot" \
"ParameterName=max_heap_table_size,ParameterValue=268435456,ApplyMethod=pending-reboot" \
"ParameterName=internal_tmp_mem_storage_engine,ParameterValue=TempTable,ApplyMethod=pending-reboot"
:::## 3: Apply to DB Instances
Once the parameters are ready, apply them to the DB instances. By specifying the --no-apply-immediately option, the application (and restart) will be deferred until the next maintenance window, preventing immediate impact.
During the maintenance window, the parameter group will be automatically applied and the instance will be restarted.
- Input
# Apply to read replica 1
aws rds modify-db-instance \
--db-instance-identifier my-aurora-instance-1 \
--db-parameter-group-name "aurora-mysql80-temp-table-optimized" \
--no-apply-immediately
# Apply to read replica 2
aws rds modify-db-instance \
--db-instance-identifier my-aurora-instance-2 \
--db-parameter-group-name "aurora-mysql80-temp-table-optimized" \
--no-apply-immediately
The response will return the current state of the target instances.
4: Verification and Monitoring After Application
After restart, verify that the settings have been properly applied.
4.1 Verify Parameter Application
Connect to the database using the MySQL client and execute the following SQL to confirm that the global variables have been changed.
- Input
SELECT
@@tmp_table_size/1024/1024 as tmp_table_size_mb,
@@max_heap_table_size/1024/1024 as max_heap_table_size_mb,
@@internal_tmp_mem_storage_engine as storage_engine;
- Output
+-------------------+------------------------+----------------+
| tmp_table_size_mb | max_heap_table_size_mb | storage_engine |
+-------------------+------------------------+----------------+
| 256.00000000 | 256.00000000 | TempTable |
+-------------------+------------------------+----------------+
1 row in set (0.000 sec)
Just in Case: Rollback Procedure
In case unexpected issues occur, it's important to be able to revert to the original state quickly. Apply the original parameter group immediately and restart the instances.
- Input
# Replace <original-parameter-group-name> with the name confirmed in STEP1
aws rds modify-db-instance \
--db-instance-identifier my-aurora-instance-1 \
--db-parameter-group-name <original-parameter-group-name> \
--apply-immediately
aws rds modify-db-instance \
--db-instance-identifier my-aurora-instance-2 \
--db-parameter-group-name <original-parameter-group-name> \
--apply-immediately
# Immediate reboot
aws rds reboot-db-instance --db-instance-identifier my-aurora-instance-1
aws rds reboot-db-instance --db-instance-identifier my-aurora-instance-2
```### Improvement Effects in This Environment
In the environment discussed in this article, we observed the following improvements after the parameter tuning:
- RDS CPU usage: Normalized from 99%+ to below 80%
- ALB 5XX errors: Reduced from hundreds per 5 minutes to fewer than 10
- ALB average response time: Improved from 2-3 seconds to less than 0.5 seconds
- Estimated cause: Resolution of `table is full` errors
However, these effects greatly depend on the application's workload and data volume, so similar results cannot be guaranteed in all environments.
### Summary
In this article, we explained the step-by-step solution for Aurora MySQL performance issues using AWS CloudShell to adjust DB parameters. The key was identifying the root cause (temporary table depletion) behind the symptom (CPU spike) through error logs, and safely implementing configuration changes.
I hope the investigation process and specific solutions introduced in this article will help AWS operators and developers facing similar issues.
### References
**Official Documentation:**
[Modifying Parameters in an Amazon RDS DB Parameter Group](https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.Modifying.html)
[MySQL 8.0 Reference Manual / Alternative Storage Engines / The MEMORY Storage Engine](https://dev.mysql.com/doc/refman/8.0/ja/memory-storage-engine.html)
## About Annotation Inc.
Annotation Inc. is a Classmethod Group company specializing in operations. Our specialized teams in support, operations, development maintenance, information systems, and back-office utilize the latest IT technology, high technical capabilities, and accumulated know-how to solve customer issues. We are recruiting members for various positions. If you are interested in our culture, systems, and work styles that realize "Operation Excellence" and "Work like yourself, live like yourself," please visit the Annotation Inc. recruitment site.
