..bits & bytes teleported

The Bangalore MySQL User Group is having a meetup this Saturday- 21 March. Please RSVP if you are coming.

We have the following sessions planned:

1. MySQL Query Analyzer -- How to optimize queries using query analyzer-  by Thava Alagu, Sun Microsystems
   
2. Using MySQL + DTrace -- By Amit Saha, Sun Microsystems, this session will be derived mainly from my article.
   

See you there!

.. is up on MySQL Developer Zone at http://dev.mysql.com/tech-resources/articles/getting_started_dtrace_saha.html

Have a comment on the quality, or the clarity or the usefulness? Let me know!

Many thanks to Giuseppe, Lenz, Martin MC Brown and Aaron for helping in various ways.

As part of Sun's university relations, seven Sun engineers- Vadiraj, Varun, Blessen, Lawrence, Nirmal, Gautham and yours truly went to Vellore Institute of Technology, Vellore during the past weekend for a multi-track technology talks on Java ME, GlassFish, MySQL, JavaFX, Open Solaris Virtualization technologies.

There were three parallel tracks:

• J2ME, GlassFish, MySQL- which form a nice combination of Client, Middleware and Database technologies and our (Vadiraj, Varun and Me) idea was to give hints here and there to the students how to bring all the three technolgies to develop applications and how each fits into the holistic idea. My MySQL slides are available here.
• JavaFX
• Open Solaris Virtualization technologies

It was a hot Sunday, thought not enough keep the ever enhusiastic students in their dorm rooms, ofcourse. All the sessions were well attended and I am sure this Sun Tech Day at VIT will lead to more awareness and adoption of the technologies among Students

