1

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?

2
  • Is all your data InnoDB, MyISAM, or a Mixture of Both ??? Jun 21, 2013 at 20:48
  • Everything in InnoDB as I require transactions. Jun 21, 2013 at 21:34

1 Answer 1

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.