I am about to launch a new Magento website and I was interested in your feedback on this issue? Do you think the Magento PHP code is slower or the Magento MySQL operations are slower? Which should I split out into the larger box of my two servers?
Our experience: We only host Magento stores.
It is sadly a very, very common misconception with Magento that you should even use a separate DB server, and worse, that you should use your most powerful server as the DB server.
Unless you are in a clustered configuration with multiple web servers, we never advocate using a separate database server for Magento.
I'm going to back this up with the latest stats from some servers.
I have taken the load average from a small Magento website (4,000 unique visitors per day). You can see for yourself that the separate DB server uses very little CPU and resources in general. In fact, the only thing it does require is a lot of RAM.
MySQL is absolutely not the bottleneck for any Magento installation. But if you must use two servers....
... I am going to provide this recommendation on the basis your site has around 10,000 daily unique visitors. BUT in order for an accurate reply, you should really post:
- Your target level of traffic, visits, hits
- Your full server specifications
For your webserver
- You want as many, and as fast CPUs as possible (the more cores, the greater the concurrency support - the faster the clock speed, the faster the individual page load times)
- You need a proportional amount of RAM to CPU, usually 1GB per core is a good guide
- I/O is not an issue, so RAID1 7200RPM+ disks will suffice (WD RE4 or WD 3000 HLFS)
For your DB server
- You do not need many cores, dual core would be more than enough. But again, higher the clock speed, the faster the individual queries.
- You want as much RAM as possible. Magento uses InnoDB and InnoDB craves RAM to run well.
- I/O and general IOPS make/break a DB server - so you want a fast disk set-up, but for a 10k visitor store, RAID1 7200RPM+ disks will suffice (WD RE4 or WD 3000 HLFS).
You could also load balance between web traffic between the two servers, but the configuration for this is way beyond the scope of this reply.
Database server load
Web server load
Having had to bounce the php memory_limit up to 512MB for certain processes to run, I've watched each Apache process loaded essentially bug out memory wise. You will learn to drop mod_php5 really quickly as turning your Apache web services into php interpreters IS NOT THE BEST USE OF RESOURCES. Under FastCGI you get to run Apache WorkerMPM returning it to serving html which it does best while FastCGI runs the PHP interpreter as a separate process. On top of this you will need some sort of opcode caching. Your primary fastest server optimized for memory and CPU use needs to be your Web/PHP server. Your secondary server optimized for fast disk I/O is where you run MySQL services.
It's common with two server systems to run a quad core system with 12GB+ memory for the web server and a two core system with 8GB+ and a fast disk subsystem for the db server.
I think it's a better idea to put the database on the bigger server, cause if you have a non-trivial site (requiring multiple servers), then you're going to put a lot of load on the database, querying for stuff. And certainly that load is going to be a bigger bottleneck than parsing php files.
I believe it's usually better to put database to bigger and more powerful server.
The reason is that it's much easier to move application to anther server with no downtime. There is much easier and effective to scale-out application itself. Two application servers properly load-balanced gives you almost 2x increase, while with database you'll never get such values and would be forced to play with replication and/or sharding, which is much harder that configuring http load balancer.
So, if you application would grow, use best database server as you could afford now - that would give you more time before you'll have to move/scale-out database, which is harder that moving/scaling out application/http servers.