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回答
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年前

ログインしていません。 ログイン 回答を投稿する。

優れた回答とは、質問に明確に答え、建設的なフィードバックを提供し、質問者の専門分野におけるスキルの向上を促すものです。

質問に答えるためのガイドライン

関連するコンテンツ