Vince Carbone on MySQL and DTrace: Dynamically tracing better performance

A year ago this past week (on January 16, 2008), Sun Microsystems announced a historic deal to acquire MySQL AB for a billion dollars. Since then, MySQL-ers (aka Sun Dolphins) have settled in with Sun folks. While Sun has had an influence on MySQL, MySQL has also had an influence on several projects within "Sun Classic". Over this week, we hope to explore some of the projects around Sun which use MySQL and how the acquisition has helped bring us all closer together.

Today we talk to Vince Carbone in Sun's Performance Technology Group about MySQL with DTrace, the dynamic tracing application that is part of Solaris and OpenSolaris and set to be a hot topic at the 2009 MySQL User Conference.

Hi Vince, tell us a bit about yourself and about your role with DTrace

I've been at Sun for over 8 years focusing on commercial database benchmarks and performance in the Performance Technology Group. We do a lot of work on the "bleeding-edge". When we get our hands on the hardware and software for a project it is probably six months to a year away from being ready for release. By that time everything is functional but we invariably run into performance issues that no one has seen yet.

The release of DTrace in Solaris 10 has really helped by allowing us to drill down and find the root cause of performance issues. DTrace, Dynamic Tracing, allows real-time probing of almost any OS and user function. It provides facilities to capture all sorts of information about the function including: counts, timings, system stack, user stack, tracings and so on.

What's the coolest thing about DTrace from your perspective?

The coolest thing about DTrace is that you can get into the inner workings of a live system to see what's going on. Every function in Solaris can be probed but since you only activate probes you are interested in, the system impact is usually minimal. Also DTrace provides the ability to probe any user process, including MySQL, using the PID provider.

Another cool thing is our user community: there are many DTrace scripts available on OpenSolaris.org and other sites. The DTrace scripting language, D, is pretty extensive, so very sophisticated scripts have been developed by the community which you get to try out and use. However, most of the time you can get really far with simple scripts.

Can you give us a brief insight into using DTrace with MySQL servers?

DTrace is most useful once you have a working hypothesis, that is you have an idea of what you are looking for. I use the monitoring tools that Solaris provides: lockstat, mpstat, prstat, plockstat, etc. to form a hypothesis and then drill down using DTrace. If it looks like the issue is in MySQL I use the PID provider to find where in MySQL all the time is being spent. It's takes a bit of sleuthing and it's an iterative process, it also doesn't hurt to have the source code available.

MySQL 5.1 and 6.x will have built-in DTrace probes that will give visibility to areas of the MySQL Server to help analyze query loads, profile stored procedures, examine the query cache and so on.

Recently I used DTrace to trackdown poor performance in a MySQL workload where the system monitoring tools showed high system time (as shown by vmstat) and an inordinate number of certain system calls (as shown by truss -c). I wrote a simple DTrace script to get the user stacks associated with the system calls along with counts. I also ran a sophisticated DTrace script written by a colleague that reports the query text and the amount of time spent in each query. From this data I was able to connect the dots to identify the problem query, rewrite it and solve the problem.

How has the acquisition of MySQL into Sun influenced/changed/helped how the work on DTrace with MySQL has progressed over the last year?

As a user of DTrace I've really benefited from the cooperation of Sun and MySQL folks working together to implement DTrace probes within MySQL. This joint effort is ensuring that the right probes are implemented in the right way.

What do you see in the future for MySQL and DTrace?

I expect that DTrace will be one of the major tools used to provide observability into the MySQL server and storage engines. The number of probes will increase and I suspect people will develop higher-level tools that are based on DTrace.

You're giving a talk with Mikael Ronstrom at the 2009 MySQL Users Conference, can you tell us a bit about it?

As an everyday user of DTrace and MySQL, I plan to give a introduction to DTrace from a practical point of view. I will review a case study of how to use Solaris monitoring tools and dTrace to root-cause a performance issue. Of course, we will go over the DTrace probes currently available in MySQL with examples.

What do you think of Sakila the Dolphin?

Dolphins are magical! :)

Thanks Vince, we appreciate the time you took for this interview and can't wait to see you at the 2009 MySQL Users Conference!

This interview was performed in January, 2009.