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.

CREATE TABLE geometry_table (
    geometry_column GEOMETRY NOT NULL,
    SPATIAL INDEX(geometry_column)
) ENGINE=InnoDB;

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:

5.6:

MySQL [test]> alter table t1 add key i3(f3);
Query OK, 0 rows affected (1 min 14.65 sec)
Records: 0  Duplicates: 0  Warnings: 0

In 5.7:

MySQL [test]> alter table t1 add key i3(f3);
Query OK, 0 rows affected (37.85 sec)
Records: 0  Duplicates: 0  Warnings: 0

Extending VARCHAR columns

With 5.6, attempting to change the length of a VARCHAR column is not allowed in-place:

MySQL [test]> ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN f2 f2 VARCHAR(255);
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

and the same operation via table copy takes time:

MySQL [test]> ALTER TABLE t1 ALGORITHM=COPY, CHANGE COLUMN f2 f2 VARCHAR(255);
Query OK, 1000000 rows affected (1 min 37.55 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

With 5.7, this operation is possible if the starting and the final length are both within the range 0..255 or within 256..65535 and it is instantaneous:

MySQL [test]> ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN f2 f2 VARCHAR(255);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Optimize Table

OPTIMIZE TABLE is also much faster in 5.7:

MySQL [test]> optimize no_write_to_binlog table t1;
+---------+----------+----------+-------------------------------------------------------------------+
| Table   | Op       | Msg_type | Msg_text                                                          |
+---------+----------+----------+-------------------------------------------------------------------+
| test.t1 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.t1 | optimize | status   | OK                                                                |
+---------+----------+----------+-------------------------------------------------------------------+
2 rows in set (5.49 sec)

vs.

MySQL [test]> optimize no_write_to_binlog table t1;
+---------+----------+----------+-------------------------------------------------------------------+
| Table   | Op       | Msg_type | Msg_text                                                          |
+---------+----------+----------+-------------------------------------------------------------------+
| test.t1 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.t1 | optimize | status   | OK                                                                |
+---------+----------+----------+-------------------------------------------------------------------+
2 rows in set (36.60 sec)

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.