All Products
Search
Document Center

ApsaraDB RDS:What do I do if the "the table '/home/mysql/xxxx/xxxx/#tab_name' is full" error message is displayed when I use an ApsaraDB RDS for MySQL instance?

Last Updated:Aug 24, 2023

Problem description

The following error message is displayed when I use my RDS instance. In the error message, [$Tab_Name] indicates the name of a temporary table.
the table '/home/mysql/xxxx/xxxx/#[$Tab_Name]' is full

Causes

Internal temporary tables may be used when you perform the following operations: GROUP BY, ORDER BY, DISTINCT, UNION, update on multiple tables, subqueries, and table joins. This also applies if you call the group_concat and count(distinct) functions. MySQL creates temporary tables for the MEMORY storage engine in memory. If the size of the temporary tables is excessively large, the temporary tables are automatically converted to temporary tables for the MyISAM storage engine on the disk. If the queries contain fields of the BLOB or TEXT type, MySQL uses temporary tables on the disk. This error message indicates that the physical size of temporary tables on the disk is limited and can no longer be increased. This error message is displayed because the size of the internal temporary table used by the query statement has reached the upper limit that is specified by loose_rds_max_tmp_disk_space. The default value of loose_rds_max_tmp_disk_space is 10 GB. The internal temporary table is stored on the disk for the MyISAM storage engine.

Solutions

Note
  • Before you perform high-risk operations such as modifying the configurations or data of an instance, we recommend that you check the disaster recovery and fault tolerance capabilities of the instance to ensure data security.
  • Before you modify the configurations or data of an instance, such as an Elastic Compute Service (ECS) instance or an ApsaraDB RDS instance, we recommend that you create snapshots or enable backup for the instance. For example, you can enable log backup for an ApsaraDB RDS instance.
  • If you granted the permissions on sensitive information or submitted sensitive information in the Alibaba Cloud Management Console, we recommend that you modify the sensitive information at the earliest opportunity. Sensitive information includes usernames and passwords.
Use the following methods based on your business requirements:
  • Log on to the ApsaraDB RDS console, go to the Parameters page of your RDS instance, and then increase the value of loose_rds_max_tmp_disk_space based on the remaining storage of the RDS instance and the storage usage of your application. This parameter is supported by MySQL 5.5 and MySQL 5.6. We recommend that you set this parameter to a value that is smaller than the remaining storage of your RDS instance. This way, storage is reserved to store binary log files and data files. This prevents your RDS instance from being locked and ensures service stability.
    Note
    • loose_rds_max_tmp_disk_space is measured in bytes. The default value is 10737418240 bytes, which equals 10 GB. The maximum value is 107374182400 bytes, which equals 100 GB.
    • You can go to the Monitoring and Alerts page of your RDS instance to view the available storage of the instance. For more information, see View the metrics of an ApsaraDB RDS for MySQL instance.
    • The modification of loose_rds_max_tmp_disk_space does not trigger an instance restart.
  • Decrease the number of sessions that concurrently use temporary tables on the disk. loose_rds_max_tmp_disk_space specifies the total size of temporary tables on the disk. You can decrease the number of sessions that concurrently use temporary tables to prevent the size of the temporary tables from exceeding the upper limit that is specified by this parameter.
  • Log on to the ApsaraDB RDS console, go to the Parameters page of your RDS instance, and then increase the value of tmp_table_size to increase the upper limit for the size of temporary tables in memory.
    Note tmp_table_size is measured in bytes. The default value is 2097152 bytes, which equals 2 MB. The maximum value is 67108864 bytes, which equals 64 MB.
  • We recommend that you do not use fields of the BLOB and TEXT types during queries.
  • Optimize query logic to avoid excessive intermediate dataset operations.

References

The following section describes how to check whether a query uses an internal temporary table.

Execute the EXPLAIN statement to view the execution plan. If Using temporary is displayed for the Extra field in the output, an internal temporary table is used.
explain select * from alarm group by created_on order by detail;
Execution plan