Slashdot videos: Now with more Slashdot!

View

Discuss

Share

We've improved Slashdot's video section; now you can view our video interviews, product close-ups and site visits with all the usual Slashdot options to comment, share, etc. No more walled garden! It's a work in progress -- we hope you'll check it out (Learn more about the recent updates).

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.

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.

Here's what gets me about MySQL. They say they have "pluggable" storage engines, but there's no clean abstraction. Each engine supports some things and not others.

Pluggable engines might be useful if the only differences are in the implementation, storage requirements, performance, and other administrative aspects. However, a constraint violation that will cause an error in one storage engine passes right through another storage engine. So, it's not like you can just swap one

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

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 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!

He probably did read the details. Some people automatically think that because something is GPL you can't have commercial use, just because then who'd pay you when they could get it for free?In many situations (you sell a product that uses MySql, for example) it just makes more sense to buy a license instead of abiding by the GPL.

However, there are a few (obvious) situations where you can use GPL software for profit:1) Server-side use - no need to provide source code.2) Contractors who are required to give

So what if you have to provide the source for InnoDB? There's a process boundary between InnoDB and the program that uses the database. Why would you have to provide the source for your program that only communicated with MySQL over IPC, even under the GPLv3?

There is of course a work-around. Having a search table that is MyISAM and has fulltext enabled.

Better yet, use something that's been built from the gound-up to search: Apache Lucene (which has even been ported to PHP).

Using Lucene will give you much better weighted results than the MySQL FullText, plus it has a natural language query parser so people can use the kind of search queries they are used to using in google etc.

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.

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

From what I know, the MVCC is in-memory only. I've followed the happenings at Firebird and Netfrastructure (Jim Starkey's previous company, from which Falcon is derived), and he has commented that the MVCC is handled in-memory. Disk-based records are always a single copy with no concept of back-records.The backup and restore issue in Firebird hopefully will be worked on soon, as I agree it is a big problem with high transaction rate applications. This is completely different than the sweep/vacuum issue, whi

I certainly understand the benefits of using GUID/UUIDs for primary keys, I often need to generate PKs/tags/identifiers in my real time system without doing a blocking INSERT and read the auto-increment value.However, as a user of InnoDB and its clustered indexes, I've found that you can easily use a UNIQUE index for the table and either let InnoDB create the "unseen" auto-increment INT PK, or just create the auto-increment INT PK openly, but use the UNIQUE GUID key for your identifiers. The performance is

That's the usual workaround for this predicament but it tends to create headaches with some ORMs. Anyway the usual purpose of using guids is to aid replication collusions and adding an autoincrement field complicates that (yes I know mysql has a workaround for that too!).Too bad postgres doesn't come with a GUID type. You have to track down some guys patch.

Crazy huh? How both of the most popular open source databases are lacking some really basic features like controlling the clustering of your indexes, g

mysql is avaliable via the gpl. however, there are different backends that you can use within mysql. the default current one is kinda lame. there is another one that is legally owned by someone not mysql (oracle now, I think). in theory this falcon engine is super awesome, free (as in tinkle), and isn't encumbered with ownership issues.

Ironically PostGreSQL has a bsd license so it's destined to die, not for me though, I prefer a unix like bsd system instead of a minix like windows os.Just like I prefer a "free for whatever" license instead of a "free as long as its free" license.The way I prefer Python over Perl or Java. All are personal choices, I'll never say what somebody else must do, only what you could do and I do judge people on their choices.Just as I'm judged on my choices.

Why would you be concerned about the license for a database? The licence is only important if you are modifying the code AND distributing the modified code. Most database users I know just install the binary and use that in which case you don't care if it's BSD or GPL licenced.

It depends how it is being accessed from the applications. Are they using the c APIs for example? If so they are linking to the client library which would usually be considered a derivative work and so would be bound by the terms of the license.

If they are doing something like using JDBC it becomes less clear and you would need to talk to a lawyer. (actually, you may want to talk to a lawyer anyway)

Most people use ODBC/JDBC either that or the libraries that come with their language like ruby, python, haskell or whatever. Anyway no need to talk to a lawyer unless you are modifying code and distributing it. The GPL does not apply to anybody who is simply using the binaries.

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 [mysql.org].

Apparently, MySQL is a mish-mash of all kinds of different code. Heck, even the part that handles esoteric stuff such as stored procedures (note tongue in cheek) is part of some external module. I can't imagine that having multiple layers of API's is a good thing, considering how critical databases can be. Personally, I want my database to be consistent, and thoroughly tested, but the best being when the whole thing is designed to work together. Call me crazy, but it certainly does not give me a warm fu

He means he was produced in an Oracle farm, and never saw the light of day.//Not that Oracle isn't good, its just can be too much for the job when the job is something under a 10 million dollar budget.

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.

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]

Like others said already.. First of all, Falcon allows MySQL to not be as dependent on InnoDB as it is right now. When Oracle got hold of InnoDB, people had serious doubts about MySQL's future. For a solution like MySQL, it's always good to have competing (Even if similar) engines for it's own survival / growth.

InnoDB is licensed under the GPL. InnoDB is no more dependent on Oracle then Linux is on Red Hat. MySQL could if need be develop InnoDB themselves.

Except that a large chunk of MySQL AB's revenue would disappear as they'd no longer be able to sell a commercial version of their database with InnoDB support. And believe me, few people are going to buy the commercial version if the preferred storage engine is MyISAM. MySQL pay InnoSoft for the right to distribute the InnoDB engine as part of their commercia

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?

