I’d like to know if there is a PHP/MySQL configuration option (or combination of options) that can prevent long-running PHP scripts in a small number of places from creating hanging and sleeping MySQL transactions that accumulate and can cause the entire MySQL server to crash.
Here’s the scenario I’m looking to avoid:
Let’s say I've launched a new site and one of the sections has a really inefficient PHP loop that never got addressed or was unforeseen. Once the site launches and it sees a huge amount of traffic the query rears its head and we receive 504 errors.
At this point we're accumulating a massive amount of sleeping MySQL connections and, if we hit our max connection limit, it brings down the database server and the rest of the site with it. All because one query on one page.
What I'm hoping to do is to prevent badly performant Twig/PHP code from having even the possibility of bringing down the server. Some mechanism or set of server config options that would cause the user on just that page to see an error, preventing hanging transactions, and keeping the database from a catastrophic failure that would affect everyone else.
I’m aware that, ultimately, the query itself needs optimization. But what I’d like to do is setup preventative measures that keep the servers from crashing for the other users on the site.
Does something out there exist to prevent this?
