New MySQL 5.7 Features for your Galera Cluster: Spatial Indexes, JSON Support and more

Introduction

Now that Galera Cluster 5.7 GA has been released, let’s take a look at some features in MySQL 5.7 that improve Galera performance or allow new workloads and applications to be Galera-enabled.

Spatial indexes come to InnoDB and Galera

Previously, only MyISAM tables could have indexes on spatial columns. Starting with 5.7, it is now possible to use spatial indexes with InnoDB tables as well. This way applications in the areas of GIS, geo-location, mapping, etc. can take advantage of Galera replication for their spatial data.

It is also possible to migrate existing spatial data stored in MyISAM or replicated using traditional asynchronous replication to InnoDB and Galera.

JSON support

If you have previously considered using another database for storing your JSON data, 5.7 now includes JSON support, including a JSON data type, document validation and data-manipulation functions. Combined with Galera replication, MySQL becomes a fully-featured, highly-available document database that is also able to handle relational data.

Faster DDL Operations

Certain DDL operations on InnoDB tables have been made faster in 5.7. For Galera Cluster, this means shorter interruptions when executing such statements under TOI. If executing under RSU, the node remains desynchronized from the rest of the cluster for a shorter period of time.

Here are some examples:

Adding an index

In 5.7, indexes are created using bulk loading technique called “Sorted Index Build”. Here is a simple comparison of running times for ADD INDEX:

Conclusion

In addition to the improvements mentioned above, 5.7 has enhancements in various areas not directly related to replication, such as better security and a refactored SQL optimizer.

If you are currently running 5.6, we encourage you try 5.7 in your testing environment to take advantage of the additional benefits that it provides.

If you are currently running MySQL 5.7 with traditional replication and use features from 5.7, we encourage you to try Galera Cluster 5.7, as this will allow you to take advantage of a stable, production-ready synchronous replication, while remaining on the 5.7 codebase.