https://blogs.oracle.com/wim/entry/mysql_5_6_20_4?utm_source=tuicool&utm_medium=referral

By WimCoekaerts-Oracle on 

The MySQL team just released MySQL 5.6.20. One of the cool new things for Oracle Linux users is the addition of MySQL DTrace probes. When you use Oracle Linux 6, or 7 with UEKr3 (3.8.x) and the latest DTrace utils/tools, then you can make use of this. MySQL 5.6 is available for install through   or from  . You can just install it using yum.

 

# yum install mysql-community-server

Then install dtrace utils from .

 

# yum install dtrace-utils

As root, enable DTrace and allow normal users to record trace information:

# modprobe fasttrap# chmod 666 /dev/dtrace/helper

Start MySQL server.

# /etc/init.d/mysqld start

Now you can try out various dtrace scripts. You can find the reference manual for MySQL DTrace support .

 

Example1

Save the script below as query.d.

 

#!/usr/sbin/dtrace -qws#pragma D option strsize=1024mysql*:::query-start /* using the mysql provider */{  self->query = copyinstr(arg0); /* Get the query */  self->connid = arg1; /*  Get the connection ID */  self->db = copyinstr(arg2); /* Get the DB name */  self->who   = strjoin(copyinstr(arg3),strjoin("@",     copyinstr(arg4))); /* Get the username */  printf("%Y\t %20s\t  Connection ID: %d \t Database: %s \t Query: %s\n",      walltimestamp, self->who ,self->connid, self->db, self->query);}

Run it, in another terminal, connect to MySQL server and run a few queries.

 

# dtrace -s query.d dtrace: script 'query.d' matched 22 probesCPU     ID                    FUNCTION:NAME  0   4133 _Z16dispatch_command19enum_server_commandP3THDPcj:query-start 2014     Jul 29 12:32:21 root@localhost	  Connection ID: 5 	 Database:  	     Query: select @@version_comment limit 1  0   4133 _Z16dispatch_command19enum_server_commandP3THDPcj:query-start 2014     Jul 29 12:32:28 root@localhost	  Connection ID: 5 	 Database:  	     Query: SELECT DATABASE()  0   4133 _Z16dispatch_command19enum_server_commandP3THDPcj:query-start 2014     Jul 29 12:32:28 root@localhost	  Connection ID: 5 	 Database: database 	     Query: show databases  0   4133 _Z16dispatch_command19enum_server_commandP3THDPcj:query-start 2014     Jul 29 12:32:28 root@localhost	  Connection ID: 5 	 Database: database 	     Query: show tables  0   4133 _Z16dispatch_command19enum_server_commandP3THDPcj:query-start 2014     Jul 29 12:32:31 root@localhost	  Connection ID: 5 	 Database: database 	     Query: select * from foo

 

Example 2

Save the script below as statement.d.

 

#!/usr/sbin/dtrace -s#pragma D option quietdtrace:::BEGIN{   printf("%-60s %-8s %-8s %-8s\n", "Query", "RowsU", "RowsM", "Dur (ms)");}mysql*:::update-start, mysql*:::insert-start,mysql*:::delete-start, mysql*:::multi-delete-start,mysql*:::multi-delete-done, mysql*:::select-start,mysql*:::insert-select-start, mysql*:::multi-update-start{    self->query = copyinstr(arg0);    self->querystart = timestamp;}mysql*:::insert-done, mysql*:::select-done,mysql*:::delete-done, mysql*:::multi-delete-done, mysql*:::insert-select-done/ self->querystart /{    this->elapsed = ((timestamp - self->querystart)/1000000);    printf("%-60s %-8d %-8d %d\n",           self->query,           0,           arg1,           this->elapsed);    self->querystart = 0;}mysql*:::update-done, mysql*:::multi-update-done/ self->querystart /{    this->elapsed = ((timestamp - self->querystart)/1000000);    printf("%-60s %-8d %-8d %d\n",           self->query,           arg1,           arg2,           this->elapsed);    self->querystart = 0;}

Run it and do a few queries.

 

# dtrace -s statement.d Query                                                        RowsU    RowsM    Dur (ms)select @@version_comment limit 1                             0        1        0SELECT DATABASE()                                            0        1        0show databases                                               0        6        0show tables                                                  0        2        0select * from foo                                            0        1        0