For the next release of its open source MySQL, Oracle is making a number of changes designed to vastly boost the speed of the open source relational database management system.
Such a sizeable performance bump could help organizations save money in server purchases, because it would require fewer servers to run large jobs. Or, it will allow them to run complex queries that might have taken too long to run on earlier versions of the database system, said Tomas Ulin, Oracle vice president of MySQL engineering.
On Monday, the company released the latest development version of the software, MySQL Development Milestone 5.7.4, along with a number of associated programs for managing the database. The last major version of MySQL, version 5.6, was released in February 2013.
Oracle typically issues milestone releases of upcoming versions of MySQL on a quarterly basis, offering them to users for testing purposes.
This new version of MySQL has demonstrated the ability to respond to 512,000 read-only queries per second (qps), more than twice 250,000 read-only QPS that MySQL 5.6 was capable of performing.
Performance has also been improved for users of the Memcached caching plug-in, which goes around MySQL’s default InnoDB database engine to access the database rows directly, for speedier performance. This approach can now offer a read-only throughput of over a million QPS.
There was not one single revision that Oracle made that improved the performance; rather it is the cumulative effect of many individual changes, Ulin said.
The performance improvements are especially timely given the changing nature of the servers that MySQL runs on, according to Ulin.
Historically, MySQL was designed to run on commercial servers with single processor cores. Customers today are buying servers with 16, 32 or even 64 processor cores. So much of the performance work around MySQL has been around better handling multiple threads operating over the same data structure.
“We have to evolve to where the mainstream is,” Ulin said. “People won’t be happy if they upgraded from a 16-core to 32-core machine and they get no benefit.”
Performance improvements were made around other parts of the DBMS as well. For instance, the software also reduces the amount of time it takes to establish a connection with the database, thanks to some work Facebook contributed.
Last week, Facebook, along with Google and a number of other large Internet services companies, announced they were working together to coordinate development of code that would better equip MySQL for large Web-scaled operations, in a project called WebScaleSQL.
Ulin praised this work, noting that the group would simplify work for the Oracle MySQL development team. This will allow the participants to “work out among themselves what they want the next improvement to be. We just have one party to work with rather than four parties,” Ulin said.
Beyond performance, Oracle is also enhancing MySQL in a number of other ways.
The company has expanded the software’s performance schema, which defines the metrics used to gauge the performance of the database. The database collects performance on itself through various internal probes. The schema can be used to extract and summarize that information through the database or with external tools, which can be handy for diagnosing performance issues.
The schema now offers a lot more information about what is happening within the server’s memory. It can be used to pinpoint problems around metadata locking and other elusive issues. A user, for instance, can use SQL to extract all the memory performance metrics around a specific database table.
MySQL Workbench 6.1, which Oracle also released on Monday, includes a set of new graphical diagnostic tools based on these new memory probes.
One change that users may not immediately notice is that Oracle has started rearchitecting the MySQL code, making it a lot more modular. The software’s parser, optimizer and replication capabilities are being rewritten in a modular format.
“MySQL is a codebase that stems back, in some parts, to the 1980s,” Ulin said, noting that MySQL grew in a piecemeal fashion as more and more features were added over time. “To be frank, the whole architecture wasn’t well thought out for the end-product,” Ulin said.
A more modular MySQL will help third-party developers extend MySQL a lot more easily, since each specific set of functionality will have a clean interface, distinct from other parts of the program.
The company is also cooking up a number of other new functions that aren’t yet mature enough for this milestone release, but might be part of version 5.7. One feature would be the first to have multi-master replication.
Until now, each backup server could only be assigned to replicate data from a single master server. Now a backup server can have multiple master servers. This would allow an office, for instance, to run a single backup database for multiple primary databases.
In addition to previewing MySQL 5.7, the company has also released a number of number of companion or adjunct MySQL tools.
The company has issued a release candidate of MySQL Fabric, which allows an organization to shard, or slice up, a very large database to it can run across multiple servers. MySQL Fabric is part of the MySQL Utilities 1.4.2 package. It has also issued a preview of the next version of MySQL Cluster, version 7.4.