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.

Comments

DB is down...

[Anonymous Coward]

I'd post the FIRS POST, but the db is down...

Finally!

[Anonymous Coward]

A cool open source project name. I've been waiting.

Overheard a few days earlier

[Anonymous Coward]

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

Please explain

[Shimmer]

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

[Anonymous Coward]

It doesn't suck.

Re:Please explain

[Anonymous Coward]

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

[Anonymous Coward]

RTFA?

Re:Please explain

[SirThomas]

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:Please explain

[slightcrazed]

"I'm a developer, but I've never used MySQL." Nice to meet you. I'm a mechanic who's never used a wrench. My brother, the surgeon who's never used a scalple will be by shortly.

Re:Please explain

[Geekboy(Wizard)]

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.

Re:Please explain

[0racle]

MySQL can use many different storage engines, some open, some not. Having another Open engine is just a good thing to have.

Re:Please explain

[AslanTheMentat]

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.

Re:Please explain

[MindStalker]

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.

Re:Please explain

[Shimmer]

Actually, I use SQL Server every day, but thanks for standing up for me :)

Re:Please explain

[namityadav]

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.

Re:Please explain

[Anonymous Coward]

He's just a tool.

Re:Please explain

[slightcrazed]

To say one is a developer within this thread implies that you are an app developer who is familiar with database interaction within an application. To be in that line of work and not have at least a basic understanding of one of the major players (and yes, I consider MySQL a major player in the DB sector) is a farce at best, and an admittance of stupidity at worst. My comment may have been tongue in cheek, but the point was still valid.

Re:Please explain

[Entrope]

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

Re:Please explain

[Anonymous Coward]

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

Also note, InnoDB => no full-text indexes. It's really annoying to have the sort of dilemma of picking one or the other, full-text indexes or integrity.

(someone PLEASE scream at me if they've actually gotten InnoDB full-text).

Re:Please explain

[NineNine]

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 fuzzy feeling to know that stuff as critical as my database's file system is tacked on as an afterthought. (No, I don't use MySQL, primarily for these reasons).

Re:Please explain

[rongage]

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

[Entrope]

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!

Re:Please explain

[clark0r]

last time i checked, this company used MySQL in their products.

Re:Please explain

[Giometrix]

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

Not to mention not everybody has to use his Db of choice.

Re:Please explain

[NineNine]

Where do they use it? I'd use MySQL for something like a contact list, or something like this. Do they use it for anything mission critical? I kinda' doubt it.

Re:Please explain

[OverDrive33]

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 was unaware that mechanics only had one wrench to work with.

Re:Please explain

[Ed Avis]

Yes, but does it run on the Atari Falcon?

Re:Please explain

[kv9]

adwords

Re:Please explain

[stg]

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 away the source to their client anyway.
3) Companies that sell support to the code.
4) You sell the hardware, not the software.

Re:Please explain

[nuzak]

It doesn't support foreign keys, so sucking is still one of its main feature points.

Re:Please explain

[0racle]

Yes it does.

Re:Please explain

[kosmosik]

> GPL for non commercial, extra dollars for commercial.

Well you can use GPL version for commercial projects and The Other license for totally uncomercial projects.

Re:Please explain

[Anonymous Coward]

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.

Re:Please explain

[kerecsen]

Stolen directly from the mysql website:

Mandatory comment about how copyright violation is not the same as theft, and about the loathsomeness of RIAA propaganda and fair use.

Re:Please explain

[Ambidisastrous]

Dual licensing is perfectly legal if all the authors (copyright holders) agree to both licenses. For example, Trolltech also uses a dual GPL/commercial license for Qt, and Mozilla uses a triple license of MPL, GPL and LGPL for some things.

Re:Please explain

[Anonymous Coward]

Mandatory comment about how copyright violation is not the same as theft

The colloquial use of stolen is not the same as the legal one. English is full of metaphorical uses such as this. It is disingenuous wordplay to try to apply it to acts such as copyright infringement.

Re:Please explain

[vandan]

You're that Postgres user I hear so much from, aren't you? Run along, son, and vacuum me a database while you're at it.

Re:Please explain

[Torst]

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.

Re:Please explain

[Mysticalfruit]

The company I work for uses MySQL for mission critical stuff and it works just fine. We even have a slave node and mirror our data in realtime without issue.

Re:Please explain

[killjoe]

Does it have unclustered primary keys? That's one thing that sucks with innodb and makes it virtually impossible to use GUIDs as primary keys.

Re:Please explain

[nuzak]

You might want to inform MySQL AB and tell them to update their page at http://www.mysql.org/doc/refman/5.1/en/se-falcon-l imits.html which says "Foreign key support is currently not available." I'm sure they'll be thrilled to hear the news.

Re:Please explain

[BasharTeg]

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 good, and it's easy enough to work out a join against child records by matching against the UNIQUE field, and then joining that PK against the child records or even joining on the GUID.

Performance would probably be better if you could create non-clustered PKs for GUID PK tables, but I haven't seen any slowness with the UNIQUE method.

My perspective might be slightly skewed on the performance because I'm running 8 core opteron, 32gb, on Solaris 10, but seriously if you benchmark it I think it's fine using UNIQUE.

Re:Please explain

[kestasjk]

It doesn't suck.

