Very high page execution times and CPU spikes

Hi -

My drupal install is experiencing very high page execution times and the CPU spikes to 100% when I first load a page. Upon reloading that same page, things happen faster, but the execution times still are high and CPU often gets up to 60-70%. This is for authenticated users. For anonymous users, the site is performing well.

Server stats/info

php running as mod_php

Average daily page views is very modest - 400, peak 700

Running eAccelerator with 128MB
Running Boost 6.x-1.18

I have another drupal site on the same server but with a different codebase. This site has fewer modules and is less complex, but doesn't exhibit this behavior at all.

For example, when creating a new article (and then the same page reloaded 2 more times:
/node/add/article
1st: Executed 321 queries in 390.57 milliseconds. Page execution time was 38098.59 ms.
2nd: Executed 283 queries in 166.99 milliseconds. Page execution time was 2980.29 ms.
3rd: Executed 283 queries in 153.54 milliseconds. Page execution time was 2484.43 ms.

For example, when loading our business directory (initial load and then a refresh)
/business-directory
1st: Executed 330 queries in 876.81 milliseconds. Page execution time was 11164.65 ms.
2nd: Executed 258 queries in 114.74 milliseconds. Page execution time was 1381.37 ms.

This is what I've been trying the past few days.

• I increased kernel.shmmax to 130MB and allocated 128MB to eAccelerator

• I increased realpath_cache_size from 16K to 64K

• I installed dbtuner and added some new indexes to recommended tables

• I went through most of the views on my site and added caching when possible

• I upgraded to MYSQL 5.1

This is the current behavior I'm seeing.
When I go to load a page that I haven't loaded in some time, I'm still getting the super long page execution times. For example, some recent page requests:
Executed 558 queries in 446.57 milliseconds. Page execution time was 19595.37 ms.
Executed 485 queries in 684.2 milliseconds. Page execution time was 25814.39 ms.

When I monitor "top" while trying to do this I see that my CPU will totally max out .. sometimes over 100% (user: nobody, command: httpd)

32513 nobody 17 0 183m 60m 24m S 99.8 3.9 0:02.12 httpd

when i run "mysqladmin -i3 processlist" while trying to load a new page I don't see any errors per se.. just a long "sleep" time .. 33 seconds in this example

+------+---------------+-----------+-------------------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+---------------+-----------+-------------------+---------+------+-------+------------------+
| 7870 | leechprotect | localhost | leechprotect | Sleep | 3828 | | |
| 8011 | root | localhost | | Query | 0 | | show processlist |
| 8018 | drupal_db1 | localhost | drupal_db1 | Sleep | 33 | | |
+------+---------------+-----------+-------------------+---------+------+-------+------------------+

When I refresh the same page CPU still goes up to about 65% but doesn't max out and the "sleep" time is more like 3 seconds

Once a page has been loaded and i guess presumably is cached to a certain degree, the page load times are better .. but if apache/mysql gets restarted or I access a page that hasn't been touched in some time, the page execution times are still off the charts.

The CPU maxing out seems like a clue, but I'm not sure where to go next

Can you suggest a list of things that might cause that CPU spike? I haven't really delved into troubleshooting individual modules yet - does that seem like the next logical thing to do? When I perform similar page loads on another drupal install on the same server but with a different codebase, I do not see the CPU spikes. So it really seems like it must have something to do with either this codebase or maybe the shear number of modules being used (this problem site is definitely a much more complicated site than the other drupal site on the server)

These are my keep alive setting - do these look okay. Could this have anything to do with it?
apache2handler
Max Requests Per Child: 10000 - Keep Alive: on - Max Per Connection: 100
Timeouts Connection: 30 - Keep-Alive: 5

I changed realpath_cache_size to 64K from 16K .. does that make sense? does it matter?

I feel like i'm slowly eliminating possible issues and so in theory closing in on the problem, but after all of this I still don't feel much further along.