Examining MySQL in real time using DTrace

#!/usr/sbin/dtrace -s

#pragma D option flowindent

pid$target:mysqld::entry
{
}
pid$target:mysqld::return
{
}

# ./mysqlflow.d -p `pgrep -x mysqld`
 0  -> sync_array_get_nth_cell       
 0  <- sync_array_get_nth_cell       
 0 <- sync_array_print_long_waits      
 0 -> os_thread_sleep            
 2 <- os_thread_sleep            
 2 -> sync_arr_wake_threads_if_sema_free  
 2  -> sync_array_enter          
 2   -> os_mutex_enter          
 2    -> os_fast_mutex_lock       
 2    <- os_fast_mutex_lock       
 2   <- os_mutex_enter          
 2  <- sync_array_enter          
 2  -> sync_array_exit           
 2   -> os_mutex_exit           
 2    -> os_fast_mutex_unlock      
 2    <- os_fast_mutex_unlock      
 2   <- os_mutex_exit           
 2  <- sync_array_exit           
 2 <- sync_arr_wake_threads_if_sema_free 
...

#!/usr/sbin/dtrace -s

#pragma D option quiet

pid$target:mysqld:*dispatch_command*:entry
{
 printf("Query: %s\n", copyinstr(arg2));
}

# query_watch.d -p `pgrep -x mysqld`
Query: show tables
Query: select * from Country LIMIT 10
Query: explain user
...

Because we're able to see queries entering the MySQL dispatcher in real time just watching the queries fly by, we can easily identify patterns and trends. Were you aware that your CRM was executing hundreds of identical queries every second?

For those of you reading this who have just realized the security implications of what I'm teaching you to do, you'll be happy to know that only root (or an explicitly authorized user in Solaris) can run DTrace. If your knee-jerk reaction is to be scared of MySQL on a DTrace capable system, remember that we can use DTrace like this for - Oracle, PostgreSQL, Mozilla or any other application. DTrace sees all. If you've never understood the full power of DTrace, I hope it's sinking in.

#!/usr/sbin/dtrace -s

#pragma D option quiet

dtrace:::BEGIN
{
    printf("Tracing... Hit Ctrl-C to end.\n");
}

pid$target::*dispatch_command*:entry
{
    @query[copyinstr(arg2)] = count();
}

When this script is run, it will begin collecting data and won't actually output its report until it completes. The aggregation function count() is filling up @query with the results of arg2 passed to the "dispatch_command" function.

# querycounts.d -p `pgrep -x mysqld`
Tracing... Hit Ctrl-C to end.

^C
 
 select * from CountryLanguage LIMIT 5  1
 show tables     4
 select * from City    10

Obviously, this example output isn't from a real production system. When using in production, you may expect far more output.

The examples we've covered here are only the tip of an iceburg of possibility. When you combine a good MySQL internals reference book with the ability to watch the flow of functions within MySQL, you'll quickly start to understand it in a way you never imagined possible. With this newfound knowledge you'll be able to leverage DTrace to quickly pierce into parts of the code of production workloads. Then, you'll be able to comprehend behavior that you couldn't do so in any other way.

I hope this puts you on solid footing to go forward. Use it to learn, impress your coworkers or save the day. Happy tracing.