This message, posted to one of the highest volume sites on the internet, brought to you by InnoDB (one of MySQL's default storage engines).

Re:Please explain

[Anonymous Coward]

Does the mean the forthcoming MySQL/NitroSecurity combination will be GPLed?

Re:Please explain

[killjoe]

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, guid data types, case insensitive collations etc.

Re:Please explain

[(pvb)charon]

... Transactions, ACID...
Yeah, it's about time mysql supports this stupid smiley face. Opera's had this for ages!

Honestly, why can't anyone come up with original acronyms?

Re:Please explain

[Anonymous Coward]

Looks like the author read the PostgreSQL white paper.

Re:Please explain

[jadavis]

It doesn't support foreign keys

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 storage engine for another.

Re:Please explain

[jadavis]

Note that the GPL applies to the mysql client libraries as well.

This means that, if you want to connect to a mysql server, your client needs to be GPL also.

Re:Please explain

[JM Apocalypse]

Don't use MySQL's fulltext indexes. Instead, use Sphinx (http://www.sphinxsearch.com/) which is incredibly fast and indexes your MySQL database directly. You can even query it through MySQL using a custom storage engine.

Re:Please explain

[Anonymous Coward]

Define mission critical.

Re:Please explain

[14CharUsername]

You're very wrong about MySQL. Its not a mish-mash of code, they're basically just keeping the interface separate from the storage. This means you can write your queries without thinking about what storage engine you're using and choose your storage engine without having to thinking about how people are going to query it. This gives MySQL a huge amount of flexibility. If you're building a webforum and want speed, but aren't really worried about transactions, you can use MyISAM. If you are doing something serious and need transactions then you can choose InnoDB, or someday, Falcon. And what's really cool is you can mix it up and have InnoDB for some tables and MyISAM for others all within the same database.

And why are you upset about having plugins? would you prefer to have all kinds of features you don't need all there by default? If that's the case you must work for MS.

There are disadvantages to MySQL. But keeping the storage engines modularised is not one of them. The fact that you identified MySQL's greatest strengths as a weakness marks you as someone who hasn't even tried it. You are in grave danger of becoming a database snob. And there's nothing worse than a database snob.

Re:Please explain

[jadavis]

This means you can write your queries without thinking about what storage engine you're using and choose your storage engine without having to thinking about how people are going to query it.

That would be cool if it were true. Different MySQL engines affect the behavior of the highest level operations, such as constraints and ACID. Change storage engines and your app may start getting errors where there were none before... or not get errors when it should be.

If you want a real abstraction, check out PostgreSQL's various generalized index APIs, like GiST and GIN. Those allow a lot of flexibility to address the physical locations of the records that you need, without affecting the higher level functions at all except for (hopefully higher) performance. Also look at how much PostgreSQL can do with any index (BTree, GiST, or GIN), for example bitmap scans that fetch from the relation file in disk-sequential order (which is useful for index queries that return a good fraction of the pages in the relation file).

PostgreSQL only has one real "storage engine", but the way indexes can be (and are) used is probably more important. For instance, GIN is great for full text search or array overlap queries, and it didn't require a new storage engine at all.

Re:Please explain

[JimStarkey]

Falcon does not have clustered indexes. Falcon performs indexed retrievals with a two phase schema. The first phase screams through the index setting record numbers in a sparse bit map. After the index pass, Falcon fetches record (unless they're already in the record cache) in record order, which is reasonably close to physical order on disk. So, other things being equal, there is no performance difference between primary and secondary indexes. (Actually, it's a little more complicated than this because Falcon keeps partial indexes in memory until a transaction is really quite committed.) Falcon is also capable of and-ing and or-ing sparse bit maps from scans of multiple indexes before fetching records, but this isn't accessible through MySQL (yet).

Please explain part 2

[alexandreracine]

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

Re:Please explain part 2

[Anonymous Coward]

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:Please explain part 2

[TBone]

Knock Knock.

Who's There?

Falcon.

Falcon Who?

Now we're Falcon with Oracle!

Re:Please explain part 2

[Anonymous Coward]

Damn, you just blew up my 'buzzword compliance' meter!

New Microsoft Sql Server

[Anonymous Coward]

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.

Re:New Microsoft Sql Server

[Frosty Piss]

idiot.

Re:New Microsoft Sql Server

[Anonymous Coward]

nuh-huh. you are. sheesh. idiot.

Re:New Microsoft Sql Server

[Lisandro]

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

[Thundersnatch]

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:New Microsoft Sql Server

[Anonymous Coward]

Umm, Postgres has had automatic row-level compression (automatic compression of TOASTed) values for some time now.

Re:New Microsoft Sql Server

[FooBarWidget]

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 Manager" or something like that) didn't help. The usability of it absolutely sucks.
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 in 30 minutes. With Windows 2003 and MS SQL Server, I spent 1 to 2 weeks. The documentation told me that it's important to have a backup plan, and that every business should have a backup plan, and more of that nonsense, but nowhere did it mention HOW to actually make a backup! Only after days of searching did I finally find the command to make a backup, but I couldn't find any commandline tools like mysqldump! Eventually I found out that something can be done with the SQL server scheduler (I think it was called MS SQL Agent). I spent more than a week trying to figure out how their scheduler works and writing non-graphical tools to upload the backup to a remote server.
3. Bad, bad performance. While the game server was running (which used 100% CPU; the game server software sucks but that's another story), the SQL Query Analyzer's performance dropped to near zero. It took 2 minutes to execute a simple "SELECT * FROM foo WHERE bar = 2". One day I wrote a tool in Perl (using DBI and ODBC) to automate some database actions. And guess what? Running the same select statement in the Perl tool was almost instantaneous! I have no idea what SQL Query Analyzer is doing to make the query literally 100 times slower.
4. One day I noticed that the server ran out of disk space. It turned out that the database transaction log (not the database itself!) grew to 120 GB! The database itself was only about 100 MB. The documentation told me everything that I don't want to know, but it didn't tell me how to clear the transaction log. I spent 2 days searching Google on how to clear the transaction log, but I didn't find anything useful. Eventually I managed to clear it anyway by playing with the Enterprise Manager for an entire day. I don't remember how I did it, but it was absolutely non-intuitive.
5. It runs on Windows. It's hard to automate things. It doesn't have a good shell like Unix. Yes I know Cygwin exist, but scripts cannot run in the background and always need to have a DOS window. Yes there are tools to hide the DOS window but I don't want to add yet more bandaids to the fact that Windows's shell absolutely sucks. I ended up having to write nearly everything, while on Unix nearly everything is already there.

I'm sure someone will tell me that points 1 and 2 say I'm an incompetent database administrator. Well, this isn't a multi-million dollar business and I don't want to waste money on a database administrator which costs me more than $1000 per month! I just want to run the damn game server! I'm competent in SQL. On Linux I don't need a database administrator to maintain a database of this size, so why should I with Windows?

Seriously, this is not a troll. Yes I know I sound frustrated, but that's because I am. This is my own experience with Win2003 and MS SQL Server. This, together with other Windows quirks which I will not mention in this post, have convinced me that maintaining a Windows is an absolute nightmare.

Re:New Microsoft Sql Server

[sonofagunn]

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.

Re:New Microsoft Sql Server

[Anonymous Coward]

Let me guess, Aegis?

Re:New Microsoft Sql Server

[0racle]

Meh, I liked it better when they called it Sybase.

Re:New Microsoft Sql Server

[imemyself]

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 getting started using SQL Server. Irregardless though, SQL Server 2005's tools seem fairly user-friendly to me.

2. I'm not sure why you found it so difficult to do backups. I can understand a little bit of awkwardness if you didn't know to use SQL Server Agent, but performing the actual backup is fairly simple. I use SQL Server Agent to backup to a file(which is then backed up by BackupExec, since BE 10 seems to be bitchy as far as working with SQL Server 2005 goes. Typical clueless Symantec...), and all I use is
BACKUP DATABASE [dbname] TO [Name of Backup Device Created in SQLSvr]
WITH
DESCRIPTION = 'blah blah blah',
RETAINDAYS=14;


for each database. It probably took me a few hours when I first had to set this up (on SQL Server 2005, and I hadn't used backups in SQL Server or SQL Server Agent before). Admittedly, it probably took longer than it did for me to make by backup shell script for my MySQL and PostgreSQL databases running on Linux.

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 a server that had 100% CPU usage? That is a really bad idea, regardless of what database you're using, if you actually care about being able to access that data.

4. Yeah, I've had that sort of thing happen too(not the that extent, but the logs were still several gigs for a few meg DB). It is pretty damn annoying, but it is pretty simple to fix. All you have to do to stop it from happening (before it happens) is to change the Recovery Model (under the Options tab for the database's properties in SQL Server 2005) to Simple instead of Full. To shrink the DB logs, I think you run dbcc shrinkfile (Name_of_The_DB's_Log). Atleast that's what I put in my Knowledge Base. After changing the recovery model and running that command if necessary, I've never had any problems with it. More information can be found here

5. If you want to automate things in SQL Server, use SQL Server Agent. That's one of the things its there for. And if you really want to, you can use Task Scheduling in Windows to run a batch script or something automatically. I use task scheduler to automatically delete the file that my SQL Server backs its databases up to after BackupExec backs it up. I don't know what you were trying to do, but it sort of seems like you're wanting to do everything like you would on *nix. You sort of have to think differently and use different features to accomplish tasks on Windows. You're not going to be able to think about everything in the same way.

SQL Server and Windows aren't really that hard, once you get familiar with it. If they're used correctly, and their usage is well planned, then they are pretty damn solid. Not much different than PostgreSQL and Linux, or in some circumstances MySQL and Linux. I've used (and currently use) MySQL, PostgreSQL, and SQL Server. I don't know if I really have a favorite, but I do find a lot of SQL Server's and PostgreSQL's features really useful (things that MySQL isn't as strong in). You can't just expect to be able to start using a different piece of software and be able to do everything in the same way as you have done with another piece of software, and that goes for pretty much any of those databases.

Like I said, I use both Linux and Windows, as well as MySQL, PostgeSQL and MSSQL Server, so I'm

Re:New Microsoft Sql Server

[Anonymous Coward]

Come on. SQL server is a nightmare on earth for web application developers.

I've been working with both MySQL and SQL Server for five years on web projects, and I'll take an old PHPMyAdmin and MySQL's command line any day and save hours of frustration in dealing with Microsoft's latest and greatest.

SQL Server may have some extra features, but to web developers they're lost behind the "Server Management Studio" and its buggy, archaic and inefficient UI. I haven't encountered any PC application in the past 5 years more buggy than SQL server.

Re:New Microsoft Sql Server

[Lisandro]

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 in 30 minutes. With Windows 2003 and MS SQL Server, I spent 1 to 2 weeks.

I did something similar with a few installations and never took me more than 5 minutes - automatic backups and stored procedures triggering. The dedicated utility (SQL Server Agent) is very easy to setup via the GUI, IMHO.

3. Bad, bad performance. While the game server was running (which used 100% CPU; the game server software sucks but that's another story), the SQL Query Analyzer's performance dropped to near zero. It took 2 minutes to execute a simple "SELECT * FROM foo WHERE bar = 2". One day I wrote a tool in Perl (using DBI and ODBC) to automate some database actions. And guess what? Running the same select statement in the Perl tool was almost instantaneous! I have no idea what SQL Query Analyzer is doing to make the query literally 100 times slower.

I have to politely disagree here :) I've created fairly complex DB systems involving triggers, linked tables, stored procedures and lots of functions, and the performance of the server (running on a non-dedicated PIII with 512mb, Win2k3 Server) was stellar. This was both from the query analyzer and being polled from a website (about 10-15 stored procedures every 5 seconds per user).

4. One day I noticed that the server ran out of disk space. It turned out that the database transaction log (not the database itself!) grew to 120 GB! The database itself was only about 100 MB. The documentation told me everything that I don't want to know, but it didn't tell me how to clear the transaction log.

I limited the log size to a sensible value when i first installed the DB, and was a pretty straightforward procedure. Given, i was warned about this issue beforehand by a fellow programmer.

I'm sure someone will tell me that points 1 and 2 say I'm an incompetent database administrator. Well, this isn't a multi-million dollar business and I don't want to waste money on a database administrator which costs me more than $1000 per month! I just want to run the damn game server! I'm competent in SQL. On Linux I don't need a database administrator to maintain a database of this size, so why should I with Windows?

Oh, much agreed. Sadly, we do a lot of work both deploying and customizing SCADA setups, and this kind of systems run only on Windows platforms, which make SQL Server somehow a viable option (even when setting up a dedicated Linux/BSD server can be done for a fraction of the cost). That's when databases other than SQLS are an option - most SCADA software is "certified" to work with a handful of databases. If i was given the choice, i'd reccomend PostgreSQL, which, as far as i've tried, seems to be perhaps the most complete OSS database engine arround, miles ahead of MySQL.

Having said that, i found SQL Server to be an extremely competent product. If it's worth or not its price is debatable, but i really meant that is the best piece of software i've seen MS releasing that i can recall. The database engine is extremely fast and well-featured, and comes with a nice pack of utilities - notably, the Query Analyzer. I've seen commercial alternatives for MySQL, PSSQL and the works, but i've yet to come across an OSS one.

Re:New Microsoft Sql Server

[aztracker1]

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 interface, I happen to like the newer management interface for SQL Server 2005... Honestly, I don't see a great benefit to using the .Net interfaces in it, but some must...

PostgreSQL is much richer in terms of programming and standards than mySQL is... mySQL 5.x is better, but too much legacy crap is built on quirks in mySQL now... *sigh*

Re:New Microsoft Sql Server

[Anonymous Coward]

M$ FANBOI?

Re:New Microsoft Sql Server

[killjoe]

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.

Re:New Microsoft Sql Server

[perbu]

I've used MySQL with had ACID compliance, row-level locking, MVCC, foreign key and a multithreaded backend since 2001 - MySQL 3.23 w/InnoDB. Falcon adds no new features to MySQL.

Re:New Microsoft Sql Server

[BasharTeg]

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 testing of the performance. But the one thing that really disappointed me was that Microsoft JUST added MVCC in 2005. I personally consider the concurrency of MVCC to be essential for modern applications, at least in the field I work. The fact that they didn't even have it available as an option until now is incredible, and from what I read, you have to maintain the tempdb database when you're using MVCC (aka snapshot) or you could receive errors on update operations. It seems like they hacked snapshot on, rather than making MVCC a core feature of their database engine. I know row level locking gives a great deal of concurrency, and Microsoft's locking types are amazingly fine grained, but MVCC's memory footprint for large numbers of row level locks destroys Microsoft's which just starts escalating locks to save memory, and MVCC beats simple row level locking for concurrency any day. I'll take the 100% non-locking selects.

Also, although my applications rarely involve large transactions, I find it interesting that InnoDB's default mode is repeatable read, while MS sticks with read committed by default (especially if you want snapshot that isn't nearly serialized, you use RCSI). InnoDB's repeatable read is more than Microsoft's RR, because it also protects against phantom rows, giving you near serialized consistancy with fairly high performance. I'm afraid that to get comparable performance on MS SQL 2005, I'd have to give up certain levels of transactional consistancy, in addition to MVCC.

Most MS SQL 2005 users tell me "concurrency / transaction consistancy has never been a problem for me" because they're running their servers at 10-25% load and not exactly serving real time systems with it. But in my industry (telecom) I have to have the best possible concurrency while maintaining data consistancy, and I run my database servers a lot harder than most people do, and have a lot higher requirements for response times.

I am still looking into the advantages of both systems, and intend to test MS SQL 2005 head to head with MySQL 5.0.30 Enterprise / InnoDB in the near future. If anyone has any input on this subject, I would be very interested in hearing it. Feel free to email basharteg@basharteg.com

Re:New Microsoft Sql Server

[StrawberryFrog]

SQL Server may have some extra features, but to web developers they're lost behind the "Server Management Studio" and its buggy, archaic and inefficient UI.

What, you would maybe prefer maybe the buggy, archaic and utterly lacking-in-functionality tools that oracle gives? You do know that SQL server has commandline tools too?

I haven't encountered any PC application in the past 5 years more buggy than SQL server.

That's a very odd statement. This opinion isn't shared by anyone that I have spoken to.

Re:New Microsoft Sql Server

[Anonymous Coward]

You might also want to have a look at Firebird. It has had MVCC since day one.

Re:New Microsoft Sql Server

[FooBarWidget]

BACKUP DATABASE [dbname] TO [Name of Backup Device Created in SQLSvr]

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 a server that had 100% CPU usage? That is a really bad idea, regardless of what database you're using, if you actually care about being able to access that data.

I wasn't talking about SQL Server's performance, but the Query Analyzer's performance. As I wrote in my post, the Perl script I write could perform a query in a fraction of the time that Query Analyzer could.

This is the reason why I ran the database server on a machine with 100% CPU usage:
1. I already had 2 server machines. 66.6% of the game server was run on server A, while the other 33.3% of the game server and SQL Server ran on server B.
2. Both machines were dual core. On server B, core 1 would be running the game server program, and core 2 would be running the SQL Server.
3. The game itself ran just fine, and the Perl script that I wrote could perform queries very quickly, so I know the machine can handle it. The problem lies in the *Query Analyzer*, not SQL Server. Query Analyzer needed 2 minutes for a simple query while my Perl script needed 2 seconds.

5. If you want to automate things in SQL Server, use SQL Server Agent. That's one of the things its there for. And if you really want to, you can use Task Scheduling in Windows to run a batch script or something automatically. I use task scheduler to automatically delete the file that my SQL Server backs its databases up to after BackupExec backs it up. I don't know what you were trying to do, but it sort of seems like you're wanting to do everything like you would on *nix. You sort of have to think differently and use different features to accomplish tasks on Windows. You're not going to be able to think about everything in the same way.

Yes, I realized that. Making Agent make the backups is not hard. But I also want to automatically compress the backups and send it to a remote Linux server, preferably by ssh. The backup server is not on the same network.

Agent made things more complex by running commands as the SYSTEM user. If any of the commands need user input, then I'm screwed, and the entire command will freeze, because the user input window is never displayed to the user. Windows is highly graphical in nature so it's hard to find any tools that are non-interactive. I couldn't find any non-graphical scp tools for Windows, except Cygwin ssh (in combination with ssh public keys), so that's all I could use. But then I found out that Cygwin ssh would look in the SYSTEM user's home folder for the ssh keys, and the SYSTEM user doesn't have a home folder! In the end I had to write a Perl script that periodically checks the backup folder for new backups. This Perl script is run every time a user logs in (I put it in the Startup folder).

The guy who took over the server setup ODBC for Unix. And how he makes backups by sending the BACKUP command from the backup (Linux) server to the database server. He then copies the backup from the database server to the backup server through ssh (a Cygwin ssh daemon was running on the database server).

Is there a better way than this?

My conclusion is that SQL Server may work for large enterprises that have expensive and trained database administrators, but for small users (like me, who just want to setup a non-commercial game server, and who don't have tons of money to waste), SQL Server is way too complex and phrone.

Re:New Microsoft Sql Server

[Anonymous Coward]

But I also want to automatically compress the backups and send it to a remote Linux server, preferably by ssh. The backup server is not on the same network. You can set up SQL Server Agent jobs with command-line tasks too. I've written one in the past to backup, zip the backup and then transfer it to a share on another machine. (This is all 2000, I haven't played with agent jobs on 2005.)

IIRC transaction logs are processed as part of the backup job: to keep transaction log sizes in check you just need to run regular full backups.

Re:New Microsoft Sql Server

[CodemasterMM]

Surprised someone would mention that; I am a developer for eAthena (every now and then, college gets in the way).

But I'm anxious to see what performance differences and nifty features Falcon will hold when the Open Source Community gets their hands on it. I currently use MySQL with a mixture of MyISAM and InnoDB tables, but that may all change depending on what new storage types appear.

Re:New Microsoft Sql Server

[AntiDragon]

I think it's a case of typical front-end bloat. I'm tasked with looking after a few MSSQL DBs - 7, 2000 and 2005 as well as occasionally developing light apps with them and when they are set up and running well, they are indeed decent RDBMS and very feature rich - as in *useful* features. But day to day admin tasks are horrible and documentation is poor. Lot's of it, but as you mentioned it's all very "business speak" and only skims the technical stuff you actually need.

Far from being an MS fan, I have to admit that a fair amount of software they produce *is* impressive. But then they invariably tack on horrible interfaces or bloated extra features until you have to spend days digging through the morass before you find the functionality you were looking for.

In fact, this applies to the OS as a whole - good core, crappy shell.

Re:New Microsoft Sql Server

[Thundersnatch]

InnoDB does not appear to be ACID-compliant, at least according to the ANSI standard. See:
http://dev.mysql.com/doc/refman/5.1/en/innodb-erro r-handling.html

See the 2nd bullet point. Silently accepting some statements in a transaction while discarding others is a pretty bad idea.

Re:New Microsoft Sql Server

[BigZee]

Oracle 9i and newer supports row compression. Apart from the obvious space usage benefits, there can, under appropriate circumstances be benefits in performance as well. This tends to be in applications that compression is most needed such as large datawarehouses where queries can have a huge overhead in i/o and memory usage as the blocks remain in compressed form even when in memory. The downside is the potential increase in processing required but as large queries tend to be disk bound, it is often the case that the increase in CPU usage is in no way detrimental.

Re:New Microsoft Sql Server

[14CharUsername]

Yes but it means that the database snobs should stop bitching about MySQL. Although they still will, its just that now they will look like bigger asses than before.

MySQL's counter-attack to Oracle's advances

[atomic777]

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

InnoDB

[friedo]

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

Re:InnoDB

[Just Some Guy]

What does Falcon do that the InnoDB engine doesn't currently do?

Get developed by a company that doesn't hate MySQL, for starters.

Re:InnoDB

[keeboo]

The development of this new engine does not depend on Oracle.

Re:InnoDB

[namityadav]

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.

Re:InnoDB

[Anonymous Coward]

It protects MySQL from Larry Ellison.

Re:InnoDB

[0racle]

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.

Re:InnoDB

[keeboo]

I'm aware of that.
The problem when they take the whole team who maintains the project is that, in practice, is not exactly easy to simply fork.

Re:InnoDB

[LizardKing]

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 commercial version - whether Oracle allows them to renew this agreement when it expires is somewhat doubtful.

Yes!!!

[Anonymous Coward]

Big Fucking Deal!!

You stupid fucktards and your Open Source!

Give me a Goddamn Break!!!

Open Source is for Retards!!!! It's garbage!!!! GNU is shit!!!! Fuck you!!!!

I refuse to use Open Source!!!! It's shite...don't listen to these fucktards!

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

[porkchop_d_clown]

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:Okay, I'm stupid. What is a storage engine?

[rhavenn]

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.

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

[Anonymous Coward]

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?

What do you use MySQL for, if you haven't used storage engine? I bet you're just confused and don't know what you're talking about.

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

[Bacon Bits]

You know, I really hate to say this, but:

Spoken like a true MySQL developer.

Multiple different storage engines....

[Anonymous Coward]

MySQL seems to get a different "storage engine" about every other week nowadays... with various assortments of different legal baggage to carry around for each different storage engine deployed.

This scatterbrainedness, and ridiculous inconsistency of MySQL has blown its credibility to smithereens... rendering the entire product to not be able to be taken seriously anymore. Nobody I know trusts it enough to develop any serious app around it anymore. It's now become only an academic curiosity, which is a disappointing tragedy considering so many folks once upon a time had a lot of faith in it as a powerful and nimble lightweight database. Now we have no idea where the fluck it's heading anymore.

Re:Multiple different storage engines....

[crayz]

So you posit that people are no longer using MySQL?

Re:Multiple different storage engines....

[Anonymous Coward]

It sounds like you don't have a clue. MyISAM is the default storage engine and it has been like that for years. And there are several serious sites using MySQL like Slashdot for example.
SQ

Re:Multiple different storage engines....

[Anonymous Coward]

MySQL is yesterday's news. It's as dead as *BSD. Postgres ftw.

Re:Multiple different storage engines....

[jazir1979]

I realise your post doesn't say that Slashdot are using MyISAM, but just to make it clear, Slashdot are using InnoDB http://www.mysql.org/doc/refman/5.1/en/innodb-over view.html.

Re:Multiple different storage engines....

[MPHellwig]

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.

Re:Multiple different storage engines....

[larry bagina]

slashdot uses a very customized version of mysql and a LOT of caching. Remember a few months back when threaded comment posting was disabled for 24+ hours so they could rebuild the comment index?

Re:Multiple different storage engines....

[killjoe]

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.

Re:Multiple different storage engines....

[MartinG]

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)

Re:Multiple different storage engines....

[killjoe]

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.

Nice FUD though too bad it's so easily countered.

Re:Multiple different storage engines....

[MartinG]

The GPL does not apply to anybody who is simply using the binaries.

No, it does _not_ just apply to a modification of the original work, but also to any derived works. (see GPL snippet below)

Linking usually constitutes a derived work. Whether using GPL jdbc drivers by putting it in yourl classpath etc. constitutes a derived work is a subject of debate. Seriously, there is legal opinion in both directions on this issue. I have personally discussed this with people at a well known open source java application server vendor who face this exact issue. There is even disagreement between FSF and The Apache Foundation about shipping LGPL classes, never mind GPL ones.

Seriously, if you are building an application that relies on GPL libraries of any kind, you may technically be creating a derived work and you really should talk to a lawyer.

From the GPL:

"b) You must cause any work that you distribute or publish, that in whole or in part contains or is derived from the Program or any part thereof, to be licensed as a whole at no charge to all third parties under the terms of this License."

Re:Multiple different storage engines....

[tf23]

Whether OSTG uses a "customized version of MySQL" has absolutely nothing to do with what happened with the threaded comment posting being disabled a few months ago. That was a schema issue, plain and simple.

I've never heard they used *anything* custom in MySQL. InnoDB, MyISAM, replication and multiple masters on some beefy hardware is the general consensus. For caching, it's mainly memcached, just like a lot of the bigger sites.

Re:Multiple different storage engines....

[killjoe]

"Linking usually constitutes a derived work."

Yes but linking in the code sense not in the "I am using this library" sense. If I write a driver and I link to the mysql libraries then I have to release my code under the GPL.

If you are not writing drivers you are fine.

Again this is old FUD, you need to find better FUD.

Re:Multiple different storage engines....

[larry bagina]

ask Jamie McCarthy over at slashcode.org. Slashdot is 6 servers: 4 of them serve cached content, 1 serves dynamic (perl) content, 1 runs mysql (a custom version). When a logged in user reads a story, front page, etc, the db is queried for the user account and preferences, everything else comes from cached html files. An AC reading doesn't even require a database hit.

Re:Multiple different storage engines....

[MartinG]

This is certainly not FUD. Please stop simply labelling what you disagree with as FUD and instead find something to back up what you are saying.

How is it so clear to you that "writing drivers" constitutes creating a derived work, but using mysql via jdbc does not? I'm not saying you are wrong, I'm just saying it is legally unclear and nothing you have presented suggests otherwise. If you have examples from case law to cite, please go ahead.

Re:Multiple different storage engines....

[killjoe]

Here let me try and make it clear for you.

If you are not writing code you are not violating the GPL. Got that? Wring SQL statements doesn't count.

If you are writing code and you are using a library you did not write then you are not violating the GPL. If there is a GPL violation it was done by the guy who wrote the library. SO if you are using JDBC, ruby, python, php, ocaml, haskell, rebol, or brainfuck to write your program that access your mysql server you are safe. Also if you are not distributing your application then you are safe no matter what.

If you are writing a library to access mysql and you do not use the mysql libs then you are not violating the GPL.

If you are writing a library to access mysql and you are using the mysql libs and you are not distributing those libraries to anybody outside of your company then you are not violating the GPL.

Got that? Now stop spreading FUD. Read the fucking GPL and understand it, it's not that fucking complicated. In order to violate the GPL you have to write code derived form GPLed code AND you have to distribute it.

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

[PurifyYourMind]

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

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

[PinkPanther]

Since the article and the product's website all state that this is an ALPHA, you likely will be quite happy that your host will not upgrade any time soon.

Why not PostgreSQL?

[Just Some Guy]

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?

[Anonymous Coward]

Perhaps MySQL compatibility?

MySQL is a lot like Windows. It's adequate, just a little substandard, not entirely compatible with everything-else, is prone to giving you the occasional headache, and it's absolutely everywhere. You'd think it was kudzu or something.

Re:Why not PostgreSQL?

[pembo13]

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?

Re:Why not PostgreSQL?

[unoengborg]

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.

Re:Why not PostgreSQL?

[Just Some Guy]

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.

Re:Why not PostgreSQL?

[nacturation]

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. Your advice appears to be at odds with your sig.

Re:Why not PostgreSQL?

[sumbry]

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

http://www.postgresql.org/docs/8.0/static/storage- toast.html

Re:Why not PostgreSQL?

[pembo13]

Not at all.

Re:Why not PostgreSQL?

[Jeffrey Baker]

Actually, TOAST has been around since long before 8.0. I think the improvement in 8.0 is that you can disable compression, whereas it was mandatory before.

Re:Why not PostgreSQL?

[pbaker]

Two words why not Postgresql: repli cation

Re:Why not PostgreSQL?

[jadavis]

PostgreSQL with Slony-I is one of the best replication solutions available. PostgreSQL also has Two-Phase Commit (2PC) in case you need to make synchronous modifications.

These solutions may not be everything to everyone, but they are very useful to a lot of people. Usually people who complain about PostgreSQL replication are people who just want a magic switch without considering what they actually want to happen in the wide range of possible failures that can happen on or between nodes.

Gotcha.

[porkchop_d_clown]

Okay. Thanks; I didn't realize that MySQL abstracted the actual file I/O layer, although that certainly makes sense.

It might have potential...

[puppetman]

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

Re:It might have potential...

[atomic777]

MyISAM will probably still have its uses in read-intensive applications where simplicity is most important even after Falcon is released officially, as will InnoDB and MySQL cluster have their uses. This is the benefit of MySQL's pluggable architecture -- you can choose the right tool for the job.

Re:It might have potential...

[TopSpin]

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.

Re:It might have potential...

[galvanash]

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.

Jim Starky, the designer of falcon (and Interbase and Netfrastructure) would say that that is the point (if you ever heard him speak you would get the joke). Falcon is designed from the ground up to be as independent as possible from disk performance. The basic fundamental design philosophy is "do everything that matters in memory, disk is only a backfill". Once the data gets "hot" disk performance pretty much doesn't matter anymore. Writes to disk are performed in the background and NEVER block, so their performance isn't that important. It also has a clever 2 level indexing scheme that makes ALL indexes behave like clustered indexes, so that helps with disk I/O too, but like I said the intent is not to have to worry too much about disk performance.

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

Although feature wise it is much more like innoDB, you are right that it will probably end up being more of a MyISAM replacement. It offers alot of things that innoDB offers, but it aims to be fast, simple to administer, easy to understand, not need ANY tuning, and will be 100% open source - things MyISAM users would probably appreciate more.

MySQL versus PostgreSQL

[Anonymous Coward]

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). PGSQL should have thrashed MySQL long ago. If you wait long enough, competing projects will gain parity and the game is over.

Re:MySQL versus PostgreSQL

[Azarael]

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

[Just Some Guy]

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:MySQL versus PostgreSQL

[swillden]

But in something as mission-critical as a database, of all things, reliability trumps everything.

That depends on the database.

Re:MySQL versus PostgreSQL

[Anonymous Coward]

I meant it mostly as flame-bait. I wouldn't use anything other that PostgreSQL and the technology in the recent releases is awesome. I still find it a PITA to administer. The GUI/Web front ends are shite IMHO. Documentation is way better than in the past.

Re:MySQL versus PostgreSQL

[aztracker1]

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

Re:MySQL versus PostgreSQL

[Anonymous Coward]

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.

I agree that reliability trumps all, but it sounds like you're saying Postgres was always super-stable. Not so, sadly ...

If you want to make crappy code into good code, you can. You just need to decide that you want to.

The MySQL guys *could* decide that version N+1 will be ACID-compliant, 100%, with every backend, and then work to make that happen.

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

Re:MySQL versus PostgreSQL

[vidarh]

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.

Re:MySQL versus PostgreSQL

[Just Some Guy]

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.

Ironically, PostgreSQL often benchmarks much faster than MySQL, particularly when non-trivial loads are involved. When you can formally prove that a class of error conditions can't happen, you can stop testing for them.

Re:MySQL versus PostgreSQL

[Just Some Guy]

That depends on the database.

No it doesn't.

A DBMS featuring a "/dev/null" backend would be worthless for all applications other than entertainment value (not that MySQL has ever been anywhere near that bad). Unless a system is reliable enough to actually manage your data, every other feature is moot.

Re:MySQL versus PostgreSQL

[swillden]

Unless a system is reliable enough to actually manage your data, every other feature is moot.

That's not remotely the same as saying that reliability trumps everything. For example, consider an application that uses a database as a read-only, or nearly read-only, working store, but requires very high performance. In that case, reliability just has to be reasonably good, performance is the driving requirement. Or consider a low-end home photo management tool: it's annoying if the database gets lost or corrupted, but since it only stores metadata, not the photos themselves, it's not a huge loss. The driving requirement there is simplicity of installation and configuration. I could easily come up with a half-dozen more scenarios where once a basic level of reliability is achieved, ease of use, performance, size, developer-friendliness, or even popularity would be more important than adding another nine -- or even two or three of them -- to reliability.

As I said, it depends on the database.

Re:MySQL versus PostgreSQL

[Just Some Guy]

I could easily come up with a half-dozen more scenarios where once a basic level of reliability is achieved [...]

In other words, the first priority is reliability. After that, other considerations come into play. See? We agree.

Re:MySQL versus PostgreSQL

[swillden]

I could easily come up with a half-dozen more scenarios where once a basic level of reliability is achieved [...]

In other words, the first priority is reliability. After that, other considerations come into play. See? We agree.

Perhaps where we disagree is the meaning of "trumps". I interpreted your comment to mean that until a very high level of reliability was achieved, nothing else mattered. I think a much lower level of reliability -- the level provided by MyISAM, or perhaps a bit less -- is perfectly adequate for plenty of applications.

Re:MySQL versus PostgreSQL

[GooberToo]

That's not remotely the same as saying that reliability trumps everything. For example, consider an application that uses a database as a read-only, or nearly read-only, working store, but requires very high performance.

Then you should probably not be looking at an RDBMS. Rational databases are about reliability (ACID is generally assumed) and relationships; not performance. Your criteria is about performance whereby, the dataset can provide for optimal performance outside of relational trappings.

Remember, SQL is not an end-all, be-all of databases.

Re:MySQL versus PostgreSQL

[GooberToo]

The phrase "very high level" is not quantitive. Besides, what good is it to deliver lots of bad data fast? And if you're strictly delivering read-only data, then a data cache or some other representation of your data should provide much faster data access then the layers associated with a SQL engine. In other words, given your criteria, there probably exists a far better solution than MySQL or PostgreSQL; and the solution wont include SQL in it.

Re:MySQL versus PostgreSQL

[swillden]

Then you should probably not be looking at an RDBMS. Rational databases are about reliability (ACID is generally assumed) and relationships; not performance.

As a long-time fan of object databases and the blistering performance they can deliver (often as much as three orders of magnitude faster than RDBMSs), I agree with you. However, there's another constraint, and that is that RDBMSs and SQL is what developers know.

In addition, for many applications the data integrity requirement depends on the nature of the data stored, rather than the nature of the application. Two installations of the same app can have radically different performance and reliability requirements. The nice thing about SQL -- particularly if it's DB-independent SQL -- is that you can choose a storage engine that is appropriate to the requirements.

Of course, a decent OODBMS would be both reliable and hugely faster than any RDMBS, but now we're getting back to "what the developers know".

Re:MySQL versus PostgreSQL

[swillden]

The phrase "very high level" is not quantitive.

:-)

Besides, what good is it to deliver lots of bad data fast?

Obviously none. But the only scenario where that's likely is a non-ACID database, with insufficient locking and competing writes resulting writers overwriting one another's commits, which may even break referential integrity. If you have an application with that access pattern, you obviously must use a better DB. Many apps, however, are read-mostly, write-rarely, so heavy-handed locking is okay, and even ignoring the issue of consistency altogether is occasionally fine.

Heck, even with "real" databases, it's not uncommon to turn the transaction isolation level down, because the app developers know that full ACID consistency isn't required. I'm working on a project right now that uses a big DB/2 cluster as its storage engine, and we set ANSI SQL isolation level 1 (READ COMMITTED). An analysis of transaction patterns showed that we really could use level 0.

And if you're strictly delivering read-only data, then a data cache or some other representation of your data should provide much faster data access then the layers associated with a SQL engine. In other words, given your criteria, there probably exists a far better solution than MySQL or PostgreSQL; and the solution wont include SQL in it.

See my other response.

Re:MySQL versus PostgreSQL

[TheSunborn]

The problem is that nobody have ever made a "decent OODBMS".
I have at least not been able to find any, but if you know of any, i would be really interested in our expierence with it.

Martin

Re:MySQL versus PostgreSQL

[swillden]

The problem is that nobody have ever made a "decent OODBMS".

It depends, of course, on what your requirements are for a "decent" OODBMS. For C++, I think ObjectStore rocks.

Re:MySQL versus PostgreSQL

[TheSunborn]

Thanks for the link to ObjectStore. It really seems like they have updated and improved their website and product since I last visited them a year ago :}

