Angelo's Soapbox

Optimizing the DTrace logger for MySQL queries

The Data Charmer Giuseppe Maxia did some quick tests on the DTrace script and was wondering if I could optimize it a little.

One issue with the old script is that it prints every SQL statement and this can be pretty expensive. This can be minimized by printing to a file. Here is a script that will do just that. The freopen() is not documented but it opens a file and sends all prints to the file. Giuseppe reports a 30% improvement in logging performance with this improved script.

#!/usr/sbin/dtrace -qws
BEGIN
{
	freopen("/tmp/sqls");
}

pid$1::*dispatch_command*:entry
{
	printf("%d::%s\n",tid,copyinstr(arg2));
}

One more optimization that you can use with DTrace is the use of aggregates. Aggregates provides summary information. So this script will not provide you with the running log but if performance of the logger is important and you can live with the summary then this is the script for you.

#!/usr/sbin/dtrace -qs 
pid$1::*dispatch_command*:entry 
{ 
	@[copyinstr(arg2)]=count(); 
}

Strings in DTrace are 256 bytes by default. So these scripts will only show the first 256 chars of the SQL. If you need more then just change the default. Here is the script.

#!/usr/sbin/dtrace -qws
#pragma D option strsize=1024

BEGIN
{
	freopen("/tmp/sqls");
}

pid$1::*dispatch_command*:entry
{
	printf("%d::%s\n",tid,copyinstr(arg2));
}

Finally, DTrace does the printing/aggregating asynchronously, so the performance of DTrace logging should be better than the typical database logging, at least on a system with enough free cycles.

PS: For those trying out the oracle SQL statements I found a better script than mine here

Posted at 12:20PM Oct 24, 2008 by angelo in DTrace  |  Comments[1]