Aurora Local Write Forwarding
Optimizing Aurora Cost using Aurora Local Write Forwarding, and the Risks of this feature
AWS recently released a rather very interesting feature that solves a really important problem that has been there from a long time i.e Read Write splitting, most solutions are implemented as a proxy layer on top of the mysql cluster for example ProxySQL, MariaDB MaxScale, Heimdall Proxy.
AWS Blog: https://aws.amazon.com/blogs/database/local-write-forwarding-with-amazon-aurora/
Application Level Read / Write Splitting
Currently the application code needs to decide whether to send the query/transaction to the writer or reader each time, in some cases the decision isn’t just based on the query itself but sometimes it might require context that needs to be passed down i.e providing some APIs with strong consistency and some others with eventual consistency for the same query. One easy alternative is to use a proxy like the ones mentioned above to do the read write splitting.
Routing Reads to Writer after any Write operation in http/grpc API
One low effort solution is to just implement at the library level to route all reads after a write operation to the writer node only. This is really a good solution for php, python(without multi-threading), ruby but for other languages like golang you need to religiously pass the request context for the library to properly determine whether any write operation happened before during the API request.
Moreover this will route all reads to the writer node unnecessarily because of a single write operation during the request.
Proxy Level Read / Write Splitting
But for a proxy solution, when it comes to read after write consistency a smart proxy can use the GTID of the write operation and implement wait time for the read operation so that it can send the read query to corresponding reader node that caught up the write operation performed earlier, this is considering session or connection level read after write consistency but not a global read after write consistency. like proxysql adaptive query routing feature
One other limitation is that most languages use connection pools, so it is quite possible that during the lifecycle of an http/grpc API request a write operation used one connection to proxy and a read operation used another connection to the proxy, so even though proxy can provide the feature it is still not easy to achieve session level read after write consistency, although this is still possible to solve at proxy layer by having the proxy initiate a get gtid from writer and wait for reader to catchup for each read operation, or make use of `WAIT_FOR_EXECUTED_GTID_SET` at a global level.
Cost Benefits of Local Write Forwarding
Given all these challenges to properly achieve read write splitting, its quite understandable to keep things simple and just use the writer node for all reads and write needs. and this system would definitely scale for a long time but unfortunately it also wastes money in maintaining an idle reader node, this is essentially because in case of any issue to the current writer node we need a reader to failover and this gets worse as the workload increases i.e if your writer node is `db.r6g.4xlarge` you need a keep a spare `db.r6g.4xlarge` that doesn’t get used at all.
But implementing read write splitting at application level is too much of an effort during the initial stages of development. Local Write Forwarding while not really an ideal solution(due to risks mentioned below) it surely is a decent middle ground to quickly achieve better resource utilisation.
What’s great is that now you can even control the consistency level per session or at global level using `aurora_replica_read_consistency`, so now you can start with global consistency level as the default in the app and start utilising readers from day 1 and eventually start overriding the consistency level on a case by case basis for better performance. This really changes the current default behaviour which solves consistency and simplicity but trades away cost, but with `aurora_replica_read_consistency` all 3 problems get solved considering a few resiliency disadvantages that very well fit in many companies cost & resiliency budget configuration.
Even with really ideal read/write splitting logic at application level, it is still possible that your reader and writer utilisation is highly imbalanced because for read operations that require strong consistency currently the app will route to writer, but with local write forwarding, the read query can wait for the replication to finish and run the query locally on that reader node. This feature importance really shows up in highly transactional workloads like payments, orders etc,… where strong consistency is needed in most read queries. even though those read queries can afford higher latency, in the current system you have to send these reads to writer node as the application can’t determine when the reader node catches up with the previous write operation. WAIT_FOR_EXECUTED_GTID_SET solves the same problem but that also requires you to enable binlog and trade away 10-15% of aurora performance. So local write forwarding is useful in improving reader utilisation even for mature applications that already implement proper read/write splitting logic.
Risks
But this is not without its own set of compromises and risks. added latency as given in the AWS blog is a major compromise for sure. but it has a number of reliability issues as well
Reader Failure
With this the reader failure would start impacting your writes as well. Its one thing to not be able to see your orders on a platform but its quite different to not be able to place new order, no one likes revenue loss. To be fair this is not an issue of local write forwarding feature but due to not using application level read/write splitting. While its a good starting point to pick, eventually it might be better to do the read write splitting at application level itself.
Complete reader failures are still fine as the clients will simply retry on another reader or the writer, but a network partition between the reader and writer type failure would lead to an really worse scenario, normally when all writes are sent to the writer node, and any issue happens you could simply initiate a failover but now you will need to find the faulty reader and remove it from the pool to avoid.
Deadlocks
While local write forwarding doesn’t cause the deadlocks, if the application already has query patterns that contain deadlocks, moving to this feature might surface them up early on due to the added latency between the write and read operations leading to some other transaction acquiring the lock and causing the deadlock.
Notes
Session Pinning Challenges in golang
even though `aurora_replica_read_consistency` is configurable at session level, it still has similar challenges in golang as mentioned in the previous section i.e during a http/grpc request lifecycle mysql client library could be using different connections at different points.
ACK ALL Write vs WAIT_FOR_EXECUTED_GTID_SET
Coming from kafka world, it was insightful to see why mysql didn’t go for ACK ALL writes but with WAIT_FOR_EXECUTED_GTID_SET, rather than failing a write operation you get better control of consistency at reader stage, so a win in terms of reliability and a win in terms of control over what consistency is needed.
Although this is only a no-disadvantage scenario when considering aurora architecture where write operations are durable stored over multiple AZs, but in normal RDS architecture, ACK ALL is much better when you consider durability. probably an NFS can solve such durability problem but does come with an eye watering aws bill.
Continuous Cloud Cost Optimization
After grappling with compounding cloud expenses and ongoing inefficiencies in cloud management for over five years, I recognized the urgent need for a platform dedicated to continuous cost optimization but not a one time optimization. If you're experiencing issues with AWS cloud costs, please reach out to me at sri@optiowl.cloud or signup at optiowl.cloud