X

Oracle Linux, virtualization , Enterprise and Cloud Management Cloud technology musings

  • July 31, 2014

MySQL 5.6.20-4 and Oracle Linux DTrace

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 ULN or from public-yum. You can just install it using yum.

# yum install mysql-community-server

Then install 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 you 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=1024
mysql*:::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 probes
CPU 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 quiet
dtrace:::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 0
SELECT DATABASE() 0 1 0
show databases 0 6 0
show tables 0 2 0
select * from foo 0 1 0

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha