Optimize Apache/PHP/MySQL on VPS for Heavy Load

The kind of numbers you are talking about are tiny, and as you observed load didn't change during that time period, so I don't expect this to be an issue of tuning apache or mysql directly.

In my experience of chasing issues like this down it's almost always ties into SQL. I would isolate what part of the page is consuming all of this time. Insert timer calls throughout the page in question and store the data somewhere you can review it (spit it to stderr and apache's error log if nothing else). Until you know the precise part of the code that is causing your slow down you will just be guessing.

Some possibilities:

• A query into a table that has grown very large and is now slow (lacks index? needs pruning?)
• A session table that is never pruned and has grown massive
• A very subtle corruption (run through and optimize all your mysql databases to fix)
• An ugly query that does heavy calculations every page load that could be optmized by saving the result in a cache.

I would also go back to basics and see if you can't find out what changed "a couple days ago". Was the page modified? Upgrades?

Another thing to check is I/O (iostat) to see if you may be I/O bound, though usually you will see symptoms in load for that.

I don't know of any such script for Apache likely because, compared to MySQL, there are only a few things which affect Apache's memory usage:

1. MaxClients: The more clients the more memory used.
2. Mods: Similarly, the more mods/extensions installed the more memory needed.
3. Dynamic Scripts: Running dynamic scripts, like PHP, may increase memory usage depending on your Apache settings. For example, say you have 10 small PHP files and one rarely used large/memory intensive one. Eventually all Apache clients will run this large PHP script and use more memory.
4. Compile Time: I haven't played much with Apache compile time settings but I wouldn't be surprised if there were some memory savings to be found there.

A few specific things you can try are listed below. Try to measure the effect and test on your development platform first if possible and/or implement any changes gradually on the production site.

1. Keep MaxClients as low as possible. On a low volume site you can likely get away with numbers well under 10. You can also keep other related settings like MaxSpareServers low.
2. Compare the prefork vs worker modes of Apache to see if there's any significant difference in memory usage.
3. Eliminate all mods/extensions you are not actually using. This may be more difficult than it sounds as it is not always easy to determine which ones you are using.
4. Use a low value (100-1000) for MaxRequestsPerChild if you are using any dynamic scripts or are seeings the Apache instances slowing increasing in memory usage over time. This stops memory hungry or leaking scripts from using up too much memory.
5. Look into compile time memory optimizations only if you really need to, or have a lot of past experience compiling Apache. Personally I would look into alternative servers before this step.