Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a database that stores the users of my application. When a new user signs up, a record is inserted into the database for that user. I have a replicated version (slave) of this database (using mysql for now).

What I'm concerned about is this scenario:

step 1: user signs up and user record is inserted into the database

step 2: user then tries to login, and the login process queries the database for the user. however, this query hits the slave database, but the user record has not yet been replicated in the slave and it returns an error that the user does not exist.

This is a pretty trivial example, but I can see how it can apply to a lot of cases. Is there a strategy for configuring replicated databases to help prevent this situation?

share|improve this question
add comment

3 Answers

up vote 3 down vote accepted

This is a real problem and it happens almost as soon as you start trying to do writes to the master and reads from the slave. Although the time gap between user signup and user login is probably long enough to not have to worry about this, most of the things your users will want to do will cause this problem.

Actions like making a forum or blog post will often result in a redirect that takes them back to a page where they can view the post they just made. If this page reads from the slave, there will only a fraction of a second between the write and the read which will often not be enough to allow for the replication.

There are three solutions that I have used in the past and one that I haven't personally used.

  1. Monitor the replication delay on the slave, take it out of the pool if it lags behind the master. The most reliable way to manage this is using the heartbeat tool from the Percona toolkit. This probably won't solve the forum post problem.
  2. Stick to making queries from the master for certain categories of queries. For instance, the post-successful page and any pages within the CMS or admin centre would only query from the master. This places extra load on the master.
  3. Use caching. Something like Memcached or similar. Writes that are susceptible to this problem should be simultaneously written to memcached and reads should come from there first. Misses are read from the slave, meaning it's still worthwhile having one. You will have to modify every part of your application that reads or writes but if you have abstracted properly this shouldn't be difficult. You could also implement this using something like mysql-proxy but I can't actually recommend that option.
  4. Synchronous replication. I don't think MySQL can do this but they have something called semi-synchronous replication coming in the next major release. The idea is that the original write query will not return until the data has also been written to the slave(s). This has the downside of causing your write queries to take longer and your entire platform to go down when the slave does.

Option 3. has been the most successful in the past for me and is the option I would choose in the future.

share|improve this answer
    
#3 sounds reasonable. I guess under am extremely high number of writes the value may get evicted from the cache before it is written to the slave but that is unlikely in this case –  Jeff Storey Mar 21 '12 at 12:04
add comment

Before running the query, check if the database is slave, if so, check if slave threads are running and if so, check the Seconds_Behind_Master. All in SHOW SLAVE STATUS;. If it is not running or if there is a significant lag, go and run the query on master.

share|improve this answer
    
Wouldn't that quickly become a bottleneck under high loads? –  Jeff Storey Mar 21 '12 at 12:08
    
Really depends on what you mean with high load. But since its only one single-row query and it's coming of memory, it's not that bad. You could cache the result once per second or few times in an interval equal to acceptable delay. –  Fox Mar 21 '12 at 23:24
add comment

Not really (that I know of); you would have to have some significant lag (or have had a serious fault, causing the slave to be out of sync) for a user to log in and potentially hit the slave database faster than the slave database can replicate the server.

The only alternative I can think of is to have something at the application level which hits up the master if the slave can't find the record; but that rather defeats the purpose of being able to query the slave in the first place.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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