Last time I talked about point queries.  The conclusion was that big databases and point queries don’t mix.  It’s ok to do them from time to time, but it’s not how you’re going to use your database, unless you have a lot of time.  Today, I’d like to talk about range queries, which seem much more useful for the analysis of big databases, say in a business intelligence setting.

Recall that the focus is on the storage engine (a la MySQL) level, and a database on a single disk—the one we are using for illustration is the 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 random pairs, each 8 bytes.  So that’s 62.5 billion pairs.

Range Queries

Suppose the above data is stored in a B-tree, and that you’d like to iterate over all the data in order by key.  Further suppose that the …

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 …

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 …

I haven't been blogging about the things I used to -- how-tos and technical hacks -- because I've been working quite hard on MySQL Toolkit and, believe it or not, innotop. I've made it possible to write innotop plugins, which have been very useful to our team at work, and I'm working on documentation. Plugins won't make it into the upcoming release; it'll just be bug fixes and documentation. These projects have taken up most of my free time.

I've also tentatively joined a group of people working on a very large, exciting, secret project which may consume the rest of my summer and/or much of the rest of the year. (I've been turning down consulting gigs and other projects because of this). If this secret project works out, I'll be writing more about it here, you can count on that.

In the …

Thanks to Vanessa Fox (nude!) [1] for tagging me in the latest Friday blogging meme.

Here are my five reasons for blogging, with very little deep thought:

1. I like to write. Hell, I even wrote a book once and you can read all about it on that outdated website.
2. People actually read this stuff. Seriously. I don't know who most of you are (just some of you), but I appreciate the fact that I'm able to entertain, annoy, or otherwise occupy bits of your free time. It makes me feel loved (and hated).
3. It's a very efficient way to cause trouble, …

I was getting bombarded with fake porno trackbacks, and I couldn’t find a way to get WordPress to shove them in my moderation queue, so I turned them off.

Let me know if you have any problems. I don’t know how to bulk edit existing posts to do this inside WordPress, so I fired up MySQL and manually disabled them via a SQL query. Hopefully, I didn’t break anything.

Of all the features that I've added to my homebrew blogging software tagging (or folksonomys) has been my favorite. Here's why:

Tag Clouds

I've designed the home page of my blog to make it easy for a first time visitor to know what my blog is about. Nothing does this better than the tag cloud of popular tags they are presented with:

With a quick glance you can see that this blog is mostly about ColdFusion/CFML, but I also talk about MySQL, Java, RSS, …