MySQL 5.4 gets bigger anyway, encroaching on new parent Oracle's turf

Fast-forward eleven years: MyISAM isn't really all that sequential either, which is why it's so fast. But because it's still algorithmic search grafted onto the sequential model, it's not very well adapted to distributed processing. That's okay if you're dealing with a Web-based server where text searches, for example, are executed from one location -- MyISAM is very well suited for that. But for accounting purposes especially, a database architect needs a way for multiple tables to appear to be updated simultaneously, as well as for those updates to be rolled back simultaneously in case of an error.

A common example for a transactional database involves a system where there are multiple bank accounts. If money is transferred from one account to another, from a sequential standpoint -- the "S" in ISAM -- that's two queries: one withdrawal and one credit. But should the power go out on the server in-between those two queries, the withdrawal might disappear. (That's the secret reason why early database writers performed the credit first.) With a transactional database engine, although both queries are written as though they were sequential, the update appears to take place in parallel. In case of an error, the update doesn't appear to have taken place at all, which is better than the alternative.

InnoDB is a transactional engine; MySQL developers can create tables "in" InnoDB instead of "in" MyISAM. When they do, the texture of their programs changes somewhat to take advantage of the transactional model. And in some cases, searches actually slow down because InnoDB has a larger overhead. But the payoff comes in the form of fuller functionality and greater reliability -- and Larry Ellison knew three years ago that such a payoff would be necessary for MySQL's evolution. He said so explicitly, and that's why he purchased Innobase OY.

Now the MySQL community marches forward, with what was a plan to take on Oracle head-on by supporting a tool...owned by Oracle. Now it's a plan to make a bigger place for itself than just a slot in the Ellison stack.

The early numbers for MySQL are dramatic, with easily measurable performance improvements of about 56% in workload tests, and as much as triple the performance in distributed connectivity tests, thanks to that 16-way support. In a December 2008 test of a MySQL 5.4 beta by Sun's Robin Schumacher, the new engine's capacity for subquery optimization -- a smarter way to break down queries nested within queries -- led to speed improvements in subquery benchmarks of as much as 40,000%. That's not a typo; that comma is in the right place.

As MySQL senior software architect Mikael Ronstrom reports on his blog today, "We have consistently seen improvements in the order of 30-40% of sysbench top numbers and on large number of threads 5.4.0 drops much less in performance than 5.1. The new InnoDB Thread Concurrency patch makes the results on high number of threads even more impressive where the results have gone up by another 5-15% at the expense of 1% less on the top results (there are even some DBT2 runs that gave 200% improvement with the new algorithm)."

Version 5.4 would have been released today had Larry Ellison never entered the picture; in which case, our headline might have read, "MySQL gets bigger: Now what will Oracle do?" If making MySQL bigger makes it better, Ellison's 2005 move may be his insurance policy.