One more thing. Do you have any idear about the price(Or just price range(1000$ 10000$ ??) for ObjectStore? All I could find on their website was a "call us for pricing", and it is my expierence that when a companies don't dare to offer a price on their website it's because their product is extreamly expensive.

Well I think I will just write to them and find out.

Re:MySQL versus PostgreSQL

[swillden]

I used ObjectStore in the mid to late 90s and I thought it was a great product then. It was moderately expensive then, though cheaper than the high-end RDBMSs. I would suppose it would be about the same now.

It's so easy to work with, I think they really should GPL their PSE version -- dual-license it like Trolltech, Sleepycat, etc. I think it would help them get some traction among developers which would help them sell both PSE and their higher-end products. But, ObjectStore has always seemed to be great technology saddled with lousy management, so I'm not surprised they don't see the opportunity.

VACUUM?

[dskoll]

So if Falcon uses MVCC, does it require something like PostgreSQL's VACUUM? Or does it have some other way to detect and remove dead tuples?

Also, has anyone looked at making PostgreSQL a storage plugin for MySQL? :-)

Re:VACUUM?

[gavinroy]

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

Re:VACUUM?

[GroovinWithMrBloe]

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.

Re:VACUUM?

[Anonymous Coward]

From my understanding it doesn't have interbase underpinnings, other than the author. Many of the limitations in interbase are there from the early realases when those limitations were way big, but are so involved in the internal architecture of the product that they have remained. Since this is a new system, I assume they have learned from the past and have immidiate generation merge sweeps ala innodb or similar.

