SQL query fills up RDS free storage space after upgrade to MySQL 5.7

Question

After the upgrade to MySQL 5.7 the execution of a SQL query fills up the free storage space.

The upgrade was performed through the AWS console, selecting the automatic procedure to upgrade the RDS from MySQL 5.6.27 to MySQL 5.7.11. However, the same query that runs smoothly on mysql5.6 exhaust the available file system space on the mysql5.7 instance.

Researching on the problem, there was identified that the /rdsdbdata/db/innodb/ibtmp1 file was extended up to use all the free storage space. The behaviour is shown on the figures.

An additional comparison of the execution plan between mysql5.6 and mysql5.7 shows differences in the inclusion of 256 million records for 5.7, even after the optimizer_switch parameter was aligned between the two database versions.

Some evidence exhibits a relation to with utilisation of user defined variables, but it is not conclusive. The SELECT statement, for example, includes a @count := @count + 1 attribute.

Question: How to mitigate the fact that MySQL 5.7 changed the execution plan therefore filling up the available storage space of the RDS instance.

Answer 1

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.