Real-time detection of MySQL with DTrace

Unlike most of us imagine, DTrace does not need to make any changes to MySQL when it comes to MySQL. The most powerful "provider" of DTrace (provider, a set of observable probes) is the FBT (functional boundary tracing, function boundary tracking) provider. We can combine it with a PID provider for in-depth observation of the userland process. Just having some basic knowledge of the MySQL code, we can develop these capabilities.
So how do we get to know the MySQL code? There are two ways. First, you can buy a better related book, such as MySQL internals. If you are serious about MySQL, be sure to buy this book. The second approach is to understand the MySQL code through DTrace itself. One of the best features of DTrace is a compile-indication option called "flowindent". This function displays entry and return accordingly and generates a logical tree so that the user can understand its internal function call relationships. In fact, we can understand the internal situation of MySQL in this way.
Here is the code (MYSQLFLOW.D):
#!/usr/sbin/dtrace-s

#pragma D option Flowindent

Pid$target:mysqld::entry
{
}
Pid$target:mysqld::return
{
}
Place the code in a text file, and then change the file to an executable file. Here, we use the PID provider to observe the "mysqld" process. Because we are not specifying a function (remember, the detector format is PROBIDER:MODULE:FUNCTION:NAME), so it is translated as wildcard. Therefore, we will see all the functions, the entry and return names of all functions are the initial probe names.
When we run the file, we need to use Dtrace–p to specify the PID (procedure ID) of the MySQL daemon. In Solaris, we can easily get the PID by using the following "Pgrep" command.
#./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
...
Use ^c to end the trace. That alone could be enough for a couple of hours.
The first thing you'll find is that InnoDB is very "talkative", even when the database is very quiet. This seemingly constant "sync_array_get_nth_cell ()" function flow is actually the result of a InnoDB checkpoint.
The example above shows us the ability of dtrace to monitor MySQL functions in real time. But to make it work, we also need to understand what has come into these functions. We need to look at some specific functions and their arguments (argument). We all like to watch people, right? What would you think if I told you that I didn't need to log in to the database and not have to look at any log files to see every query that MySQL handled? In fact, it's very simple, you can do it by tracking the "dispatch_command ()" argument.
Here is the script to be used (QUERY_WATCH.D):
#!/usr/sbin/dtrace-s

#pragma D option quiet

Pid$target:mysqld:*dispatch_command*:entry
{
printf ("Query:%s\n", Copyinstr (arg2));
}
Similarly, save the script to a text file and change it to an executable file, execute it with "-P PID", and stop the trace using ^c.
# query_watch.d-p ' Pgrep-x mysqld '
Query:show tables
Query:select * FROM country LIMIT 10
Query:explain User
...
Now that we can see all the queries that go into MySQL dispatcher in real time, we can easily see the situation and trends of the query. I don't know if you noticed, your CRM will execute hundreds of identical queries per second.
You may realize that I want to imply your security problem, only the root user (or a privileged user specifically specified in Solaris) can run DTrace. If your instinctive response is to be afraid of MySQL in the DTrace system, we can use DTrace for Oracle, PostgreSQL, Mozilla, or any other application. DTrace can see everything. If you don't know the power of dtrace yet, I want you to know.
Let's look at this example in more detail below. Duplicate queries can cause serious problems, usually caused by slow client storage or poor application logic. We can use DTrace to calculate all of the duplicate queries and report how many times each query was executed at a specific time period.
Use the following script (QUERYCOUNTS.D):
#!/usr/sbin/dtrace-s

#pragma D option quiet

Dtrace:::begin
{
printf ("Tracing ... Hits ctrl-c to end.\n ");
}

Pid$target::* Dispatch_command*:entry
{
@query [Copyinstr (arg2)] = count ();
}
When you run the script, it starts collecting data until it is finished before the report is output. The combined Calculation function count () assigns the result of the arg2 of the "Dispatch_command" function to @query.
# 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, the output of this example is not the result of a real production system. If used in production, you will get far more results than that output.
The example discussed here is just the tip of the iceberg. If you combine a good MySQL internal reference book, you'll soon find that you understand the way you've never thought of it before. You can take dtrace to various parts of your production code.
I hope this article will help you forge ahead, step by step. You can use it to learn, to attract your colleagues, to save time. Finally, I wish you a happy track!
Special thanks to Derek Crudginton.
About Ben Rockwood is the system Manager for Cloud computing Infrastructure Joyent Corporation. He is a Solaris expert and a sun promoter.

Real-time detection of MySQL with DTrace