Just came across this paper:  Optimizing MySQL Database Application Performance with Solaris Dynamic Tracing

Looks useful.

Just came across this paper:  Optimizing MySQL Database Application Performance with Solaris Dynamic Tracing

Looks useful.

This Thursday (February 12th, 14:00 UTC), MC Brown will give a MySQL University session on Using DTrace with MySQL. MC has been involved not just with documenting DTrace but also with DTrace development; see his recent blog post for details.

For MySQL University sessions, point your browser to this page. You need a browser with a working Flash plugin. You may register for a Dimdim account, but you don't have to. (Dimdim is the conferencing system we're using for MySQL

This Thursday (December 18th), Martin "MC" Brown will talk about using DTrace with MySQL. MC is the Solaris (and, naturally, DTrace and ZFS) expert on the Sun Database Group documentation team. He's helped the openSolaris team port MySQL to openSolaris.

Note that we'll be using a new session address / Dimdim URL:

http://webmeeting.dimdim.com/portal/JoinForm.action?confKey=mysqluniversity

You can bookmark this address, since it will remain valid for all future MySQL University sessions. Remember, though, that the meeting room will

There have been plenty of blog entries and writings about the MySQL Query Analyzer, for what I think are good reasons. Labeling it a potential Killer App, causing many MySQL users to become paying Sun customers, may be a daring thing. However, the Query Analyzer might very well have what it takes. The key benefit of it is that it identifies the source of performance bottlenecks. In that sense, one could perhaps instead call it a

So it’s been a while since I’ve hit my blog, but I feel compelled to respond to Baron’s post, and many of the other (perhaps short sighted) criticisms out there against this new functionality that we’ve been working on for so long.

Everybody seems to be saying that this functionality should be implemented in the server, or that the better way to do this is to use these patches which add functionality to the logging that MySQL already provides. Well guess what people - what does that give you, other than some more details on you queries?

More I/O.

What’s bad on a database server?

More I/O.

Query Analyzer, whilst it does currently use a proxy to collect the statistics, doesn’t hit your disk at all. Everything is collected

Angelo recently showed an easy way to dump SQL queries using DTrace, while reading the articles I felt that some important information is missing: The name of the user executing the query and the selected database. So I sat down a few minutes and tried to collect that data.

For the database name I found a quite simple solution: It is passed as parameter to the check_user() function to MySQL so we can easily add a thread-local variable to keep that name. Simple script for that:

#!/usr/sbin/dtrace -s

#pragma D option quiet

pid$1::*check_user*:entry
{
    self->db = arg4 ? copyinstr(arg4) : "(no schema)";
}

