Entered 50 minute late (thanks to a wrong timezone calculation) into the MySQL University Session: MySQL and OpenSolaris by Martin MC Brown.


Incase you have missed it altogether, go grab the slides from the Wiki page.

Future and past univesity sessions are listed here at http://forge.mysql.com/wiki/MySQL_University

Entered 50 minute late (thanks to a wrong timezone calculation) into the MySQL University Session: MySQL and OpenSolaris by Martin MC Brown.


Incase you have missed it altogether, go grab the slides from the Wiki page.

Future and past univesity sessions are listed here at http://forge.mysql.com/wiki/MySQL_University

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, copyinstr(arg2));
}

Getting the username is a bit harder, for the …

The Data Charmer Giuseppe Maxia did some quick tests on the DTrace script and was wondering if I could optimize it a little.

One issue with the old script is that it prints every SQL statement and this can be pretty expensive. This can be minimized by printing to a file. Here is a script that will do just that. The freopen() is not documented but it opens a file and sends all prints to the file. Giuseppe reports a 30% improvement in logging performance with this improved script.

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

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

One more optimization that you can use with DTrace is the use of aggregates. Aggregates provides summary information. So this script will not provide you with the running log but if performance of the logger is important …

To save Solaris from a certain death ?

Reading Planet MySQL the last couple of hours I'm trying really hard to convince myselve the Solaris offensive there is not orchestrated.. but I can't.
It might ofcourse be the fresh MySQL users that Sun brought in on their platform that started out blogging but hey .. I`m paranoia right :)

Are they really trying to get at least a fraction of the MySQL community on Solaris. Do they really think they can ? Yes they lost a zillion of Solaris customers that were running a proprietary database to MySQL on Linux users ,, but why would they want to move back to a semi proprietary setup ?

According to Linuxjournal Alan Cox seems to think that ZFS is the only thing that is keeping Solaris alive. I don't think DTrace was a bigg mass tool that would convince the crowds to suddenly move to an other operating system.

So is Sun trying to Lock In a community ? …

(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.

1. Grab the MySQL 6.0 alpha tarball from here
2. Extract it

3. Go to the source root, and do the following: (The text following a '#' should be treated as comments)

       $ ./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 the flag --enable-dtrace, 'cc' and 'CC' are Sun C …

(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.

1. Grab the MySQL 6.0 alpha tarball from here
2. Extract it

3. Go to the source root, and do the following: (The text following a '#' should be treated as comments)

       $ ./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 the flag --enable-dtrace, 'cc' and 'CC' are Sun C …

(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.

1. Grab the MySQL 6.0 alpha tarball from here
2. Extract it
3. Go to the source root, and
   1. ./configure --enable-dtrace --prefix=/export/home/amit/mysql60-install CC=cc CXX=CC --without-falcon
      
   2. make
   3. make install

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.

…

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 …

DTrace allows you to instrument any live running application in production without the need of extra coding, application recompile or even an application restart. All you need is that the application is running on an OS that supports DTrace. Today Solaris, OpenSolaris, OS X and FreeBSD are a few that have DTrace built in.

For example here is a D-script that instrument MySQL to observe the SQL statements that is being executed in production. Just a 3 line script like this can be extremely useful to observe a live MySQL database

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

You need to pass the pid of the mysql process as the first argument to this script.

Here is a sample output from the script. We are observing the SQL statements that are executed to bring up the SugarCRM login screen.

 …