April 18, 2014

Should MySQL and Web Server share the same box ?

This is interesting question which I thought it would be good to write about. There are obviously benefits and drawbacks for each of methods.

Smaller applications usually start with single server which has both MySQL and Web server on it. In this case it is not usually the question but once application growths larger and you need to have multiple servers you may decide ether to grow system in MySQL+Apache pairs or split MySQL And Web Server and place them on different boxes.

Generally using separate boxes for MySQL and Web Servers is rather good practice.

It is more secure – Compromising your web server does not directly give access to your database, even though most applications have enough database access permissions to be allow intruder to trash/dump data.

It is easier to analyze – Troubleshooting bottlenecks on shared boxes is more complicated compared to systems running only MySQL or only Web server. In this case you already know who is troublemaker by simply looking at system wide stats.

Easier to maintain – Same thing if box happens to run multiple things it is harder to maintain. I would not call the difference significant in this case though.

Easier to balance – Lets say you have Web application and just added some new feature, ie chat application which increases load on your web server but does not really affect database part of load. If you can operate database farm and web server farms separately you can simply increase number of web servers.

It is less expensive – You typically want database boxes to be secure, using good hardware with ECC memory to avoid database corruption, use RAID to avoid loosing database with any hard drive loss etc. Database boxes also generally require more monitoring and maintainence such as backups so you end up using some serious hardware for this boxes to keep their
number manageable. With Web boxes it is different – you’re quite OK using crappy hardware for them as all you need is CPU power. If box starts to misbehave it is easy to shut it down without affecting site operations. Also you rarely would have data corruption due to web boxes memory failure, more likely you’ll have web server crashes and this sort of things. You can ether clone web servers from template hard drive or even have them disk less booting by NFS.

So if using dedicated boxes is so great why to think about sharing MySQL and Web server at all ? Well mostly it is for cheap guys.
In many applications you will find database servers to be IO bound so CPUs are doing virtually nothing and you’re wasting resources. This is the reason for some cheap environments to have Web servers also on database boxes, might be only handling partial load etc.

I would however only use it in last resort – placing some data crunching scripts on database server is often better use of their free CPU time.

Second thing you may feel bad about it is Web Servers memory. Getting certain amount of memory is pretty cheap, ie 4GB of memory per box costs very close to 2GB, while jump from 16GB to 32GB may be much more expensive (even in price by GB).
So you can get Web boxes with relatively plenty of memory cheap but unless you’re running 500 Apache children with mod_P (php,perl,python) per box (which is probably bad idea anyway).

The good use for such extra memory is probably caching – Web page caching, if you do not have separate layer for it, local memory or cacheing type of caching (depending on your application needs) is very good idea.

One more benefit of local access to MySQL is latency. This was problem many years ago with 10Mbit network but with 1Gbit networks being commodity these days you should not worry too much about it, unless you have each page generated by 1000+ queries, which is bad idea already.

One case I should mention when shared MySQL and Web server makes sense is Web Services architecture when you can have certain boxes providing you with some simple “Services” – these could be small enough to be single shared box (or pair of shared boxes for HA). In such cases I would think about Web Server mainly being provider of different protocol to access your data – it is typically simple and would not require much of CPU and other resources itself.

For example you can see this “Shared” kind of architecture in CNET systems using ATOMICS component to talk to MySQL over HTTP. (not what I’m great fan of this idea though).

About Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.

Comments

  1. Special case for shared box: circular multi-master replication topology, with each node running web & db, datasources point to localhost. This will (in principle) scale linearly with number of boxes, until propagation delays become problematic (but less of an issue if using sticky sessions). Useful for read-heavy apps.

  2. peter says:

    John,

    Thank you for comment.

    It scales linearly only from the first glance. In reality it has problems with scaling writes (you mention it already) second as database size grows it may change from CPU bound workload to IO bound workload which slows things down dramatically.

    This is not to mention conflicting updates and complicated failure recovery for circular replication.

    In general I can only see it used then conflicting updates are not an issue and application can’t be made aware of multi server configuration.

  3. Michael says:

    Do you have any comments on using VMWare to partition your web servers / databases as virtual machines on one or multiple (physical) boxes? To me the separate physical web server on one box and database is a better idea, but some people keep on recommending this to me.

  4. peter says:

    Michael,

    I think using VMWare and other virtualization techniques are good for two cases – testing and if you share same server among different people (in this case not VMWare but other techniques should be used of course)

    Some people also use virtualization to ease with cloning as well as configuration moving to other server – I think it is easy enough to do standard way.

    Also sharing any way limits you to resources of single server – dedicated physical web and database box will surely have more power.

  5. I had an infrastructure class during my undergraduate studies and we used VMWare for the entire course. VMWare was very good for simulation of different types of issues but also had a lot of problems. It is hard to simulate real systems using virtual machines and virtual machines are very easily corrupted so you need to ensure you backup your information very frequently.

  6. srmrao says:

    I feel that Web Server and Database Server should be hosted on separate Servers. When both are hosted on the same Server, it is causing a lot of delay in accessing the database and data updation.

  7. Adeel sami says:

    Hello Peter,

    It sounds like splitting the servers for both MySQL and web, will require external help either experienced developer or assistance from hosting server to split the servers. Not something easy to do even for the good-known forum owner ?

Speak Your Mind

*