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 https://www.percona.com/percona-lab.html

3 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Peter Zaitsev

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.

0
Baron Schwartz

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

0
Hareesh

Hi,

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.

🙂
Hareesh

0