So you're suggesting that people use something that they are not comfortable with, instead of something that they are and which is getting better at an acceptable pace?

Yes. I am indeed suggesting that people spend a few minutes learning how another Free, faster, and more powerful database works. If you're dabbling, then it's no harder to learn one than the other. If you're serious, then you owe it to yourself and your clients to know what's available.

So you're suggesting that people use something that they are not comfortable with, instead of something that they are and which is getting better at an acceptable pace?--As a boy I jumped through Windows, as a man I play with Penguins.

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 productionready. 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 MySQLmakes some progress. Besides a little competition never hurts.

> Well, there is one thing, Falcon compresses data while Postgresql doesn't. can't help wondering what this will do to performance.

This is actually not true. If you have a variable length column in Postgres and insert data that exceeds 2K in size, it will automatically be compressed by Postgres using a simple LZ compression algorithm. This has been the case since Postgres 8.0. You have to specifically disable this feature if you don't want to use it.

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.

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

I've never had to deal with anything more than a personal/small business database, and therefore am certainly no expert in database queries, but there have been many cases where I've been unable to figure out just what the hell I need to do to get the results i'm looking for straight from MySQL (or Access, which is unfortunatly what the small business database uses, and I am working on converting that sometime soon)...so, I just end up requesting a result set, get that, do some operations on it in PHP, the

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?

Tried it. Found out there was no support for case insensitive collations and then dropped it. Every other database in the world supports case insensitive collations so now I am testing out firebird, mysql and db/2. All are OK, I probably don't need everything db/2 offers so I will most likely end up with one of the open sourced ones.

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...

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.

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.

A couple of questions you may be able to answer - if MVCC is being done in memory, and disk writes being delayed as long as possible, then I stand to lose an awful lot of data if my master DB server goes down dont't I? My slave will be well behind, and even if I can recover the master, then a lot of data wont have been saved to disk and the log checkpoints will be just as stale. Or is this some compromise, where a commit triggers a write to disk? If so, then I still don't get what's so great, as in all the

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.

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.

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.

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.

I really have to agree for the most part... I will use MS-SQL when there is already a server in place, but prefer Postgres myself. I've never had much hassle with documentation, but agree the GUIs need a lot of work. I think the additional windows support is great... Distributed databases would be a really nice thing though... implementation of GUIDs similar to MS's for database replication/distribution would be great too...

Perhaps the damning thing about MySQL is not that they can't, but that they haven't chosen to.

That's a good thing. For many applications ACID isn't worth the overhead. There are many applications where performance is more important than reliability, for instance because the data can be trivially rebuilt if something goes wrong.

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.

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.

You've got to be kidding. I used SQL Server 2003 a year ago on a game server, and it was horrible. Just to name a few things:1. Near-incomprehensible administration panel. The fact that they gave it a fancy name ("Enterprise Manage

First of all, I have a somewhat hard time taking someone seriously when they can't even get the name right. Perhaps it was just a typo, but there is no such thing as SQL Server 2003. There is SQL Server 2000 and SQL Server 2005. I'm presuming you were using SQL Server 2000, since the management util is called SQL Server Studio in 2k5(and is more intuitive IMO).

1. It's been a while since I've used SQL Server 2000, so I don't remember what my thoughts were on Enterprise Manager then, when I was just ge

I figured that out too. But I wanted the system to make backups automatically, with no human intervention, and I wanted the backups to be sent to a remote server so that if this server crashes my data is still intact. Running the BACKUP DATABASE command easy, but automating the whole thing is not.

3. I've never had any problem with SQL Server's performance. Its generally been pretty good actually. Why on earth were you running a database on

Perhaps we've tried different versions, but my experience was nowhere as awful...1. Near-incomprehensible administration panel. The fact that they gave it a fancy name ("Enterprise Manager" or something like that) didn't help. The usability of it absolutely sucks.

Given. It's not unusable, but i've seen better.

2. Bad documentation. All I want is to automatically make database backups every night at 3 AM and upload it to a remote server in encrypted form. With Linux, MySQL and SSH, I can write such a script i

I really apprecite some of the sweet improvements coming out of Microsoft these days, unlike a lot of the Linux fanbois here who will knock anything Microsoft does. I know SQL Server 2005 has some massive enhancements over SQL Server 2000, and some of them are amazing. I actually just finished reading Inside Microsoft SQL Server 2005: The Storage Engine, and it was a very interesting read. I was considering switching from InnoDB to SQL Server 2005 depending on the results of my research and in-house test

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.

Actually, compression can improve performance. Often disk I/O can slow you down more than the extra CPU work to compress/decompress.
I have written some flat-file processing code at my work and it runs much faster reading from and writing to zipped text files instead of uncompressed text files. The space savings are just a bonus.

MSSQL only runs on windows. It also only supports 4 gigs of data unless you want to pay. I would much rather use db/2 which has no data storage limit for the free version or an open source product and as a bonus I get to use any platform I want.

rotflmao, then you haven't had to deal with any relatively complex data with mysql in the past 5 years... There's been a lot of issues with how it deals with data. Even in "compliance" mode... which of course has to be disabled to be compatible with too many PHP apps out there.

I can't stand MS's politics, but there are plenty of command-line tools for MS-SQL if you prefer that route... most of the management interfaces run T-SQL script commands in the background. You can use isql, or the older interfac