How to scale MySQL with multiple machines?

Question

I have a web app running LAMP. We recently have an increase in load and is now looking at solutions to scale. Scaling apache is pretty easy we are just going to have multiple multiple machines hosting it and round robin the incoming traffic.

However, each instance of apache will talk with MySQL and eventually MySQL will be overloaded. How to scale MySQL across multiple machines in this setup? I have already looked at this but specifically we need the updates from the DB available immediately so I don't think replication is a good strategy here? Also hopefully this can be done with minimal code change.

PS. We have around a 1:1 read-write ratio.

Answer 1

There're only two strategies: replication and sharding. Replication comes often in place when you have less write and much read traffic, so you can redirect the reads to many slaves, with the pitfall of lots of replication traffic with the time and a probability for inconsitency.

With sharding you shard your database tables across multiple machines (called functional sharding), which makes especially joins much harder. If this doenst fit anymore you also need to shard you rows across multiple machines, but this is no fun and depends a sharding layer implemented between you application and the database.

Document oriented databases or column stores do this work for you, but they are currently optimized for OLAP not for OLTP.

Answer 2

You may not think replication is not the optimal strategy, but take a look at this link which provides a fairly simple and straightforward tip for using replication to help balance the load across several machines.

Answer 3

You should take a look at MySQL Performance Blog. Maybe you'll find something useful.

Answer 4

Clustrix (http://www.clustrix.com) has the best and easiest solution for this. We've built a clustered database system that looks just like a MySQL server, except this one can scale as far as you need to take it.

I work there. Check us out.