MySQL Document Store Developments

by Sarang Nagmote



Category - Databases
More Information & Updates Available at: http://insightanalytics.co.in

---

This blog post will discuss some recent developments with MySQL document store.

Starting MySQL 5.7.12, MySQL can be used as a real document store. This is great news! 

In this blog post, I am going to look into the history-making MySQL work better for "NoSQL" workloads and more of the details on what MySQL document store offers at this point.

First, the idea of using reliable and high-performance MySQL storage engines for storing or accessing non-relational data through SQL is not new. 

Previous Efforts 

MyCached (Memcache protocol support for MySQL) was published back in 2009. In 2010 we got HandlerSocket plugin, providing better performance and a more powerful interface. 2011 introduced both MySQL Cluster (NDB) support for MemcacheD protocol and MemcacheD access to InnoDB tables as part of MySQL 5.6.

Those efforts were good, but focused a rear-window view. They provided a basic (though high-performance) Key-Value interface, but many developers needed both the flexibility of unstructured data and the richness inherent in structured data (as seen in document store engines like MongoDB). 

When the MySQL team understood the needs, MySQL 5.7 (the next GA after 5.6) shipped with excellent features like JSON documents support, allowing you to mix structured and unstructured data in the same applications. This support includes indexes on the JSON field as well as an easy reference "inside" the document fields from applications. 

MariaDB 5.3 attempted to support JSON functionality with dynamic columns. More JSON functions were added in MariaDB 10.1, but both these implementations were not as well done or as integrated as in MySQL 5.7—they have a rushed feel to them. The plan is for MariaDB 10.2 to catch up with MySQL 5.7.  

JSON in SQL databases is still a work in progress, and there is no official standard yet. As of right now different DBMSs implement it differently, and we’ve yet to see how a standard MySQL implementation will look.

MySQL as a Document Store

Just as we thought we would have wait for MySQL 5.8 for future "NoSQL" improvements, the MySQL team surprised us by releasing MySQL 5.7.12 with a new "X Plugin." This plugin allows us to use MySQL as a document store and avoid using SQL when a different protocol would be a better fit.

Time will tell whether the stability and performance of this very new plugin are any good—but it’s definitely a step in the right direction! 

Unlike Microsoft DocumentDB, the MySQL team choose not to support the MongoDB protocol at this time. Their protocol, however, looks substantially inspired by MongoDB and other document store databases. There are benefits and drawbacks to this approach. On the plus side, going with your own syntax and protocol allows you to support a wealth of built-in MySQL functions or transactions that are not part of the MongoDB protocol. On the other hand, it also means you can’t just point your MongoDB application to MySQL and have it work.  

In reality, protocol level compatibility at this level usually ends up working only for relatively simple applications. Complex applications often end up relying on not-well-documented side effects or specific performance properties, requiring some application changes anyway. 

The great thing about MySQL document store is that it supports transactions from the session start. This is important for users who want to use document-based API, but don’t want to give up the safety of data consistency and ACID transactions.

The new MySQL 5.7 shell provides a convenient command line interface for working with document objects and supports scripting with SQL, JavaScript, and Python.

The overall upshot of this effort is that developers familiar with MySQL, who also need document store functionality, will be able to continue using MySQL instead of adding MongoDB (or some other document store database) to the mix in their environments. 

Make no mistake, though: this is an early effort in the MySQL ecosystem! MongoDB and other companies have had a head start of years! Their APIs are richer (in places), supported by more products and frameworks, better documented, and more understood by the community in general... and are typically more mature. 

The big question is when will the MySQL team be able to focus their efforts on making document-based APIs a "first-class citizen" in the MySQL ecosystem? As an example, they need to ensure stable drivers exist for a wide variety of languages (currently, the choice is pretty limited). 

It would also be great to see MySQL go further by taking on other areas that drive the adoption of NoSQL systems—such as the easy way they achieve high availability and scale. MySQL’s replication and manual sharding were great in the early 2000s, but is well behind modern ease-of-use and dynamic scalability requirements.

Want to learn more about this exciting new development in MySQL 5.7? Join us at Percona Live! Jan Kneschke, Alfredo Kojima, Mike Frank will provide an overview of MySQL document store as well as share internal implementation details.