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 …

The Open Season team got on the phone with Marten Mickos, CEO of MySQL, and Rich Green, SVP of Software at Sun Microsystems, shortly after the announcement of the acquisition was made. You can listen in here.

As Matt and Dave see it, Sun may well become the center of ...

Here’s something I wrote up a while back for basic MySQL knowledge. MySQL Competency

This is also a useful list of interview questions for potential hires. DBA Interview Questions

The above is a YouTube clip of Kaj Arnö leading MySQL and Sun executives in a rendition of  "Helan går" the official Swedish drinking song at MySQL.  This was followed by 400 shots of vodka being downed in unison.