MySQL Document Store Developments

MySQL Document StoreThis blog 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 generally 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 LiveJan Kneschke, Alfredo Kojima, Mike Frank will provide an overview of MySQL document store as well as share internal implementation details.

Share this post

Comments (9)

  • Ulf Wendel

    SQL/JSON is a de-facto standard. ANSI long done.

    April 15, 2016 at 1:41 pm
  • Mark Callaghan

    Does MariaDB really need to continue doing things differently for JSON? I don’t get how value is added from spending time and money reimplementing that feature.

    April 15, 2016 at 2:04 pm
  • Peter Zaitsev

    Hi Ulf,

    Per Peter Gulutzan MySQL has adopted different JSON in SQL implementation compared to SQL Server and Oracle. So are you saying MySQL tried to follow De-facto standard or not ?
    http://ocelot.ca/blog/blog/2015/11/26/standard-sqljson-and-mysql-5-7-json/

    April 15, 2016 at 2:09 pm
    • Ulf Wendel

      Hi Peter,

      At some point I learned about the SQL/JSON standard effort. SQL/JSON was the initial input for the JSON support in MySQL 5.7. It then got modified and extended in some ways. The moment we decided to use it as a starting point was the moment, I didn’t care too much any more and others made some hopefully useful extensions. Oracle and IBM should follow the SQL/JSON draft more closely.

      April 15, 2016 at 3:04 pm
  • Peter Zaitsev

    Mark,

    I do not know whenever MariaDB Foundation is going to do that, and even if it does I do not know if MariaDB Corporation is going to chose to Adopt it in MariaDB Enterprise (the only Certified and safe to use version, I hear) 🙂

    At Percona we chose to avoid maintaining our code when similar upstream implementation exist which we believe is a better choice for community and also allows us to focus on things which really matter. We adopted MySQL implementation for Safe replication on Slave, Buffer Pool Preload, Partitioned Adaptive Hash Index and many other features.

    April 15, 2016 at 2:13 pm
  • Todd Farmer

    Peter,

    Great post, thanks for that! You did leave out one tiny step taken towards JSON support that I think is important to highlight, and that is the excellent work by Sveta Smirnova (now with Percona, at the time part of the MySQL Support Team at Oracle) creating the JSON UDFs for MySQL 5.6. While the MySQL 5.7 Server implementation didn’t directly reuse her code, she blazed the trail for others to follow and provided interim support for JSON where none existed.

    April 15, 2016 at 4:28 pm
  • Peter Zaitsev

    Thank you Todd,

    Indeed… how could I forget! Thank you for reminder and thank you Sveta for your contribution of JSON @ MySQL

    April 15, 2016 at 4:32 pm
  • Ulf Wendel

    Peter,

    Regarding performance – don’t expect too much. Performance was no major objective for this first release. There was plenty to do to get it reasonable stable. If it turns out to be on par with the classic MySQL Client/Server Protocol, be gentle, judge it as a success.

    There is room for tuning on the client side and in the plugin. Clients and plugin together can – over time – start to expose the – currently – mostly theoretical advantages of the new protocol (pipelining, async, batch, crud commands = AST on the wire). The plugin may be able to map some crud commands to lower level APIs.

    What’s really hot here is the architecture. Something most users won’t recognize due to the NoSQL/document noise and because the average user will never go that deep. However, we now have a MySQL that can support /any protocol/ for /SQL/, HandlerSocket or InnoDB API. It will take some time to explore the best mix. Who knows, maybe, we find time when done with the document story. Document story goes first and it is not over…

    Ulf

    April 17, 2016 at 4:20 pm
  • ANUJ

    Great article !!

    December 30, 2018 at 2:18 am

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.