Re:VACUUM?

[thevoice99]

Speaking of which. Isn't Falcon's feature set a copy of Postgres?

Re:VACUUM?

[SdnSeraphim]

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, which again isn't an issue because there are no on-disk back-records to sweep.

Suggesting that since Falcon and Interbase/Firebird share a common "parent" they are indentical in performance, features, or drawbacks is a foolish argument. Jim Starkey hasn't been involved in Interbase development in more than 15 years (maybe 20). To recommend against a piece of software because the same architect designed/coded it is quite short-sighted. The matra should be the right tool for the job. If Firebird/IB didn't work for your purposes then fine, it was right for you to switch to something else. It does not mean that it is not right for other peoples purposes. What about all of the things that Firebird provides that Postgres does not? Postgres would absolutely not work for some of my projects, where FB is a perfect fit.

Re:VACUUM?

[Anonymous Coward]

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

It sounds ridiculous, but it actually looks kinda cool!

Re:VACUUM?

[Claws Of Doom]

Yes, but that looks more like a 2CV than a Bug. Sheesh, do you guys know nothing? ;)

Re:VACUUM?

[GooberToo]

MVCC requires extra overhead at the time of the operation or a vacuum. PostgreSQL went the vacuum route as it's believed this provides for an off-peak window where this can better be addressed. If you don't push for a vacuum approach, you must take the performance hit at the time of the update or delete operation.

