Apache VERY high page load time

Question

My Drupal 6 site has been running smoothly for years but recently has experienced intermittent periods of extreme slowness (10-60 sec page loads). Several hours of slowness followed by hours of normal (4-6 sec) page loads. The page always loads with no error, just sometimes takes forever.

------Edit----- Updated Question:

How can I troubleshoot this problem?

I've used:

1. webpagetest.org
2. windows task manager
3. netstat
4. apache and windows logs
5. Firewall packet capture

------End edit-----------

My setup:

• Windows Server 2003

• Apache/2.2.15 (Win32) Jrun/4.0

• PHP 5 MySql 5.1

• Drupal 6

• Cold fusion 9

• Vmware virtual environment

• DMZ behind a corporate firewall

• Traffic: 1-3 hits/sec avg

  Troubleshooting

• No applicable errors in apache error log

• No errors in drupal event log

• Drupal devel module shows 242 queries in 366.23 milliseconds,page execution time 2069.62 ms. (So it looks like queries and php scripts are not the problem)

• NO unusually high CPU, memory, or disk IO

• Cold fusion apps, and other static pages outside of drupal also load slow

• webpagetest.org test shows very high time-to-first-byte

The problem seems to be with Apache responding to requests, but previously I've only seen this behavior under 100% cpu load. Judging solely by resource monitoring, it looks as though very little is going on.

Here is the kicker - roughly half of the site's access comes from our LAN, but if I disable the firewall rule and block access from outside of our network, internal (LAN) access (1000+ devices) is speedy. But as soon as outside access is restored the site is crippled.

Apache config? Crawlers/bots? Attackers? I'm at the end of my rope, where should I be looking to determine where the problem lies?

------Edit:-----

Attached a waterfall chart from webpagetest.org showing a 15 second load time, I've seen times as high as several minutes. And again, the server runs fine much of the time. The green areas indicate that the browser has sent a request and is waiting to recieve the first byte of data back from the server. This is certainly a back-end delay, but it is puzzling that the CPU is barely used during this slowness.

Solution

After much research, I may have found the solution. If I'm correct, it was an apache config problem. Specifically, the "ThreadsPerChild" directive. See... http://httpd.apache.org/docs/2.2/platform/windows.html

Because Apache for Windows is multithreaded, it does not use a separate process for each request, as Apache can on Unix. Instead there are usually only two Apache processes running: a parent process, and a child which handles the requests. Within the child process each request is handled by a separate thread.

ThreadsPerChild: This directive is new. It tells the server how many threads it should use. This is the maximum number of connections the server can handle at once, so be sure to set this number high enough for your site if you get a lot of hits. The recommended default is ThreadsPerChild 150, but this must be adjusted to reflect the greatest anticipated number of simultaneous connections to accept.

Turns out, this directive was not set at all in my config and thus defaulted to 64. I confirmed this by viewing the number of threads for the second httpd.exe process in task manager. When the server was hitting more than 64 connections, the excess requests were simply having to wait for a thread to open up. I added ThreadsPerChild 150 in my httpd.conf.

Additionally, I enabled the apache status module http://httpd.apache.org/docs/2.2/mod/mod_status.html

...which, among other things, allows one to see the total number of active request on the server at any given moment. Right away, I could see spikes of up to 80 active request. Time will tell, but I'm confident that this will resolve my issue. So far, 30 hours without a hiccup.

Comments

[zigojacko]

Have you run your site through something like tools.pingdom.com to confirm where the bottleneck(s) occur?

[Aaron Waller]

Yes, I've used webpagetest.org. On the waterfall chart, I can see very high "time-to-first-byte" times for the initial request as well as several other head requests. Once the server responds, the page loads fairly quickly. I'll try to add an image of the waterfall chart in the morning.

[zigojacko]

Okay, is the MySQL database particularly large?

[Aaron Waller]

@Geoff Jackson The largest table is about 35k records (6.5mb), and drupal devel module shows acceptable query and page execution times.

[joshuahedlund]

"as soon as outside access is restored the site is crippled." - Crippled when accessed from outside, or inside goes from speedy to crippled as well?

[Stephen Ostermiller]

As it stands, this question is a "troubleshoot my site" question which is off-topic here. If you have a specific question about something that could be causing your site to be slow, then please ask.

[Aaron Waller]

@joshuahedlund During these intermittent periods of slowness, both inside and out are crippled when the connection to the internet is restored. Something about the traffic from the internet is causing the slowness. And seems like if it were simply a high volume of traffic, I would see high CPU usage.

[Aaron Waller]

@StephenOstermiller Edited the question to be more specific. Is asking for common troubleshooting tools more acceptable?

[Stephen Ostermiller]

Asking for third party resources is also off-topic because it attracts self promotion and spam answers

[Aaron Waller]

@StephenOstermiller Can you please rewrite the question so it will meet the guidelines?... Just kidding... I can certainly respect those guidelines as they are what make this site an excellent (and non spammy) resource. I use it daily. I'll try rewording again...