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

Share this post

Comments (3)

  • peter

    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.

    September 22, 2008 at 10:21 am
  • Baron Schwartz

    Currently mysqlsla isn’t very er, intuitive for post-filtering 🙂 I took the same approach Ewen did and found it easier.

    September 22, 2008 at 9:01 pm
  • Hareesh


    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.


    September 22, 2008 at 10:50 pm

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.