By realneel on Jun 10, 2009
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.You can download the script here