MySQL Falcon Storage Engine Open Sourced

An anonymous reader writes "The code for the Falcon Storage Engine for MySQL has been released as open source. Jim Starkey, known as the father of Interbase, is behind its creation; previously he was involved with the Firebird SQL database project. Falcon looks to be the long-awaited open source storage engine that may become the primary choice for MySQL, and along the way offer some innovation and performance improvements over current alternatives." This is an alpha release for Windows (32-bit) and Linux (32- and 64-bit) only, and is available only in a specially forked release of MySQL 5.1.

Overheard a few days earlier

"This Falcon Storage Engine better be released as Falcon open source, or I'm gonna be Falcon pissed."

Please explain

I'm a developer, but I've never used MySQL.

Isn't MySQL already open source? If so, how does the Falcon storage engine differ from the "regular" storage engine that comes with MySQL?

Re:Please explain

MySQL itself is Open Source. But that only gives you a few storage Engines. The specific storage engines have different licenses. It is perfectly possible to have commercial storage engine for MySQL.

MySQL has no "native" way to store or obtain data - everything goes through plugins, some of which ship with MySQL some don't.

MyISAM - the most common and fastest. But no transactions, no ACID, etc. Good for many read-only or non critical tables.
InnoDB - licensed from InnoSoft (now oracle). GPL for non commercial, extra dollars for commercial. Transactions, ACID, but a bit slow. .... other storage engines also exist

Re:Please explain

This is easy and one of the tenants of so-called dual licensing setups...

Basically, if you don't want to pay to use the software, you are bound to the terms of the GPL. If you don't want to be bound to the terms of the GPL, you gotta pay.

Re:Please explain

Stolen directly from the mysql website:

Falcon has been specially developed for systems that are able to support larger memory architectures and multi-threaded or multi-core CPU environments. Most 64-bit architectures are ideal platforms for the Falcon engine, where there is a larger available memory space and 2-, 4- or 8-core CPUs available. It can also be deployed within a standard 32-bit environment.

The Falcon storage engine is designed to work within high-traffic transactional applications. It supports a number of key features that make this possible:

        * 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.
        * Flexible locking, including flexible locking levels and smart deadlock detection keep data protected and transactions and operations flowing at full speed.
        * Optimized for modern CPUs and environments to support multiple threads allowing multiple transactions and fast transaction handling.
        * Transaction-safe (fully ACID-compliant) and able to handle multiple concurrent transactions.
        * Serial Log provides high performance and recovery capabilities without sacrificing performance.
        * Advanced B-Tree indexes.
        * Data compression stores the information on disk in a compressed format, compressing and decompressing data on the fly. The result is in smaller and more efficient physical data sizes.
        * Intelligent disk management automatically manages disk file size, extensions and space reclamation.
        * Data and index caching provides quick access to data without the requirement to load index data from disk.
        * Implicit savepoints ensure data integrity during transactions.

Re:VACUUM?

That's like putting a Volkswagen Bug body on top of a tank ;-)

Re:Please explain

adwords [blogspot.com]

MySQL's counter-attack to Oracle's advances

I've been very excited since I first heard about this new storage engine adapted from Netfrastructure. Not only does it give MySQL a transactional storage engine that is not controlled by a hostile company, but the engine appears to be designed from the bottom up to support web traffic. Jim gave a great talk at the Boston MySQL meetup that you can watch here http://video.google.com/videoplay?docid=1929002440 950908895 [google.com]

InnoDB

What does Falcon do that the InnoDB engine doesn't currently do? The description seems rather vague.

MVCC: Multi-Version Concurrency Control

The big thing Falcon brings is MVCC which allows safe simultaneous reading and and writing without locks.

Here is a good explanation of PostgreSQL's MVCC [onlamp.com].

Okay, I'm stupid. What is a storage engine?

I mean, I've been using MySQL for years without a "storage engine" so what does this offer me that I don't have now?

Re:Eh. MySQL user, actually.

Nah, I'm just giving you a hard time. I've worked with a couple of people who have only ever worked on MySQL, and they tend to not know some pretty essential things for a DBA like ACID compliance and such.

