How to unload mysql server and debug queries?

Question

MySQL

There is a site written in Laravel, the database is placed separately on a different server. Server 24 core, that
is, not the weakest server and everything was fine, up to a certain point. Approximately
1-2 weeks ago, every other day, and sometimes every day, the processes on the server are clogged almost by 100%

, that is, from 70 to 100% of individual cores. When I began to look at what was loading through htop, I saw that there was
a huge number of mysqld processes.



The question is, how can you log which requests flies the most, in order to understand and optimize in the future?
Is it possible to do this separately on the server with the base or do I need to change something on the back?

It happens at about the same time from 18 to 19 pm, during rush hour.
Perhaps someone has come across and knows how to fix it.

Answer 1

Probably the easiest way to start is to enable the query log at the database level.
Another option is to do \DB::listen() in Laravel and analyze which queries leave.

Answer 2

Go to mysql console and execute
SHOW FULL PROCESSLIST;
so you will see the currently running queries, then you can already study them using EXPLAIN