Mysql - Finding the reason for temporary disk tables

Question

Recently, I noticed that my MySQL server creates a fairly large number of disk tables [created temporary disk tables: 67, temporary tables created: 304].

I tried to determine which queries these tables create, but I was out of luck. I have included a slow query log for queries that take more than 1 second, but the queries that appear there do not make sense. The only queries that regularly appear in the slow query log are single-row updates to the user table using the primary key as the where clause.

I run “explain” in all queries that run regularly, and I approach the culprit.

Answer 1

The EXPLAIN report might say "Using filesort", but this is misleading. This does not mean that it is written to the file, but only means sorting without using an index.

The EXPLAIN report may indicate "Use temporary", but this does not mean that it uses a temporary table on disk. It can make a small temporary table in memory. The table should fit in the smaller max_heap_table_size and tmp_table_size . If you increase tmp_table_size, you must 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 information on temporary table management.

But 4 concerts for this value are very high! Note that this memory could potentially be used for each connection. The default value is 16 megabytes, so you have increased it by 256 times.

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

If you run MySQL 5.1 or later, you can set GLOBAL long_query_time = 0 so that all queries are displayed in the slow query log. Be sure to do this only temporarily and return it to a non-zero value when you're done !:-)

If you start Percona Server , the slow query log is expanded with additional information and configuration, including the question of whether the query caused a temporary table or a temporary disk table. You can even filter the log of slow queries to include only queries that invoke the temp table or temp disk table (the documents I refer to).

You can also process the Percona Server slow query log with mk-query-digest and filter the queries that invoke the temporary disk table.

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

Answer 2

Often queries using ORDER BY will need to use the temporary table (s). If you run EXPLAIN with these queries, you can see:

using filesort ; using temporary tables

Search for queries with ORDER BY

Answer 3

If you are using MySQL 5.6 or higher, you can use the performance scheme. Try something like:

 select * from events_statements_summary_by_digest where SUM_CREATED_TMP_DISK_TABLES>0\G