SQL Error [1114] [HY000]: The table '/rdsdbdata/tmp/#sql161_17a011_a' is full

Question

We are using AWS Aurora MySQL(8.0.mysql_aurora.3.04.0) InnoDB engine with large instance(16GB RAM). One particular “SELECT” query running against one large table (250GB - not partitioned) in the "reader" instance which does some calculations including min,max,window functions, group by, etc. This seems to be resource intensive and after few seconds of execution, it throws the following error

SQL Error [1114] [HY000]: The table '/rdsdbdata/tmp/#sql161_17a011_a' is full

I tried to increase the size of these parameters (@@temptable_max_ram,@@temptable_max_mmap) from 1GB upto 2GB each and still results in same error. Table statistics is up-to-date and also changed these parameters to the following values and still same error.

SET session aurora_tmptable_enable_per_table_limit = ON;
SET session tmp_table_size =  134217728; 
SET session max_heap_table_size =  134217728; 

I tried these variables, but returns empty

show variables like '%Created_tmp_disk_tables%';
show variables like '%Created_tmp_tables%';

innodb_file_per_table   = ON
innodb_data_file_path   = ibdata1:12M:autoextend
internal_tmp_mem_storage_engine = TempTable

I am new to mysql and would like to know,

1. if i am missing something here and
2. way to see the temp table usage when the query is running to see how much size we actually need & fix for this error

Any suggestions would be helpful.

Answer 1

The reason why you are getting this issue is because of the new MYSQL option - temptable_max_mmap. According to what I understand, the query needs to allocate more memory than the default value of 1GB for temptable_max_ram. MySQL then verifies that the temptable_use_mmap option is enabled before allocating disk space in the form of memory-mapped temporary files. And it was working well for me with the prev. version. But MySQL 8.0.* brought a new option temptable_max_mmap (default value is 1Gb too). This option sets the limit for memory-mapped temp files and my query starts to reach it. Afterwards MySQL tries to use InnoDB on-disk internal temporary tables and in this step just fails.

Solution: The internal_tmp_mem_storage_engine variable defines the storage engine used for in-memory internal temporary tables. Permitted values are TempTable (the default) and MEMORY. Please set the value internal_tmp_mem_storage_engine=Memory to resolve this issue . You can also optimise your query as a secondary solution.