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
…

Last night and today, I ran a series of benchmarks against Drizzle. These benchmarks were designed to isolate the performance improvement or regression from one change: using Eric Day's new libdrizzle client library instead of the legacy libdrizzleclient library from MySQL. The results are in, and they are stunning.

Here is a graph showing the difference between Drizzle sysbench on a readonly workload with the only difference being sysbench using the libdrizzle driver versus using the libdrizzleclient (libmysql) driver for sysbench:

As you can see, with libdrizzle, the throughput is dramatically increased, with Drizzle scaling to 8x the number of cores on the benchmark machine before a …

The open source coderati announced a new database today called “Monsoon”.  Norm “Al” Modelle said much effort was spent by the team so it can handle internet sized floods of data.  He expected it to have the licensing of PostgreSQL, the multi-engine support of MySQL, the functionality of Oracle, the ease of use of MS SQL Server, and the massively parallel scalability of DB2.

Not to be outdone, Computer Dilettantes (CD) also announced a new database, the oddly named “Mud Puddle”. Eye Samme, the Executive VP in charge of databases we intend to milk for support revenue, said he knows the customer is being squeezed by licensing costs in these hard times.  So CD spent considerable effort designing a real time application that constantly analyzes the customer’s use of the database.  It then notifies the sales force how they can maximize revenue extraction from the customer. He said the database …

I am pleased to announce a cloud storage version of the PBMS engine.

What I have created is a version of PBMS that stores the BLOB data in a cloud and when a request for the data comes in the client is sent a redirect to get the BLOB directly from the cloud. The BLOB reference tracking and metadata is handled the same as before in that they are stored in the BLOB record in the repository but the actual BLOB is stored somewhere else.

This has several advantages over storing the BLOB in the repository record:

1. It reduces the disk storage requirement of the database server’s machine.
2. It reduces the bandwidth requirement of the database server’s machine.

The beauty of it is that the client application doesn’t need to know anything about the cloud …

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 …

There has been some great discussion lately about internal database schedulers and I/O handling on the Drizzle mailing list, and I thought I’d blog about it to summarize and see what others thought. These are ideas we’re toying around with for Drizzle, but they could be interesting for other databases (or other types of servers). The key things we are focusing on are modularity and concurrency. We also need well defined interfaces to allow anyone to come along and write/rewrite a component for their own needs.

History

First, a little background for those not familiar with Drizzle or MySQL internals. In MySQL, a client is accepted, assigned to a THD (thread) object, and is given an actual thread to run in. The client basically sits in a loop of reading a command, parsing it, executing it, and writing the result back. This works fine when …

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 …

I’ve spent the past couple weeks ripping out the old client library (based on libmysql) from the Drizzle client utilities (drizzle, drizzledump, drizzleadmin, …). I’ve replaced it with new code that uses the new libdrizzle. Brian merged this patch into the trunk yesterday, so we are one step closer to being rid of the old library! It is still used internally within the server, so my next task is ripping it out from there.

This means that the old library is no longer installed, so all new client and application development needs to use the new libdrizzle. It also means libdrizzle is now required to be able to build Drizzle. While I’ve done my best to convert the old utilities, I’m sure I’ve missed a few things. Please report any bugs you find with them. …

This week, I'm finishing up my book "Developing Web Applications using Perl, memcached, MySQL and Apache". I just finished up Chapter 1, which is the first chapter where I discuss several things such as how much things have changed in the last ten years with web development and Open Source in general. This lead me to write a small section about the future. I was pondering -- where are we going to be in ten years from now? What projects will be popular? This lead me even to a more specific question, and one that I would ask here: what is to become with the development of MySQL? What will be the official development branch of MySQL, and where is all the energy and excitement going to be around?

There is now one fork and one major branch of MySQL now that I will mention, both are exciting projects-- and neither of them is coming from official MySQL/Sun:

(The order of these is strictly alphabetic and not priority!)
…

We're making steady progress in removing bottlenecks in the Drizzle code base. So far, a number of mutexes have been removed and we've begin to replace a number of contention points with atomic instructions which remove the need for a lock structure on platforms which support atomic fetch and store instructions.

I'm pretty positive about the direction we are going so far. We're seeing the right trends in our scaling graphs, with very little performance drop off in read-only workloads up to 4X the number of cores on the machine, and little performance drop off on the read-write workloads up to 2X the number of cores, as you can see from the graphs below.

It's a little difficult to see, but we've made a small but steady improvement from r950 to r968, with numbers increasing around 1-2% across most concurrency levels. You can see the raw numbers here:

+--------------------------------+-------+-----+---------+----------+
| …