Re: Server Stopped Responding When Having Around 1000 Request

[Rick James]

Ow! Limit 1093900,100
That has to scan over a million rows, delivering the last 100. What the heck is going on that would need to scan that far? Maybe a search engine crawler? Perhaps the page timed out on doing the SELECT, built a page with no data, but with a "Next" link. This way the crawler could continue to cause trouble. Don't build the Next link if it times out. But this won't suffice to prevent your hangs...

Order By Legal_PracticeName
Is that indexed? Even if it is, Limit 1093900,100 will take a long time.

A solution to Limit 1093900,100 is here:
http://mysql.rjweb.org/doc.php/pagination

max_threads=10000 -- Ouch!
Previously I saw
max_connections 1910
which is high, but did not seem dangerous. If you change that to 10000, that is far too much. 1000 is more reasonable.

The SELECT statement seem to be truncated before the interesting parts. Please provide the rest of it (and mask out any sensitive info). Also provide SHOW CREATE TABLE. It could be that the WHERE clause is filtering on something that is not indexed, thereby leading to slow table scans. Then, when lots of clients do such SELECTs, they hang onto the connection and the table long enough to cause trouble.

(Bad news. Your passphrase has been compromised. Suggest you "SET @pwd = '...'", then use @pwd in the SELECT. Or, since it seems to be in a Stored Routine, use a DECLARE or parameter. Meanwhile, you should fetch every row (one at a time, or 100 at a time) and re-encrypt things. Keep such a script around; you may need to run it again, or you may need to do it periodically. Have you heard of SOX (Sarbanes Oxley)? Have your heard of the hassles when a laptop with sensitive info is stolen? Sorry if you don't like what I am saying, but security is an important issue.)