Re:VACUUM?

[JimStarkey]

OK, I'll bite. Which is the Bug and which is the tank?

Re:VACUUM?

[JimStarkey]

Falcon, unlike Interbase/Firebird and Postgres, is multi-version in memory but single version on disk, so there is nothing like the Postgres vacuum process. Sweep in Interbase/Firebird is quite different from the Postgres vacuum and is used to reduce the amount of transaction state information that needs to be lugged around. I got quite tired of hearing about sweep problems and designed Netfrastructure (of which Falcon is a small subset) to have neither sweep nor vacuum issues. Falcon does have a record scavenge thread that kicks off when the size of the record cache reaches a pre-determined limit to free up memory consumed by boring records, but that's a different issue.

LOLOLOLOLOLOL

[Anonymous Coward]

Slashdot is a "serious" site? This place is on its deathbed. Digg has murdered /. Now they're desperately trying to emulate Digg's bookmark feature. It's a futile attempt. No site other than incestuous NewsForge has added this pathetic copycat.

I had to drop MySQL

[caluml]

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

Re:I had to drop MySQL

[The Barking Dog]

You must've had an ancient version of MySQL. Versions > 4.0 or 4.1 (can't remember which) support subqueries, and I know I've done negative subselects like that.

Re:I had to drop MySQL

