Mysql - Finding cause of temp disk tables

Question

I've recently noticed my MySQL server is creating a reasonably large number of disk tables [created temp disk tables: 67, created temp tables: 304].

I've been trying to identify what queries are creating these tables, but I've had no luck. I've enabled the slow query log for queries taking more than 1 second, but the queries showing up in there don't make sense. The only queries showing up regularly in the slow query log are updates to a single row on a user table, using the primary key as the where clause.

I've run 'explain' on all the queries that run regularly, and I'm coming up blank on the culprit.

Answer 1

The EXPLAIN report may say "Using filesort" but that's misleading. It doesn't mean it's writing to a file, it only means it's sorting without the benefit of an index.

The EXPLAIN report may say "Using temporary" but this doesn't mean it's using a temporary table on disk. It can make a small temp table in memory. The table must fit within the lesser of max_heap_table_size and tmp_table_size. If you increase tmp_table_size, you should also increase max_heap_table_size to match.

See also http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html for more info on temporary table management.

But 4 gigs for that value is very high! Consider that this memory can potentially be used per connection. The default is 16 megs, so you've increased it by a factor of 256.

So we want to find which queries caused the temp disk tables.

If you run MySQL 5.1 or later, you can SET GLOBAL long_query_time=0 to make all queries output to the slow query log. Be sure to do this only temporarily and set it back to a nonzero value when you're done! :-)

If you run Percona Server, the slow query log is extended with additional information and configurability, including whether the query caused a temp table or a temp disk table. You can even filter the slow-query log to include only queries that cause a temp table or temp disk table (the docs I link to).

You can also process Percona Server's slow-query log with mk-query-digest and filter for queries that cause a temp disk table.

mk-query-digest /path/to/slow.log --no-report --print \
  --filter '($event->{Disk_tmp_table }||"") eq "Yes"'