Profiling Apache+Mysql+Php server - which is the bottleneck?

How do I profile an Linux + Apache + Mysql + Php server for speed?

I have a server with a heavily modified MediaWiki instance running on Ubuntu 8.04. It's a bit sluggish - I haven't done anything to optimize it yet so I'm sure there is plenty of low-hanging fruit to make it quite a bit faster.

But in order to optimize you need to measure first. How do I find out which of the components (Apache, Php, Mysql) take up the biggest chunk of time to serve a page?

Well when your profiling anything like this to find the bottleneck you need to rule things out one by one. You'll need a baseline to get comparisons against. If you have the "ab" tool installed (it comes with apache) you can use this.

To get your baseline I recommend getting the average of at least a couple of hundred requests. Here's an example:

$ ab -n 400 http://yousite/

Look through the results for the line of "Time per request", it'll look something like:

Time per request:       96.031 [ms] (mean)

Make note of that time as this is the baseline.

To rule out apache as the culprit make a static page on your server (just save the html of a page that you consider slow/sluggish) and run ab against it again.

Throw some PHP into the static page. It doesn't have to be a large amount of it but it should be actually doing some work. MediaWiki is pretty good code so if there is a PHP bottleneck on your system my money would be on the actual loading of the PHP stack into memory and perform the test again.

Look at the three numbers and see where the biggest jump is between the next step up. My bet would be on MySQL being the slowest of the three but it could very well be that you are loading up a lot of images on a page that is slowing down the total request time in which case you might want to rethink the design of the page.

We run a good size MediaWiki installation and we quickly noticed that MediaWiki benefits greatly from having a memcached instance. Otherwise it has to load up a lot of language files and user data on every request.

Zombat and Frank Farmer have a couple of nice suggestions (I like the slow query suggestion, myself) at Stack Overflow 697802. ab for apache is also quite useful for testing the changes you make.

Also extremely important is the configuration of the prefork module.. i drastically reduced the numbers in the specific section of /etc/apache2/apache2.conf.. since then i had massive latency, where the page would be stalling for up to a minute before finishing loading... tracing with drupal's trace and devel modules, aswell as profiling with xdebug left me clueless.. i set up APC and modded the MYSQL conf but the culprit actually were the too small values for the prefork module.. i raised those to a moderate AND sufficient level and now the server is back to lightning fast. Watch out for these numbers if you have inexplicable high page execution times....

<IfModule mpm_prefork_module>
    StartServers          5
    MinSpareServers       5
# original values:
#    MaxSpareServers      10
#    MaxClients          150
# far too conservative experimental values:
#    MaxSpareServers       5
#    MaxClients            5
# good compromise:
    MaxSpareServers       10
    MaxClients            20
    MaxRequestsPerChild   0
</IfModule>

These values are for a virtual host environment with 512MiB RAM + 256MiB swap.. good luck finding your server's optimum ;)