It's just a function of how easy MySQL is to set up. It's trivial to set up, but a lot of the default decisions are generally bad for an SQL database, and the documentation -- while good -- never encourages you to go beyond the defaults.

It's like hearing someone say they can design websites, and then finding out they mean with FrontPage.

Drupal is one example of something that works great with MySQL. I can argue about MySQL's faults over and over, but at the end of the day it's easy to use and it's good enough for most people. CMS systems and forums are where MySQL really shines.

Sweet... but one of those long-term things :-(

I just setup a phpBB site that uses MySQL and I'm always keeping an eye on storage space and performance now. I don't suppose my host will upgrade MySQL any time soon, but this is still cool news...

Why not PostgreSQL?

OK, although I don't agree with it, I know that a lot of people use MySQL because it's the most common database supported by web hosts. Isn't it almost as likely for a hosting company to have PostgreSQL, though, as to upgrade to a bleeding-edge version of MySQL when this is finally readied for public consumption? Will this new backend give any extra functionality that PostgreSQL doesn't offer?

Re:Why not PostgreSQL?

As a I see Falcon is mainly an attempt to:

1) Get better performance on multiprocessor systems

2) Get a decent storage engine that is not controlled by MySQL competitors

As far as I can tell there is nothing in it that you can't get in Postgresql.
Postgresql already performs better than the standard MySQL on multiprocessor systems.
It remains to be seen if Falcon will be better than Postgresql once its production
ready. Well, there is one thing, Falcon compresses data while Postgresql doesn't. can't help wondering what this will do to performance.

On the other hand there seam to be a lot missing from Falcon that you find in Postgresql.
If you read the Falcon limits page on the mysql site you find that it lacks e.g:

- SELECT FOR UPDATE

- No online backup

- No foreign keys

All in all, I would say Postgresql would be a better choice, if your web hosting company allows you to use it.

MySQL have a tendency to slow down on many concurrent or complex queries. Postgresql is far better at handling triggers and can be programmed in many different programming languages. Support for domains and much more. MySQL also lacks EXCEPT, this makes some types of queries (relational division) much more complex than they have to be.

Still for people that aren't free to choose their database, it is nice to see that MySQL
makes some progress. Besides a little competition never hurts.

It might have potential...

for smaller databases, but limiting the tablespace to a single file per database/schema doesn't sound very flexible, and won't allow DBAs to maximize their disk throughput.

I am guessing that this is more of a MyISAM replacement than an InnoDB replacement, so it's not really a shot across Oracle's bow (as some comments make it sound like).

I had to drop MySQL

I was forced to learn Postgres because I wanted to do a SELECT * FROM foo WHERE bar NOT IN ( SELECT x FROM y WHERE z > 4 ) type query - basically a negative subselect. I couldn't do (or couldn't work out how to) use table JOINs to perform the same thing, and at the time, MySQL didn't do it.

I'm so thankful. Postgres is so much better. And the table JOIns I did have ran so much faster in PG. s/mysql_/pg_/g *.php

Oh, please

Falcon looks to be the long-awaited open source storage engine that may become the primary choice for MySQL, and along the way offer some innovation and performance improvements over current alternatives.

Come on. Give me PostgreSQL [postgresql.org] any day. After fiddling with MySQL at work for a few custom developments (both versions 4 and 5.0.2) i'm ready for anything else. Gave PSGSQL a shot at home and it runs very nice, with lots of advanced features. Anyone with real-world deploying feedback to share?

i see a naming conflict on the horizon...

when i first read falcon storage engine, i initially thought of a storage software called falconstor [falconstor.com]. falconstor is more in the data backup/archive/recovery market, often used as a disk-to-disk-to-tape solution, or sometimes as a virtual tape device, but i imagine that often times a name that's close but different is almost always considered "too close" and forces a name change by somebody... let's see how this gets resolved. who knows, maybe the other falcon store [falconstudios.com](NSFW) might be the winner...

Platforms for Falcon

The summary does not mention that the alpha only supports x86 platforms... ie, on Linux, it may be compiled for 80486 and better 32 bit IA32 compatible microprocessors, and Intel EM32T compatible 64bit microprocessors such as the AMD's Athlon 64 family and of course the newer non-Itaniam 64bit Intel microprocessors.
Support for other families of microprocessors, such as PowerPC and Sparc will come later.

