How to scale a LAMP architecture?

[littlebigman]

Hello

Suppose I have a single Apache server connected to a single MySQL server. Due to increasing load, I move to two Apache servers and two MySQL servers (one master, and one slave) and a load balancer in front of the two Apache servers.

If a user makes changes to the master MySQL server, there's a delay before the change is replicated on the slave MySQL server. During this delay, users who are directed to the slave server won't see the change (I think it's called "dirty read").

For those of you used to scalibility, how should I set things up so I can keep adding more Apache front-ends and MySQL back-ends without creating bottlenecks?

Thank you.

[chazzy]

have you looked at MySQL Cluster (http://www.mysql.com/products/database/cluster/) ?

[svidgen]

I have to recommend High Performance MySQL 2nd ed. (http://www.amazon.com/High-Performance-MySQL-Jeremy-Zawodny/dp/0596003064). That has a few chapters dedicated to web application scalability--what works, what doesn't, and what to do in the interim.

[svidgen]

To expand on that a little ...

What you do exactly depends greatly on the the structure and access patterns for your data. In general, if your site reaches a "critical level," you'll want to have a black-hole database server dedicated to replicating to all the necessary slaves, so the master isn't dragging behind trying to update copious other servers. You'll likely also be able to identify data that is accessed "together" and data that is accessed independently, which can allow you to divide and partition your data into multiple databases, spreading both reads and writes across multiple servers/clusters.

... But really, just buy the book. My general tips are already getting clumsy sounding.

[chazzy]

svidgen, i think you're understating/under thinking the problem. no matter the database, the need to be able to deploy multiple servers will exist, doesn't matter how "Fine tuned" your application is, deploying to a single machine is just not manageable; and assuming that there's only need for one server will make your app less scalable.

[svidgen]

@chazzy - Huh??? My whole second post was aimed at distributing the data across multiple servers. Where'd you get the idea I was suggesting a single-server solution?

[littlebigman]

Thanks guys. I'll read through "High performance MySQL" to see how to solve this issue.