[corsec67]

Mysql 5.0 supports subqueries, but they are incredibly slow compared to mysql.

Re:I had to drop MySQL

[hitchhacker]

true.. but how slow are they compared to MySQL?

-metric

Re:I had to drop MySQL

[RobKow]

Well, for your particular example:

SELECT * FROM foo LEFT OUTER JOIN y ON foo.bar = y.x AND y.z > 4 WHERE y.x IS NULL ;)

But I'm sure there are nasty cases where you can't substitute joins readably or maybe at all.

Re:I had to drop MySQL

[XO]

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, then figure out what I need to request from there to get what I want.

It's a big mess, but there doesn't seem to be a hell of a lot of good tutorials for how to achieve certain things in the various DBs out there. Took me damn forever to figure out how Access syntax differed from mSQL/MySQL, the two i've used before that.

Re:I had to drop MySQL

[caluml]

I did investigate thoroughly at the time, and yes, as mentioned in another answer to my comment, it was 4.0, or something like that. But for some reason, the JOIN with NULL didn't cut it - I can't remember why - the only solution (apparently) was NOT IN.

Re:I had to drop MySQL

[eric2hill]

SELECT * FROM foo WHERE bar NOT IN ( SELECT x FROM y WHERE z > 4 ) This is exactly the type of query that Oracle fails miserably to execute. It performs no inner-query optimization, and I'm assuming mysql does the same thing. Oracle's method of operation is to execute the inner query for each row of the outer query, which I believe is per the SQL spec, but I could be wrong. Obviously this is not optimal for when you're just trying to exclude matching records. The most common rewrite of this type of expression is:

SELECT * FROM foo WHERE bar IN (
    SELECT bar FROM foo
    MINUS
    SELECT x FROM y WHERE z > 4
)

This generates a requested keyset in the inner query and will execute an order of magnitude faster as IN queries don't need to be executed for each row if the inner query doesn't reference the outer query. I've taken real-world queries with the NOT IN syntax that ran over several hours (end-of-day transaction reports) and dropped the query time to just a few seconds with this technique.

And yes, I'm a certified Oracle DBA.

GODDAMN IT... YOU'RE A JAVA MONKEY, AREN'T YOU?

[Anonymous Coward]

Be warned: Java causes irreversible brain damage.

Oh, please

[Lisandro]

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?

Re:Oh, please

[Anonymous Coward]

Despite the fact that you've been marked "Flamebait," we use it here in a real-world deployment, but we use it as an application backend for a limited set of simultaneous users (although we've managed to cram image data into it and have it work ok).

The biggest problem I ran into with 7.4.x was its optimizer sometimes made funny guesses, especially w.r.t. queries involving VIEWs. Other than that, all the support for the "relational" part of RDBMS was a breath of fresh air compared to MySQL.

Re:Oh, please

[Anonymous Coward]

Gave PSGSQL a shot

Took one look at that and thought that Sony was releasing a database to run on the PS3

PSGSQL - PlayStation (R) Gooder-than-your (TM) Structured Query Language !!

Re:Oh, please

[killjoe]

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.

Re:Oh, please

[mw]

What exactly do you mean with case insensitive collations? Could you please give an example?

Re:Oh, please

[GooberToo]

Extentions exist to address this issue (including a case insensative text data type) or he can simply use ILIKE vs LIKE. If this is his sticking point, it's about as weak a troll as you can get. It's not like viable solutions are not readily available.

Re:Oh, please

[Orlando]

I would like to agree with you, but you haven't given any reasons why you prefered one over the other. Can you expand a little?

Re:Oh, please

[Lisandro]

Basically, performance. Anything involving subqueries seems to run substantially faster on my system, for example, after a few days of testing. Postgre also supports a wealth of features not available in MySQL until version 5, or with a much better implementation - triggers, stored procedures, functions, the works. You don't know how much you miss these until you find that MySQL doesn't do them, or do them in a completely bizarre way.

Finally, Postgre has a track record of reilability, and for me that fact alone makes it worth checking out. I've seen corrupted databases on MySQL installations for websites, for example.

Re:Oh, please

[killjoe]

The SQL standard specifies that you should be able to set collations on a per column basis. Postgres does not support that. All the database manufacturers and all the open source databases except for postgres give the user the option of selecting a case insensitive collation option so that the where name = 'mcnab' and name='McNab' and name = 'Mcnab' return the same record.

Postgres has chosen not the implement a case insensitive collation in their database. Instead they reccomend you use various non standard workarounds like ILIKE and *~ or using UPPER in your where clauses. Of course this limitation and their reccomended workarounds means that you can never port an existing application to postgres without rewriting all your SQL statements and basically rules out postgres if you are using an object relational layer such as activerecord.