Don't underestimate this one

I've read through all comments with 2 or more in rating, and it seems that people really underestimate what Jim is doing here.

We're talking in-memory MVCC here. This means you can add 1000 records, do a rollback, and the harddisk hasn't been accessed. Even if you commit, performance will eventually be magnificent compared with on-disk MVCC systems. You can run larger systems on one server with this, than you would be able to run on a cluster with other database systems.

This system has been designed to provide very good performance improvements for those who do know how to create SQL statements, but probably even better performance improvements for those who don't. And we don't have a tradeoff between performance and transactions any more - transactions and better performance are both included.

Also, please note that this technology will make MySQL a trustworthy data storage for many commercial applications out there, giving added value to their apps and their businesses. It will also enable small but very skilled development teams able to use MySQL as a trustworthy database for specialized applications - previously only Firebird and Postgresql were able to provide this for free, and even though Firebird has a very high deployment in USA's top 500 companies, postgresql seems to be very much *nix only in deployment statistics.

I have been programming database applications for more than 20 years, and have been programming Oracle, MSSQL, MySQL, postgresql, Firebird, dBase, Paradox, Access and other databases. I see Jim's contributions to MySQL as extremely important for the database market. Instead of having "just" a transaction layer on top of a storage layer, MySQL now provides mechanisms that give this design an advantage over those database systems where the transactions are stored on disk (like Firebird, Postgresql).

And - by the way - this has NOTHING to do with "optimizing for web applications". Web applications are just as diverse as GUI applications and other systems, and GUI applications will benefit from this as much as web applications.

Re:New Microsoft Sql Server

idiot.

Re:Please explain part 2

Actually, a good question would be : What is the difference between Mysql and Mysql falcon?

Mysql maximizes rich channels and empowers cross-platform convergence letting you drive mission-critical niches whereas Falcon utilizes scalable initiatives by scaling end-to-end networks for reintermediate granular platforms (win32 and linux 32/64).

Re:MySQL versus PostgreSQL

I wouldn't rush to call it pairity yet. It may be that Falcon brings MySQL into to paritiy with Postgres but we'll probably have to wait and see. My gut feeling is that Falcon may not be as mature as the storage engine used by Postgres, and may not be for some time.

Re:MySQL versus PostgreSQL

is evidence that easier trumps better when it comes to the early adoption curve, something I wish the PGSQL folks had understood (or rather cared about).

But in something as mission-critical as a database, of all things, reliability trumps everything. I don't think they could have developed PostgreSQL any other way and still supported its primary goal of safety.

PGSQL should have thrashed MySQL long ago. If you wait long enough, competing projects will gain parity and the game is over.

What gave you the (wrong) impression that PostgreSQL folks have been sitting around twiddling their thumbs? Version 8.2 just came out within the month and includes several performance boosts that make it fly on our production systems.

Re:New Microsoft Sql Server

New Microsoft Sql Server coming soon to a store near you. Cant wait for M$ to secretly add this to their SQL server and call it innovation.

Laugh all you want, but MS is one of the leaders in the database world with SQL Server for Windows, and have been for a while now. SQLS is by a long, long shot the best software product Microsoft has ever released, IMHO, and keep doing, right to the latest version. It works great.

Re:New Microsoft Sql Server

Microsoft SQL Server has had almost all of these features since its first release in the early 90s. MVCC was just introduced in Microsoft SQL Server 2005. There is no row-level compression in SQL Server (or Oracle, or DB2, or PostGreSQL... which is probably a *good thing* from a performance perspective).

This is a nice step forward for MySQL, but for the most part it is just a means for catching up to the other commercial DBs and PostgreSQL. ACID compliance, granular locking, MVCC, and multithreading are *not* differentiating features in the database world.

Re:It's only a matter of time

mysql had better develop it's *own* engine

MySQL *have* done so with Falcon. MySQL *do* own Falcon.

[Jim has worked on other RDBMS in the past] != [MySQL do not own the one he's currently working on]