How to find the cause of MySQL overload?

Question

MySQL

There is an application working with the MySQL database. Several hundred users work in parallel in it. InnoDB tables are used. There are tables that contain several tens of millions of records. Queries contain many JOINs, there are nested SELECTs. Often WHERE conditions are built dynamically (for example, when the user sets up filters).
All this usually works fine and fairly quickly. But from time to time (several times a month) the execution time of almost ALL heavy requests suddenly starts to grow sharply, even those that were previously executed instantly. Grows from fractions of seconds to several minutes. At the same time, the load on the CPU from MySQL increases sharply, the load average can many times exceed the number of cores. The MySQL process list begins to flood with processes with execution times of up to several minutes (often in a state of sorting or copying temporary tables, which are required for sub-optimal queries). The whole application starts to slow down terribly and responds very slowly. The problem can be solved by restarting the server, but not always if users continue to actively try to continue working with the application.
It happens that the database just starts to slow down, but you can continue to work. And it happens that the server goes "out of order", and the only solution is to restart MySQL or the entire server.
At the same time, during such situations, there is no particular increase in user activity. It seems like the usual activity of users, nothing unusual. I believe that the problem is related to the fact that some users are building some kind of unsuccessful combination of SQL queries, which starts the server "over the top".
Watching slow_query_log is useless. In a similar state of the system, almost all requests start to fall into this log, and in the normal state nothing gets there.
The question is how to catch what exactly provokes such a situation? Where to dig?

Answer 1

There is a suspicion that you are running out of memory and the system goes into swap. It would be nice to see the output of free ps avx --sort rss | tailat the moment of bluntness as well.

Answer 2

1 set up server monitoring: cpu, memory, work with hdd,
2 set up MySQL monitoring - the more the better
3 then study the graphs - which at times of overload does not correspond to normal load - for example, creating huge temporary tables, etc.

Answer 3

Maybe the problem is in deadlocks? Try
SHOW ENGINE INNODB STATUS

Answer 4

Pay attention to the cache in the application, the situation is similar in description, to the fact that at some point the cache goes bad and starts to be generated again and constantly because there are a lot of requests from clients, at this moment your server may bend down