Day 52: Cloud pattern: database read-write splitting
When your database is handling a large amount of queries, the traditional pattern is to setup more database servers, increasing QPS by spreading load across many instances. We basically redirect writes on the master and reads on the slave(s).
In the best scenario, database instances have identical data, whatever your replication method (mostly master/slave). But this is not always true. Most master/slave replications are asynchronous and replication can break or slow down for many reasons.
R/W splitting strategies:
- Always read from the master for queries with need for strict consistency .
- Always read from the slave for queries without need for strict consistency.
- Before read operations, ask to the slave the replication lag. If to long, fallback on master (:warning: this doesn’t fix scaling issues if every requests go to master).
- Check the replication binlog position on master after write operation, then compare with the position on the slave before reading. This allow to compare if the change has been committed on the slave.
- Setup a proxy that filter queries and redirect read or write operations to the right server. (Such as ProxySQL for MySQL)
:warning: Using proxy method and magic ORM can be dangerous.
:warning: With the proxy method, please test your transaction model carrefuly: transaction queries must go to the same database server.
:warning: With the proxy method, if you need to read after a write operation, please put your read query in the same transaction, to avoid request splitting and a fucking race conditions.