Yet another one DevOps blog

Ops and DevOps stories

Sep 21, 2019 - 2 minute read - Comments - technical mysql percona systems pitfalls

MySQL/Percona 5.7+ and indefinitely growing temporary tables on the disk

Some time ago I saw an alert in our monitoring, The alert notified that production MySQL/Percona server had less than 10% of disk space left.
I was worried and curious - where did a 300 GB of free space go? And started to investigate the issue.

I found huge MySQL temporary table on the disk.
Issue was fixed by restarting MySQL server (temporary table become small again).

Incident was serious enough to have a post-mortem.
And we found out that by default in MySQL/Percona 5.7 temporary tables are growing indefinitely on the disk.
So how to fix that?

MySQL 5.7 and temporary tables on the disk

I should also mention that indefinite growing of temporary tables on the disk didn’t happen when we had MySQL/Percona 5.6.
That gave us some clue to investigate the issue.
We started to look what’s changed between MySQL/Percona 5.6 and 5.7.

And it appears that in MySQL/Percona 5.7 default storage engine for temporary tables on the disk was changed from MYISAM to InnoDB.
And InnoDB temporary tables on the disk cannot shrink, they can only grow - till you restart the MySQL server.

The best possible fix we found - change storage engine for temporary tables on the disk back to MYISAM.
You just need to add to mysql server config file:


We did that - and live happily ever after.


Terraform >= 0.12.6 map resources import

comments powered by Disqus