pid$1::*dispatch_command*:entry
{
    printf("%s: %s\n", self->db,

#!/usr/sbin/dtrace -qws
BEGIN
{
	freopen("/tmp/sqls");
}

pid$1::*dispatch_command*:entry
{
	printf("%d::%s\n",tid,copyinstr(arg2));
}

(Note: This post is relevant only if you want to have 'DTr ace' support in the MySQL server. Packages for Solaris are available at http://dev.mysql.com/downloads/.  You can install the MySQL packages for Solaris 10 on Open Solaris)

We shall use the Sun Studio C/C++ compilers and linkers to build MySQL 6.0 alpha on Open Solaris b98 on a x86 system.

$ ./configure --enable-dtrace CC=cc CXX= CC --without-falcon --prefix=/export/home/amit/mysql60-install # MySQL 6.0 alpha contains DTrace probes. To enable it use

(Note: This post is relevant only if you want to have 'DTr ace' support in the MySQL server. Packages for Solaris are available at http://dev.mysql.com/downloads/.  You can install the MySQL packages for Solaris 10 on Open Solaris)

We shall use the Sun Studio C/C++ compilers and linkers to build MySQL 6.0 alpha on Open Solaris b98 on a x86 system.

$ ./configure --enable-dtrace CC=cc CXX= CC --without-falcon --prefix=/export/home/amit/mysql60-install # MySQL 6.0 alpha contains DTrace probes. To enable it

(Please note that you will have to build MySQL from sources only if you want to try out the DTrace facilities. Otherwise, binary packages are available for installation) 

We shall use the Sun Studio C/C++ compilers and linkers to build MySQL 6.0 alpha on Open Solaris b98 on a x86 system.

Note:

Please make sure that the location of the Sun tools are ahead in your PATH. GNU tools should be located later in the PATH. For eg.

#!/usr/sbin/dtrace -qs
pid$1::*dispatch_command*:entry
{
    printf("%d::%s\n",tid,copyinstr(arg2));
}

I decided to take the new probes for a spin. Oh, and rather than do it on a Solaris system, I figured I'd give it a shot on my Intel Core 2 Duo MacBook Pro, since MacOS X 10.5 (Leopard) supports Dtrace.

To begin with I pulled down and built MySQL 6.0.5 from Bit Keeper, thanks to some help from Brian Aker.

I decided to take the new probes for a spin. Oh, and rather than do it on a Solaris system, I figured I'd give it a shot on my Intel Core 2 Duo MacBook Pro, since MacOS X 10.5 (Leopard) supports Dtrace.

To begin with I pulled down and built MySQL 6.0.5 from Bit Keeper, thanks to some help from Brian Aker.

I decided to take the new probes for a spin. Oh, and rather than do it on a Solaris system, I figured I'd give it a shot on my Intel Core 2 Duo MacBook Pro, since MacOS X 10.5 (Leopard) supports Dtrace.

To begin with I pulled down and built MySQL 6.0.5 from Bit Keeper, thanks to some help from Brian Aker.

We have fixed the issue with the PHP dtrace extension not working in Cool Stack 1.2. As I mentioned in my announcement post, we had already identified the issue but didn't have time to fix it before the release. The issue was that /usr/ccs/bin/ld was being used to do the linking but this doesn't work for dtrace as some initialization code needs to be called from the .init section and this is not setup correctly if we don't use 'cc' to do the linking (rather than 'ld'). 

So a simple addition to the configure line:
LD="cc"

did the trick.

We now have two files : dtrace_1.2_sparc.so and dtrace_1.2_x86.so posted.

We have fixed the issue with the PHP dtrace extension not working in Cool Stack 1.2. As I mentioned in my announcement post, we had already identified the issue but didn't have time to fix it before the release. The issue was that /usr/ccs/bin/ld was being used to do the linking but this doesn't work for dtrace as some initialization code needs to be called from the .init section and this is not setup correctly if we don't use 'cc' to do the linking (rather than 'ld'). 

So a simple addition to the configure line:
LD="cc"

did the trick.

We now have two files : dtrace_1.2_sparc.so and dtrace_1.2_x86.so posted.

We have fixed the issue with the PHP dtrace extension not working in Cool Stack 1.2. As I mentioned in my announcement post, we had already identified the issue but didn't have time to fix it before the release. The issue was that /usr/ccs/bin/ld was being used to do the linking but this doesn't work for dtrace as some initialization code needs to be called from the .init section and this is not setup correctly if we don't use 'cc' to do the linking (rather than 'ld'). 

So a simple addition to the configure line:
LD="cc"

did the trick.

We now have two files : dtrace_1.2_sparc.so and dtrace_1.2_x86.so posted.

In a previous post, I mentioned the availability of the dtrace extension for Cool Stack's PHP.  Using this extension and the Cool Stack MySQL, it is possible to analyze the performance of your application running on this stack. At JavaOne, we demoed this using the open source MediaWiki and SugarCRM applications.  dtrace is especially useful in analyzing complex multi-tier applications like AMP. Thanks to Angelo Rajadurai for the creation of the scripts that I describe below.

Analyzing PHP calls

So, let's look at a simple dtrace script that counts how many times a particular PHP function is called :

#!/usr/sbin/dtrace -Zqs

php*:::function-entry
/arg0/
{
        @[copyinstr(arg0)]=count();
}

You can copy

During JavaOne this year Rags and I did a hands on lab on DTrace. The topic was "Using DTrace on Java and other Web 2.0 Languages in Solaris". Due to some unforeseen reason the lab was scheduled from 8:45 PM to 10:45 PM (Yes PM) and that to on the day of the big party at JavaOne. So I was expecting to be speaking to an empty room but lo and behold we had close to 100 engineers ( a few pretty drunk) and most stayed until the finished the

Dtrace is one of the coolest things in Solaris 10, adding great observability to applications. A Dtrace provider for PHP that adds probes to function entry and exit points has long been available. I finally got around to integrating this with the php in Cool Stack. 

Bryan Cantrill has some excellent examples of how to use dtrace to trace through your php code, down to the system libraries and kernel (if you want to go that far !) .

Instructions for installing the php dtrace extension

1. Download the shared library for the extension :

    dtrace.so.x86.bz2