2

I have got an PHP application running a report and firing off about 1 million SQL queries to SQL server within 90 seconds. During the period of time no one else can use this web-based application - the egg timer is rolling but nothing loads until the report is timed out or completed. I tested this problem in an isolated environment with just myself in there, ran the report in a browser, then any actions from other browser windows to this application site hung.

Some details about the testing environment:

Windows 2008 R2 x64 - IIS 7.5 - PHP 5.3.8 via FastCGI 
Windows 2008 R2 x64 - SQL Server 2008 R2 x64

FastCGI settings in IIS:

Instance MaxRequests = 200
Max Instances = 16
Activity Timeout = 70
Idle Timeout = 300
Queue Length = 1000
Rapid Fails PerMin = 10
Request Timeout = 90

Each of the SQL requests completes less than 60ms on the SQL server side. CPU loads of both of the Web server and the SQL server are less than 10%. The Web server has 16GB RAM and about 60% RAM are available when running the report.

It seems that, PHP's been firing off too many requests to the SQL server and becoming too busy to handle other requests. If this is the case then there should be something I can tweak to make PHP handle more concurrent requests.

Does anyone know? Please help!

user1646590
  • 23
  • 4
  • 2
    Out of curiosity, have you tried accessing the site through a completely different browser (e.g. using Firefox for one and Chrome for another). Just trying to rule out the idea that maybe it is just your session that is locked up and not the whole site. Using different browsers would result in multiple sessions being created. Different tabs in the same browser would share the same session. – Eric Petroelje Sep 04 '12 at 15:21
  • Check out [this](http://stackoverflow.com/questions/12070345/php-multiple-ajax-requests-first-request-block-second-request/12070517) question. Possibly you have the same problem – Viktor S. Sep 04 '12 at 15:24
  • Yep, def session lock... http://www.php.net/manual/en/ref.session.php#64525 – Salketer Sep 04 '12 at 15:24
  • Thanks guys, looks you are right - it was session locking during my test. I tried to run the report in IE and browse the same site in Chrome and it worked. – user1646590 Sep 04 '12 at 15:39

1 Answers1

5

I'll just stab in the dark here and assume it's due to session locking.

When you use the standard session handler that comes with PHP, it makes sure that your session files cannot be corrupted by using (advisory) write locks throughout the course of your script's execution (unless session_write_close() is called earlier).

Other scripts that try to access the same session (your browser would pass the same cookie value) will wait for the lock to get released, as long as it takes.

You can verify this by using two completely different browsers to simulate two users (one runs the report, the other accesses the site). If that works, you're pretty sure it's due to session locking.

This shouldn't be a problem, because you will know when you're running a report, but if this might cause issues nonetheless you can consider two things:

  1. don't start a session for the report script (but this also means unauthorized users could try to run your report script)
  2. close the session before your grunt work starts, by using session_write_close() after you have verified the user's identity.
Ja͢ck
  • 170,779
  • 38
  • 263
  • 309
  • Many thanks for the advise, but how come the users got the session locks too? Most of them are connecting to the website from their own local PC's through a web proxy. I asked one of the users to run this report from his PC to replicate the problem, then logged onto the IIS server but still encountered the same issue until the report timed out...we all used IE 8 browsers though. – user1646590 Sep 04 '12 at 16:09
  • I'm not sure why that would happen, especially since you did a test to verify it was working with two different browsers ... no idea what naughty stuff the proxy does, but it shouldn't be sending its own cookies anyway. – Ja͢ck Sep 04 '12 at 16:12
  • The root cause of the problem may be different then. Many thanks for the help anyway. – user1646590 Sep 04 '12 at 16:22
  • @user1646590 Let's hope someone else comes along with another suggestion then :) – Ja͢ck Sep 04 '12 at 16:25
  • Some additional info: I noticed in the HTTP logs there are some strange patterns when the real problem occurred (not the ones I tried to replicate in the testing environment) - there usually about 25 HTTP request in avg. per second for this web application; when the problem started, the amount of the requests was gradually reduced; after 3 minutes, HTTP log stopped logging anything, until the PHP Max_execution_time (600sec) reached. Does this info help? – user1646590 Sep 04 '12 at 16:38