Is Drizzle good for MySQL?

Have you heard of Drizzle? It was announced at OSCON yesterday and is all over the blogosphere. From the Drizzle FAQ:

* So what are the differences between is and MySQL?

No modes, views, triggers, prepared statements, stored procedures, query cache, data conversion inserts, ACL. Fewer data types. Less engines, less code. Assume the primary engine is transactional.

Also from the FAQ is that, right now at least, there is no intention to make this run natively on windows and they make the point:

* "This is not a SQL compliant relational..."

Very true, and we do not aim to be that.

It is a fork of MySQL that takes it backward to pre-5.0 in features but hopefully greatly reduces the bugs and instabilities. I plan to look at it but I don't see much enterprise adoption. It was the enterprise users who wanted stored procedures, views and most of the other stuff that is being removed. I think it will be adopted mainly by read-only (or mostly) web sites that want to serve many pages. That's ok. There's a huge market for that. If I found a fit for a client, I would consider it.

They are very honest with the goals:

* What is the target?

Deliver a microkernel that we can use to build a database that meets the needs of a web/cloud infrastructure. To this end we are exploring http interfaces, sharding enhancements, etc... do not expect an Oracle, MySQL, Postgres, or DB2.

I was reading my usual stable of blogs this morning and ran across two blog entries that got me thinking. First is Ronald Bradford's blog, The new kid on the block - Drizzle. Ronald gives a great overview of what Drizzle is trying to achieve, the current state of MySQL and reasons why Drizzle is a good idea. This is well worth a read. I'm just this far from being convinced. ;-)

The second was On MySQL Forks and MySQL's non-open source documentation on Jeremy Cole's blog. To answer his question, I did not know that the MySQL documentation was not open source. That is a very interesting point and one that never even occurred to me. I wonder if Sun would consider opening it?

Anyway, both of those got me thinking about forking MySQL. If you look at the Postgres community (not comparing the databases, just the goals of the community) over the years, they have taken great pains to not fork the database. The contrib modules are there so that the database can add functionality without forking. The companies who have tried to make a go at monetizing Postgres have also taken great pains to not fork the database.

In fairness to Drizzle, it may have a sort of contrib module like functionality (bolding is mine):

* What is the goal?

A micro-kernel that we then extend to add what we need (all additions come through interfaces that can be compiled/loaded in as needed). The target for the project is web infrastructure backend and cloud components.

With Postgres, even companies like Yahoo and Skype who needed specific functionality have contributed that back to the community so that the code will eventually be reworked into the mainline (or as a contrib). There is a very anti-forking bias in the Postgres community and I think that has helped advance the database.

Ignoring any commercial interests, will the MySQL community become fragmented by forking? Because honestly, the community is the important part of any open source project. If Sun is putting its resources behind MySQL 5.1 and 6, and if they don't open the documentation, where will Drizzle go?

I'm just not convinced that Drizzle is a good thing or that it's needed. Then again, if they concentrate on making this a natively scalable database for the cloud, maybe it is time for a fork. Although, I'm not sure that goal is achievable starting with the MySQL codebase. What do you think?

LewisC

Technorati : competition, fork, fragmentation, mysql

Tablespaces in MySQL, Oracle and Postgres

If you are not familiar with tablespaces you may be wondering what the big deal about them is. Tablespaces are a logical addition to a database that helps maintenance, and potentially, can improve performance.

In Oracle and MySQL, a tablespace is a logical unit meant to store segments (i.e. tables and indexes). In Postgres, a tablespace is a physical unit. It is a symbolic link to a directory. Postgres does not allow tablespaces on operating systems that do not support symbolic links (such as windows).

The data file is the actual physical storage mechanism in Oracle and MySQL. Postgres stores tables in individual files. Postgres support of tablespaces is minimal. In MySQL and Oracle, performance can be improved by a more granular spread of data across disks. Ease of maintenance is maintained due to the logical grouping of tablespaces.

