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.

show global status like 'Created_tmp%' | Created_tmp_disk_tables | 91970 | | Created_tmp_files | 19624 | | Created_tmp_tables | 1617031 |

1 2 3 4 5

show global status like 'Created_tmp%'   | Created_tmp_disk_tables           | 91970        | | Created_tmp_files                 | 19624        | | Created_tmp_tables                | 1617031      |

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

mysqladmin ext -ri60 | Created_tmp_disk_tables | 74 | | Created_tmp_files | 3 | | Created_tmp_tables | 357 |

1 2 3 4 5

mysqladmin ext -ri60   | Created_tmp_disk_tables           | 74           | | Created_tmp_files                 | 3            | | Created_tmp_tables                | 357          |

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.

SET GLOBAL log_slow_filter:= “tmp_table_on_disk,filesort_on_disk”;

1	SET GLOBAL log_slow_filter:= “tmp_table_on_disk,filesort_on_disk”;

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

SET GLOBAL log_slow_filter:="";

1	SET GLOBAL log_slow_filter:="";

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