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개 답변
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.

AWS
지원 엔지니어
답변함 5달 전

로그인하지 않았습니다. 로그인해야 답변을 게시할 수 있습니다.

좋은 답변은 질문에 명확하게 답하고 건설적인 피드백을 제공하며 질문자의 전문적인 성장을 장려합니다.

질문 답변하기에 대한 가이드라인

관련 콘텐츠