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, you now choose between the TempTable and MEMORY storage engines.
Storage Engine for Internal (Implicit) Temporary Tables
The TempTable storage engine is the default. TempTable uses a common memory pool for all temporary tables that use this engine. The size of this memory pool is specified by the temptable_max_ram parameter. It defaults to 1 GiB.
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: temptable_max_mmap. This parameter chooses whether the data overflows to memory-mapped temporary files or InnoDB internal temporary tables on disk.
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 to 0 turns off the use of memory-mapped temporary files on writer instances.
- You can’t set temptable_max_mmap to 0 on reader instances.
Configuring TempTable Storage Engine (in a Cluster Parameter group)
The below parameters are dynamic and take effect without a system reboot. For example:
aurora_tmptable_enable_per_table_limit = 0 #default value
temptable_max_ram = 10737418240 (10GB)
temptable_max_mmap = 21474836480 (20GB)The above values in this example were calculated while running the query, causing the ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlxx_xxx' is full
And simultaneously monitoring sys.user_summary table (repeatedly run below (or similar) until problem query fails):
MySQL [(none)]> select current_memory from sys.user_summary where user = 'root';
----------------
3.38 GiBConclusion
Fine-tuning how internal temporary tables work in Aurora MySQL can be better achieved using DB Instance Parameter groups rather than a single Cluster Parameter group, as outlined in this example. The client experiencing the ERROR 1114 had just completed a forced Aurora MySQL version 2 to version 3 upgrade (in production) and was effectively experiencing a system outage.
For more information on this topic, refer here: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/ams3-temptable-behavior.html
Check out our MySQL Remote Managed Services and Support.
Contact us for any questions.