Re: MySQL 5.5 who is creating temp tables on disk

[Rick James]

I'm going to guess that the 'real' problem is that some queries are bogging down the system by heavy use of CPU and/or I/O and/or temp table space.

To address that, turn on the slowlog. (It would be good to first crank down long_query_time to something like 2, not the default of 10.) Then look through the slowlog (using MEB or pt-query-digest) to see what is hogging things. The slowlog will point to the villain, while also providing temp table usage and other metrics.

Keep in mind that temp tables are necessary in many cases. This, for example, queries like this _may_ create two temp tables:
SELECT a, sum(b) AS tot FROM foo GROUP BY a ORDER BY tot;

Another thing to note is that not all temp tables actually touch the disk. But that is another discussion.