How do I troubleshoot temporary table and storage full issues in Amazon RDS and Aurora MySQL-Compatible?

Short description

When you run queries in Amazon RDS or Aurora MySQL-Compatible, you might encounter the following error message:

"The table '/rdsdbdata/tmp/#sqlxx_###' is full"

You get the preceding error message when you used all the temporary table storage on your Amazon RDS or Aurora MySQL-Compatible instance. You also get the error message when you run queries that generate large temporary tables that exceed the configured storage quotas.

Resolution

Resolve full temporary tables

MySQL 8.0 and Aurora MySQL Compatible 3.x use a TempTable storage engine for complex queries that require you to store intermediate results. 

The TempTable storage engine creates temporary tables in memory, up to the default size for the temptable_max_ram parameter. When you exceed the quota, temporary tables use memory mapped files to overflow to disk storage, up to the default size for the temptable_max_mmap parameter.

If the temporary data that queries generate exceeds default size for the temptable_max_mmap parameter, then you might get the "Table is full" error.

To resolve this issue, take the following actions:

• Increase the temptable_max_ram and temptable_max_mmap parameters to allow more memory and disk space for temporary tables. For more information, see temptable_max_ram and temptable_max_mmap on the MySQL website.
  Note: The temptable_max_mmap parameter must not exceed 80% of the local storage size on the Aurora instance.
• Set the internal_tmp_mem_storage_engine parameter to MEMORY to avoid the temptable_max_mmap disk quota. Also, set the tmp_table_size and max_heap_table_size parameter to limit the size of individual internal temporary tables. For more information, see internal_temp_mem_storage_engine, tmp_table_size, and max_heap_table_size on the MySQL website.
  Note: The MEMORY setting applies only to the Aurora writer instance. On reader instances, Aurora MySQL uses the TempTable storage engine.
• Turn on the aurora_tmptable_enable_per_table_limit and tune tmp_table_size parameters to restrict maximum memory for each internal temp table.
• Use the FreeLocalStorage Amazon CloudWatch metric to monitor the local storage space that's associated with the Aurora DB instance. To monitor the free data storage space that's associated with the RDS MySQL instance, use the FreeStorageSpace CloudWatch metric.

Optimize queries to reduce temporary tables

Some queries might generate large temporary tables that can cause errors.

To optimize queries, take the following actions:

• Use appropriate indexes, especially for queries that involve GROUP BY, ORDER BY, subqueries, and views on partitioned tables.
• Use partition pruning for queries on partitioned tables so that you don't scan the entire table.
• Use indexes for queries that do filesorts to generate implicit temporary tables. Or, adjust the sort_buffer_size parameter.

Resolve other causes of storage full issues

The following issues can also cause storage full issues in Amazon RDS and Aurora MySQL-Compatible instances:

• Large database or table sizes that exceed the local storage capacity. 
• Local disk space usage by audit logs, slow query logs, or general query logs.

To resolve these issues, take the following actions:

• For DDL operations, such as index rebuilds or table alterations, use the Percona toolkit so that you don't generate large temporary tables. For more information, see pt-online-schema-change on the Percona website.
• Monitor local storage usage.
• Optimize queries and table structures.
• Turn off logs.
• Scale your Aurora MySQL instance to increase the local storage capacity.

Related information

Use the TempTable Storage Engine on Amazon RDS for MySQL and Amazon Aurora MySQL

New temporary table behavior in Aurora MySQL version 3

Temporary storage limits for Aurora MySQL