New temporary table behavior in Aurora MySQL version 3
Aurora MySQL version 3 handles temporary tables differently from earlier Aurora MySQL versions. This new behavior is inherited from MySQL 8.0 Community Edition. There are two types of temporary tables that can be created with Aurora MySQL version 3:
-
Internal (or implicit) temporary tables – Created by the Aurora MySQL engine to handle operations such as sorting aggregation, derived tables, or common table expressions (CTEs).
-
User-created (or explicit) temporary tables – Created by the Aurora MySQL engine when you use the
CREATE TEMPORARY TABLE
statement.
There are additional considerations for both internal and user-created temporary tables on Aurora reader DB instances. We discuss these changes in the following sections.
Topics
Storage engine for internal (implicit) temporary tables
When generating intermediate result sets, Aurora MySQL initially attempts to write
to in-memory temporary tables. This might be unsuccessful, because of either
incompatible data types or configured limits. If so, the temporary table is
converted to an on-disk temporary table rather than being held in memory. More
information on this can be found in the Internal Temporary Table Use in MySQL
In Aurora MySQL version 3, the way internal temporary tables work is different from
earlier Aurora MySQL versions. Instead of choosing between the InnoDB and MyISAM
storage engines for such temporary tables, now you choose between the
TempTable
and MEMORY
storage engines.
With the TempTable
storage engine, you can make an additional choice for how to handle certain data. The data
affected overflows the memory pool that holds all the internal temporary tables for the DB instance.
Those choices can influence the performance for queries that generate high volumes of temporary data, for example while
performing aggregations such as GROUP BY
on large tables.
Tip
If your workload includes queries that generate internal temporary tables, confirm how your application performs with this change by running benchmarks and monitoring performance-related metrics.
In some cases, the amount of temporary data fits within the TempTable
memory pool or only overflows the
memory pool by a small amount. In these cases, we recommend using the TempTable
setting for internal
temporary tables and memory-mapped files to hold any overflow data. This setting is the default.
The TempTable
storage engine is the default. TempTable
uses a common memory pool for all
temporary tables that use this engine, instead of a maximum memory limit per table. The size of this memory pool is
specified by the temptable_max_ram
In some cases when you use the TempTable
storage engine, the
temporary data might exceed the size of the memory pool. If so, Aurora MySQL stores
the overflow data using a secondary mechanism.
You can set the temptable_max_mmap
Aurora MySQL stores the overflow data differently depending on your choice of data overflow destination and whether the query runs on a writer or reader DB instance:
-
On the writer instance, data that overflows to InnoDB internal temporary tables is stored in the Aurora cluster volume.
-
On the writer instance, data that overflows to memory-mapped temporary files resides on local storage on the Aurora MySQL version 3 instance.
-
On reader instances, overflow data always resides on memory-mapped temporary files on local storage. That's because read-only instances can't store any data on the Aurora cluster volume.
The configuration parameters related to internal temporary tables apply differently to the writer and reader instances in your cluster:
-
On reader instances, Aurora MySQL always uses the
TempTable
storage engine. -
The size for
temptable_max_mmap
defaults to 1 GiB for both writer and reader instances, regardless of the DB instance memory size. You can adjust this value on both writer and reader instances. -
Setting
temptable_max_mmap
to0
turns off the use of memory-mapped temporary files on writer instances. -
You can't set
temptable_max_mmap
to0
on reader instances.
Note
We don't recommend using the temptable_use_mmap
Limiting the size of internal, in-memory temporary tables
As discussed in Storage engine for internal (implicit) temporary tables, you can
control temporary table resources globally by using the temptable_max_ram
You can also limit the size of any individual internal, in-memory temporary table by using the tmp_table_size
The tmp_table_size
parameter defines the maximum size of temporary tables created by the MEMORY
storage engine in Aurora MySQL version 3.
In Aurora MySQL version 3.04 and higher, tmp_table_size
also defines the maximum size of temporary tables
created by the TempTable
storage engine when the aurora_tmptable_enable_per_table_limit
DB
parameter is set to ON
. This behavior is disabled by default (OFF
), which is the same behavior as
in Aurora MySQL version 3.03 and lower versions.
-
When
aurora_tmptable_enable_per_table_limit
isOFF
,tmp_table_size
isn't considered for internal, in-memory temporary tables created by theTempTable
storage engine.However, the global
TempTable
resources limit still applies. Aurora MySQL has the following behavior when the globalTempTable
resources limit is reached:-
Writer DB instances – Aurora MySQL automatically converts the in-memory temporary table to an InnoDB on-disk temporary table.
-
Reader DB instances – The query ends with an error.
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sql
xx_xxx
' is full
-
-
When
aurora_tmptable_enable_per_table_limit
isON
, Aurora MySQL has the following behavior when thetmp_table_size
limit is reached:-
Writer DB instances – Aurora MySQL automatically converts the in-memory temporary table to an InnoDB on-disk temporary table.
-
Reader DB instances – The query ends with an error.
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sql
xx_xxx
' is fullBoth the global
TempTable
resources limit and the per-table limit apply in this case.
-
Note
The aurora_tmptable_enable_per_table_limit
parameter has no effect when
internal_tmp_mem_storage_engineMEMORY
. In this case, the maximum size of an
in-memory temporary table is defined by the tmp_table_size
The following examples show the behavior of the aurora_tmptable_enable_per_table_limit
parameter for writer
and reader DB instances.
Example of writer DB instance with aurora_tmptable_enable_per_table_limit
set to OFF
The in-memory temporary table isn't converted to an InnoDB on-disk temporary table.
mysql> set aurora_tmptable_enable_per_table_limit=0; Query OK, 0 rows affected (0.00 sec) mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@temptable_max_ram,@@temptable_max_mmap; +--------------------+------------------+------------------------------------------+---------------------+----------------------+ | @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@temptable_max_ram | @@temptable_max_mmap | +--------------------+------------------+------------------------------------------+---------------------+----------------------+ | 0 | 3.04.0 | 0 | 1073741824 | 1073741824 | +--------------------+------------------+------------------------------------------+---------------------+----------------------+ 1 row in set (0.00 sec) mysql> show status like '%created_tmp_disk%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Created_tmp_disk_tables | 0 | +-------------------------+-------+ 1 row in set (0.00 sec) mysql> set cte_max_recursion_depth=4294967295; Query OK, 0 rows affected (0.00 sec) mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 60000000) SELECT max(n) FROM cte; +----------+ | max(n) | +----------+ | 60000000 | +----------+ 1 row in set (13.99 sec) mysql> show status like '%created_tmp_disk%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Created_tmp_disk_tables | 0 | +-------------------------+-------+ 1 row in set (0.00 sec)
Example of writer DB instance with aurora_tmptable_enable_per_table_limit
set to ON
The in-memory temporary table is converted to an InnoDB on-disk temporary table.
mysql> set aurora_tmptable_enable_per_table_limit=1; Query OK, 0 rows affected (0.00 sec) mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@tmp_table_size; +--------------------+------------------+------------------------------------------+------------------+ | @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@tmp_table_size | +--------------------+------------------+------------------------------------------+------------------+ | 0 | 3.04.0 | 1 | 16777216 | +--------------------+------------------+------------------------------------------+------------------+ 1 row in set (0.00 sec) mysql> set cte_max_recursion_depth=4294967295; Query OK, 0 rows affected (0.00 sec) mysql> show status like '%created_tmp_disk%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Created_tmp_disk_tables | 0 | +-------------------------+-------+ 1 row in set (0.00 sec) mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 6000000) SELECT max(n) FROM cte; +---------+ | max(n) | +---------+ | 6000000 | +---------+ 1 row in set (4.10 sec) mysql> show status like '%created_tmp_disk%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Created_tmp_disk_tables | 1 | +-------------------------+-------+ 1 row in set (0.00 sec)
Example of reader DB instance with aurora_tmptable_enable_per_table_limit
set to OFF
The query finishes without an error because tmp_table_size
doesn't apply, and the global
TempTable
resources limit hasn't been reached.
mysql> set aurora_tmptable_enable_per_table_limit=0; Query OK, 0 rows affected (0.00 sec) mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@temptable_max_ram,@@temptable_max_mmap; +--------------------+------------------+------------------------------------------+---------------------+----------------------+ | @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@temptable_max_ram | @@temptable_max_mmap | +--------------------+------------------+------------------------------------------+---------------------+----------------------+ | 1 | 3.04.0 | 0 | 1073741824 | 1073741824 | +--------------------+------------------+------------------------------------------+---------------------+----------------------+ 1 row in set (0.00 sec) mysql> set cte_max_recursion_depth=4294967295; Query OK, 0 rows affected (0.00 sec) mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 60000000) SELECT max(n) FROM cte; +----------+ | max(n) | +----------+ | 60000000 | +----------+ 1 row in set (14.05 sec)
Example of reader DB instance with aurora_tmptable_enable_per_table_limit
set to OFF
This query reaches the global TempTable resources limit with aurora_tmptable_enable_per_table_limit
set
to OFF. The query ends with an error on reader instances.
mysql> set aurora_tmptable_enable_per_table_limit=0; Query OK, 0 rows affected (0.00 sec) mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@temptable_max_ram,@@temptable_max_mmap; +--------------------+------------------+------------------------------------------+---------------------+----------------------+ | @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@temptable_max_ram | @@temptable_max_mmap | +--------------------+------------------+------------------------------------------+---------------------+----------------------+ | 1 | 3.04.0 | 0 | 1073741824 | 1073741824 | +--------------------+------------------+------------------------------------------+---------------------+----------------------+ 1 row in set (0.00 sec) mysql> set cte_max_recursion_depth=4294967295; Query OK, 0 rows affected (0.01 sec) mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 120000000) SELECT max(n) FROM cte; ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
Example of reader DB instance with aurora_tmptable_enable_per_table_limit
set to ON
The query ends with an error when the tmp_table_size
limit is reached.
mysql> set aurora_tmptable_enable_per_table_limit=1; Query OK, 0 rows affected (0.00 sec) mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@tmp_table_size; +--------------------+------------------+------------------------------------------+------------------+ | @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@tmp_table_size | +--------------------+------------------+------------------------------------------+------------------+ | 1 | 3.04.0 | 1 | 16777216 | +--------------------+------------------+------------------------------------------+------------------+ 1 row in set (0.00 sec) mysql> set cte_max_recursion_depth=4294967295; Query OK, 0 rows affected (0.00 sec) mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 6000000) SELECT max(n) FROM cte; ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_8_2' is full
Mitigating fullness issues for internal temporary tables on Aurora Replicas
To avoid size limitation issues for temporary tables, set the temptable_max_ram
and
temptable_max_mmap
parameters to a combined value that can fit the requirements of your workload.
Be careful when setting the value of the temptable_max_ram
parameter.
Setting the value too high reduces the available memory on the database instance,
which can cause an out-of-memory condition. Monitor the average freeable memory on
the DB instance. Then determine an appropriate value for
temptable_max_ram
so that you will still have a reasonable amount
of free memory left on the instance. For more information, see Freeable memory issues in Amazon Aurora.
It is also important to monitor the size of the local storage and the temporary table space consumption. You can monitor the temporary storage
available for a specific DB instance with the FreeLocalStorage
Amazon CloudWatch metric, described in Amazon CloudWatch metrics for Amazon Aurora.
Note
This procedure doesn't work when the aurora_tmptable_enable_per_table_limit
parameter is set to
ON
. For more information, see Limiting the size of internal, in-memory temporary tables.
Example 1
You know that your temporary tables grow to a cumulative size of 20 GiB. You want to set in-memory temporary tables to 2 GiB and to grow to a maximum of 20 GiB on disk.
Set temptable_max_ram
to 2,147,483,648
and temptable_max_mmap
to
21,474,836,480
. These values are in bytes.
These parameter settings make sure that your temporary tables can grow to a cumulative total of 22 GiB.
Example 2
Your current instance size is 16xlarge or larger. You don't know the total size of the temporary tables that you might need. You want to be able to use up to 4 GiB in memory and up to the maximum available storage size on disk.
Set temptable_max_ram
to 4,294,967,296
and temptable_max_mmap
to
1,099,511,627,776
. These values are in bytes.
Here you're setting temptable_max_mmap
to 1 TiB, which is less than the maximum local storage of 1.2
TiB on a 16xlarge Aurora DB instance.
On a smaller instance size, adjust the value of temptable_max_mmap
so that it doesn't fill up the
available local storage. For example, a 2xlarge instance has only 160 GiB of local storage available. Hence, we
recommend setting the value to less than 160 GiB. For more information on the available local storage for DB instance
sizes, see Temporary storage limits for Aurora MySQL.
User-created (explicit) temporary tables on reader DB instances
You can create explicit temporary tables using the TEMPORARY
keyword in your CREATE TABLE
statement. Explicit temporary tables are supported on the writer DB instance in an Aurora DB cluster. You can also use
explicit temporary tables on reader DB instances, but the tables can't enforce the use of the InnoDB storage
engine.
To avoid errors while creating explicit temporary tables on Aurora MySQL reader DB instances, make sure that you run all
CREATE TEMPORARY TABLE
statements in either or both of the following ways:
-
Don't specify the
ENGINE=InnoDB
clause. -
Don't set the SQL mode to
NO_ENGINE_SUBSTITUTION
.
Temporary table creation errors and mitigation
The error that you receive is different depending on whether you use a plain CREATE TEMPORARY TABLE
statement
or the variation CREATE TEMPORARY TABLE AS SELECT
. The following examples show the different kinds of
errors.
This temporary table behavior only applies to read-only instances. This first example confirms that's the kind of instance the session is connected to.
mysql>
select @@innodb_read_only;+--------------------+ | @@innodb_read_only | +--------------------+ | 1 | +--------------------+
For plain CREATE TEMPORARY TABLE
statements, the statement fails when the NO_ENGINE_SUBSTITUTION
SQL mode is turned on. When NO_ENGINE_SUBSTITUTION
is turned off (default), the appropriate engine substitution
is made, and the temporary table creation succeeds.
mysql>
set sql_mode = 'NO_ENGINE_SUBSTITUTION';mysql>
CREATE TEMPORARY TABLE tt2 (id int) ENGINE=InnoDB;ERROR 3161 (HY000): Storage engine InnoDB is disabled (Table creation is disallowed).
mysql>
SET sql_mode = '';mysql>
CREATE TEMPORARY TABLE tt4 (id int) ENGINE=InnoDB;mysql>
SHOW CREATE TABLE tt4\G*************************** 1. row *************************** Table: tt4 Create Table: CREATE TEMPORARY TABLE `tt4` ( `id` int DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
For CREATE TEMPORARY TABLE AS SELECT
statements, the statement fails when the
NO_ENGINE_SUBSTITUTION
SQL mode is turned on. When NO_ENGINE_SUBSTITUTION
is turned off
(default), the appropriate engine substitution is made, and the temporary table creation succeeds.
mysql>
set sql_mode = 'NO_ENGINE_SUBSTITUTION';mysql>
CREATE TEMPORARY TABLE tt1 ENGINE=InnoDB AS SELECT * FROM t1;ERROR 3161 (HY000): Storage engine InnoDB is disabled (Table creation is disallowed).
mysql>
SET sql_mode = ''; mysql> show create table tt3;+-------+----------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------+ | tt3 | CREATE TEMPORARY TABLE `tt3` ( `id` int DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +-------+----------------------------------------------------------+ 1 row in set (0.00 sec)
For more information about the storage aspects and performance implications of temporary tables in Aurora MySQL version 3,
see the blog post Use the TempTable storage engine on Amazon RDS for MySQL and Amazon Aurora MySQL