Thread: Getting Started with DTracing MySQL

Trace is a dynamic tracing facility built into the Solaris and Open Solaris operating systems and can be used by systems administrators and developers to observe the
runtime behaviour of user-level programs and of the OS itself. On one hand, DTrace can be used to identify potential bottlenecks in the running processes on a production system, while on the other hand it can help you understand the runtime behaviour of an external program such as MySQL better.

Originally available on Solaris, DTrace has now been ported to Mac OSX. FreeBSD and an experimental Linux port is also available. In this article, I shall use the OpenSolaris 2008.11 release to demonstrate how it works.

Some concepts first

The DTrace architecture [check the References] gives you a good look at the various components of the DTrace framework. The graphic in Figure 1 (reproduced from the DTrace how-to at WWw.sun.com/software/ solaris/howtoguides/ dtracehowtojsp ) illustrates the DTrace framework and its various components. Note that 'probes', about which we will learn more shortly (and not shown in the figure) can be best visualised as sensors available to be probed by the DTrace consumers in user space.

We shall now learn the basic DTrace concepts that will help us during some serious playing around with DTrace and MySQL.


Probes, providers and consumers

DTrace dynamically modifies the operating system kernel and user processes to
record data at locations of interest (or instrumentation points) called probes. The probe is user specified, and its specificity and description determine the benefit derived from DTrace. A probe is a location or activity to which the DTrace framework can bind a request to perform actions, such as logging the system calls, the function calls in user level processes, recording a stack trace and so on. A probe is said to fire when the activity, as specified by the probe description, takes place. When a probe is fired, the requested action will take place. A probe has the following attributes that identity it uniquely:
•It has a name and a unique integer identifier
•It is made available by a provider
•It identifies the module and the function that it instruments

The current probes available on your system can be displayed by pfexec dtrace-l. By using various switches, it is also possible to display only the probes belonging
to, say, a particular module. For example,pfexec dtrace-l m:mysqi" will list all the probes available via the mysql" provider. (Note the 'in mysqF' denotes all modules with names starting with mysql.)

DTrace probes are implemented by kernel modules called providers, each of which perform a particular kind of instrumentation to create probes. Providers can thus be described as publishers of probes that can be used by DTrace consumers. Providers can be used for instrumenting kernel and user-level code. For user-level code, there are two ways in which probes can be defined: User-Level Statically Defined Tracing (USDT) or the PID provider.

In USDT, custom probe points are inserted into application code according to well-defined guidelines and practices. Refer to www.solarisinternals.com/wiki/index. php/DTrace_Topics_USDTfor more details. Once the custom probe points are integrated, the application code is compiled and the binary is run, the probes become available for consumption by DTrace user-level consumers. However, unless they are used, the probes have a zero impact on the performance ofthe application or the system as a whole.

Does this mean that DTrace cannot be used with applications with no USDT probes defined? No, it doesn't.

The PID provider can be used to probe any user-level process, whether USDT probes were defined for it or not. Using the PID provider is a very generic and easy way to play around with DTrace. Code a simple application in your favourite programming language and have fun with DTrace by observing the function call flow, stack trace and a lot more. In the later part of this article, we shall use both of the above for DTracing a running MySQL server.

Probe descriptions": A DTrace probe, as mentioned earlier, is uniquely specified by a 4-tuple, and usually takes the follOwing form:

If one or more of the fields are missing, the specified fields are interpreted in a right-to-left fashion, i.e., if a probe description is given asfoo:bar, the probe description matches all probes with the functionfoo and the name bar, regardless of the provider or the module. To obtain the desired results, specity all the required fields. You may also want to match all the probes published by any given provider, for which you would use the probe description like fbt:::, which matches all the probes ofthejbtprovider. [You can read the manual page ofjbt at docs.sun.com/app/ docs/ doc/816-5177 /6mbbc4g4t?a=view.]

A DTrace consumer is any process that interacts with the DTrace framework. The consumer specifies the instrumentation points by specitying probe descriptions. dtrace is the primary consumer ofthe DTrace framework. (Now, do you see the difference between DTrace and dtrace?)

DTrace scripts or D-scripts
Programs or scripts to interact with the DTrace framework are written in the D programming language. A D program source file consists of one or more probe clauses that describe the points of instrumentation to be enabled by DTrace. Each probe clause has the following form:

A D program can consist of one or more such probe clauses. The predicate and the list of action statements are optional and may not be required in some scenarios. D programs are described in detail at docs.sun.com/app/ docs/doc/817-6223/chp-prog?a=view. A D program can be executed by specifying it via the '-s' switch to dtrace or making it an executable (like a shell script) and setting 'dtrace' as the interpreter, by putting #!/usr/sbin/dtrace in the script.

