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

I found the following reference while debugging the same issue. It looks like the cause is what @manas sharma was describing in another comment. Aurora MySql 8.0 changed how large queries will function under the hood. You will most likely need to increase the temptable_max_mmap variable for your database. I would take a look at the link I posted as it gives a pretty in-depth explanation on how to tune your db based on your expected workloads.

Additionally this blog post explains how it works under the hood if you are interested.

Answer 2

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.

Answer 3

we have to rewrite the sql to fix this issue, given that we cannot afford to go for larger instances.

Answer 4

It looks like you're running out of disk space for a temporary file.

Some Linux and other UNIX-derived OSs use a RAM disk or other small but fast storage medium for their /tmp/ directory. Some MySQL / MariaDB operations use a lot of temporary file space sometimes. And by default that space is on /tmp/.

You can try doing

SET @@global tmpdir='/var/tmp'

to tell it to use a much larger filesystem instead. If that works, you can change the MySql config to make that the default on startup. Read this. Changing the tmp folder of mysql