MySQL 8.2 Read/Write Splitting: a what cost ?

We’ve seen in previous articles what Transparent Read/Write Splitting is with MySQL 8.2 and how to use it with MySQL Connector/Python.

I love this new feature, but I was wondering if it was really worth it.

Will the application benefit from offloading reads to another node, and won’t using the MySQL Router and parsing the request slow down connections?

These are the kinds of questions I’d like to cover and answer in this article.

The environment

To perform the test, I use the following environment:

• Linux Kernel 5.15.0 – aarch64
• MySQL Community Server 8.2.0
• MySQL Router 8.2.0
• sysbench 1.1.0 using LuaJIT 2.1.0-beta3
• VM.Standard.A1.Flex – Neoverse-N1 (50 BogoMIPS) 4 cores
• 24GB of RAM

MySQL InnoDB Cluster running on 3 machines and one machine for MySQL Router and Sysbench.

Sysbench is prepared with 8 tables of 100000 records each.

MySQL Connections are using SSL.

The MySQL InnoDB Cluster

This is the overview of the cluster in MySQL Shell:

JS > cluster.describe()
{
    "clusterName": "myCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "topology": [
            {
                "address": "mysql1:3306", 
                "label": "mysql1:3306", 
                "role": "HA"
            }, 
            {
                "address": "mysql2:3306", 
                "label": "mysql2:3306", 
                "role": "HA"
            }, 
            {
                "address": "mysql3:3306", 
                "label": "mysql3:3306", 
                "role": "HA"
            }
        ], 
        "topologyMode": "Single-Primary"
    }
}

JS > cluster.status()
{
    "clusterName": "myCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "mysql1:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "mysql1:3306": {
                "address": "mysql1:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.2.0"
            }, 
            "mysql2:3306": {
                "address": "mysql2:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.2.0"
            }, 
            "mysql3:3306": {
                "address": "mysql3:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.2.0"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "mysql1:3306"
}
JS > cluster.status()
{
    "clusterName": "myCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "mysql1:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "mysql1:3306": {
                "address": "mysql1:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.2.0"
            }, 
            "mysql2:3306": {
                "address": "mysql2:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.2.0"
            }, 
            "mysql3:3306": {
                "address": "mysql3:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.2.0"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "mysql1:3306"
}

And the MySQL Router that has been bootstrapped to the cluster, where we can see all the ports:

JS > cluster.listRouters()
{
    "clusterName": "myCluster", 
    "routers": {
        "router.sub09280951550.mysqlgermany.oraclevcn.com::system": {
            "hostname": "router.subXXXXXXX.mysqlgermany.oraclevcn.com", 
            "lastCheckIn": "2023-11-15 09:27:18", 
            "roPort": "6447", 
            "roXPort": "6449", 
            "rwPort": "6446", 
            "rwSplitPort": "6450", 
            "rwXPort": "6448", 
            "version": "8.2.0"
        }
    }
}

OLTP Read/Write

We use oltp_read_write.lua script for sysbench using directly the primary node (on port 3306), then we run it again using MySQL Router’s read/write dedicated port (6446) and finally the read/write split port (6450).

8 threads are used and 3 runs are made each time.

This is the command used, the host and the port are changing of course:

$ sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql \
   --mysql-user=sysbench --mysql-password=XxxxXX --mysql-ssl=REQUIRED \
   --mysql-host=<...> --mysql-port=<...> --tables=8 --table-size=100000 \
   --threads=8 run

8 threads isn’t a lot, and there isn’t much contention in this workload, but we can see that the difference is minimal. Of course, analyzing each transaction/query and sending them to different hosts has a cost.

OLTP Read Only

This time, we run again sysbench but we use the oltp_read_only.lua script. Again 8 threads and 3 runs:

We see a a bigger difference here, as there are no writes and a single machine can easily handle the full load. The cost of parsing the request and sending it to several servers is therefore higher.

Of course this is only because the server can handle the load without any problem.

If we have a read-only workload, then I’d suggest using MySQL Router’s Read-Only port: 6447.

OLTP Write Only

We also tested the write only workload using the oltp_write_only.lua script. Again 8 threads and 3 runs:

We can see that again the difference is minimal but it exists of course.

More Complex Workload

Using a more complex and less optimized workload, such as oltp_update_non_index.lua, we can see a significant difference…

Conclusion

MySQL 8.2’s Transparent Read/Write splitting is a very interesting feature that we’ve all been waiting for, and comes at a very small price in terms of performance – even beneficial for less linear workloads.

You really should test it out and send us your comments.

Enjoy MySQL, MySQL Router and all integrated solutions (InnoDB ReplicaSet, InnoDB Cluster, InnoDB ClusterSet, InnoDB Read Replicas).