Alright so I thought I might throw my $0.02 into the whole fanfare about Sun acquiring MySQL. I think it was a surprising but good move. Sun has a lot to provide, is likely looking to strengthen their platform, and appears to be committed to open source. While it remains to be seen whether their promise of keeping MySQL relatively unchanged still needs to be proven, I tend to believe that they understand the value of MySQL (they certainly should given the price tag) and would not do anything to jeopardize that. MySQL is, after all, the world's most popular open source database (according to MySQL, but I think that is still true).

This move I think is good for MySQL as well (maybe even better). For one, it seems like it would protect them for a buyout by a competitor. Now realistically, if Oracle bought out MySQL, the GPL'd parts of it would be forked off quite quick and the remaining alienated users would likely move to PostgreSQL or …

Read here how you can create them, how you can find them and what is the problem with them...

What is a sparse file?

"A sparse file is a file where space has been allocated but not actually filled with data. These space is not written to the file system. Instead, brief information about these empty regions is stored, which takes up much less disk space. These regions are only written to disk at their actual size when data is written to them. The file system transparently converts reads from empty sections into blocks filled with zero bytes at runtime." [1]

In other words: Files are not as big as expected.

With databases this can be seen often: For example the MySQL Cluster REDO log files are created as sparse files or some ORACLE tablespace files.

But first let us create such a sparse file:

 # dd if=/dev/zero of=sparsefile count=0 obs=1 seek=100G
 
 # ls -lah sparsefile
 -rw-r--r-- 1 oli users 100G 2007-10-24 11:18 sparsefile
 
 # df -h .
 Filesystem            Size  Used Avail Use% Mounted on …

Insertion and Queries

Databases are complicated beasts, but I’d like to focus on the storage engine, just the part that talks to the storage system, and doesn’t have to worry about SQL, etc.: just transactions, concurrency, compression, updates and queries.  In the next couple of blog entry, I’d like to just focus on updates (insertions and deletions) and queries (point and range).  (This delineation between the
front end and the storage engine is clearly architected in MySQL.) And in particular, I’d like to explore which features of a disk limit performance for which operations.

The question is how fast can these operations go?  Point queries are the slow ones, so let’s start with them first.  Suppose you have data on a disk—say a 1TB Hitachi Deskstar 7K1000.  It has a disk seek time 14ms and transfer rate of around 69MB/s [See tomshardware.com] Now imagine filling the disk with a …

A few days ago I wrote about O_DIRECT + EXT3 not working.

This is not a wide-spread problem, and may be isolated to 2.6.9. So, it makes sense that others who run O_DIRECT with EXT3 do not see the issue. I will use this post for future updates.

Ok here is the research that I did, and found the cause of my O_DIRECT problem

As Mark Hinkle has already noted, The 451 Group recently published a brief on open source mergers and acquisitions, written by open source research director, Raven Zachary, and financial analyst Brenon Daly. It is a happy coincidence that the report was published the day before Sun announced its acquisition of MySQL.

Clearly the report does not take the Sun/MySQL deal into account, but this is clearly something that we are tracking on a long term basis. Expect some further and deeper analysis in the future to take in the MySQL deal as well as any others that follow.

Additionally, it …

How can I have a ranking on a result with MySQL? The answer you can find here...

A friend of me asked me long time ago: "How can I have a ranking on a result with MySQL?". Now I found some time to write it down:

Lets do first some preparation for the example:

 CREATE TABLE sales (
     id     INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
   , fruit  VARCHAR(32)
   , amount DECIMAL
 );
 
 INSERT INTO sales
 VALUES (NULL, 'apple', 12.75), (NULL, 'orange', 1.89), (NULL, 'pear', 19.23)
      , (NULL, 'banana', 4.25), (NULL, 'cherry', 123.75), (NULL, 'plum', 23.15)
 ;

Now lets query:

 SELECT fruit, amount
   FROM sales
  ORDER BY amount DESC
 ;
 
 +--------+--------+
 | fruit  | amount |
 +--------+--------+
 | cherry |    124 |
 | plum   |     23 |
 | pear   |     19 |
 | apple  |     13 |
 | banana |      4 |
 | orange |      2 |
 +--------+--------+

Hmmmm...., this not yet what we want!

And now with ranking:

 SET @rank=0;
 
 SELECT @rank:=@rank+1 AS rank, fruit, …

Given the timing of my recent blog, Are Proprietary Databases Doomed?, I've been asked if I knew in advance about Sun's recent MySQL acquisition. Not at all! I was just as surprised and delighted as most others in the industry when I saw the news.

In the blog I outlined counter strategies that proprietary database companies might use to respond to the rise of Open Source Databases (OSDBs). One strategy was acqusition and I noted that MySQL, being privately held, was probably the most vulnerable.

The good news is that MySQL is no longer vulnerable. Sun has an unparalleled commitment to open source. No other organization has …