2

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?

jscott
  • 24,204
  • 8
  • 77
  • 99

4 Answers4

5

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:

  1. Your target level of traffic, visits, hits
  2. Your full server specifications

For your webserver

  1. 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)
  2. You need a proportional amount of RAM to CPU, usually 1GB per core is a good guide
  3. I/O is not an issue, so RAID1 7200RPM+ disks will suffice (WD RE4 or WD 3000 HLFS)

For your DB server

  1. You do not need many cores, dual core would be more than enough. But again, higher the clock speed, the faster the individual queries.
  2. You want as much RAM as possible. Magento uses InnoDB and InnoDB craves RAM to run well.
  3. 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.

MySQL Queries

Database server load

Database Server Load

Web server load

Web server load

Ben Lessani
  • 5,174
  • 16
  • 37
2

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.

Fiasco Labs
  • 563
  • 4
  • 10
  • You don't need to drop Apache entirely, put it behind nginx and configure nginx to serve static files directly and proxy to Apache for PHP requests. Agreed that the web server needs more ram and CPU than the db server but the db server needs fast disks as Magento is very heavy on disk writes (most reads will be from RAM if you have enough). – ColinM Mar 09 '12 at 00:49
  • We ditched it for LiteSpeed. Black Friday was nicely in the green for it. LiteSpeed and lsphp5 are a lot more efficient at handling the php end of things. Combine this with full page caching and a lot of Magento's performance problems go away. – Fiasco Labs Mar 09 '12 at 00:58
  • @FiascoLabs - pretty unlikely. Even Apache/mod_php can match LiteSpeed like-for-like in performance for Magento because it isn't the bottleneck. Then add in licence limitations and costs, it makes LiteSpeed a poor choice. – Ben Lessani Jun 20 '12 at 22:15
0

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.

Cyril Gupta
  • 139
  • 1
  • 8
  • 3
    magento makes lots of includes and quite large objects for data storage so rather thinking that mysql is a bottleneck I would go for web server apache/ngnix/whatever fastest processor and more ram you can get where as mysql is hardly a bottleneck here cause the configuration really counts and fast drives are a must – Anton S Apr 25 '11 at 15:05
  • Hi Anton, you seem to be more familiar with the system. Thank you for your insight. I will try that setup. –  Apr 25 '11 at 15:34
  • 1
    +1 for Anton's almost-answer. When running Magento as three tiered hosting the biggest drain is PHP's FastCGI process. @Anton, post that as a real answer and get the recognition you deserve. – clockworkgeek Apr 25 '11 at 17:05
0

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.

rvs
  • 4,027
  • 1
  • 25
  • 30