Oracle has recently announced the general availability of MySQL 8.2, which includes support for Read/Write Splitting. This long-awaited feature has been introduced in the latest innovation release and helps optimize database performance and scalability.
Read-write splitting enables applications to direct transparently all write traffic to read-write (primaries or sources) instances and all read traffic to read-only instances, the instances that are secondaries in an InnoDB Cluster or the primary or secondary instances in a Replica Cluster. Frederic Descamps, MySQL community manager, explains:
At scale, we distribute reads between replica(s), but this has to be managed somehow in the application: pointing writes somewhere and reads somewhere else. With MySQL 8.2, MySQL Router is now able to identify reads and writes and route them to Primary Instances in the case of an InnoDB Cluster, or to an asynchronous replication source for the writes and to secondary instances or replicas for the reads.
Using read-write splitting, each client session can communicate with one read_write and one read_only destination, and the router classifies each query as read or write and directs it to the appropriate backend. Peter Zaitsev, founder at Percona and open source advocate, comments:
Great MySQL 8.2 feature! What is not clear though is what, if any consistency level is guaranteed for reads in this case. Can you read stale data? If yes how stale?
Connecting to MySQL using the Read/Write Port (by default, 6450), connections will reach the replica (secondary) if they do a read, but they will reach the replication source (primary) if they start a transaction.
Source: MySQL documentation
The new feature has generally been appreciated by the community, but Sunny Bains, software architect at PingCAP, comments:
MySQL Group Replication provides timeline consistency, the router can't do better than that unless the router can track the consistency across the replica, seems doable but it will require either a round trip via polling or some type of event notification from the cluster to the router.
Descamps concludes:
This is a valuable feature for optimizing database performance and scalability without having to make any changes in the application. (...) This feature not only enhances the overall user experience but also simplifies database management and deployment.
Earlier this year, Oracle changed the versioning model for MySQL, introducing the innovation and LTS releases. MySQL 8.2.0 is the latest quarterly innovation release and includes bug fixes, security patches, and new features: the improvements include hash table optimization for set operations, enhancements for MySQL Enterprise Firewall, and a new WebAuthn authentication method to support devices such as smart cards, security keys, and biometric readers.
MySQL 8.2.0 is generally available and can be downloaded from the Oracle website.