Durch die Nutzung von AWS re:Post stimmt du den AWS re:Post Nutzungsbedingungen

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 Antwort
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-TECHNIKER
beantwortet vor einem Jahr

Du bist nicht angemeldet. Anmelden um eine Antwort zu veröffentlichen.

Eine gute Antwort beantwortet die Frage klar, gibt konstruktives Feedback und fördert die berufliche Weiterentwicklung des Fragenstellers.

Richtlinien für die Beantwortung von Fragen