Re: master-slave replication good solution?
Posted by:
Rick James
Date: March 15, 2009 04:51PM
* Replication, being asynchronous, does not guarantee getting to the slave before the INSERT returns to the client.
* Various tricks can be employed to see if the slave is caught up -- Seconds_Behind_Master, sequence number in heartbeat, etc. Normally, stuff gets replicated in less than a second, but there is no upper limit on how long it _could_ take. Example: ALTER TABLE on a multi-GB table will delay replication for hours.
* The Application can know that it is doing a "critical read" and deliberately go to the Master for such. You may need a 'cookie' to keep track of such. Probably most of your reads can still go to a Slave, so it is not too much traffic being shifted to the Master.
* The Master is, as you say, a single point of failure. Dual-Master is the best available improvement on that. Each Master is both Master and Slave to the other. However, write to only one of them. Dual Master brings the failover time down from hours to minutes.
* For real HA, you have to deal with the whole site going down.
* 1M uniques/day -- how many SQL queries does that equate to? Are any of them more complicated than a primary key lookup? Nasty queries could kill you.
* Will your data be bigger than you can cache in RAM? If so your app could fall off a cliff when it grows beyond that point. We can discuss that further in the context of some of your hairy queries.
* Sharding gives you many Masters, so it scales the writes, but...
* Sharding -- If you don't think about it before you design your schema, you will be in for a painful exercise when you have to do it. It is easy to shard on "user id", but then where is the info/pics/etc on my "friends"? They are scattered around on other machines. Searches and joins go from trivial to nearly impossible.
* A thought for putting off sharding -- if Photos will be the bulky part of the system, move them off to separate machine(s). All you really need in the user's info is a url of the photo (and caption, comments, etc). Html can take care of gathering photos from any number of machines.
* I don't know enough about Cluster to judge it. But, be sure to investigate how to scale beyond one cluster, how to do replication, what to do when the indexes are bigger than the collective RAM (that is a hard limit), etc.