Should our MySQL DB be separate from our Apache servers?

Question

My company currently runs a single LAMP-based website on a VPS server so the DB and web server are, in effect, on the same box.

We are developing a new site, which we plan to host on the same single server (i.e. we will have everything for foo.com and bar.com on Server A).

However, we expect traffic to increase and are keen to improve resilience and scalability. I have suggested a load balanced architecture with a separate DB server i.e.:

                                Internet
                                   ¦
                              Load Balancer
                               /        \
                       ¦ Server A ¦   ¦ Server B ¦
                               \        /
                              ¦ MySQL DB ¦

Is this a sensible approach? Or is it over-engineered? It feels risky to me to keep everything on a single server. Whilst we might not require the Load Balancing at this stage is it still sensible to split the DB from the web server?

I've seen a couple of questions similar to this but I'm not sure if they apply in an Apache/MySQL context. When I've worked on Windows server environments they have always had separate DB servers.

Answer 1

Yes it's a good idea to split the database and web servers. Definitely.

One of the major reasons is that each technology (web server, database) will compete for physical resources. For example, imagine you also put caching on the single server. Now you have a web server, caching and a database all competing for RAM. Only so much to go around.

Plus the different technologies will scale at a different rate. You might only need 2-3 database machines in a cluster and 10 web servers. They have different needs and will grow differently. Not a bad idea to bite the bullet now with an eye towards growth.

As far as being nervous about a single MySQL server ... I would consider it mandatory to set up a job to backup the entire database every night.

Also I have seen production machines replicate data to another MySQL box that acts as a hot backup. Not that hard to set up, but does cost another machine.