All Products
Search
Document Center

Solution to the error "the table '/home /MySQL /xxxx/xxxx/#tab_name' is full" appears in RDS mysql

Last Updated: Sep 07, 2020

Problem description

The following error message is displayed when you use apsaradb RDS for MySQL. [$Tab_Name] is a temporary table.

the table '/home/mysql/xxxx/xxxx/#[$Tab_Name]' is full

 

Causes

When you perform the following operations: group by, order by, distinct, union, multi-table update, group_concat, count(distinct), subquery, and table join, mySQL may use internal temporary tables. MySQL first creates a temporary Memory engine table. When the temporary table is too large, it will be automatically converted to a disk-based MyISAM engine temporary table. When the query involves Blob or Text type fields, mySQL uses temporary disk tables directly. This error message indicates that the physical size of the temporary table on the disk is limited and cannot be extended. This error is caused by the fact that the total size of the internal disk temporary table (MyISAM engine table) used by the query statement has reached the limit specified by the instance parameter loose_rds_max_tmp_disk_space. The default value is 10GB.

 

Solution

Alibaba Cloud reminds you that:

  • When you perform operations that have risks, such as modifying instances or data, check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.
  • Before you modify the configurations and data of instances including but not limited to ECS and RDS instances, we recommend that you create snapshots or enable RDS log backup.
  • If you have authorized or submitted security information such as the logon account and password in the Alibaba Cloud Management console, we recommend that you modify such information in a timely manner.

Perform the following steps as needed.

  • On the parameter setting page of the console, adjust the parameter loose_rds_max_tmp_disk_space based on the current usage of the idle space and application space of the RDS instance (this parameter is only supported by MySQL 5.5 or 5.6). We recommend that you select a smaller value than the currently idle space, and reserve some space for binlogs and data files. This way, the total space occupied by temporary disk tables is too high, and the instance lock is exceeded, which may affect the business.
    Tips the loose_rds_max_tmp_disk_space parameter is specified in bytes. The default value is 10GB and the maximum value is 1,000 GB.
    loose_rds_max_tmp_disk_space
  • Reduce the number of sessions that simultaneously use temporary disk tables. Because the loose_rds_max_tmp_disk_space parameter specifies the total disk temporary table file size, reducing the number of concurrent sessions using the disk temporary table can avoid exceeding the limit specified by the parameter.
  • On the parameter setting page in the console, increase the tmp_table_size parameter to increase the upper limit of the temporary memory tables.
    Tips tmp_table_size is measured in bytes. The default value is 256 kB and the maximum value is 64MB.
    tmp_table_size
  • Avoid using fields of the Blog and Text types during queries.
  • Optimizes query logic to avoid operations on large intermediate datasets.

 

More information

Tips this topic describes how to determine whether a query uses an internal temporary table.

Run the explain command to view the execution plan. The Extra field contains Using temporary, indicates that the internal temporary table is used.

explain select * from alarm group by created_on order by detail;
The command output is as follows:

执行计划

 

Application scope

  • Apsaradb for MySQL