MySQL 5.7 – more of a database than you might have guessed

One of the cool stories of Oracle OpenWorld and JavaOne 2015 is the story of MySQL, in particular of release 5.7 of MySQL. For me, working primarily with Oracle Fusion Middleware, it is easy to overlook MySQL. However, MySQL is a very serious database platform that powers not only some of largest web sites and some of the fastest growing enterprises (the unicorns) but also some of the largest PaaS platforms for SQL and relational database (Amazon and Google). Knowing the functional richness as well as the scalability potential of MySQL, may force you to consider using MySQL in your architecture. Note that Oracle has suggested that it may offer a MySQL Cloud Service from its Public Cloud. Additionally, through the Docker and Application Container Cloud Services, it is already possible to run your own containerized MySQLdatabase in the Oracle Public Cloud.

MySQL started life far away from Oracle – 20 years ago. MySQL was acquired by Sun Microsystems and shortly afterwards – somewhat ironically – moved to Oracle, early 2010. The last five years at Oracle have been quite beneficial to the evolution of the product: investments were higher (twice the engineering head count) and the rate of innovation accelerated.

Release 5.7 of MySQL was published in October 2015, shortly before the OOW 2015 conference. The previous release was 5.6, published in the Spring of 2013. Connoisseurs indicate that the range of improvements in this release would have warranted a jump to 6.0 or even higher: release 5.7 is a major upgrade!

Status of MySQL

MySQL is available in the community (free, open source) and an enterprise edition from Oracle. MySQL is used by many of the largest enterprises on the planet and by some for the largest challenges in terms of scalability. Some of these have created custom implementations by extending the base product: for example Facebook, Google, LinkedIn and Alibaba. For example: YouTube built Vitess to help scale MySQL itself as a cloud-native app within Google’s worldwide container cluster. With the new open source Kubernetes project Vitess can be made to work the same way on public clouds as it does for YouTube.

Additionally, some forks have been created – deviations from the original base versions that now evolve on their own: Amazon Aurora, Percona and MariaDB.

The situation with MySQL and MariaDB is somewhat similar to that between Jenkins and Hudson. Until 2013 the code base was the same. Then MariaDB changed its implementation. A lot of FUD (fear, uncertainty and doubt) has been spread around MySQL from the MariaDB side. Oracle ACE Director and MySQL expert Giuseppe Maxia gets a little aggrevated over this: “The engineering team for MySQL is far larger than the one for MariaDB. Features in MySQL are more robust and more thoroughly tested, even though MariaDB may claim to have more features.” Perhaps this solid 5.7 release will help score points in the popularity contest. When it comes to enterprise use, there is no real contest between MySQL and the other contenders.

The DB-Engines Ranking index (http://db-engines.com/en/ranking) shows MySQL at a comfortable second spot, after the clear leader: Oracle Database.

In this list, MariaDB ranks 23^rd.

The importance of MySQL within the Oracle eco system keeps growing too. MySQL is at the heart of the recent Oracle OpenStack V2 release. A Docker image is available from DockerHub. Oracle Enterprise Manager has a plugin for MySQL Database to monitor database connections, index usage, replication status, compliance scores, and other key configuration and performance metrics. The next figure shows a screenshot of this EM 12c plugin:

The list of companies using MySQL is spectacular. Most startups use it for example – no capital to start with, so you pick a database platform that has a low TCO and can grow as you grow. The list includes the startups that have skyrocketed after starting up (the unicorns). Some names of well-known MySQL users: Pinterest, Twitter (processing 500M tweets/day), Uber, WhatsApp, Dropbox, Spotify, Yelp, AirBnB, Booking.com, Ticketmaster, Zappos, SaaS providers such as Workday, RightNow (Oracle Service Cloud), GitHub, New Relic. MySQL also powers PaaS cloud services such as Amazon RDS, Google Cloud SQL, Go Daddy and Joyent.

Release 5.7

The brief summary: this release delivers greater performance, scalability and manageability, plus enhanced NoSQL capabilities with JSON support and MySQL Router, which makes it easy to connect applications to multiple MySQL databases. The performance improvements over release 5.6, especially under high load, are 300% for queries and about 50% for DML, with release 5.7 scaling very well – almost linearly – up to 72 cores and beyond.

The core of MySQL – the InnoDB engine – is improved with new capabilities for increased concurrency, enhanced on-line operations, native full text search, spatial indexes and native partitioning as well as faster connect/disconnect. Also: improved cache preloading on startup (configurable percentage to load), to load the hottest data at startup

MySQL replication is the foundation for both scalability and high availability for many of the world’s busiest services, particularly those that operate at scale on the web. Important enhancements to MySQL’s replication features in 5.7 include multi-source replication (to consolidate updates from multiple Masters into one Slave), enhanced Global Transaction Identifiers (GTIDs), and improved multi-threaded slaves for better scalability and availability. Semi-synchronous replication is provided, where a write is guaranteed to be received by a slave before being observed by clients of the master. Other enhancements: online configuration changes, options for tuning the replication stream performance, support for advanced topologies, support for group replication.

A new MySQL SYS Schema is introduced that provides helper objects that answer common performance, health, usage and monitoring questions (sure sounds familiar from an Oracle Database background). A new cost based optimizer (again – sounds familiar, does it not?) has been added.

In the area of query parsing and execution plan construction, MySQL has the concept of pre and post parse query rewrite APIs that allow users to create their own plugins to intercept troublesome queries and influence the query and its execution plan, for example to deal with crappy SQL from third party applications (sounds like the SQL Translation Framework in Oracle Database 12c).

Native JSON support has been added; this is comprised of:

· a native JSON data type, an internal binary format for efficient processing & storage.

· built-in JSON functions to store, search, update, and manipulate Documents

· JSON Comparator for easy integration of Document data within SQL queries

· indexing of documents

Virtual columns with generated values have been introduced and indexes can be created on those columns. The virtual column data can be generated using functions, so to some degree, the “virtual index” can be viewed as a form of functional index. Since the virtual columns are “nonmaterialized,” they can be added/dropped without rebuilding the table, thus making the table schema change an instant operation with little disruption. These columns can be created for sections of JSON documents too, making possible fast searches based on JSON contents. MySQL 5.7 also has more GIS support: native InnoDB spatial indexes allow for fast searches in geographic data.

Also new is MySQL Router, which simplifies application development by intelligently routing queries to MySQL databases for increased performance and uptime. MySQL Router also provides cross-language support for MySQL Fabric, making it simpler to manage groups of MySQL databases and delivering both high availability and scalability through automated data sharding.