Oracle syntax for creating a tablespace is much the same as MySQL but with many more options. Oracle also allows a single tablespace to be made up of many data files.

Below is a very simple example of creating and using a tablespace in MySQL:

mysql> create tablespace testts

-> add datafile 'myfirstfile'

-> engine = falcon;

Query OK, 0 rows affected (0.48 sec)

mysql> create table testmyts (

-> abc integer )

-> tablespace testts;

Query OK, 0 rows affected (0.11 sec)

mysql>

The first command creates the tablespace (naming it testts) and assigning it a file name of myfirstfile. Of course, we are using the falcon engine. If you look in your MySQL data directory after running this command, you should a new file named myfirstfile.

The second command creates a table using our new tablespace. If you look at the tablespace now, it should be bigger. If not, insert a bunch of rows and watch it grow.

Oracle syntax, in its simplest form, is very close to MySQL syntax. Here is the same example in Oracle.

SQL> create tablespace testts

2 datafile 'myfirstfile'

3 size 10M;

Tablespace created.

SQL> create table testmyts (

2 abc integer )

3 tablespace testts;

Table created.

SQL> 

In Oracle, we leave off the engine keyword and we need to declare a size. We can create a small data file and use autoextend if we want, but we MUST declare an initial size.

In Oracle, it is possible to create each partition of a partitioned table in its own tablespace. I'm not sure if that's possible with MySQL, but I plan to try it out!

Falcon Test

I just downloaded Falcon and was running some tests. I was getting an error:

C:\MySQL\bin>mysqladmin version status proc 
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'ODBC'@'localhost' 
                             (using password: NO)'

To fix this error, just add a -u parameter, i.e.:

C:\MySQL\bin>mysqladmin version status proc -u root
mysqladmin  Ver 8.42 Distrib 6.0.2-alpha, for Win32 on ia32
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free 
software, and you are welcome to modify and redistribute it 
under the GPL license

Server version          6.0.2-alpha-community-nt-debug
Protocol version        10
Connection              localhost via TCP/IP
TCP port                3306
Uptime:                 27 min 43 sec

Threads: 1  Questions: 4  Slow queries: 0  Opens: 15  Flush tables: 1  
Open tables: 8  Queries per second avg: 0.2
Uptime: 1663  Threads: 1  Questions: 4  Slow queries: 0  Opens: 15  
Flush tables: 1  Open tables: 8 
 Queries per second avg: 0.2
+----+------+----------------+----+---------+------+-------+------------------+
| Id | User | Host           | db | Command | Time | State | Info             |
+----+------+----------------+----+---------+------+-------+------------------+
| 5  | root | localhost:4682 |    | Query   | 0    |       | show processlist |
+----+------+----------------+----+---------+------+-------+------------------+

I'm going to be blogging about my attempts to use some of the newer enterprise features of Falcon, as well as general MySQL programming info. LewisC

MySQL 6.0 in Alpha Test

I just found out that MySQL 6.0 with the Falcon Storage Engine is available as Alpha. Falcon adds ACID compliance, tablespaces and performance improvements. It's supposed to also simplify administration but I'm not sure how MySQL can be simplified. It's pretty simple now. I've always thought that the ability to swap out the storage engine is the best feature of MySQL. It reminds me of the replaceable database drivers from my clipper days in the late 80s and early 90s. From my reading about Falcon, it looks like one advantage will be the fact that Falcon has been engineered to take advantage of 64 bit architectures and large memory caches. User data files in Falcon can be up to 100 terabytes. That should pretty much cover most uses. It looks like you only get a single data file per Falcon database. Falcon has what it calles the Falcon serial Log. It's the equivalent of the Redo Log in Oracle or the Write Ahead Log (WAL) in Postgres/EnterpriseDB. This log file is also used for crash recovery (which I believe is completely automated in MySQL 6.0). Falcon uses 4 different memory caches: log, system and index, page and record. It uses a sophisticated algorithm to determine what data remains when the max is hit. Supposedly this algorithm is faster and more complex that the LRU.