Response: DTrace MySQL & SQL statement
A lot of comments on my last blog. Here is my response
How can I get more details for each SQL statement?
The second argument to dispatch_command is a pointer to the THD structure. It contains all sorts of information. You can dig for all the info you may ever need. Only issue is that we are now dealing with the internal structures and these may change for every release. So the script will not be portable. When I get a few minutes I'd blog a sample but your millage may vary.
A better way to get these structures is using the embedded MySQL DTrace probes. These will expose the info you need. For details on the embedded DTrace probes and example scripts on how to get these details see the discussion thread on OpenSolaris DTrace Discuss
Finally the tid was printed to give you some amount of isolation based on connections. Not a great solution but good enough in most cases.
Why not just use MySQLProxy?
Because you need to turn it on and restart your mysql instance. With DTrace this process is truly dynamic. But as Kay Röpke explains, in MySQL 5.1 you can turn on query logging on the fly. DTrace of course can be used for many other things, not just query logging. Stay tuned for more to come in my blog
Can I do this for Oracle?
Of course you can. Its just another process as far as Solaris and DTrace are concerned. Here is a simple script that will print the SQL statements for Oracle. The function name is sqlcxt() the third argument is a pointer to the sqlexd structure. Search the web for the definition of the structure for your version of Oracle. Put it in a include file Oracle.h Then copy and run this script
#!/usr/sbin/dtrace -Cs
#include "oracle.h"
pid$1::sqlcxt:entry {
this->s = (struct sqlexd *) copyin(arg2,sizeof(struct sqlexd));
self->query = copyinstr((uintptr_t)this->s->stmt);
printf("%s: %s\n",execname, self->query);
}
Why can't I do this in Linux? Is Sun making Linux the second choice
Kay already provided a very good response. I just want to point one more things. You can use Solaris Branded Zones to run DTrace on a Linux Application. See
for details.
Not only are we not treating Linux as a second class citizen we have gone out of the way to help out our friends in the Linux community even though they have been reluctant in getting DTrace ported.
Posted at
09:52AM Oct 17, 2008
by angelo in DTrace |
Angelo,
Great post!
Just to set the record straight, you don't need to restart the MySQL server to use MySQL Proxy.
Giuseppe
Posted by Giuseppe Maxia on October 17, 2008 at 10:30 AM EST #
And to nit-pick, it was Stephane who pointed out that in 5.1 you can turn on the general query log at runtime :)
Posted by Kay Röpke on October 17, 2008 at 11:08 AM EST #
I may be mistaken, but I think the reason there isn't dtrace on linux is not due to "reluctance", it's due to incompatible licenses (dtrace -> cddl, Linux -> gpl).
Posted by Mark Matthews on October 17, 2008 at 01:00 PM EST #
Thanks for the clarifications and correction. I'm just a Solaris guy trying to learn MySQL so pardon my mistakes.
As far as the licensing issues, I'm not a lawyer and do not have any legal background to even talk about it. But see http://blogs.sun.com/ahl/entry/what_if_machine_dtrace_port for a good discussion on this topic.
Posted by Angelo Rajadurai on October 17, 2008 at 01:27 PM EST #
Greatl article, yet another gem out of you Dtrace collection to fix an issue on the fly
- Stefan
Posted by Stefan Schneider on October 17, 2008 at 02:23 PM EST #
Hi,
Where do I track down oracle.h - it doesn't seem to be in the ORACLE_HOME?
Gawie
Posted by Gawie on October 20, 2008 at 05:48 PM EST #