feat: Support Read/Write Splitting

[ndiaz-addepar]

Is is possible to configure the driver to route writes to the write-end-point and reads to the reader Endpoint of Aurora?
I did not see anything regarding this in the docs. Sorry if I missed something obvious.

[sergiyvamz]

Hello @ndiaz-addepar

Unfortunately the driver doesn't support such splitting. Could you provide more details about your case and why your application needs read/write splitting? Thank you!

[csyperski]

We current use the mysql replication driver (https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-source-replica-replication-connection.html) with AWS RDS mysql to allow for the driver to send the read-only queries to a replica. We do this via springboot's @transactional(readOnly=true) at the service tier. This allows us to off load query for things like reporting, dashboard graph data, and other data that doesn't have to be up to the millisecond off to one of the replicas. I believe under the hood is the connection is set to read-only via setReadOnly(), but the driver uses that hint to determine where to send the query. This has worked well for us, but we are about to renew our RIs on AWS and were considering using Aurora instead.

[ndiaz-addepar]

This is nice but still passes the work down to the developer (determining what should and what should not be read-only. Which becomes specially difficult on nested calls or constructs that try to read an ID of a just inserted row).

At least is something that can be used by some applications without too much extra work.

[csyperski]

I think this needs to fall on the dev as the dev needs to consider the use-case. For example on a crud operation, if you add an item, you want your read immediately after the insert/update to be read from the writer node to ensure when you reload your list of items, that it is included/updates. But when you execute a report, that can come from a replica with eventual consistency, if someone added something 1ms ago, and it isn't included in the report that isn't the end of the world.

In the case of nested calls or nested (mixed read-only and non read-only) transactions this is always done on the writer as the connection wouldn't be set to read-only, spring handles that pretty well.

Without this, I feel like the replicas in Aurora just become what multi-AZ is in RDS and you lose one of the benefits Aurora in my mind.

Personally, for me this ability is more important than the fast fail-over, for the simple fact that fail-over happens so rarely, where as I get many bursts in my app and having the ability to distribute the load over the read-replicas is much more important to me. For example, if my writer fails and I need to fail-over and that takes a minute, that isn't the end of the word, but it I can't use my read-replicas that would require me to spend a ton more on my master node to handle both the reads and writes.

Just my opinions, they many not be representative, but thanks for the consideration.

[dstaviss]

I second the need for the devs to take responsibility for setting the connection readonly/readwrite - we actually are using "jdbc:mysql:replication" driver on Aurora to split the connections between one Master and 2 read replicas - that gives us the significant boost in read performance and evenly loads the replicas so the resources are not wasted (and we don't see additional read IOPS resulting from that - they are expensive on Aurora). The main issue with this approach tho is that mysql driver is not aware of the topology so the config has have the master & replicas' endpoints explicitly and when a failover kicks in and a replica becomes a master it all gets screwy - so if AWS driver could do all of that automatically this would be a major plus, the MySQL code is there - just need to add a topology discovery...

[ndiaz-addepar]

@sergiyvamz We are simply trying to use Aurora with a legacy application. The app currently uses one connection to do both read and write. While we embark in a re-write to separate both it would be faster if we could just split them transparently. Many SQL Proxies do that but those would not have a failover functionality this driver has, adding such ability will allow us (and I'm sure many others) to kill two birds with one stone.

[thinguy90]

Hello, we have an existing legacy application that heavily utilizes the read-only routing. Is there an ETA for the AWS driver to support this feature?

[mokamoto12]

FYI: The aws-advanced-jdbc-wrapper appears to have a read-write splitting function called the Read-Write Splitting Plugin.

[karenc-bq]

We are working to certify the aws-advanced-jdbc-wrapper for Aurora MySQL. Our development work is focused on that wrapper driver. We continue to support the driver contained in this repo though.

However, we'd like to gauge interest if you'd be able to try out the aws-advanced-jdbc-wrapper. The aws-advanced-jdbc-wrapper officially supports Aurora PostgreSQL and it currently is experimental for Aurora MySQL. Since the aws-advanced-jdbc-wrapper can be used in conjunction with the Community MySQL JDBC Driver or the Community MariaDB JDBC Driver, compatibility is preserved.

The team is currently focusing on validation testing, but as mentioned by @mokamoto12, the aws-advanced-jdbc-wrapper has the read/write splitting experimental feature available. We expect to call this feature non-experimental once validation testing is completed and we'd interested in gathering any feedback that you may have.

[gazal-k]

Is aws-advanced-jdbc-wrapper ready for production use with Aurora (MySQL compatible)? If we don't use the experimental features yet.

[karenc-bq]

Hi @gazal-k, Aurora MySQL support is experimental and not recommended for production use. We'll also provide an update here when Aurora MySQL support is officially released.