It may be surprising when a new InnoDB Cluster is set up, and despite not being in production yet and completely idle, it manifests a significant amount of writes visible in growing binary logs. This effect became much more spectacular after MySQL version 8.4. In this write-up, I will explain why it happens and how to address the problem.

MySQL InnoDB Cluster is a modern open source, high-availability solution for MySQL. It consists of three main components: MySQL Shell (cluster setup, backups, management), MySQL Server with Group Replication (SQL, data), and MySQL Router (redirects application requests).

MySQL InnoDB Cluster

But why would such a cluster experience writes even when not being accessed by an application?

Unlike the other popular proxy used with MySQL, ProxySQL, MySQL Router keeps the cluster status and configuration information in the very same cluster it’s configured for, in a dedicated metadata database. Such cluster metadata is refreshed automatically, by default, every second via the metadata cache TTL mechanism. Now, every refresh action, regardless of whether the cluster state has changed or not, updates the mysql_innodb_cluster_metadata.routers table. When no state changes are to be saved, the router does a simple last check-in timestamp update via ClusterMetadata::update_router_last_check_in function:

Which we can see on the cluster primary node via general log entries:

The v2_routers is a view pointing to the routers table, which looks like this:

You can say it is not a big deal, as it’s only one small table column update. Well, with the ROW binary format (the default and required for Group Replication), one column update is actually heavier in terms of binary log than you’d expect, as the whole row gets written twice. Therefore, an example decoded row-based replication (RBR) event will look like below, and this behavior is one of the known disadvantages:

The overhead will still be fairly acceptable in most cases, though. It’s just about 900 bytes per transaction:

MySQL Router 8.4 metadata enhancements

Quoting the documentation:

As of MySQL Router 8.4, the full configuration of routers bootstrapped against an InnoDB Cluster is stored in the InnoDB Cluster Metadata Schema and can be read by the MySQL Shell operation, object.routerOptions, for Cluster, ClusterSet, and ReplicaSets.                                                              

The 8.4.0 Router’s release notes also mention this major change:

As of this release, MySQL Router exposes its configuration in the Cluster metadata for all routers bootstrapped against it. This information is stored as JSON in the Cluster metadata schema and can be accessed by the MySQL Shell operation, object.routerOptions for Cluster, ClusterSet, and ReplicaSets. (WL #15649)

This extended functionality brings the related binlog overhead to another level. The attributes column’s JSON document size changes as follows in example router 8.4 vs 8.0 entries:

And here is how a simple check-in update query results in the RBR event size in the new major version:

To illustrate the impact better, let’s visualize (using Percona Monitoring and Management (PMM)) how the binlog writes volume increases as we add 8.4 router instances to the environment. In the graph below, there were two 8.0 routers initially, and then three more 8.4 router instances were added:

binlog data

The impact may grow pretty dramatically in the case of the typical router deployment, where it’s located alongside the application on the same host, while the environment consists of dozens of application servers, like in the official recommendations:

https://dev.mysql.com/doc/mysql-router/8.4/en/mysql-router-general-using-deploying.html

It’s easy to imagine volumes of many megabytes of binlog writes per minute just related to the router’s metadata cache check-in updates!

This does not look reasonable, so let’s see how to avoid that unnecessary cost!

Binlog MINIMAL image

The easiest way to avoid storing the whole row image in the binlog RBR event is to switch from the default FULL setting to MINIMAL, via the binlog_row_image variable.

This way, only the changed column will be logged in the “after” row image, in addition to the primary key required in the “before” image! An example RBR event will now be like:

The event is now remarkably smaller! And here is how the write volume can be optimized after implementation of the binlog_row_image change:

This solution may not be suitable for all, though. The image format change is global, while some applications that track binary logs may require full row images.

Group replication notifications

Fortunately, another option was implemented: the Group Replication cluster can notify the routers asynchronously and immediately about any changes! This is thanks to worklog WL#10719, which used the MySQL X protocol to implement MySQL to Router communications.

To benefit from this optimization, it is enough to enable the use_gr_notifications option in the MySQL Router configuration.

This dramatically changes the behavior. Regarding the router’s metadata cache refreshing, the TTL setting now only plays an additional safeguard role (i.e., if the X protocol connection is down), while the actual updates are triggered via the GR notification only. No more frequent check-in timestamp updates occur, hence the overhead is down to an absolute minimum. 

The Router opens an X listener defined in the following code section:

With this functionality enabled, the router’s log will confirm this mode is being used by the presence of the X listener:

Then, when a real cluster event happens, for example, a GR node is stopped, the router immediately receives the notification about, i.e.:

Below is an illustration of how enabling this feature changes the binlog write volume as compared to the default settings:

We can keep the binlog_row_image=full if needed, and there will be no costly binlog overhead from the fact of running many router instances, except when actually something happens in the cluster, like in the example below:

Summary

Running InnoDB Cluster with default MySQL Router settings, especially with versions 8.4 or higher, may bring unexpected write penalties. While increasing the metadata cache TTL value could be one option to lower the impact, it would delay routing changes, and there are much better options. In my opinion, enabling the use_gr_notifications feature is the best solution to minimize the metadata cache handling overhead to the absolute minimum. For some reason, it is not enabled by default yet, even in the current innovation 9.4 version, but I could not find any drawbacks to using it. The only possible obstacle I see would be cases where the X protocol TCP port would not be allowed in certain network environments. Therefore, I suggested the default setting change: https://bugs.mysql.com/bug.php?id=118868.

 

Subscribe
Notify of
guest

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments