Mitigating MySQL replication delay in single master/multiple slave setup

Question

For a particular web application that requires high availability I am thinking of scaling out by setting-up MySQL replication with one "write" master and multiple "read" slaves (as explained here http://dev.mysql.com/doc/refman/5.1/en/replication-solutions-scaleout.html).

The only issue that I have is how to mitigate the replication delay between the master and the slaves? For example, let's say that the user posts an item (writing to the master), and very quickly thereafter wants to view his posted items (reading from one of the slaves). What efficient solution can I put in place to make sure that the a read operation from one of the slaves will always have all the content of any previously completed write operation to the master?

Answer 1

Since "native" MySQL uses asynchronous replication, there is no system-wise way to guarantee nor force a slave to apply a master's statement at any given time.

Semi-sync replication shortens this lag time by at least ensuring your statement is recognized by the slave -- but does not go as far as making sure it has been applied on your slave.

This is a common issue. Ultimately, your application needs to be replication-aware, such that your query "knows" it is being executed on master or on slaves. In your case, the common solution is to make sure a SELECT query which immediately follows a write operation is executed on master.

You may choose to use synchronous solutions: MySQL NDB Cluster or Galera Replication. Even with Galera you would need a special configuration to make sure a consistent write-and-read is successful.