Increasing the tmp_table size in Aurora MySQL

0

I am facing an error in my glue job while accessing the huge volume of data from the source[Aurora MySQl].

[Error] The table '/rdsdbdata/tmp/#sql104_24f40a_0' is full

I want to know the root cause of this issue also I've gone through several blogs that suggested increasing the tmp table size.

SET global tmp_table_size = 3*16777216; 
SET global max_heap_table_size = 3*16777216;

If am running this statement in production db does it create any impact or any other better solution to sort out this issue?

It would be more helpful if anyone help me on this to fix this issue.

1 Answer
3

Hello,

This should be the issue at RDS side. In MySQL v8.0, there is a new storage engine named 'TempTable' that enabled with a new variable called "internal_tmp_mem_storage_engine" in parameter group.

This TempTable engine will occupy disk space by creating memory mapped temp files when the max amount of memory has been used, which can be defined using "temptable_max_ram" variable. The problem with these memory mapped files are created in /tmp directory. Once the partition is full, the query fails with "The table 'xxxxx' is full" error.

Please check if you have internal_tmp_mem_storage_engine parameter group is set to TempTable in the RDS instance. If yes, you can modify the parameter group internal_tmp_mem_storage_engine to 'MEMORY'

When storage engine is using MEMORY, then the maximum size for in-memory temporary table is defined by the tmp_table_size or max_heap_table_size" which can be increased as you mentioned. In that case, when the in-memory temporary table exceeds the limit, MySQL automatically converts the data to on-disk temporary table.

profile pictureAWS
SUPPORT ENGINEER
answered 10 months ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions