Filesort Probes

The filesort probes are triggered whenever a filesort operation is applied to a table. For more information on filesort and the conditions under which it occurs, see , "ORDER BY Optimization".

filesort-start(database, table)
filesort-done(status, rows)

filesort-start: Triggered when the filesort operation starts on a table. The two arguments to the probe, database and table, will identify the table being sorted.
filesort-done: Triggered when the filesort operation completes. Two arguments are supplied, the status (0 for success, 1 for failure), and the number of rows sorted during the filesort process.

An example of this is in the following script, which tracks the duration of the filesort process in addition to the duration of the main query:

#!/usr/sbin/dtrace -s
#pragma D option quiet dtrace:::BEGIN
{
 printf('%-2s %-10s %-10s %9s %18s %-s \n',
 'St', 'Who', 'DB', 'ConnID', 'Dur microsec', 'Query');
}
mysql*:::query-start
{
 self->query = copyinstr(arg0);
 self->who = strjoin(copyinstr(arg3),strjoin('@',copyinstr(arg4)));
 self->db = copyinstr(arg2);
 self->connid = arg1;
 self->querystart = timestamp;
 self->filesort = 0;
 self->fsdb = '';
 self->fstable = '';
}
mysql*:::filesort-start
{
 self->filesort = timestamp;
 self->fsdb = copyinstr(arg0);
 self->fstable = copyinstr(arg1);
}
mysql*:::filesort-done
{
 this->elapsed = (timestamp - self->filesort) /1000;
 printf('%2d %-10s %-10s %9d %18d Filesort on %s\n',
 arg0, self->who, self->fsdb,
 self->connid, this->elapsed, self->fstable);
}
mysql*:::query-done
{
 this->elapsed = (timestamp - self->querystart) /1000;
 printf('%2d %-10s %-10s %9d %18d %s\n',
 arg0, self->who, self->db,
 self->connid, this->elapsed, self->query);
}

Executing a query on a large table with an ORDER BY clause that triggers a filesort, and then creating an index on the table and then repeating the same query, you can see the difference in execution speed:

Filesort Probes

Retornar