Oracle MySQL Upgrade Challenges NoSQL Onslaught

Hoping to fend off fast-growing NoSQL competitors including MongoDB, Cassandra and Riak, Oracle on Tuesday released MySQL 5.6, a major upgrade with a bevy of features aimed at high-scale deployments.

The long list of upgrades, most of which have been previewed by the MySQL community, break into three categories: performance, availability and scalability. All three areas are important to all users, but key availability and scalability features are clearly aimed at would-be NoSQL database users. New online data definition language (DDL) operations available in 5.6, for example, let you change the database schema by renaming dimensions or adding indexes or columns. These sorts of changes previously had to be done offline, taking down applications with the database in a process that might have taken hours or even days.

"It's a really significant improvement because one of the claims to fame of NoSQL solutions is that they're schema-less, so you avoid all the downtime associated with changing schemas," said Tomas Ulin, VP of MySQL Engineering, in an interview with InformationWeek.

Another 5.6 upgrade aimed at would-be NoSQL adopters is direct access to data through the Memcached API. This NoSQL-style shortcut is up to nine times faster than accessing data through SQL parsing, yet you can still revert to the powerful, proven and widely known SQL language as needed.

[ Oracle knows NoSQL has its place. Read Oracle Upgrades NoSQL Database, Big Data Appliance. ]

"If you want to do something simple like a join in a NoSQL system, you have to write it yourself with complex code," Ulin said.

That's a bit of an overstatement, as many NoSQL products have added query basics, but there's no doubt that SQL can't be matched on depth or breadth after more than 30 years of development.

To improve scalability, the MySQL database storage engine has been rewritten to eliminate thread-handling and locking bottlenecks when scaling up on high-performance servers. As a result, 5.6 can be deployed on 48-core machines, up from 16- to 32-core machines previously.

For better scale-out performance on commodity x86 clusters, the MySQL upgrade improves performance with multithreaded slave nodes and it simplifies management with global transaction IDs that make it easier to manage failovers, according to Ulin. What's more, a new utility monitors master and slave nodes and automatically promotes new slaves and reconfigures the cluster so data is applied correctly and the cluster continues to run.

Highlights among the many other changes with MySQL 5.6 include subquery optimizations that streamline how result sets are selected, sorted and returned for faster query execution. New Index Condition Pushdown and Batch Key Access features change the way data is filtered and retrieved, speeding execution of certain queries by as much as 280 times, according to Oracle. Database optimizer diagnostics are said to provide better metrics, with new EXPLAIN operations for database inserts, updates and deletes. Finally, optimizer traces let administrators track optimizer decisions.

The Oracle MySQL team would like to think that all these upgrades will encourage current and prospective user to give up on NoSQL databases.

"MySQL can provide the best of both worlds, supporting both high requirements for database inserts using the Memcached API as well as more complex querying using SQL on a common data set," Ulin said. "You don't have to split your data and manage two databases."

But making schema changes "easier" does not necessarily make MySQL easy to manage in big data scenarios. Nor does making it "faster" and more scalable make it as fast and scalable as NoSQL alternatives in all scenarios.

The flexible, schema-less design and scalable processing power of NoSQL databases will undoubtedly keep them growing, particularly when data variability and scale are simply beyond the ken of most any relational database. In these circumstances, the MySQL 5.6 upgrades add a bucket in the face of a data Tsunami.

In the Eight Steps To Securing Small Databases report, we recommend database security strategies and tools for smaller enterprises and workgroups--as well as for smaller databases--that have a big stake in protecting their data but don't have big budgets or skilled, and dedicated, security staff. (Free registration required.)