Database is creating temporary tables on disk - Amazon Aurora
Supported engine versionsContextLikely causes for this issueActionsRelevant metrics
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Database is creating temporary tables on disk

Your recent on-disk temporary table usage increased significantly, up to percentage. The database is creating around number temporary tables per second. This might impact performance and increase disk operations on db-instance.

Supported engine versions

This insight information is supported for all versions of Aurora MySQL.

Context

Sometimes it's necessary for the MySQL server to create an internal temporary table while processing a query. Aurora MySQL can hold an internal temporary table in memory, where it can be processed by the TempTable or MEMORY storage engine, or stored on disk by InnoDB. For more information, see Internal Temporary Table Use in MySQL in the MySQL Reference Manual.

Likely causes for this issue

An increase in on-disk temporary tables indicates the use of complex queries. If the configured memory is insufficient to store temporary tables in memory, Aurora MySQL creates the tables on disk. This can impact performance and increase disk operations.

Actions

We recommend different actions depending on the causes of your insight.

  • For Aurora MySQL version 3, we recommend that you use the TempTable storage engine.

  • Optimize your queries to return less data by selecting only necessary columns.

    If you turn on the Performance Schema with all statement instruments enabled and timed, you can query SYS.statements_with_temp_tables to retrieve the list of queries that use temporary tables. For more information, see Prerequisites for Using the sys Schema in the MySQL documentation.

  • Consider indexing columns that are involved in sorting and grouping operations.

  • Rewrite your queries to avoid BLOB and TEXT columns. These columns always use disk.

  • Tune the following database parameters: tmp_table_size and max_heap_table_size.

    The default values for these parameters is 16 MiB. When using the MEMORY storage engine for in-memory temporary tables, their maximum size is defined by the tmp_table_size or max_heap_table_size value, whichever is smaller. When this maximum size is reached, MySQL automatically converts the in-memory internal temporary table to an InnoDB on-disk internal temporary table. For more information, see Use the TempTable storage engine on Amazon RDS for MySQL and Amazon Aurora MySQL.

    Note

    When explicitly creating MEMORY tables with CREATE TABLE, only the max_heap_table_size variable determines how large a table can grow. There is also no conversion to an on-disk format.

Relevant metrics

The following Performance Insights metrics are related to this insight:

  • Created_tmp_disk_tables

  • Created_tmp_tables

For more information, see Created_tmp_disk_tables in the MySQL documentation.