The Shine of Open Source

MySQL puts the M and the power in LAMP.

By Tom Haunert

July/August 2013

MySQL is the world’s most popular open source database, and Oracle recently announced the release of MySQL 5.6. Oracle Magazine Editor in Chief Tom Haunert sat down with Tomas Ulin, vice president of MySQL engineering, to discuss the past, present, and future of MySQL.

Oracle Magazine: How is MySQL technology being used today?

Ulin: MySQL is the number one open source database, with 15 million installations worldwide and tens of thousands of new downloads every day. MySQL is widely used on the Web and powers 9 out of the 10 most highly trafficked Websites in the world. MySQL is the M in the LAMP stack—together with L for Linux; A for Apache; and P for PHP, Perl, or Python—which is the most popular Web development stack.

MySQL is also used by enterprise and governmental organizations for Web apps as well as custom enterprise apps, such as data marts. These are very complementary use cases to Oracle Database that are typical in ERP [enterprise resource planning] and other applications.

Oracle Magazine: Oracle recently announced the release of MySQL 5.6. As an open source database, what’s involved in the development and release process of MySQL in general, and MySQL 5.6 in particular?

Ulin: MySQL has a huge user base of both community users and customers, and we work closely with all constituents to ensure that MySQL continues to meet the needs of both existing applications and the requirements of emerging markets. We have a number of mechanisms for working with the community, including forums, mailing lists, the bug database, industry conferences, and code repositories such as Launchpad.

Because MySQL is an open source database, we regularly publish development milestone releases [DMRs] to preview new features, which cumulatively add up to formal GA [general availability] in the end.

These DMRs enable the community to get early access to code for evaluation and feedback. In the case of MySQL 5.6, we published five DMRs and one release candidate prior to GA in February of this year.

New features can be developed both by my engineering team at Oracle and by the community users, who submit code under an Oracle Contributor Agreement. Whether new features come from inside or outside Oracle, they all undergo the rigorous QA process we have in place to ensure the stability and reliability of MySQL.

Oracle Magazine: What are the key new and updated features in MySQL 5.6?

Ulin: There is a very long list of new features in MySQL 5.6. In terms of features and in terms of lines of codes added, I would say that MySQL 5.6 is the biggest new release in the history of MySQL. The key new and updated features in MySQL 5.6 include online DDL [data definition language] for InnoDB, improved scalability and performance, improved replication, improved optimizer, improved performance schema, and a new NoSQL access layer.

MySQL 5.6 online DDL allows you to flexibly evolve your schema without loss of service, as opposed to having your data read-only while altering a table. If you’d like to add, for example, an index or a column to one of your tables, you can now do this fully online if you’re using InnoDB, which is the most popular storage engine for MySQL.

Scalability and performance features in MySQL 5.6 allow you to scale on the latest generations of commodity hardware and achieve linear scalability on mixed, read-write, and read-only workloads up to 48 CPU threads.

Some optimizer improvements in MySQL 5.6 include subquery execution, index condition pushdown, file sort optimization, and batch key access, delivering a range of about 4 times to up to 300 times faster execution, depending on the data and the query.

Replication is one of the most popular features in MySQL, enabling users to scale out their databases across distributed farms of commodity servers, while also providing a foundation for high availability. We have introduced a lot of replication improvements in MySQL 5.6, and the most prominent are multithreading on the slave side and binary log group commit on the master side, which provide, overall, better performance and scalability of MySQL replication. Benchmarks demonstrate about five times improvement on both masters and slaves. MySQL 5.6 also includes global transaction identifiers, which is a significant addition to replication to enable self-healing replication setups.

Performance schema was one of the most popular additions to MySQL 5.5, and MySQL 5.6 builds and improves on it with many more instrumentation points and less overhead. It delivers developers and DBAs much more visibility into their databases to tune and optimize performance.

Last but not least, MySQL 5.6 introduces a new NoSQL direct access API, and it’s one of the features that has received the most attention. It allows you to directly access InnoDB data—it bypasses the SQL layer to achieve lower latency and scalability for key/value-based queries. It delivers up to nine times better performance in some cases.

With the addition of the NoSQL API, you can now use SQL when that’s your best option, and you can use NoSQL when that’s your best option. You’re accessing the same data and you’re maintaining the ACID [atomicity, consistency, isolation, durability] properties that you’re used to in a database. MySQL 5.6 combines the best of the SQL and NoSQL worlds.

Oracle Magazine: MySQL is available in different editions. What are the different editions, and what are they designed for?

Ulin: MySQL Community Edition is free to use under the Open Source GPL License, version 2.0. The commercial editions are available under a simple subscription or a more traditional license-plus-support model, and they provide features that are designed to simplify development and reduce total cost of ownership. They include, for example, 24/7 Oracle Premier Support as well as a number of database management, security, and monitoring features.

There are three different commercial editions: MySQL Standard Edition, MySQL Enterprise Edition, and MySQL Cluster Carrier Grade Edition. Each edition provides access to more features. If you move, for example, from MySQL Standard Edition to MySQL Enterprise Edition, you will add monitoring, backup, security, auditing, scalability, and high-availability features. And moving from MySQL Enterprise Edition to MySQL Cluster Carrier Grade Edition will add additional features related to real-time performance, scalability, and high availability.

Oracle Magazine: In recent years, there have been questions about Oracle’s commitment to MySQL. How does MySQL 5.6 address these questions?

Ulin: Oracle acquired MySQL more than three years ago; since then, both MySQL 5.5 and 5.6 have been released with higher quality than ever before and with more new features than ever before. The MySQL engineering team is much bigger than ever before. Substantial engineering resources have been invested into making sure that MySQL now integrates with almost every product in the Oracle product family. All of this testifies to Oracle’s commitment to MySQL. Ubiquity, availability, and ease of use are what make MySQL the world’s most popular open source database, and we intend to keep it that way.

Next Steps

 LEARN more about MySQL

 LISTEN to the interview

Photography by Dmitry Ratushny,Unsplash