Should our MySQL DB be separate from our Apache servers?

Question

Currently, my company manages one LAMP-based website on a VPS server, so the database and the web server are essentially in the same field.

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

However, we expect more traffic and strive to increase resiliency and scalability. I suggested a load balancing architecture with a separate ie server:

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

Is this a smart approach? Or is it too complicated? It seems risky to keep everything on one server. Although we may not need load balancing at this stage, is it wise to split the database from the web server?

I saw a couple of questions like this, but I'm not sure if they apply in the context of Apache / MySQL. When I worked in Windows environments, they always had separate database servers.

Answer 1

Yes, it's nice to split the database and web servers. Definitely.

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

In addition, various technologies will scale at a different speed. You may need only 2-3 databases in a cluster and 10 web servers. They have different needs and will grow in different ways. It's a good idea to bite a bullet now with an eye on growth.

As for the nervous attitude towards one MySQL server ... I would consider it mandatory to set up a task to back up the entire database every night.

I also saw production machines replicate data to another MySQL field, which acts as a hot backup. Not so difficult to set up, but it costs another machine.

Answer 2

I do not agree with ryan1234.

The structure of resource use should be completely different between the DBMS and the web server.

In addition, if you plan to increase the number of machines, then, rather, it is foolish not to take the opportunity to make the site MORE more resistant to blackouts.

If it were me, I would most likely go with two mailboxes working with both web servers and mysql serving the same vhosts and asynch master-master replication in the database (with the possibility of direct traffic to one database node / single node web server.

Yes, if you need to add a lot more hardware than this to control bandwidth, then dividing the DBMS into a separate level is a good idea. but you seem far from that.