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.