Question 276898 in Amazon Web Sevices Question posted in
The official Amazon Web Services documentation can be found here.

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

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

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


  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search