SQL query fills up RDS free storage space after upgrade to MySQL 5.7
obviously this is an old issue, but we just had a massive problem with this and I thought I'd share:


SELECT queries can write a lot of data to MySQL's temporary file if executed with a 'Using temporary; using filesort;' plan as shown by EXPLAIN EXTENDED.
MySQL can change its execution plan at the drop of a hat based on changing statistics: as a totally not-hypothetical example, it can go from a perfectly reasonable plan using an index to retrieve ~100,000 rows that takes ~15 seconds to a massive on-disk mergesort that takes 45 minutes total :facepalm:.
This temporary table space will be reclaimed automatically on server restart (at least, that's how it worked for us on an AWS RDS MySQL 5.7 instance).


The temp table is usually called ibtmp1, and you can get the size like so:

SELECT *  FROM INFORMATION_SCHEMA.FILES WHERE FILE_NAME LIKE '%ibtmp%' \G

I haven't yet found a way to configure RDS to limit the size of this file.