DTrace Integration with MySQL 5.0 - Chime demo in MySQL Users Conference 2008

DTrace Integration with MySQL 5

On April 15-16, we demoed a few DTrace probes for MySQL 5.0 integrated with Chime visualization Tool at MySQL users conference 2008. Here is an snapshot of the DTrace probes in chime showing the query execution time/count in Chime tool:

 

 

 

 

 

 

 

 

 

 

 

The DTrace probes inserted into MySQL 5.0 in the demo are:

provider mysql {
    probe data__receive__start(int);
    probe data__receive__finish(int);
    probe query__plan__start(char \*);
    probe query__plan__finish(char \*);
    probe query__execute__start(void \*, char \*, char \*, const char \*, char \*);
    probe query__execute__finish(void \*, char \*, char \*, const char \*, char \*,int);
    probe query__cache__hit(void \*, char \*, char \*, const char \*, char \*);
    probe query__cache__miss(void \*, char \*, char \*, const char \*, char \*);
    probe myisam__wrlck__start();
    probe myisam__wrlck__finish();
    probe innodb__index__next__start(char \*);
    probe innodb__index__next__finish(char \*);
    probe innodb__index__next__same__start(char \*);       
    probe innodb__index__next__same__finish(char \*);
    probe innodb__index__prev__start(char \*);       
    probe innodb__index__prev__finish(char \*);
    probe innodb__index__first__start(char \*);       
    probe innodb__index__first__finish(char \*);
    probe innodb__index__last__start(char \*);       
    probe innodb__index__last__finish(char \*);
    probe innodb__rnd__init__start(char \*);
    probe innodb__rnd__init__finish(char \*);
    probe innodb__rnd__end__start(char \*);
    probe innodb__rnd__end__finish(char \*);
    probe innodb__rnd__next__start(char \*);
    probe innodb__rnd__next__finish(char \*);
    probe innodb__rnd__pos__start(char \*);
    probe innodb__rnd__pos__finish(char \*);
    probe flush__log__start();
    probe flush__log__finish();
    probe innodb__wait__buff__start();
    probe innodb__wait__buff__finish();
};

Many people seeing the demo showed interest to try with the chime tool, per their requests, I'm putting the DTrace patch and chime tool for MySQL DTrace with the instructions on how to use the tool:

1. Apply the DTrace patch for MySQL 5.0.41

    #gpatch -p2 -i mysql-5.0.41-dtrace.patch

2. Compile the MySQL src with the DTrace patch integrated:

    # ./configure --enable-dtrace --prefix=/usr/local/mysql --with-innodb ->32 bit
    # CFLAGS="-m64", CXXFLAGS="-m64" ./configure --enable-dtrace DTRACEFLAGS='-64'

      --prefix=/usr/local/mysql --with-innodb ->64 bit

In MySQL 6.0, there are a few DTrace probes inserted into the source code, you can try with the probes by compiling with "--enable-dtrace" configure option.

3. Install the chime tool for MySQL DTrace: chime.demo.tar by uncompress the chime.demo.tar from /

   # tar xvf chime.demo.tar

4. Start Chime Tool as root:

   #/opt/OSOL0chime/bin/chime

Instead of using Chime to show the probes in the user interface, we can also use the test scripts: dtrace_test.tar to run the DTrace probes. For example, by running the following script, we can get the time spending by each SQL statements waiting for reading page synchronous from disk, so that we can tuning the innodb_buffer_pool_size or SQL accordingly.

#cat innodb_buffer_wait.d

#!/usr/sbin/dtrace -qs

mysql\*:::query-execute-start

{

     this->query = copyinstr(arg4);

}

mysql\*:::innodb-wait-buff-start

{

       self->init = vtimestamp;

}

mysql\*:::innodb-wait-buff-finish

/self->init/

{

       @innittime[this->query] = sum(vtimestamp - self->init);

       self->init = 0;

}

profile:::tick-5s

{

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

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

     printf("Time wait for innodb buffer pool available");

     printa(@inittime):

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

}

# ./innodb_buffer_wait.d

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

Date: 2008 April 17 13:34:43

Time wait for innodb buffer pool available

SELECT c from sbtest where id between ? and ? order by c                       1976800  

SELECT SUM(K) from sbtest where id between ? and ?                            6184600

...

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

  

 

 

 

 

 

 

 

 

 

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

luojiac

Search

Top Tags
Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today