Angelo's Soapbox

Using DTrace to observe the SQL statements on a live running MySQL database

DTrace allows you to instrument any live running application in production without the need of extra coding, application recompile or even an application restart. All you need is that the application is running on an OS that supports DTrace. Today Solaris, OpenSolaris, OS X and FreeBSD are a few that have DTrace built in.

For example here is a D-script that instrument MySQL to observe the SQL statements that is being executed in production. Just a 3 line script like this can be extremely useful to observe a live MySQL database

#!/usr/sbin/dtrace -qs
pid$1::*dispatch_command*:entry
{
    printf("%d::%s\n",tid,copyinstr(arg2));
}

# pgrep mysql
418
254
# ./mysql.d 418
11::sugarcrm
11::SET CHARACTER SET utf8
11::SET NAMES 'utf8'
11::SELECT id, name, symbol, conversion_rate FROM currencies WHERE
status = 'Active' and deleted = 0
11::SELECT category, name, value FROM config
11::SELECT id FROM outbound_email WHERE type = 'system'
11::SELECT * FROM outbound_email WHERE id =
'592d612d-3fd2-fa02-f067-48d0467e2da0'
11::
12::sugarcrm
12::SET CHARACTER SET utf8
12::SET NAMES 'utf8'
12::SELECT id, name, symbol, conversion_rate FROM currencies WHERE
status = 'Active' and deleted = 0
12::SELECT category, name, value FROM config
12::SELECT id FROM outbound_email WHERE type = 'system'
12::SELECT * FROM outbound_email WHERE id =
'592d612d-3fd2-fa02-f067-48d0467e2da0'
12::SELECT acl_actions .*, acl_roles_actions.access_override
                   FROM acl_actions
                   LEFT JOIN acl_roles_users ON
acl_roles_users.user_id = '' AND  acl_roles_users.deleted = 0
                   LEFT JOIN acl_roles_actions
12::SELECT count(*) as the_count FROM config WHERE category='info' AND
name='sugar_version' AND value = '5.1.0'
12::SELECT category, name, value FROM config
12::SELECT id FROM outbound_email WHERE type = 'system'
12::SELECT * FROM outbound_email WHERE id =
'592d612d-3fd2-fa02-f067-48d0467e2da0'
12::INSERT INTO tracker
(monitor_id,module_name,date_modified,action,session_id ) VALUES (
'97e1d39b-c036-1e1f-bf2f-48f763a6045c','Users','2008-10-16
15:53:13','login','8fbb9c1a0ac421964fa4a45db4086a46')
12::SELECT user_name FROM users WHERE id='1'

Posted at 11:26AM Oct 16, 2008 by angelo in DTrace  |  Comments[11]