Cal Henderson (Flickr) - Scalable Web Architectures: Common Patterns and Approaches - notes by Kris Jordan
The whole article gets into a lot of details about how they scaled Flickr and well worth the read if you are into scaling web apps. Specifically I really like the db notes:
Database is the hardest part to scale. If we can, best to avoid the issue altogether and just buy bigger hardware. Dual-quad opteron with 16GB RAM can get you a long way.
What do you do when the hardware can’t keep up? We do a lot more reading than writing. Somewhere between 80/20 or 90/10 ratio of read/write. “Only a few people who like to caption pictures of kittens on the internet - but a whole lot of people who like to look at them.”
Replication is the solution for the read write problem. Master-slave replication. Add more slaves, 1 master 3 slaves = 4 times the read power. Flickr is 6 reads for every write. Problem is what happens when we need to push more writes? As we need more writes this doesn’t scale well - have to add a lot more boxes.
MySQL query cache has bad performance in most cases. MySQL query cache: for any read query stores a pointer to the result if you perform the exact same query. Any write flushes it, though. So if you do 10 reads for every 1 write you’re unlikely to get a hit before cache is invalidated. If you only write once an hour or day then this is a really good thing. Otherwise turn it off.