Some time ago I saw an alert in our monitoring: a production MySQL/Percona server had less than 10% of disk space left. Where did 300 GB of free space go?
I found a huge MySQL temporary table on disk. Restarting MySQL fixed it (the temporary table shrank back).
The incident was serious enough to warrant a post-mortem. The finding: by default in MySQL/Percona 5.7, temporary tables on disk grow indefinitely.
Why 5.7 and not 5.6#
This didn’t happen on MySQL/Percona 5.6. That gave us a clue: something changed between 5.6 and 5.7.
It turned out that in MySQL/Percona 5.7 the default storage engine for on-disk temporary tables changed from MyISAM to InnoDB. InnoDB temporary tables on disk cannot shrink — they can only grow until you restart MySQL.
The fix#
Switch the on-disk temporary-table storage engine back to MyISAM. Add to your MySQL config:
[mysqld-5.7]
internal_tmp_disk_storage_engine=MYISAMWe did that — and lived happily ever after.