Normally what happens is that requests seem to back up, and apache begins to consume more and more memory and CPU, until eventually it runs out of RAM and then ALL apache processes die, leaving all visitors with no website.
This seems to be a common problem from people I speak to but I can't seem to find a solution!
Any help much appreciated!
A rather simple way would be to limit the number of users allowed to access your site. Actual configuration depends on the MPM you're using. But usually it will be MaxClients (http://httpd.apache.org/docs/2.2/en/mod/mpm_common.html#maxclients) for the typical unix apache mpms (prefork and worker).
Also, if you are using prefork, you might like to switch to the worker-mpm, as it's faster (but all modules must be thread-safe for this, e.g. older PHP versions are not)
Yes, this is a common problem. It is also the point where most people's Apache knowledge / system knowledge ends :-)
It is a long topic. First, can you reproduce the behaviour or see it frequently? Or does it just happen "sometimes" and you notice it only when it's too late to debug anything?
The thing is, debugging the failure is also possible post-mortem by analysing logs, but those need to be prepared for the event before, but as you don't know what to look for it can be more work.
To find out where the resource bottleneck could be, a good first step could be to enable mod_status, make it accessible from localhost or some trusted location and look at the server status (/server-status). It might be impossible to look at the server status when it's "too late" and Apache doesn't react anymore, but it is possible to gather interesting data before.
Since you tagged your question "mysql", another question would be if there's a backend database that Apache has to wait for. A little mysql tuning could solve all your problems, but that would only one of the possibilities for the failure that you see. Logging the server status, a little bit of "top" output and mysqls slow-query-log would give insight into this. In addition, check the memory configuration of mysql, if you have suspicions that it might not be taken care of already.
Lastly, it is important to make sure that Apache won't be using too much memory, when it queues up requests, or the machine will start swapping and finally kill Apache (or something else). Assess how much memory each Apache prozess needs. That is not easily visible in the process status but can be calculated with this script: http://cmdline.net/misc/httpd2-memusage . Then see how much memory your system can maximally spend on Apache processes, and set MaxClients accordingly - so that Apache will never use more than what's available. (You multiply the RAM need of an average process by the number of processes, simple math.) Some other sizing directives will probably need to be adjusted as well, so they match the MaxClients. This is something that many setups lack - but is an essential protection against little "explosions".
Finally, is your Apache relatively busy? Then I can almost blindly recommend to reduce KeepaliveTimeout to 2 seconds.
Sounds as if the database might cause the problem through "hanging". For that, both KeepAlive and the MPM type won't make a difference.
Check if your tables use the InnoDB engine, and not the MyISAM engine. MyISAM will "OPTIMIZE" at random points in time, which is a table blocking operation, and could take a few minutes for a big table. That would be a "pathognomonical" situtation that makes Apache stall (and even fork to death, if not properly limited). -> "show table status"
Lack of memory assigned to the database would be the next likely thing to check. /etc/my.cnf typically ships with pretty small defaults. If the tables use the InnoDB engine, a too small innodb_buffer_pool_size value might be the culprit. You probably have more memory to spare; the table status gives you details about the actual size of the data.