Using Dtrace to find queries creating disk temporary tables

Sometimes we have a lots of small and rather fast queries which use group by/order by, thus creating temporary tables. Some of those queries are retrieving text fields and mysql have to use disk (myisam) temporary tables. Those queries usually run for less than 1-2 seconds, so they did not get into slow query log, however, they sometimes add serious load on the system.

Here is the stat example:

bash-3.00$  /usr/local/mysql/bin/mysqladmin -uroot -p -i 2 -r extended-status|grep tmp_disk
...
| Created_tmp_disk_tables           | 109           |
| Created_tmp_disk_tables           | 101           |
| Created_tmp_disk_tables           | 122           |
...

40-50 tmp_disk_tables created per second

So, how can we grab those queries? Usually we have to temporary enable general log, filter out queries with “group by/order by” and profile them all. On solaris/mac we can use dtrace instead.

Here is the simple script, which will find the list of queries creating tmp_disk_tables:

#pragma D option quiet
dtrace:::BEGIN
{
printf("Tracing... Hit Ctrl-C to end.\n");
}

pid$target::*mysql_parse*:entry
{
self->query = copyinstr(arg1);
}

pid$target::*create_myisam_tmp_table*:return
{
@query[self->query] = count();
}

put it into tmpdisktable.d, chmod +x tmpdisktable.d and run it with
./tmpdisktable.d -p `pgrep -x mysqld`

Ctrl+C after 5 seconds whatever and you will see the queries:

# ./tmpdisktable.d -p `pgrep -x mysqld`
Tracing... Hit Ctrl-C to end.
^C

Queries are stripped by the “strsize”, which is can be tweaked:

#pragma D option strsize=N

We can increase the “strsize” length now and run the script again to get the real queries examples.

Please note: running dtrace for a while can decrease performance, so do not run it for more than couple minutes on production systems.