Investigation and Resolution of MySQL Temporary Tablespace Disk‑Full Issue

Background : A client reported an RDS MySQL instance with disk‑usage alerts exceeding 80% for both the data and binlog volumes. The alert indicated that the temporary tablespace ibtmp1 had grown to 955 GB, consuming most of the disk.

Investigation Process : Logging into the host revealed a large temporary tablespace file. Examination of running sessions identified a long‑running SQL on port 4675 that generated massive temporary data. The query performed a three‑table join without proper join conditions, causing a Cartesian product, full table scans, and extensive sorting, all of which wrote temporary data to ibtmp1.

Resolution : After coordinating with the project team, the offending session was killed, which stopped further growth but did not immediately free space. A MySQL restart was required to delete and recreate the temporary tablespace, reducing its size back to the default 12 MB.

Analysis of Underlying Mechanism : Official MySQL documentation states that the temporary tablespace is removed during a normal shutdown and recreated on startup. Hence, a restart releases the space. Error logs confirmed this behavior.

Prevention Strategies :

Limit the size of ibtmp1 by configuring innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:500M to prevent uncontrolled growth.

Ensure multi‑table joins include appropriate join conditions and indexes to avoid Cartesian products and excessive temporary data.

Use EXPLAIN to review execution plans and watch for the Using temporary flag.

Additional Findings :

Queries that create temporary tables can be monitored via the dictionary tables sys.x$statements_with_temp_tables and sys.statements_with_temp_tables. Example query:

select * from sys.x$statements_with_temp_tables where query like 'select%' and db='test' order by tmp_tables_to_disk_pct, disk_tmp_tables desc\G;

In MySQL 8.0, temporary data is stored in a session‑specific temporary tablespace (e.g., temp_8.ibt) which can be reclaimed by terminating the session, unlike MySQL 5.7 where the global ibtmp1 required a server restart.

Conclusion : In MySQL 5.7, killing a session does not release disk space occupied by ibtmp1; a server restart is necessary. In MySQL 8.0, terminating the offending session frees the session temporary tablespace, avoiding the need for a restart.

References :

https://dev.mysql.com/doc/refman/5.7/en/innodb-temporary-tablespace.html

https://dev.mysql.com/doc/refman/8.0/en/innodb-temporary-tablespace.html

http://mysql.taobao.org/monthly/2019/04/01/