MySQL clients uses COM_* commands to communicate with the MySQL server. MySQL show status breaks these commands into many categories and displays how many times each of these commands were executed. It, however, does not display the time taken to processes those commands as well as how many times the command execution resulted in an error. Enter Dtrace

I wrote a small DTrace script to figure out what commands(COM_*) are being executed on the server and summarize them. If you have used truss -c on Solaris before, you must liked its concise summary; I have tried to present the output in a similar fashion.

For Sysbench read-write (10 queries per transaction) test with 1 thread, each executing 1 transaction, you see

# ./cmdtruss
Sampling... Hit Ctrl-C to end.
^C
Command                  seconds     calls  errors
Query                      0.042         2        
Quit                       0.000         2        
CloseStmt                  0.000        10        
Prepare                    0.000        11       1
Execute                    0.001        20        
                         -------     -----  ------
total:                     0.043        45       1

As you can see from above, each query is COM_STMT_PREPARE, then for each query sysbench executes COM_STMT_EXECUTE 20 times. and then each query is closed via COM_STMT_CLOSE. Each thread connects and disconnects from the server two times. There is one COM_STMT_PREPARE that results in an error. You will also notice that COM_CONNECT is not being captured. This is because the MYSQL_COMMAND_START probe does not capture it.

Here is an example where I am deliberately executing a query that results in an error. # while true; do mysql -e "show foobar"; done . cmdtruss shows

# ~/cmdtruss 
Sampling... Hit Ctrl-C to end.
^C
Command                  seconds     calls  errors
Quit                       0.000        80        
Query                      0.000       160      80
                         -------     -----  ------
total:                         0       240      80

As you can see only half the queries result in an error. Every invocation of mysql program results in select @@version_comment limit 1 being executed (and this succeeds). The other query show foobar results in an error.

This script uses the static probes defined in MySQL 5.4beta, so you cannot use it with earlier versions. Please feel free to use it and give me feedback. I think this is a quick and easy way to figure out what's happening in the MySQL server.