Using DTrace with MySQL
As mentioned earlier, there are two ways in which DTrace can be used with any user-level process-USDT and the PID provider. We shall see demonstrations of both these mechanisms as we start using DTrace with the MySQL server, or specifically 'mysqld'. One thing to note here is that DTrace one-liners can be used to demonstrate a lot of what we will be doing. But, to make the learning easier, we will use D-scripts, however small they may be. Familiarity with the MySQL source code is required to cierive the maximum advantage from the rest of the article.

Before I start off with writing D scripts, here are some common points worth noting:

•Like a shell script, you can make a D script executable by using chmod +x and specifying the location of the script interpreter using a#!, like: #!/usr/sbin/dtrace.

•You can .specify various switches to dtrace. For example, to specify a D script to dtrace, you will use the -s switch.'

•The parameters of the function being traced are available to a D script using built-in variables: argO, argl, arg2 ... Other built-in variables like timestamp, walltimestamp are described at docs.sun.com/app/ docs/ doc/817-6223/chp- variables-5?a=view. timestamp gives the current value of a nanosecond counter, which increments from an arbitrary time in the past and is useful for relative time calculation. walltimestamp, the current number of nanoseconds since UTe, is more suited when date/time value is required.

•copyinstr is used to copy the value of a char" type parameter to a variable in your D script. By default, strings up to a maximum size of256 can be stored. You can change it using #pragma D option strsize=l024.

•When monitoring probes for a multi-threaded application, such as mysqld, it is essential that each thread (and its variables) is treated as such. Thread-local variables, denoted using a "self-> ": makes it possible to prevent corruption of the variables of one thread by another.

•DTrace allows use of clause-local variables. To declare a variable as clause-local, specify it as this->, such as this->bar. As suggested by its name, the scope of a clause-local variable is limited to the probe clause or predicate, in which it is used. For more information on thread-local and clause-local variables, please refer to wikis.sun.com/ display /DTrace/Variables.

•A number of macro variables are available in DTrace. A very commonly used one is "$target": which is used in scripts using the PID provider.

Using the PID provider

To use the PID provider, you need to have a mysqld instance running on an (Open)Solaris system. (You won't need any special build of MySQL for this.) Please note that the function names are garbled in a binary. Hence any command, for example, mysql_parse will not be exactly the same, but will have other text at the starting and the ending. We can use "nm" to see the garbled names:

Hence, we shall simply use the regex'*' at the beginning and end of the function name in our D scripts.

•Watching queries

Save this script to a file, say watch.d. A D script is specified to dtrace with the -s switch. The Process ID (PID) specified via the switch -p is automatically made available to the $target macro in the D script.

Fire up a MySQL client and run some queries. The D script should display the queries that you executed from the client:

• Follow the query execution:

An SQL query before execution passes through various other stages, the first of which is the query parsing. The query parsing is taken care of by the mysq(parse function in sql/sqlyarse.cc. Since all the other stages, such as query optimisation and, finally, execution follows from there, by using the following script, we can track all the functions that call and return from after mysql_parse:

• Logging queries:

So, we have watched queries go by; now how about capturing them into a file so as to use them for our own file logging purposes? We shall use a DTrace destructive function freopen that redirects all that is written to a standard output into the specified file. We are going to snoop on the "dispatch_command':' (in sq_parse.cc) function:

Using the embedded static probes

The PID provider helps us get up to speed really fast when we are learning to DTrace any user-level application. It also doesn't need a specially-built application binary. However, we need to know the source code of the application really well. A basic knowledge will enable us to write D scripts, which are only as good. DTrace static probes in an application partially reduce the need to know the code, end-to-end, in order to write useful probes. The reason is that the embedded probes can be the highest level of abstraction to the important functions that are useful and likely to be monitored for performance considerations. As noted earlier, as long as the static probes are not used, no performance hit is experienced.

Static probes are being gradually integrated into MySQL. As of MySQL 6.0.9, there are around 55 static probes. The probes are defined and documented in the sql/ probes.d file, which is a good place to look at the currently available probes and understand how to use them in your D scripts. The currently available probes are also well described in the MySQL reference manual at dev.mysql. com/ doc/refman/6. 0/ en/ dba-dtrace- mysqld-rej. html.

Where can DTrace help the MySQL community?

The niche MySQL community into which DTrace can breathe life is the database administrators (DBAs) who strive to keep the database in good health at all points. It's easy to identify the performance bottlenecks that might have crept into the server over a period of time. With intelligent probe descriptions, it's simple to get relevant statistics of a running MySQL server.

Besides the DBAs, DTrace is a great tool to understand how the control flow occurs in the MySQL server, from when it receives a client request, till it serves the request. This makes it very easy to understand all the different sub-components of the MySQL server architecture.