MySQL 5.6.20-4 and Oracle Linux DTrace

Https://blogs.oracle.com/wim/entry/mysql_5_6_20_4?utm_source=tuicool&utm_medium=referralBy Wimcoekaerts-oracle on Jul, 2014The 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 your use is Oracle Linux 6, or 7 with UEKR3 (3.8.x) and the latest DTrace utils/tools. MySQL 5.6 is available for install through ULN or from Public-yum. You can just install it using YUM.

# yum Install Mysql-community-server

Then install the DTrace utils from ULN.

# 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 the can try out various dtrace scripts. You can find the reference manual for MySQL DTrace support here.

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-&G T;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 probescpu ID function:name 0 4133 _z16di  Spatch_command19enum_server_commandp3thdpcj:query-start Jul 12:32:21 [email protected] Connection Id:5 Database:Query:select @ @version_comment Limit 1 0 4133 _z16dispatch_command19enum_server_commandp3thdpcj:query -start Jul 12:32:28 [email protected] Connection id:5 Database:Query:SELECT Database () 0 41 _z16dispatch_command19enum_server_commandp3thdpcj:query-start Jul 12:32:28 [email protected] Connecti On Id:5 database:database query:show databases 0 4133 _z16dispatch_command19enum_server_commandp3thdpcj:query- Start 12:32:28 [email protected] Connection id:5 database:database query:show tables 0 4 133 _z16dispatch_command19enum_server_commandp3thdpcj:query-start 12:32:31 [email protected] Connect Ion 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*:::d Elete-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*:::d elete-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->que    Rystart)/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

MySQL 5.6.20-4 and Oracle Linux DTrace