Sunday, April 1, 2007

Me, Myself and MySQL Falcon

I have been getting more and more into MySQL for the last couple of months, and to tell you the truth i was really impressed, i can't say i am a database guru, but i had my experience with database engines before, and for my surprise.. MySQL does match up with the big players in this sector.

I can still remember MySQL from the old days when it was used to be thought of as a light, fast database storage engine, that can only use with personal websites, but now MySQL has everything an enterprise DB engine would need.... and more.

What got me really dazzled in this engine, is its layered architecture and pluggable storage engine architecture, this DB engine was designed in a way to separate the storage engine from the other system, so you can plug in any engine from a vast list, or even do your own !!

One of the new players in MySQL's storage is FALCON , this one is to be said the Oracle innodb killer, or isn't it?

Falcon (code name) is a transactional storage engine, based on Netfrastructure database engine, extended and integrated into MySQL.

The main goals of Falcon are to exploit large memory for more than just a bigger cache, to use threads and processors for data migration. Falcon has a larger row cache with age group scavenging. Falcon is multi-version in memory and single version on disk, True Multi Version Concurrency Control (MVCC) enables records and tables to be updated without the overhead associated with row-level locking mechanisms. The MVCC implementation virtually eliminates the need to lock tables or rows during the update process, also data and index caching provides quick access to data without the requirement to load index data from disk.

So it seems that it has everything to be the ace of all storages, no? think again, a benchmark was made to compare the performance of this new engine with old MyISAM and InnoDB here, but regretfully benchmarks are not in the favor of Falcon.. InnoDB and MyISAM scored over Falcon in different queries.

From what I read I can say that Multi version Concurrent Control system implemented in the new engine, made a draw back in some cases rather than boosting the performance, having multiple snapshots for every session, so achieving better locking, but at the same time the need to access the data beside the key is needed, and as we know keys are used a lot for optimizing, this is beside the overhead coming from such mechanism.

We can see also that it has a bug with queries that have LIMIT in it, the performance drops drastically when used.

The question here lies, will Jim Starkey bring his house into order and make Falcon the number one storage engine as promised? In all ways we are the winners, as we have other alternatives , and as they say.. competition is the consumer's number one friend.

1 comment:

hakan said...

The LIMIT optimization for Falcon is implemented now for quite a while.