Slashdot Mirror
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.
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
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.
.... other storage engines also exist
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.
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.
This is a real quick, detailess explanation, but explanation nonetheless... Essentially there are several options for "storage engine", each with its own set of features... The vanilla engine w/ MySQL is MyISAM, which among other things doesn't support transactions. In lieu of these shortcomings there is also the InnoDB engine, which does do transactions, etc.
The Falcon engine is from a renowned database developer, and as such has all sorts of neat features.
Ugh, no you haven't. The storage engine is how MySQL stores the actual data on disk. There are different formats, each have their own benefits, issues and work arounds. Think of MySQL as a SQL interpreter talking to different storage engines on the back. The default is MyISAM, but InnoDB and ISAM are others.
Are you serious? Do you think that MySql for a developer is equivalent to wrench for a mechanic? What about people who develop device drivers? What about people who write the kernel? I can go on and on for fields that have just no need / use for MySql or any other database. Not everybody in this world is doing *exactly* what you are doing.
This is probably a FAQ, and wandering off-topic, but exactly how the cheese do you have "GPL for non commercial, extra dollars for commercial" and expect it to work? Even InnoDB's licenses page implies that the limitation is "GPL, extra dollars for non-copyleft".
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
Get your own free personal location tracker
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 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 mentioned a valid dual-licensing offer in my post. The original post said that the GPL version came with a restriction on commercial use, which would be contrary to the terms of the GPL as they are generally understood. Thanks for reading the details before you reply!
Seeing as it has its base in Interbase, I would be running away as quick as possible. Interbase/Firebird(?) has a SWEEP process (read: Vacuum), however it was far more sluggish than Postgres. Also, the MVCC has a transaction count limit on Interbase where you hit ~2 billion transactions you MUST do a backup/restore -- a simple sweep won't cut it. I was working with a database that eventually required bi-monthly restores and each restore took over 12 hours. I also always found it funny that Interbase's row version was stored as a signed int (hence 2 billion version), rather than an unsigned int (giving 4 billion versions). When we moved to Postgres, on the same hardware, the backup went from over 12 hours to under 50 minutes. This is also due in part to the better tweaking capabilities available in postgres. Interbase is designed to be an easy to use/setup database system, which works fine for small cases, but when you start dealing with tens of millions of transactions per day, it becomes more work than it is worth. So if Falcon is anywhere like Interbase/Firebird, I'd be keeping well clear of it.
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.
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.
That isn't necessarily the case. Recently it has become popular to aggregate spindles into single stripe/mirror volumes with large stripe widths. This spreads I/O operations uniformly across disks. All disks contribute their IOPS capacity to all operations. Large stripe widths attempt to leverage high sequential IO bandwidth.
Oracle calls this policy SAME; Stripe and Mirror Everything. Their Automatic Storage Management (ASM) is an implementation of SAME, and it is recommended for use with both OLTP and OLAP applications.
If I had to make a MySQL Falcon instance fast I would build a large stripe/mirror volume for the (single) tablespace file. The point is that isolating high load tables on separate spindles isn't necessarily the best policy, according to current thinking. Of course there are other reasons for isolating parts of a database, but you only mentioned throughput.
Lurking at the bottom of the gravity well, getting old
That depends on the database.
Note to ACs: I usually delete AC replies without reading them. If you want to talk to me, log in.
"MyISAM - the most common and fastest. But no transactions, no ACID, etc. Good for many read-only or non critical tables. .... other storage engines also exist"
InnoDB - licensed from InnoSoft (now oracle). GPL for non commercial, extra dollars for commercial. Transactions, ACID, but a bit slow.
Unfortunately none of them handle binary blobs very well. e.g. video, audio, pictures, etc. Text is what they're optimized for.
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.
We don't see the world as it is, we see it as we are.
-- Anais Nin
Umm, Postgres has had automatic row-level compression (automatic compression of TOASTed) values for some time now.
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]
Il n'y a pas de Planet B.
As for another new storage engine coming out soon: MySQL and NitroSecurity have also recently announced that they are working together to integrate NitroEDB into MySQL as a storage engine, promising high-speed insertion and query rates even using VLDB data volumes.
My understanding is that the Falcon engine, like MyISAM and SQLite, supports the SYNTAX for defining foreign keys, but do NOT actually enforce the foreign key constraints! That's quite an important difference. I'll stick to PostgreSQL for when I care about relational integrity.
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.