I have always been a strong advocate of good testing of any system. I started on a project last year with Drizzle to produce coverage tests to facilitate verifying syntax and helping in comparison with MySQL.

From my extensive experience in code generation from the past 20 years, I produced about 3 years ago when at MySQL Professional Services a Java based solution with a small meta language to automated the creation of a large number of tests. At the time is was some 475 tests and 200k lines of mysql-test syntax when I first looked at validating the Nitro Storage Engine.

A number of issues with mysql-tests including the support of multiple storage engines had me last year write a Proposed Testing Protocol for Drizzle for further discussion. …

A new feature to MySQL Version 5 was the introduction of SQL_MODE to support STRICT… or TRADITIONAL values.

This feature enabled a closer compatibility to other RDBMS products. MySQL by default performs a number of silent data changes which do not help in providing a level of data integrity if you come from a more traditional background. MySQL by default represents these as warnings, while with an appropriate SQL_MODE, these are in turn treated as errors.

How does Drizzle handle this? Very simple. There is no SQL_MODE. By default Drizzle handling a strict mode of producing errors for any invalid data. The following are some test case examples showing the varying conditions.

Test Case

select version();
create database if not …

Continuing on from CPU, we turn our attention to Memory. One of the first steps when addressing a MySQL performance tuning problem is to perform a system audit of the physical hardware resources, then identify any obvious bottlenecks in these resources.

In auditing, I start with the ‘free’ command, the already used ‘vmstat’ command, the /proc/meminfo and /proc/sys/vm/swappiness files to get an indication of memory and swap resources. While we are looking at the Memory, the configuration of Swap is also very important. I will discuss this in more detail later.

$ free -m
             total       used       free     shared    buffers     cached
Mem:          3955       3838        117          0        402       2366
-/+ buffers/cache:       1069       2886
Swap:         1027          0 …

The Drizzle database, while similar to MySQL includes a number of significant differences. In this post we will look at data types and table syntax that is valid in Drizzle. For more background information you can also review A beginners look at Drizzle - Getting around with SHOW.

Data Types

This comparison is with Drizzle 2009.03.970 and MySQL 5.1.32 GA. More information at MySQL 5.1 Data Types.

The following data types are not valid in Drizzle.

• TINYINT
• SMALLINT
• MEDIUMINT
• BIT
• TIME
• YEAR
• BINARY
• SET
…

It is all about one bug fix, one little feature, one step at a time.

Armin Schöffmann fixed a bug (Deadlock in mysql_real_query with shared memory connections, is what Armin calls it), Armin signed the Sun Contributor Agreement (24 March 2009), Vladislav Vaintroub and Davi Arnaut reviewed, committed and queued the patch to MySQL 5.0 bug team (26 and 27th of March, 2009).

That's a great example of open, contribution-based MySQL development with real results.

Thank you Armin!

Thank you Vlad!

Thank you Chad!

Thank you Davi!

Thank you ... the one whose name I've left out! (Feel free to leave a comment below.)

Assuming you have successfully compiled Drizzle, and you are ready to start for the first time, here are some beginner differences with those familiar with the current MySQL 5.1 GA version. Note: drizzle is actually an original fork of the 6.0.x codebase however the code looks very little like mysql anymore.

Getting started differences

The first default MySQL installation step scripts/install_mysql_db is gone, and there is no replacement. The ‘mysql’ schema no longer exists. Talking about schemas, ‘test’ is also gone by default.

While a bin/drizzled_safe exists to start drizzle, this presently doesn’t operate as expected. You should start drizzle with sbin/drizzled &

They still have an empty …

I’ve not looked at compiling and running Drizzle on my server for the past four weeks. Well overdue time for a check and see how it’s going. I saw in today’s planet.mysql.com by Eric Day a new dependency is needed. libdrizzle 0.2.0 now in Drizzle is now required, so I started there.

cd ~/bzr
bzr branch lp:libdrizzle
cd libdrizzle
./config/autorun.sh
./configure
make
sudo make install

No problems there, also documented at the Drizzle Wiki. Great to see the docs up to date. I see my old work on starting the compiling page still relevant. Tested on CentOS 5 and Mac OS/X 10.5

Compiling drizzle was not much more difficult.

cd ~/bzr/drizzle
bzr update
make distclean
./config/autorun.sh
./configure --prefix=/home/drizzle/deploy …

It is all about one bug fix, one little feature, one step at a time.

Armin Schöffmann fixed a bug (Deadlock in mysql_real_query with shared memory connections, is what Armin calls it), Armin signed the Sun Contributor Agreement (24 March 2009), Vladislav Vaintroub and Davi Arnaut reviewed, committed and queued the patch to MySQL 5.0 bug team (26 and 27th of March, 2009).

That's a great example of open, contribution-based MySQL development with real results.

Thank you Armin!

Thank you Vlad!

Thank you Chad!

Thank you Davi!

Thank you ... the one whose name I've left out! (Feel free to leave a comment below.)

It is all about one bug fix, one little feature, one step at a time.

Armin Schöffmann fixed a bug (Deadlock in mysql_real_query with shared memory connections, is what Armin calls it), Armin signed the Sun Contributor Agreement (24 March 2009), Vladislav Vaintroub and Davi Arnaut reviewed, committed and queued the patch to MySQL 5.0 bug team (26 and 27th of March, 2009).

That's a great example of open, contribution-based MySQL development with real results.

Thank you Armin!

Thank you Vlad!

Thank you Chad!

Thank you Davi!

Thank you ... the one whose name I've left out! (Feel free to leave a comment below.)

Taking the work already done with vmplot.sh, a useful tool for MySQL performance tuning by Yves and Matt at BigDBAHead, and in true Open Source fashion I’ve enhanced and modified for my own purposes.

These changes include:

• Error checking for ‘gnuplot’ command on the system
• Eliminate the first row of sample data, as this is often not a complete sample for the vmstat duration.
• Created a HTML output file for easy browser viewing
• Changed Memory scale values from Kilobytes to Megabytes
• Resizing png’s for optimal 1024×768 display output (2 per row)

Download vmplot

And I get:

So more specifics of what I did.

Install gnuplot.

$ yum install gnuplot

Create vmstat sample …