This topic has come up dozens of times in the postgres mailing lists over the last five or six years. The devs are adamant that they won't implement it, their response is basically that users are stupid for having this requirement and that we should use one of the workarounds presented to us. When presented with those options most users with this requirement choose to use mysql or any one of dozens if not hundreds of databases that implement case insensitive data types of collations.

What's odd is that just recently somebody actually wrote a case insensitive data type and postgres refuses to ship it as a part of the distro not even as an add on. I suspect it's pride, after a decade of telling people they were stupid for needing a feature they can't very well implement that can they? The guy who wrote it admits it's not up to snuff, it doesn't work under all circumstances (i.e not all operators work properly) and that it could be made more performant.

Does that clear it up for you?

Re:Oh, please

[killjoe]

"Extentions exist to address this issue (including a case insensative text data type) "

No included with the postgres distro not even in the contrib. The author admits it does not work with all operators. The author admits performance problems.

Would you trust your mission critical data to an extention not blessed by the team, not included in the regression tests, written by one guy who admits it's not done yet?

Well maybe YOU would but not me.

"or he can simply use ILIKE vs LIKE."

It's easier to use a different database then to convert every single SQL with a where clause or to rewrite hibernate so that it uses funky postgres syntax like ILIKE and *~

As I said before EVERY OTHER DATABASE ON THE MARKET SUPPORT THIS FEATURE.

Alpha limitations

[Anonymous Coward]

http://www.mysql.org/doc/refman/5.1/en/se-falcon-l imits.html

No foreign key support.
No "select for update".
Online backup is not supported.
Serializable isolation levels are not supported.

Yes, I'm fully aware that it is an alpha release.

Eh. MySQL user, actually.

[porkchop_d_clown]

Unless you think using drupal makes me a sql developer.

Re:Eh. MySQL user, actually.

[Bacon Bits]

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.

Re:Eh. MySQL user, actually.

[Anonymous Coward]

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

Forget ACID, how about data integrity?

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.

I don't know whether to scold you for saying MySQL "is good enough for most people", or to praise you for ensuring a steady supply of badly-designed databases for me to fix in my consulting business. Guess I'll go with the latter, so... Yes! I agree! MySQL is AWESOME!!!!111!

Re:Eh. MySQL user, actually.

[nacturation]

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. So MySQL is like the Windows of databases?

Re:Eh. MySQL user, actually.

[Bacon Bits]

Yup. Complete with random incompatibilities and mindlessly non-standard features.

Oook. Oook.

[porkchop_d_clown]

I write device drivers, actually. I use Mysql as the backend for a club website I manage.

i see a naming conflict on the horizon...

[capsteve]

when i first read falcon storage engine, i initially thought of a storage software called falconstor. 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(NSFW) might be the winner...

Re:i see a naming conflict on the horizon...

[Anonymous Coward]

Falcon is actually already an APT repository generator, look: https://launchpad.net/falcon

FOSS Developers....

[Anonymous Coward]

So you posit that people are no longer using MySQL?

Many FOSS database application developers, like myself and about a dozen of my colleagues, have ceased using MySQL as our primary database platform for our projects over the past year or so... precisely because of this black cloud of uncertainty with MySQL. I've personally moved to Postgres myself, even though it was a pain to learn a new database to the degree of familiarity I had with MySQL, and rewrite a significant portion of my handy library of database code snippets I re-use most frequently.

Please explain Blobs.

[Anonymous Coward]

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

Unfortunately none of them handle binary blobs very well. e.g. video, audio, pictures, etc. Text is what they're optimized for.

Re:Please explain Blobs.

[FooBarWidget]

Are there any SQL databases which can handle large blobs well?

Re:Please explain Blobs.

[Anonymous Coward]

Why not just store filenames?

Re:Please explain Blobs.

[funfail]

What about backups, portability, full text search (at least for some file types), mass deletions etc.

Yeah, I can handle them within my application or through various batch scripts but why should I have to deal with those when I am using a dedicated tool to deal with data: The database... I mean, come on... Filesystem designers are trying to dump filesystems and use databases for everything.

Re:Please explain Blobs.

[Anonymous Coward]

Firebird!

Re:Please explain Blobs.

[JimStarkey]

Falcon is designed from the ground up to support large binary blobs (which, frankly, shouldn't be a surprise). In specific, blobs have different data flow path than records avoid double writing blobs to the serial and then to the disk. Blobs are not re-written during record update unless the blob itself has changed. Finally, Falcon goes through some trouble to avoid materializing unreferenced blobs during queries. And although MySQL currently does provide access, Falcon supports multi-volume blob repositories that do automatic volume rollover based on file size, time period, or both. What else would you like?

It's only a matter of time

[Anonymous Coward]

before falcon goes the way of innodb. mysql had better develop it's *own* engine; that's the least it owes to the millions of users who built its reputation, whose data is still liable for ransom as long as mysql keeps using engines it doesn't 'own'.

Re:It's only a matter of time

[Zontar The Mindless]

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]

MVCC: Multi-Version Concurrency Control

[DrJimbo]

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.

Re:MVCC: Multi-Version Concurrency Control

[nuzak]

Actually, InnoDB uses MVCC too. Hell, even BDB uses MVCC.

Getting something optimized for read loads that scales in size better than MyISAM seems to be the primary motivation for Falcon.

Re:MVCC: Multi-Version Concurrency Control

[BasharTeg]

Modded Informative? InnoDB is totally an MVCC database. It has 100% non-locking SELECTs. Come on.

Please explain Money

[Anonymous Coward]

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

Until GPLv3

"4) You sell the hardware, not the software."

Hello, Tivo!

Re:Please explain Money

[QuoteMstr]

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?

Noobs and Access, oh my!

[louzerr]

I've ended up having to support and/or migrate several Access databases designed by end users ...

These guys who've set up MySQL are gurus by all comparisons ... ;)

Platforms for Falcon

[Antony T Curtis]

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.

Work Around

[Phil John]

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.

good deal

[Anonymous Coward]

good news indeed to see jim associated with this project as interbase
while weak on the reporting/crunch end speedwise it was hella fast with
basic lookup and insert speed 6-8 years ago on P2's. When you needed to pump some serious inserts on the cheap
interbase got the job done with concurency, uptime, and practically required zero maintenance
outside of just watching the file size limit before splitting it

Postgres

[twoblink]

I'm not sure why people clamor to MySQL, and all its problems; when postgres is faster, cheaper, (yes it's cheaper, look at all the database engine within database you have to muck with for mysql, TCO is not measured only in money) and Postgres won't have these "engine" problems that Mysql has.

Yes yes, I know you Mysql toilet paper crowd will stick to Mysql no matter how much it shits on you..

http://tweakers.net/reviews/657/6 -- proof's in the pudding..

Don't underestimate this one

[dybdahl]

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:Don't underestimate this one

[LizardKing]

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 systems I have worked on, rolled back transactions are very infrequent compared to committed ones.

Re:Don't underestimate this one

[Just Some Guy]

Thanks for the explanation! However, I'm not clear on a couple things:

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.

My first reaction is that PostgreSQL also does MVCC in memory - at least until the OS flushes its disk buffers. A write ain't a write 'til it's written. Is Falcon's memory management that much better than most of its host OSs'?

You can run larger systems on one server with this, than you would be able to run on a cluster with other database systems.

In practice, how much does this affect memory usage? Is its upper bound low enough that you can still manage giant tables on a reasonably affordable machine?

Re:Don't underestimate this one

[jpkunst]

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

That's a problem with any story concerning MySQL on /. The discussion is immediately hijacked by the same old redundant MySQL is a toy database, just use PostgreSQL comments which are subsequently modded up to +5, Insightful.

JP

Re:Don't underestimate this one

[DamnStupidElf]

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.

I would hope it opportunistically writes to disk and simply discards what is written in the case of a rollback, otherwise transactions would be delayed by writing everything to disk only after a commit. At least it should be a tunable option, especially if the majority of transactions are large and commit, with only a few small rollbacks.

Re:Don't underestimate this one

[dybdahl]

No. All database systems share this:

A transaction that has not been committed, will be rolled back in case of a system crash.
When the client does a commit, it can be sure that the data is physically written to the disk when the commit has been done. Committed data will survive a system crash.

As you see, from the client side, there's no difference between Falcon and other databases.

Re:Don't underestimate this one

[dybdahl]

I don't know too much about the inner details of postgresql, but a good database does not use write caching. Committing a transaction should always mean that the data is server crash safe, i.e. that it has been physically written to the harddisk.

Falcon will physically write data to the disk that is being comitted, while not writing any data from concurrent, uncommitted transactions to disk.

Re:Don't underestimate this one

[JimStarkey]

