Performance/Latency when using mysql replication

Question

I am planning to use mysql replication, with 1 master and 1 slave. My application is written in PHP. I have added a layer which redirects all writes to master and all reads to slave server. I am afraid of the latency here. My application is a order management system. What will be the issue with such a architecture and how do i over come it.

Answer 1

Depending on how you use your master (and how loaded your infrastructure is) there will be replication lag, there is no way to avoid that. Even if it is a "only" 100ms, it might be enough to mess with you for one or more pageviews.

There is a lot of technique out there to try to avoid causing the lag in the first place, eg this article, but that still leaves you in a pickle if it happens and your code is not ready for it.

You'll have to implement some way of being able to temporarily select newly added data from the master if a fetch from the slave fails. So if the select failed to return data, but there is (for example) an indicator of new data in the session, you would try to query the master. If the select succeeds and the switch is there, remove it. This selects new data from the master until it is confirmed on the slave.

Obviously this only needs to happen for the very important records only (a user registration for example, otherwise they'd be logged out on the next pageview). A single order in a users' account is not important enough to warrant this. It is not a problem that it only appears in the overview after a couple of seconds.

One thing though: If you decide to store your users sessions in the database: double issue! If the replication is lagging, the added variables will not be there either when you reload it the next page.

Overall there's going to be some issues with newly added data, but as long as your code assumes it won't be there "for a while" you should be okay. IMO the advantages still outweigh the problems, especially for heavily visited sites.