Evaluating Group Replication with Multiple Writers in MySQL

In this blog, I want to evaluate Group Replication Scaling capabilities to handle several writers, that is, when the read-write connection is established to multiple nodes, and in this case, two nodes. This setup is identical to my previous post, Evaluating Group Replication Scaling Capabilities in MySQL.

For this test, I deploy multi-node bare metal servers, where each node and client are dedicated to an individual server and connected between themselves by a 10Gb network.

I use the 3-nodes Group Replication setup.

Hardware specifications:

For the benchmark, I use sysbench-tpcc 1000W prepared database as:

The configs, scripts, and raw results are available on our GitHub.

The workload is “in-memory,” that is, data (about 100GB) should fit into innodb_buffer_pool (also 100GB).

For the MySQL version, I use MySQL 8.0.19.

I use the following command line:

This establishes an active connection to TWO separate nodes in Group Replication.

To make sure that transactions do not work with stale data, I use group_replication_consistency=BEFORE on all nodes. See more on this in my previous post, Making Sense of MySQL Group Replication Consistency Levels.

Results

Let’s review the results I’ve got. First, let’s take a look at how performance changes when we increase user threads from 1 to 256 for 3 nodes.

Group Replication with Multiple Writers in MySQL

Interesting to see how the throughput becomes unstable when we increase the number of threads. To see it in more detail, let’s draw the chart with the individual scales for each set of threads:

Group Replication with Multiple Writers in MySQL

As we can see, there are a lot of variations for threads starting with 2. Let’s check the 8 and 64 threads with 1-sec resolution.

There are multiple periods when throughput is 0.

These are the results with 1-sec interval:

There is basically an 11-second long stall when the cluster could not handle transactions.

Conclusion

Unless I missed some tuning parameters which would improve the performance, it is hard to recommend a multi-writer setup based on Group Replication. Though Multi-primary mode is disabled by default in MySQL Group Replication, you should not try to enable this.

Share this post

Leave a Reply