Does a seperate MySQL server makes sense when using nginx instead of Apache

Question

Consider a web app in which a call to the app consists of php script running several MySQL queries, some of them memcached. The PHP does not do very complex job. it is mainly serving the mysql data with some formatting.

In the past it used to be recommended to put mysql and the app engine (PHP/Apache) on seperate boxes.

However, when the data can be divided horizontally (for example when there are 10 different customers using the service and it is possible to divide the data per customer) and when nginx +FastCGI is used instead of heavier apache, doesnt it make sense to put Nginx Memcache and MySQL on the same box? then when more customers come, add similar boxes?

Background: We are moving to Amazon Ec2. And a seperate box for mysql and app server means double EBS volumes (needed on app servers to keep the code persistant as it changes often). Also if something happens to the database box, more customers will fail.

Clarification: Currently the app is running with LAMP on a single server (before moving to EC2).

Answer 1

If your application architecture is already designed to support Nginx and MySQL on separate instances, you may want to host all your services on the same instance until you receive enough traffic that justifies the separation.

In general, creating new identical instances with the full stack (Nginx + Your Application + MySQL) will make your setup much more difficult to maintain. Think about taking backups, releasing application updates, patching the database engine, updating the database schema, generating reports on all your clients, etc. If you opt for this method, you would really need to find some big advantages in order to offset all the disadvantages.

Answer 2

If your application is able to work with PHP and MySQL on different servers (I don't see why this wouldn't work, actually), then, it'll also work with PHP and MySQL on the same server.

The real question is : will your servers be able to handle the load of both Apache/nginx/PHP, MySQL, and memcached ?

And there is only one way to answer that question : you have to test in a "real" "production" configuration, to determine own loaded your servers are -- or use some tool like ab, siege, or OpenSTA to "simulate" that load.

If there is not too much load with everything on the same server... Well, go with it, if it makes the hosting of your application cheapier ;-)

Answer 3

You need to measure carefully how much memory overhead everything has - I can't see enginex vs Apache making much difference, it's PHP which will use all the RAM (this in turn depends on how many processes the web server chooses to run, but that's more of a tuning issue).

Personally I'd stay away from enginex on the grounds that it is too risky to run such a weird server in production.

Databases always need lots of ram, and the only way you can sensibly tune the memory buffers is to have them on dedicated servers. This is assuming you have big data.

If you have very small data, you could keep it on the same box.

Likewise, memcached makes almost no sense if you're not running it on dedicated boxes. Taking memory from MySQL to give to memcached is really robbing Peter to pay Paul. MySQL can cache stuff in its innodb_buffer_pool quite efficiently (This saves IO, but may end up using more CPU as you won't cache presentation logic etc, which may be possible with memcached).

Memcached is only sensible if you're running it on dedicated boxes with lots of ram; it is also only sensible if you don't have enough grunt in your db servers to serve the read-workload of your app. Think about this before deploying it.