Error message
When you run queries on an ApsaraDB RDS for MySQL instance, the following error occurs:
the table '/home/mysql/xxxx/xxxx/#[$Tab_Name]' is full[$Tab_Name] is the name of an internal temporary table.
Cause
MySQL creates internal temporary tables during query execution. Operations that trigger temporary tables include GROUP BY, ORDER BY, DISTINCT, UNION, multi-table updates, subqueries, table joins, and calls to GROUP_CONCAT() or COUNT(DISTINCT ...).
Internal temporary tables follow a two-stage lifecycle:
In-memory stage: MySQL creates the temporary table with the MEMORY storage engine.
On-disk stage: If the temporary table exceeds the in-memory size limit, MySQL converts it to a MyISAM table on disk. Queries that contain BLOB or TEXT fields skip the in-memory stage and write to disk directly.
The error occurs when the total size of on-disk temporary tables reaches the limit set by loose_rds_max_tmp_disk_space. The default limit is 10 GB.
Diagnose the issue
Run the EXPLAIN statement to check whether a query uses internal temporary tables. If the Extra field shows Using temporary, the query creates an internal temporary table.
EXPLAIN SELECT * FROM alarm GROUP BY created_on ORDER BY detail;Example output:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | alarm | ALL | NULL | NULL | NULL | NULL | 1000 | Using temporary; Using filesort |
If a query shows Using temporary and processes a large result set, it is a candidate for optimization.
Solutions
Apply the following solutions in order. The first two provide immediate relief. The remaining options address the root cause.
Solution 1: Increase the disk temporary table size limit (immediate fix)
Increase the value of loose_rds_max_tmp_disk_space to allow larger on-disk temporary tables.
Set this parameter to a value smaller than the remaining storage of your RDS instance. This reserves space for binary log files and data files, and prevents the instance from being locked.
| Property | Value |
|---|---|
| Parameter | loose_rds_max_tmp_disk_space |
| Unit | Bytes |
| Default | 10737418240 (10 GB) |
| Maximum | 107374182400 (100 GB) |
| Supported versions | MySQL 5.5, MySQL 5.6 |
| Restart required | No |
To modify this parameter:
Log on to the ApsaraDB RDS console.
Go to the Parameters page of your RDS instance.
Increase the value of
loose_rds_max_tmp_disk_spacebased on the remaining storage of your instance.
To check available storage, go to the Monitoring and Alerts page. For more information, see View monitoring information.
Solution 2: Reduce concurrent sessions using disk temporary tables
The loose_rds_max_tmp_disk_space parameter limits the total size of on-disk temporary tables across all sessions. Reducing the number of sessions that use disk-based temporary tables at the same time prevents this limit from being reached.
Identify and terminate long-running queries that generate large temporary tables. Alternatively, schedule resource-intensive queries during off-peak hours.
Solution 3: Increase the in-memory temporary table size limit
Increase tmp_table_size to keep more temporary tables in memory. This reduces the number of conversions to on-disk MyISAM tables.
| Property | Value |
|---|---|
| Parameter | tmp_table_size |
| Unit | Bytes |
| Default | 2097152 (2 MB) |
| Maximum | 67108864 (64 MB) |
To modify this parameter:
Log on to the ApsaraDB RDS console.
Go to the Parameters page of your RDS instance.
Increase the value of
tmp_table_size.
A larger tmp_table_size keeps more temporary tables in memory. This reduces the pressure on loose_rds_max_tmp_disk_space.
Solution 4: Avoid BLOB and TEXT fields in queries
Queries that reference BLOB or TEXT columns always create disk-based temporary tables. They bypass the in-memory stage entirely. Where possible, restructure queries to avoid selecting these columns. For example, select only the columns you need instead of using SELECT *.
Solution 5: Optimize query logic
Reduce the size and number of internal temporary tables by optimizing query logic:
Avoid excessive intermediate dataset operations.
Add indexes to columns used in GROUP BY and ORDER BY clauses.
Break complex queries with multiple GROUP BY, ORDER BY, or DISTINCT operations into simpler sub-queries.
Use WHERE clauses to limit result sets and reduce the data processed in temporary tables.
Monitor temporary table usage
Track the ratio of disk-based temporary tables to total temporary tables to identify issues early:
SHOW GLOBAL STATUS LIKE 'Created_tmp%tables';| Variable_name | Description |
|---|---|
Created_tmp_tables | Total number of internal temporary tables created |
Created_tmp_disk_tables | Number of internal temporary tables created on disk |
A high ratio of Created_tmp_disk_tables to Created_tmp_tables means that many queries exceed the in-memory limit. Consider increasing tmp_table_size or optimizing the queries that generate the most temporary tables.
Before you modify instance parameters
Check the disaster recovery and fault tolerance capabilities of your instance to make sure your data is secure.
Create snapshots or enable log backup for the RDS instance before you modify configurations.
If you submitted sensitive information such as usernames or passwords in the Alibaba Cloud Management Console, change the sensitive information promptly.