Falcon keeps a serial log that used post-commit to move data to disk and, if necessary, to recover after a server crash. At commit time, all updated records and index nodes are written to the serial log, the serial log is flushed to disk with a non-buffered write, and the transaction commits. The data might not hit the actual database file for another few minutes (or more if the update rate has outstripped the disk bandwidth), but rest assured, your bits have been lovingly written to stable oxide before we consider the transaction committed.

Re:Don't underestimate this one

[JimStarkey]

Yes, you can control the amount of memory Falcon uses. The record cache is typically set to scavenge at a half gig to about a quarter gig (a record cache is a much more cpu and memory efficient that a simple disk cache), the page cache at about 1K 4,096 pages, and a serial log cache of about 8 MB. Most applications don't have anything like a half gig of "hot" data. Netfrastructure typically runs two or three municipal web sites on $389 BestBuy Sunday specials (nothing like a good supply of free printers). Seriously, the Falcon model is a partial in-memory database using the disk for backfill. The whole thing doesn't have to fit to scream, just the part in heavy use.

Re:Don't underestimate this one

[Anonymous Coward]

What happens if the disk is lying and the bits aren't on the oxide? Will Falcon still recover up to that point or will it give up and die when it hits the first failed part?

Re:Don't underestimate this one

[JimStarkey]

Yup, that's a problem. If the disk (or the operating system) lied and the data doesn't exist anywhere but in fleeting memories, you are what's known in technical circles as screwed. There is nothing, absolutely nothing, any database system can to to recover data that was never written to a persistent medium. Sorry, but those are the laws of physics. Some disks, particularly cheap ones, do lie. If you really care about your bits, I suggest that you take a hard look at your hard disk specifications. Or, maybe, we could develop a program that could detect lying disks.

MOD PARENT UP!

[Anonymous Coward]

This is a really good point... I just used up my mod points :(

at least 3 transactional alternatives to InnoDB

[toby]

a transactional storage engine that is not controlled by a hostile company

Which brings the total to at least 3 such so far: SolidDB, PBXT in addition to the well-known Falcon project.

If Oracle intended to weaken MySQL's market position by the acquisitions of Sleepycat and Innobase, they seriously miscalculated.

Re:Please explain = Falcon = InterBase

[Anonymous Coward]

About all that features, it means that was implemented based in InterBase and Firebird SQL internal Engine, two robust real Relational databases that as far as i can see are billion times better than any MySQL engine.

Wrong

[FallLine]

If you are writing code and you are using a library you did not write then you are not violating the GPL.

Sigh. You are as wrong as you are ignorant. MySQL converted their client-library licenses to GPLv2 (from LGPL) several years ago. Both the GNU zealots and MySQL believe that any applications which use libraries licensed under the GPL are derivative and thus must be wholly licensed under the GPL. This is not an academic point either as this dual-licensing model is where MySQL has derived most of their revenues from (which, depends, on the GPL's force over client libraries).

From the GPL v2:

"If your program is a subroutine library, you may consider it more useful to permit linking proprietary applications with the library. If this is what you want to do, use the GNU Lesser General Public License instead of this License"

From MySQL:

Previously, the MySQL client libraries were licensed under the LGPL (the Lesser General Public License) and now they use the GPL (the General Public License). What prompted this change?
MySQL's goal is to provide all its software under a free software/open source license. The change from the LGPL to the GPL for the client libraries was made in 2001 during the development of MySQL 4.0 to help MySQL AB more easily differentiate between a proprietary user who should buy a commercial license and a free software user who should use the GPL license. Previously there were people that were misusing the GPL by distributing the MySQL server tightly coupled with their applications and claiming that the GPL doesn't affect them because the client libraries were free to use.

This change has allowed MySQL to support its dual licensing model by better identifying when someone is using MySQL software in a closed source fashion without commitment to the open source philosophy. While MySQL supports the open source ideals, we also believe in the notion of "Quid Pro Quo" or fair exchange. For developers building open source applications using MySQL, the change in the client licensing policy has no effect.

Based on feedback from our users, MySQL has introduced an exception that makes it possible to combine the MySQL client libraries with software that uses various Free and Open Source software ("FOSS") licenses. This is known as the FOSS License Exception.

Also from MySQL:

"If you include one or more of the MySQL drivers in your non-GPL application (so that your application can run with MySQL), you need a commercial license for the driver(s) in question. The MySQL drivers currently include an ODBC driver, a JDBC driver and the C language library."

"If you develop and distribute a commercial application and as part of utilizing your application, the end-user must download a copy of MySQL; for each derivative work, you (or, in some cases, your end-user) need a commercial license for the MySQL server and/or MySQL client libraries."

SO if you are using JDBC, ruby, python, php, ocaml, haskell, rebol, or brainfuck to write your program that access your mysql server you are safe.

Wrong. The GPL affords no such protections. PHP and other open source projects were at risk when they made the change. MySQL had to create a special special exception for these FLOSS programs. A MySQL client-library derivative work (e.g., PHP) is OK as long as the source code is released (under one of the enumerated FLOSS licenses or GPL). MySQL, however, still claims that if you write an application on top of one of these other FLOSS programs, e.g., PHP, and is considered derivative, i.e., connects to MySQL with their libraries, then you must release your source code with your distribution. In other

Re:Wrong

[killjoe]

"Sigh. You are as wrong as you are ignorant. MySQL converted their client-library licenses to GPLv2 (from LGPL) several years ago."

That's right. But here let me repeat myself.

If you are not distributing code you are not violating the GPL.

Let's stop there and save me some time responding to each one of your incredibly stupid, uninformed and ignorant post.

If you are a user of mysql and you are not distributing a binary or source code to others then it's impossible for your to be violating the GPL.

Re:Wrong

[FallLine]

That's right. But here let me repeat myself.

If you are not distributing code you are not violating the GPL.

Yes, but what I said directly contradicts your previous argument, i.e., "Yes but linking in the code sense not in the "I am using this library" sense. Clearly you were trying to make a false distinction.

If you want to try to claim that your entire contention was merely that the GPL applies when something is distributed, despite getting no argument froom anyone on this matter, then you should know that this contradicts your other statements, e.g., "If you are not writing drivers you are fine" and "If you are writing code and you are using a library you did not write then you are not violating the GPL"

Please: grow up; learn to read with comprehrension; and loose the penis envy. It is unattractive. No wonder you still haven't been with a woman.

Incidentally, it is clear that your understanding of the GPL and programming in general is very poor. So for you to call real programmers, who actually do real work with the GPL, names only makes yourself look like even more of an ass.

Re:Wrong

[killjoe]

""If you are not writing drivers you are fine" and "If you are writing code and you are using a library you did not write then you are not violating the GPL"

Both of those statements are true.

Mysql has certain code under a GPL license. That includes drivers they have written (including the odbc and jdbc drivers they have written). If you link to, derive from, or modify their source code AND (AND AND AND AND AND AND AND AND AND) distribute the binaries or source code then you must also GPL your code.

That's it. Those are the only conditions. Merely USING the code does not force you to GPL your code. Never. Ever. No matter what anybody says.

Re:Wrong

[FallLine]

Both of those statements are true.

Mysql has certain code under a GPL license. That includes drivers they have written (including the odbc and jdbc drivers they have written). If you link to, derive from, or modify their source code AND (AND AND AND AND AND AND AND AND AND) distribute the binaries or source code then you must also GPL your code.

That's it. Those are the only conditions. Merely USING the code does not force you to GPL your code. Never. Ever. No matter what anybody says.

Sigh. You just can't admit you were grossly ill-informed, can you? I don't know who you are trying to fool. Anyone that reads this thread can see it.

Incidentally, when you write a statement like "If you are not writing drivers you are fine" repeatedly this implies that not writing drivers is a sufficient condition to be free and clear of the GPL's requirements (regardless of whether or not you distribute). LOGIC!

Re:Wrong

[killjoe]

You can USE the mysql drivers as long as you don't modify them, link to them or derive from them.

Period, end of sentence. Anybody who says otherwise is lying (hey that's you!!!)

Re:Wrong

[FallLine]

You can USE the mysql drivers as long as you don't modify them, link to them or derive from them.

So in other words, what you're saying is that developers can look at the mysql drivers and as long as they don't actually use them in any way they're OK. ROFL. You're simultaneously contradicting yourself and digging yourself in deeper and deeper.

Is it really that hard to admit: "I was wrong"?

Re:Wrong

[killjoe]

"So in other words, what you're saying is that developers can look at the mysql drivers and as long as they don't actually use them in any way they're OK. "

Did you even read the post you are replying to? Go read it again. I said you can USE them without any problems. If you derive from them, if you link to them, if you modify them AND you distribute your code then you have to gpl your code.

In order to do any of the above you will need access to the source code of those drivers so if are able to accomplish your task with the binaries you get from mysql you are safe.