It was great to meet folks like Rohit (whom I had 'met a day earlier via Twitter), Aditya Kochar, Ram and few others. These students ensured everything was set up for the day.

It was a nic trip, especially amusing being the fact that there is a functional public railway track which runs through the campus of VIT, Vellore.

Static probes are being gradually integrated into MySQL. As of MySQL 6.0.9, there are around 55 static probes. To use the static probes, you will have to supply an extra option to the configure script, --enable-dtrace. After the build is over, start mysqld. Now open a terminal, and type $pfexec dtrace -l | grep mysql. You should see something like this:

135 mysql23509            mysqld __1cQdispatch_command6FnTenum_server_command_pnDTHD_pcI_b_ command-done
  136 mysql23509            mysqld __1cQdispatch_command6FnTenum_server_command_pnDTHD_pcI_b_ command-start
  137 mysql23509            mysqld __1cQclose_connection6FpnDTHD_Ib_v_ connection-done
  138 mysql23509            mysqld             handle_one_connection connection-start
  139 mysql23509            mysqld __1cMmysql_delete6FpnDTHD_pnKTABLE_LIST_pnEItem_pnLst_sql_list_LXb_b_ delete-done
  140 mysql23509            mysqld __1cHhandlerNha_delete_row6MpkC_i_ delete-row-done
  .
  .

  

As is expected, the function names are mangled. You can view the demangled names with 'dem':

$pfexec dtrace -l | grep mysql | awk '{print $4}' | xargs dem | awk '{print $4}'

which you will show the demangled functions:

handler::ha_external_lock(THD*,int)
handler::ha_external_lock(THD*,int)
handler::ha_external_lock(THD*,int)
handler::ha_external_lock(THD*,int)
handler::ha_external_lock(THD*,int)
handler::ha_external_lock(THD*,int)
mysql_insert(THD*,TABLE_LIST*,List<Item>&,List<List<Item>
handler::ha_write_row(unsigned
handler::ha_write_row(unsigned
select_insert::abort()
select_insert::send_eof()
mysql_execute_command(THD*)
mysql_execute_command(THD*)
multi_delete::send_data(List<Item>&)
mysql_execute_command(THD*)
multi_update::send_data(List<Item>&)
mysql_execute_command(THD*)
.
.
.
handle_select(THD*,LEX*,select_result*,unsigned
handle_select(THD*,LEX*,select_result*,unsigned
multi_update::send_data(List<Item>&)
mysql_update(THD*,TABLE_LIST*,List<Item>&,List<Item>&,Item*,unsigned,st_order*,unsigned
handler::ha_update_row(const
handler::ha_update_row(const
mysql_execute_command(THD*)

I learned and played around a bit with the character sets in MySQL. What you see below is a simple MySQL test script to test the behaviour of MySQL with a couple of indic languages, I know- Bengali and Hindi. The key here is to create a column which will be using the 'utf8' character set and 'utf8_general_ci' collation:

CREATE TABLE t8 (c TEXT character set utf8 collate utf8_general_ci);
--warning WARN_DATA_TRUNCATED 

# Bengali
insert into t8 values(repeat('রবীন্দ্রনাথ ঠাকুর', 10000));

# Hindi
insert into t8 values('शाहरुख़ खान ये कहें कि सुपरस्टार शाहरुख़ खान एक ऐसे अभिनेता हैं जिन्होंने दिल्ली के एक आम शख्स से हिंदी सिनेमा के बेहद ख़ास अभिनेता बनने तक का सफ़र तय किया है.');
insert into t8 values(LOAD_FILE('$MYSQLTEST_VARDIR/hindi.txt'));

SELECT c, length(c) FROM t8; 

drop table t8;

Copy the above script into a file, say ctype_utf8_indic.test and (after copying it to your t/ directory) execute it with the MySQL test runner- mysql-test-run.pl script:perl mysql-test-run.pl ctype_utf8_indic. You should see something like this:

রবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ রবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ রবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ রবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ রবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ রবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ রবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ রবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ রবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্র��াথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ রবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ রবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ রবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ রবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ রবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ রবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ রবীন্দ্রনাথ ঠাকুররবীন্দ্রনাথ ঠাকু  65534
शाहरुख़ खान ये कहें कि सुपरस्टार शाहरुख़ खान एक ऐसे अभिनेता हैं जिन्होंने दिल्ली के एक आम शख्स से हिंदी स बनने तक का सफ़र तय किया है.      831

Of course, the first type you run it, you will have to do a --record, or you may copy my .result file into your r/ directory and see if you get similar results. (My hindi.txt file)

One point to note here is that you won't need to confgiure your OS for either of the languages. However, you will need a editor which supports them. I found 'gedit' handy. I compiled 'vim' with multi-byte support, but haven't had any luck yet.

For some Bengali text, refer: http://www.bbc.co.uk/bengali/ and http://www.bbc.co.uk/hindi/ for Hindi.

Thank you Bar for helping me out!

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

Looks useful.

I have written about MySQL Query Browser and MySQL Administrator in my article: A graphical way to MySQL mastery on Linux.com

PS: All my writings are archived here at http://amitsaha.in.googlepages.com/writingsandtalks

MySQL Query Analyzer is Sun's latest addition to the MySQL Enterprise tools suite. Here is the press release.

A simple architectural overview of the product is at http://www.mysql.com/common/images/enterprise/queryanalyzeragent_350.jpg

Antonio Carlos González Cabrera, of the University of Spain has kindly sent me the Spanish translation of the slides I used for a session on MySQL couple of days back. Antonio is going to use the slides in his database class.



Thanks Antonio!

Here is the slide deck: http://amitsaha.in.googlepages.com/MySQL-Intro-features-benefits-SPANIS.ppt

Some additional points for OpenSolaris 2008.11 :

When you execute $ BUILD/autorun.sh, you might get errors complaining of 'aclocal' and 'automake' not found:

BUILD/autorun.sh[23]: aclocal: not found [No such file or directory]
Can't execute aclocal

Checking for the autotools, I found that 'aclocal' and 'automake' was installed in /usr/bin, as the following binaries:


/usr/bin/aclocal-1.10: executable /usr/perl5/bin/perl script
/usr/bin/aclocal-1.9: executable /usr/perl5/bin/perl script

and similarly for 'automake'.

To get around the errors, just create a symbolic link to any of the binaries with the name 'aclocal' in /usr/bin:

$ pfexec ln -s /usr/bin/aclocal-1.10 /usr/bin/aclocal

Now, it should work fine.

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

I took a 1-hour long session (was supposed to be 2 hours, but my planned agenda was exhausted) for students of engineering colleges across Karnataka studying in colleges under VTU. The session was delivered via EDUSAT under the Sun Microsystems- VTU EDUSAT e-learning programme.

It was fun. However, the fact that there was no interaction with the students felt a little dumb. (I was speaking on the camera which was beamed live)

These are my slides:

• http://blogs.sun.com/amitsaha/resource/MySQL/MySQL-Intro-features-benefits.pdf

I also put together a short Quiz for them:

• http://blogs.sun.com/amitsaha/resource/MySQL/MySQL-quiz.pdf

While preparing the slides, the basic fact that I kept in mind was market MySQL to the students and the teachers so that they start using it in their classes instead of expensive, proprietary databases. This perhaps is the biggest challenge for MySQL- to be adopted in educational institutions in India where the use of other databases is a norm for years.

(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 and C++ compilers respectively; --without-falcon disables the building of 'falcon' SE.
   
   $ make
   
   $ make install
   
   

Note:

Make sure that the your PATH variable has the locations for the non-GNU tools first. For eg.

$ echo $PATH
/opt/SUNWspro/bin:/usr/sfw/bin:/usr/bin:/usr/X11/bin:/usr/sbin:/sbin:/usr/gnu/bin

This is very important, since having GNU tools ahead in the PATH might result in using the GNU ld and standard C++ libraries which could create a failed build due to conflicts. Thanks to Martin MC Brown for helping me with this.

http://www.youtube.com/watch?v=IxdrCDRCKuM is a case study of MySQL made available by Indian Institute of Technology, Madras.

The comments that I posted there did not appear for some reason. So here are a couple of comments:

1. The one stop resource for MySQL would be http://www.mysql.com and *not* http://www.mysql.org
2. A useful reference for Storage Engines would be http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html

The video lecture is a somewhat 'okayish' type introduction to MySQL.

I am going to deliver a lecture on MySQL with a similar target audience- undergraduate students via EDUSAT in November

Event Schedulers are "temporal" triggers.

More at http://dev.mysql.com/doc/refman/5.1/en/events-overview.html