How can I tell which page is creating a high-CPU-load httpd process?

Solution 1:

strace is a good way to start debugging this kind of problem. Try to strace the pid of one of the Apache processes consuming more CPU:

strace -f -t -o strace.output -p PID

This will show you the system calls made within that process. Take a look at strace.output and see what the process was doing. This might enlighten the way and show you where the process is hanging. The "-t" flag is very important here as it will prefix each line of the strace output with the time of the day. So, search for a leap.

On the other hand and as you think MySQL is probably the culprit, I'd enable the slow query log, take a look at it and try to optimize that queries. More info about the slow query log here.

Also, don't forget to take a look at the logfiles of your webserver.

Regarding your second question, I think it's hard to tell with only this info. Separating the frontend (webserver) from the backend (database) is always a good practice if you have the budget for it. On the other hand, I think that before adding more hardware, one should focus on trying to optimize the performance using the current hardware. Otherwise, the problem is probably just being postponed.

Hope this helps.