Using MySQL 5.6 to find queries creating disk temporary tables

In my previous post, I’ve showed how to use Dtrace to find queries creating disk temporary tables (only available for OS with dtrace: solaris, freebsd, etc).

In MySQL 5.6 (which is not released yet, use “labs” version for now) we can use new performance_schema table events_statements_history or events_statements_history_long to find all performance metrics for all queries including created disk/memory tables, use of index, etc. WOW! This is what I have been waiting for a long time!

To illustrate, I have grabbed mysql-5.6.3-labs-performance-schema-linux2.6-x86_64.tar.gz from labs.mysql.com (this feature is only in labs version) and run sysbench readonly test (you need to disable prepared statements in sysbench, seems to be not working with prepared statements, I will check it later).

Here are the results:

mysql> select * from events_statements_history_long where  CREATED_TMP_DISK_TABLES > 0 limit 10\G
*************************** 10. row ***************************
              THREAD_ID: 74
               EVENT_ID: 3295633
             EVENT_NAME: statement/sql/select
                 SOURCE: sql_parse.cc:935
            TIMER_START: 633828149000000
              TIMER_END: 633843868000000
             TIMER_WAIT: 15719000000
              LOCK_TIME: 53000000
               SQL_TEXT: SELECT DISTINCT c from sbtest where id between 847399 and 847499 order by c
         CURRENT_SCHEMA: sbtest
            OBJECT_TYPE: NULL
          OBJECT_SCHEMA: NULL
            OBJECT_NAME: NULL
  OBJECT_INSTANCE_BEGIN: NULL
            MYSQL_ERRNO: 0
      RETURNED_SQLSTATE: NULL
           MESSAGE_TEXT: NULL
                 ERRORS: 0
               WARNINGS: 0
          ROWS_AFFECTED: 0
              ROWS_SENT: 1
          ROWS_EXAMINED: 103
CREATED_TMP_DISK_TABLES: 1
     CREATED_TMP_TABLES: 1
       SELECT_FULL_JOIN: 0
 SELECT_FULL_RANGE_JOIN: 0
           SELECT_RANGE: 1
     SELECT_RANGE_CHECK: 0
            SELECT_SCAN: 0
      SORT_MERGE_PASSES: 0
             SORT_RANGE: 0
              SORT_ROWS: 1
              SORT_SCAN: 1
          NO_INDEX_USED: 0
     NO_GOOD_INDEX_USED: 0
       NESTING_EVENT_ID: NULL
     NESTING_EVENT_TYPE: NULL
10 rows in set (0.00 sec)

Or if you need only list of queries:

mysql> select sql_text, count(*) as cnt  from events_statements_history_long 
where  CREATED_TMP_DISK_TABLES > 0 
group by sql_text order by cnt desc  limit 10;
+-----------------------------------------------------------------------------+-----+
| sql_text                                                                    | cnt |
+-----------------------------------------------------------------------------+-----+
| SELECT DISTINCT c from sbtest where id between 242012 and 242112 order by c |   2 |
| SELECT DISTINCT c from sbtest where id between 797388 and 797488 order by c |   2 |
| SELECT DISTINCT c from sbtest where id between 973150 and 973250 order by c |   1 |
| SELECT DISTINCT c from sbtest where id between 478783 and 478883 order by c |   1 |
| SELECT DISTINCT c from sbtest where id between 967035 and 967135 order by c |   1 |
| SELECT DISTINCT c from sbtest where id between 602102 and 602202 order by c |   1 |
| SELECT DISTINCT c from sbtest where id between 123827 and 123927 order by c |   1 |
| SELECT DISTINCT c from sbtest where id between 980527 and 980627 order by c |   1 |
| SELECT DISTINCT c from sbtest where id between 450354 and 450454 order by c |   1 |
| SELECT DISTINCT c from sbtest where id between 674804 and 674904 order by c |   1 |
+-----------------------------------------------------------------------------+-----+
10 rows in set (0.04 sec)

We can filter and order by rows_examined, SORT_MERGE_PASSES, NO_INDEX_USED, NO_GOOD_INDEX_USED, etc.

Links:

• What is new in MySQL 5.6: all long waited great features of MySQL 5.6 (btw: Multi-Threaded Slaves are coming up, now in labs only)
• A Big Bag of Epic Awesomeness, by Mark Leith