Inserting user-defined DTrace probes into MySQL source code is very useful to help user identify the performance problems in the application level and the database server, In addition, the cost of the USDT probe is basically neglectable. Each probes inserted into the src can be enabled by adding the code like:

 

 If (PROVIDER_PROBE_ENABLED()

{

                PROVIDER_PROBE(arg0,…);

}

 

The steps to add DTrace probes into MySQL is very straightforward.

 

Step 1: Figure out what probes are needed to insert into the source code

This is the difficult part that requires you understand the MySQL implementation details. Generally, it is good to insert probes to clarify the DB response time distribution including processing query, waiting on locks and latches, doing disk I/O, receiving/sending back data. You can certainly define more probes deep into each of the MySQL engines (such as: define probes to measure the cost of innodb sync spin wait)

 

Step 2: Define Provider and probes

Create a mysqlprovider.d file as:

 

provider mysql {

                probe query__execute__start(int);

               probe query__execute__finish(int);

            …

};

It is required to define the probes with easy to understand name. The two underscore(__) is translated to hyphen(-) in the D script file, so the above two probes are called query-execute-start and query-execute-finish

 

Step 3: Define header file for probes

Create mysqlprovider.h file as:

 

#ifndef _MYSQLPROVIDER_H

#define _MYSQLPROVIDER_H

 

#ifdef ENABLE_DTRACE

 

#define MYSQL_QUERY_EXECUTE_START(arg0) \

             __dtrace_mysql__query_execute__start(arg0)

#define MYSQL_QUERY_EXECUTE_START_ENABLED() \

             __dtraceenabled_mysql__query_execute__start()

#define MYSQL_QUERY_EXECUTE_FINISH(arg0) \

             __dtrace_mysql__query_execute__finish(arg0)

#define MYSQL_QUERY_EXECUTE_FINISH_ENABLED() \

             __dtraceenabled_mysql__query_execute__finish()

extern void __ dtrace_mysql__query_execute__start(int)

extern int __ dtraceenabled_mysql__query_execute__start(void)

extern void __ dtrace_mysql__query_execute__finish(int)

extern int __ dtraceenabled_mysql__query_execute__finish(void)

 

#else

/*

*Unless DTrace is explicitly enabled with –enable-dtrace, the MYSQL macros will expand to no-ops.

*/

 

#define MYSQL_QUERY_EXECUTE_START(arg0) \

             __dtrace_mysql__query_execute__start(arg0)

#define MYSQL_QUERY_EXECUTE_START_ENABLED() \

             __dtraceenabled_mysql__query_execute__start()

#define MYSQL_QUERY_EXECUTE_FINISH(arg0) \

             __dtrace_mysql__query_execute__finish(arg0)

#define MYSQL_QUERY_EXECUTE_FINISH_ENABLED()

 

#endif

#endif  /* _MYSQLPROVIDER_H */

 

Step 4: Insert the probes into source code

You need to include the header file created for DTrace probes before inserting the probe macro. And in order to monitor the server behavior as expected, it requires the knowledge of the MySQL source code to add the probe macro into the right place.

 

#include <mysqlprovider.h>

mysql_parse {

…

bool

mysql_execute_command(THD *thd)

{

 

    MYSQL_QUERY_EXECUTE_START(thd->thread_id);

…

case SQLCOM_EXECUTE:

{

   mysql_sql_stmt_execute(thd);

  

   MYSQL_QUERY_EXECUTE_FINISH(thd->thread_id);

   Break;

}

….

 

}

 

Step 5: Build MySQL with DTrace

You will need to specify the “—enable-dtrace” as the configure option to make the DTrace probes available in MySQL on Solaris 10 and above. On the other operating system without the DTrace facility, the DTrace probes are disabled as default.

 

In the Makefile, you can compile the 64-bit MySQL with DTrace probes as bellow:

 

mysqlproviders.o: mysqlproviders.d $(mysqld_OBJECTS)

dtrace -G -64 -s mysqlproviders.d $(mysqld_OBJECTS)

 

 

Now, at this point, you have completed inserting the DTrace probes into MySQL, and the probes are ready to use. For example, to use the query-execute-start and query-execute-stop probes, you can write a simple D script(query-execute.d) to measure the time spending on the query execution for each session.

 

#!/usr/sbin/dtrace –qs

 

mysql*:::query-execute-start

{

                self->init = timestamp;

}

 

mysql*:::query-execute-finish

/self->init/

{

                @inittime[args[0]] = sum(timestamp – self->init);

                self->init = 0;

}

 

profile:::tick-5s

{

printf("--------------------------------------------------\n");

        printf("Date: %Y\n", walltimestamp);

        printf("Query execution time\n");

        printa(@inittime);

        printf("--------------------------------------------------\n");

}

 

 

Now, you can execute the script to get the data for query execution:

#./query_execute.d

--------------------------------------------------

Date: 2007 May 25 19:18:59

Query execution time

 

      149       4542802785

      146       4577178817

      148       4586742308

      147       4602289846

--------------------------------------------------

Please let me know if you find this is useful, any suggestions on which/where probes would be useful in the MySQL server and client application. You can contact me by email: Luojia.chen@sun.com, or comment on this blog.

 

Resources:

• Statically Defined Tracing for User Applications chapter of DTrace manual
• More USDT enhancements
• PostgreSQL DTrace User Guide
• Open Solaris Community:DTrace