DTrace probes in MySQL custom probe

Inserting user-defined DTrace probes into MySQL source code was very useful to help user identify the performance problems 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 is 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 is needed to insert into the source code

This is the difficult part, 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 in Nodb 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 underscore (__) is translated to hyphen (-) in the D script file, so the above and the probes are called QUERY-EXECU Te-startand 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 would 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 Mysqlsource code to add the prob e 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'll need to specify the "-enable-dtrace" as the Configure option to make the DTrace probes available in MySQL on Sola RIS ten and above. On the other operating system without the dtracefacility, the DTrace probes is 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 had completed inserting the DTrace probes into MySQL, and the probes is ready for use. For example, with 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 in 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 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 being useful in the MySQL server A ND client application. You can contact me by email:[e-mail protected], or comment on the this blog.

Resources:

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

DTrace probes in MySQL custom probe