MySQL, Innodb, and Oracle — Looking through the FUD
There has been a lot of buzz going around lately about Oracle’s recent aquizition of Innobase Oy the company that produces the innodb storage engine for MySQL. Most of this buzz is centered around MySQL being doomed or at least being set back to the years before it was considered enterprise quality. None of this is true.
First a bit of background. MySQL is unique (as far as I know) in the relational database industry with the concept of storage engines. This gives users the choice of many different features for storage making MySQL the most flexible database in the world (opinion of course, rebut if you dare 
) innodb is only one of several storage engines available to users. Contrary to popular belief it is not the only storage engine that supports transactions. MySQL also supports the bdb storage engine created by Sleepycat software. Innodb is recognized as the better of the two but the choice is still there.
Now back the point. The big question is, “What will MySQL do now?” This is the wrong question. The ball isn’t in MySQL AB’s court anymore. It’s securely in the hands or Oracle. They have a few choices. Since innodb is open source they could have been developing a product to compete against MySQL for some time. They could allow direct importing of innodb data which would be an easier migration path to full blown Oracle. While this is a good point it’s not complete. When migrating databases the hardest part is migrating the application code, not the data. This means that Oracle won’t steal any existing MySQL customers by putting out a competing product, but they might gain some new ones. Since these customers are new anyway Oracle doesn’t gain much benefit in buying innodb vs developing their own lighter weight storage technology. Except of course saving the cost of doing the actual development.
The two big questions now are, “What is Oracle going to do with innodb hot backup?” and, “What is going to happen with the current contact for commercial licenses with MySQL AB?” This is where Oracle loses some of their traction. MySQL can do a few things. They can try to renagotiate the contract with Oracle as soon as possible or they can work under the assumption that Innodb will no long be available after that contract expires. Choice B is the safest. The major sticking point with Oracle buying Innobase is not the storage engine. It’s innodb hot backup. Right now Oracle could choose to make innodb backup licenses very expensive. This again would be a small blow. They can’t make innodb hot backup licenses more expensive that an average MySQL slave server. People would just buy more hardware and run a mysql slave which they could shutdown to do a backup.
They could also have bought innodb as a direct blow against MySQL. This again is a good theory but it doesn’t hold much weight. This could actually end up helping MySQL more than hurting them. Had this buyout occured a few years ago MySQL might be in serious trouble. It didn’t. It happened now. MySQL AB has ~80 developers. Innobase has essentially one good developer. Losing 1/80th of an engineering team usually isn’t enough to collapse a company or a product. The same is true for MySQL. MySQL can rebuild part of the functionality of innodb into the storage engine level gifting that to all the underlying storage engines. I’m talking about hot backup for all storage engines, posting advanced reads and other things that are currently only part of innodb. Hot backup on the storage engine layer would make innodb hot backup worthless. MyISAM is already blazing fast. Imagine how much faster it will be with advanced reads 
October 20th, 2005 at 8:11 am
Hi Eric,
I agree that the aquisition of InnoBase by Oracle can’t mean The End of MySQL. For example, word has been round for some time now that foreign keys would be implemented for the other storage engines. To me, declarative referential integrity with foreign keys is about half of the reason to choose the InnoDB storage engine, the other half, of course, being transactions.
On the other hand, what puzzles me about your thoughts here is the actual reason why Oracle would buy InnoBase. You say it’s because of hotbackup, but then you immediately reassure us that cannot really be the reason because people will compensate by buying new hardware.
Roland
October 20th, 2005 at 10:46 am
It’s 1 part innodb hot backup, 1 part hurting mysql’s commercial license, 1 part getting a head start on their own product. I was trying to point out the different issues and show how on their own they didn’t carry much weight.
October 21st, 2005 at 8:02 am
I think this whole mess is a fault of the Mysql development model. I am no great admirer of mysql, but have always had the feeling that it is neither fish nor fowl. It claims to be an open source project, but its development model seems more closed source. And relying on third parties to provide mission critical components is just asking for trouble. I had asked a friend how a car manufacturer could outsource his engines - he replied: mysql is more like a coach builder that outsources the engines and chassis and builds the body. You wont find oracle buying out the producer of postgresql’s underlying engine.
October 21st, 2005 at 1:21 pm
That could be because postgresql does not have the concept of storage engines, toll.
October 21st, 2005 at 1:41 pm
MySQL is an open source project. They accept patches from the public all the time. When someone gets intimate enough with their code they tend to hire that person which makes it look closed source. Innodb while imporant is hardly mission critical. As I stated above some of it’s important features are being written into the storage engine level where they should be. PostgreSQL doesn’t have the concept of storage engines or even enough commercial presesnse to even get Oracle to consider looking at it.
Oracle isn’t afraid of the technology in mysql. They’re afraid of the enterprise support and service that mysql ab is offering along with a great piece of software. If postgre had that kind of enterprise support they could also be a threat. They don’t have it so they aren’t.
October 21st, 2005 at 1:48 pm
you sound as if you are already on oracles’s payroll. The only reason why mysql is ahead of postgres is that at the time of the explosion of the web in the last years of the last century, postgres was broken. Heck, even i used mysql. But no more - pg rocks!
October 21st, 2005 at 1:56 pm
Hardly. mysql is ahead because it’s a better piece of software for what it is being used for. Small fast web applications. One of the goals for mysql is to have it up and running in less than 15 minutes. Mysql succeeded because it is easy to setup and easy to use.
pg is a great alternative to mysql for those people that need all of the features that it supports. The truth is most small web applications don’t need views, triggers, stored procedures or many of the other things that pg has supported for years.
October 21st, 2005 at 2:28 pm
i agree - small fast web apps, mysql wins hands down. Mission critical apps - pg. And if mysql drops its dependence on third parties, mysql and pg can hit oracle for a six
October 21st, 2005 at 10:55 pm
Mission critical that mysql can’t handle goes to oracle. Hands down. I wouldn’t trust my mission critical applications to a rdbms that has to have it’s tables “vacuumed” once per week.
October 22nd, 2005 at 2:26 am
Eric, I couldn’t tell if you were referring to mysql or oracle for the ‘vacuuming once per week’ remark.
Anyhow, to the original point - I was a bit suprised at Oracle acquiring Innobase. Oracle has a transactional and Ref Integrity support inside it already so why buy Innobase? An obvious conclusion is ‘to hurt mysql’ but I don’t think it is that clear cut.
Oracles press release said they are committed to opensource and Innobase and they have already developed a cluster file system for Linux already.
Interestingly they also say: “Oracle fully expects to negotiate an extension of that relationship.”
October 22nd, 2005 at 1:12 pm
I was referring to PG. You’re right though, it is a very confusing purchase. I’m curious to see where it will lead.
October 23rd, 2005 at 10:51 pm
actually pg recommends vacuuming daily of not more often
October 26th, 2005 at 8:53 am
Eric, every enterprise class database has maintenance procedures that need to be performed to keep it running properly. Considering PostgreSQL now has an autovacuum daemon, which is being integrated into the backend in the next version or two, and that vacuuming has run in a non-blocking mode for the last three or so versions, I just don’t see it being an actual issue. Unless your postgresql dba is utterly incompetant. In which case, you shouldn’t trust ANY of your mission critical data to ANY database.
Also be aware that you basically have to “vacuum” mysql’s innodb tables as well, by doing the alter table … engine=innodb every so often to reclaim space. And I’ve found it not reclaiming all the space, so that eventually, I have to dump and reload. It was like travelling back in time to use PostgreSQL 7.1 all over again.
Every database has gotfchas. Oracle’s table space handling makes it quite possible for an unwatched database to start throwing errors when a table space fills up it’s allocated space and no one extends it. MySQL happily inserts 2^31-1 into an int when you try to insert too large a number. It’s interesting that while you can set a default that turns off this behaviour, the user can revert the behaviour with a single command, and you’re right back where you started.
Now that, my friend, is what makes me not trust MySQL for any enterprise class application. The Database is the gate keeper. It is the last line of defense in keeping your data coherent, and if it can’t be counted on to do that, then no other features it has are worth looking at for “real” applications, i.e. those involving accounting, airline reservations, etc… (that said, sabre’s new system has MySQL at the heart of it… We’ll see how that works out. It’s a choice I certainly wouldn’t have made.)
Scott Marlowe
Oracle and PostgreSQL DBA.
October 26th, 2005 at 9:05 am
OK, now that I’ve gotten my little postgresql rant out of my system, I wanted to address one of Eric’s remarks up above, and it’s frequent restatement by folks in the open source community:
QUOTE:
MySQL is an open source project. They accept patches from the public all the time. When someone gets intimate enough with their code they tend to hire that person which makes it look closed source.
UNQUOTE
But the problem is, if you send them patches, you have to sign over your rights to them. Then, MySQL can sell your code closed source and make money on them. Further, they could, at some point in time, say that they are no longer distributing a GPL version, and you, the mysql community, are welcome to maintain the current GPL version, but from now on MySQL is only being maintained in a commercial version. And you, the patch author, can do NOTHING about it.
This points up a fundamental difference between MySQL and PostgreSQL and, to a lesser extent, firebird (the other other open source database.) The MySQL developer community is pretty much a wholly owned subsidiary of MySQL AB. There aren’t a lot of folks hacking on MySQL’s code base in their personal time for fun, and if they are, they sooner or later enter into a contract to work for MySQL, the company.
PostgreSQL (and firebird as well) are based on a true open source community. Community being the operative word here. There are several companies that support it (postgresql inc, redhat, fujitsu (tiny little company on the pacific rim)) SRA inc, and a dozen or so other companies. Not one of these companies “controls” the future of PostgreSQL, they simply contribute. The community is made up of several core developers, who all work for a different major company, to make sure no one company forces some brain dead idea into the code. Much like another open source project that’s done well without having ONE big company behind it, apache.
If you don’t think there’s professional consulting and support services available for PostgreSQL, you simply haven’t looked. It’s there, and quite readily findable. It’s just not from one big company. Which is an advantage to me. If I don’t feel RedHat is giving me enough support, I can hire SRA, fujitsu, postgresql inc, command prompt inc, etc… And each of these companies have active developers that work on PostgreSQL and other open source projects, so they have the expertise needed to offer these services.
Neither MySQL nor PostgreSQL are perfect, but at least PostgreSQL tries to do the right thing every time. MySQL’s “optional” ansi sql switches are not the way to do the right thing.
October 26th, 2005 at 9:35 am
Every database needs maintenance operations. PG is designed in a way
that exposes the maintenance operations to the control of the DBA a bit
more than most other DBMSes do: specifically, you get to decide when
some of the overhead work happens. We think this is a feature, because
you can schedule the overhead for low-activity periods (nights,
weekends, whatever). In other DBMSes the equivalent work happens as
part of foreground queries, no matter how time-critical they might be.
Now, there’s no doubt that for a database run by a non-expert person
who can’t even spell DBA, exposing this sort of knob isn’t very helpful.
So there’s work afoot to provide automatic maintenance tools (ie,
autovacuum). Over time I think autovacuum will get smart enough that
even experts will usually use it. But that point will only be reached
when autovacuum has some idea about doing more work during low-load
periods.
Unless MySQL invents some concept equivalent to VACUUM, they won’t have
any prayer at all of being able to shift maintenance overhead to
low-load times.
October 26th, 2005 at 9:38 am
the message above is a quote from a postgres guru - i put the reference, but it didnt apear
October 26th, 2005 at 1:27 pm
The frequency of vacuums in Postgres depends on the amount and type of activity on the database. Most active databases have vacuuming strategies based around usage, not just ‘vacuum it once a week’. Vacuuming is side effect of mvcc (multi version concurrancy control). For the unitiated, mvcc lets the DBMS maintain concurrancy and data intergrity without locking. An excellent article about the benefits can be found here :
http://www.linuxgazette.com/issue68/mitchell.html
Another direct effect of this is being able use things like bitmap indexs (coming in 8.1) without having to lock rows on update. Even Oracle can’t do this.
A couple of very obivous points to make regarding this comment:
“Mission critical that mysql can’t handle goes to oracle. Hands down. I wouldn’t trust my mission critical applications to a rdbms that has to have it’s tables “vacuumed” once per week. ”
1)You comment about vacuuming once a week quite obviously points toward the fact that you really don’t know much about Postgres.
2)Yes, Oracle certainly is a viable solution for mission critical data. But, it’s expensive, a pain in the ass to maintain (compared to Postgres). With both databases, doing anything significant requires having at least one DBA that has a clue about the database.
Postgres also has a long history of running mission critical systems. The root level domain registries for .org, .info, and quite a few cctlds happen to be running (quite well) on Postgres.
January 2nd, 2006 at 6:43 pm
MySQL doesn’t need a “VACUUM”, it has a “null alter table” as described previously to be able to do reorganisations, as well as OPTIMIZE TABLE. If you are using InnoDB and want that space returned to the file system after operations such as OPTIMIZE TABLE with InnoDB, then simply use innodb_file_per_table.
With regards to doing this automatically, and at low-load times, then I’d like to point to one of the recent commits in to the 5.1 tree:
http://lists.mysql.com/internals/32956
With a few snippets:
+ uc_update_queries[SQLCOM_CREATE_EVENT]=1;
+ uc_update_queries[SQLCOM_ALTER_EVENT]=1;
+ uc_update_queries[SQLCOM_DROP_EVENT]=1;
http://lists.mysql.com/internals/32957
“drop event if exists event1;
create event event1 on schedule every 15 minute starts now() ends date_add(now(), interval
5 hour) DO begin end;
alter event event1 rename to event2;
alter event event2 disable;”
May 17th, 2006 at 8:12 am
i am in china,a student majored in CS. i am intrested in innobase’s MVCC in mysql.
but i have a little information.who can help me.
thanks a lot!