How to Handle Eventual Consistency Issues in MySQL Read/Write Splitting

Question

I've been looking into solutions to scale MySQL. One that often comes up beyond adding a Memcached layer is read/write splitting -- all writes go to the master and all reads go to a set of load balanced slaves.

The one issue that obviously comes up with this approach is "eventual consistency." When I run a write on the master, replication to the read slaves takes a certain amount of time. Thus, if I make a request for a newly created row, it may not be there.

Does anyone know of specific strategies to handle this issue? I've read about a conceptual partial solution of the ability to "read-what-you-writes". But, does anyone have anyone have any ideas how to implement such a solution -- whether it be conceptually, or specifically in a Spring/Hibernate stack?

Answer 1

I've not done this, but here's an idea. You could have a memcache server on your write database that you connect to before each read query. When you do a write, add a key of some kind to your memcache, and when you replicate¹, remove the key.

When you do the memcache read and you're reading a single record, if the key of a record is found, you should read it from the master only. If you're selecting several records, then read them from a slave, and then query each found ID against the memcache keys. If any found in memcache, re-read only those records from the master database.

You may find that there are some (write-heavy) use cases where this strategy would negate the benefits of having a read/write split. But I would wager that in most cases, the extra checking of memcache and the occasional master re-reads will still make it worthwhile.

¹ If you are using standard replication and cannot track whether a particular record has replicated fully, just timestamp all your keys, and remove/expire them after a worst-case scenario delay. For example if your slaves lag behind your master by two minutes, ignore (and delete) any keys that are older than two minutes, since they are sure to be replicated.

That all said: don't forget there are lots of cases where lag is acceptable. For example if you have a website at which users update their profiles, if their changes do not fully propagate for five minutes, this is in most cases fine. The key is, imo, not to over-engineer something to get instant propagation if it is not necessary.