Finding what Created_tmp_disk_tables with log_slow_filter


Whilst working with a client recently I noticed a large number of temporary tables being created on disk.

Looking at a 60 second sample showed there was a steady flow of queries creating disk based tables.

Luckily this client was running the Percona patched version of MySQL.

The microslow patch adds a very useful feature, the ability to log queries by execution plan.

Log only the queries that followed certain execution plan. Multiple flags allowed in a comma-separated string. [qc_miss, full_scan, full_join, tmp_table, tmp_table_on_disk, filesort, filesort_on_disk]

So now all I had to do was set the filter to “tmp_table_on_disk,filesort_on_disk” and I would get only those which use on-disk temporary storage for intermediate results.

Wait a couple of minutes and then set the filter to an empty string to stop collection.

Then it was just a question of examining the slow query log and fixing the queries.
More information about the patches is available here



  1. says

    Thanks Ewen,

    Nice summary of this not so commonly used option. There is also an other way to do it – by simply logging queries with query plans and post filtering the log file.

  2. Hareesh says


    Have you guys done any benchmark on I/O performance with msl patch ?

    I will be interesting to know the results if that is already done.


Leave a Reply

Your email address will not be published. Required fields are marked *