DTrace in MySQL: Documentation and a MySQL University Session
DTrace has been something that I’ve been trying to get into the MySQL server for more than a year now.
After a combination of my own patches and working with Mikael Ronstrom and Alexey Kopytov we finally have a suite of probes in MySQL 6.0.8. Better still, after a short hiatus while I was busy working on a million-and-one other things, the documentation for those probes is now available: Tracing mysqld with DTrace.
The documentation is comparatively light and deep all at the same time. It’s lightweight from the perspective that I’ve added very little detail on the mechanics of DTrace itself, since there is no need to replicate the excellent guides that Sun already provide on the topic. At the same time, I’ve tried to provide at least one (and sometimes two) D script examples for each of the groups of probes in the 6.0.8 release.
So what next for MySQL DTrace probes?
Well, the next version of the probes has already been worked on. I’ve been testing them for a month or so, and due to a problem with the probes on SPARC I couldn’t approve the patch, but I managed to resolve that last week. The new patch extends the probes to enable a more detailed look at certain operations, and it enables us to expand the probes to be placed anywhere within the server, including individual engine-level row operations.
If you want a demonstration of DTrace in MySQL, some of the things you can monitor without using the user probes we’ve added, and those new probes I just mentioned, then you will want to attend the MySQL University session this Thursday (12th Feb), at 14:00UTC where I’ll be doing all of the above.
As a side note, because I know there are people interested, last week I also finished the patch for these probes to go into the MySQL 5.1.30 version that we will be putting into OpenSolaris. Sunanda is working on getting that release out there as I type this.
I feel silly saying this but I haven’t had time to dig deep into DTrace yet. But I thought the benefit of it was that your code needed zero modification to benefit from it. Am I wrong? What do you need to modify the code for?
In theory you are right – you don’t need to modify you code to be able to get information out of it. But, tracing for function entry and exits, and trying to manipulate structures that you don’t know about can make it difficult to get what you want.
By adding static probes into the MySQL code we can provide DTrace users with very easy access into different parts of the MySQL code with relevant and easy to identify information, such as the user, client host and query data.
Also, by exposing these probes you can look for exactly the information you need based on some predefined tags, rather than expecting our users to understand the MySQL source code to get the information they want.
To make a comparison, imagine having a blog. Without DTrace probes, the only way to get the information out of the blog is by running raw queries and knowing the database structure so that you can write effective queries on the information. With the DTrace probes, get the nice interface and the ability to search and look for the information you need *without* having to write the complicated queries or know the underlying database structure.
Thanks!