November 21, 2014

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

About Ewen Fortune

Ewen has extensive background in networking and is MySQL Cluster certified. He is a former Percona employee.


  1. peter 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. Currently mysqlsla isn’t very er, intuitive for post-filtering :) I took the same approach Ewen did and found it easier.

  3. 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.


Speak Your Mind