使用AWS re:Post即您表示您同意 AWS re:Post 使用条款

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 年前1407 查看次数
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.

profile pictureAWS
支持工程师
已回答 1 年前

您未登录。 登录 发布回答。

一个好的回答可以清楚地解答问题和提供建设性反馈,并能促进提问者的职业发展。

回答问题的准则