AWS Aurora MySQL - Error writing file '/rdsdbdata/tmp/xXx' (Errcode: 28 - No space left on device)

Question

After some search, I understand that the problem is potentially caused by my SELECT id, name, date, score FROM student_grade ORDER BY id, date DESCquery, which creates a temporary table. As the in-memory table size is too small, it has to be written on disk. And the table file is somehow limited, thus the error. Here are my findings so far:

• Cleaning up rdsdbdata/tmp is a no-go, as this is not really manageable with RDS
• The RDS instance itself has plenty of disk space so the actual disk space should not be an issue. With innodb_file_per_table set to ON, I guess each table has its own tmp file, of which the size is limited?
• Increasing tmp_table_size and max_heap_table_size (defaulted at 16MB) would maybe do the trick. I don't know how big is enough though, any recommended size? (some says it should be 1% of DBInstanceClassMemory). Anyway, I created some modified parameter group and configure the db instance to use it. Somehow, the tmp table size remains default (16MB). I'm stuck here too.
• Without the ORDER BY part, the query works. So the sorting seems to be the issue here, is there a work around?
• The output text file of the query (without the ORDER BY part) is roughly 500MB (~200 millions records), should it be the size to set for the tmp table?

So, I've tried everything I could and I think I'll contact cloud team to help with configuration. But other than that, is there any MySql configs should I be tinkering with? Or any sql optimisation I should do with my query?

Answer 1

All InnoDB queries should read rows in index order, so if you can make it read from an index whose order matches the ultimate order you want, then the ORDER BY will be a no-op, and it won't need to sort anything. It'll just return the rows in the order it reads them.

So you would need the table to have an index with columns (id, date) as the leftmost columns of the index. But unfortunately MySQL doesn't support indexes with both ASC and DESC columns until MySQL version 8.0 (AWS Aurora 3.x is based on MySQL 8.0).

ALTER TABLE student_grades ADD KEY `my_new_index` (id ASC, date DESC);

MySQL 5.7, which AWS Aurora 2.x is based on, can only make indexes with ASC columns.

So you have to change the sort order of the result if you want it to use an index. Either ORDER BY id DESC, date DESC or ORDER BY id ASC, date ASC. Either will use the index.

Also you probably have to force the query to use the index, because if you're fetching all the rows, it'll default to a table-scan, skipping the index.

mysql> explain select * from student_grades force index (my_new_index) order by id desc, date desc \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student_grades
   partitions: NULL
         type: index
possible_keys: NULL
          key: my_new_index
      key_len: 133
          ref: NULL
         rows: 2048
     filtered: 100.00
        Extra: NULL

What I'm looking for in this EXPLAIN report is the absence of "Extra: Using filesort".

Answer 2

I had a similar problem with the same error and I fixed my issue by reading this blog post: https://aws.amazon.com/blogs/database/best-practices-for-configuring-parameters-for-amazon-rds-for-mysql-part-1-parameters-related-to-performance/. I then changed the tmp_table_size, and max_heap_table_size to the recommended 64MB. You can do this in Aurora by creating a new DB cluster parameter group for your DB version. Then, you search for those two variables, click edit, and modify them to 67,108,864 bytes (64 MB), 128 MB, or 512 MB as the documentation recommends. Then, assign that parameter group to your database and apply the changes. For my purposes, 64 MB for those variables was more than enough.