mysql – What could cause blocked requests in IIS?

I’m using IIS on Windows Server 2016 with MySQL and PHP on two almost identical servers. I’ve recently noticed a slowdown on one of my two servers but it happens only when my site tries to execute multiple instances of a script at the same time. They seem to get stuck on each other.

A perfect example is my search page. When the user types in a search query, with each keyup (after the second letter) a search is executed as long as there’s at least a 200 ms delay since the last keypress. So if you type fast, it only does one search at the end but for slower typers (those who wait more than 200 ms between key presses) this will trigger multiple calls to the search results. See this screenshot.

BAD SERVER
enter image description here

Notice all the pending requests and in this screenshot the first one just finished at 19.08 seconds. Obviously much too long. By the time they’re all done they’re all well over 15 seconds to return a simple resultset.

BAD SERVER
enter image description here

Keep in mind that these queries take just a fraction of a second when run in MySQL Workbench and also when run on my other server which is not suffering from this problem. See in this screenshot (from the good server) the exact same search returns in a quarter of a second.

GOOD SERVER
enter image description here

It seems to me that (on the bad server) they’re not able to execute simultaneously for some reason because if I execute just a single search (by typing quickly enough to trigger only a single search) it comes back quick, but if I execute multiples like this, they all get stuck like in a traffic jam. What could cause this?

This next screenshot shows the result if I trigger only a single search on the bad server. As you can see it comes back super fast. So the problem is only when executing multiples of the same script simultaneously.

BAD SERVER
enter image description here

I did make some changes to the bad server recently but as far as I can remember, the only changes I made were to allow bigger file uploads.

  • In PHP I increased post_max_size = 500M
  • In PHP I increased upload_max_filesize = 500M
  • In IIS I increased UploadReadAheadSize to 49152000
  • In IIS I increased maximum allowed content length to 300000000

It’s possible that I made other changes to this server that I can’t remember.

TEMPORARY FIX

I can mitigate this problem by allowing a longer delay between key presses when searching, and I’ve done this, increasing it to 800 ms so even slow typers don’t see this problem, but this is only a band aid solution and does not address the underlying issue which also affects other areas of my site.

WHAT I’VE TRIED

So far I’ve confirmed that my IIS config, MySQL config (my.ini) and my PHP config (php.ini) are all identical in every way that matters on both servers (at least as far as what seems obvious to me). I’ve also confirmed that the select statements I’m running in this search perform equally well on both servers if I execute them in MySQL Workbench. It’s only in my web app where I’m having this problem.

I temporarily undid the two changes I made to IIS for larger file uploads just in case, but that seemed to make no difference.

I’ve also downloaded and installed LeanSentry which is warning me once or twice a day that my site has seen blocked requests, which I assume is exactly what I’m seeing here, but unfortunately LeanSentry can only pinpoint the source of the problem with ASP pages, not PHP. So it essentially only confirms for me that there’s a problem but it can’t help me beyond that.

OTHER SYMPTOMS

I see similar problems if I open multiple reports simultaneously. If I allow one report to finish loading before opening the next one they all load quickly, but if I force my app to open multiple reports at once, they all get stuck.

What could be causing this issue of bottlenecking?