How can I find out what SQL queries are currently running on the server, or the statistics of hits by URL?

Question

MySQL

Periodically, a situation arises - a huge amount of disk operations for recording begins. This loads the CPU under 100% I saw that the mysql process creates a large load.
Such a case arises spontaneously. It seems that the software crashes and "floods" the server on the API with some request, from which the server goes down.

I want to monitor what SQL queries come from the console. Is it possible? Logging to a file, I'm afraid, put the server.
Or the same monitoring on HTTP requests to see where the requests go more often.

About the service itself: it serves as an intermediary - tasks are received by it, the software of the executors calls for them via the API.
Programming language: PHP. Framework: Yii2.

Statistics example:


PSIn general, the goal is to find out exactly where the peak load occurs in order to understand how to correct it. If you give another advice on determining this - I will be grateful.

Answer 1

Or the same monitoring on HTTP requests to see where the requests go more often.

Err... access.log, no?

as an option, but ideally monitor what requests are currently occurring in the console.

Precona , but you have to smoke mana there, but you can set it up to monitor the top loading requests and that's it, with statistics, graphs, etc...

Answer 2

To view the current load, you can use the mytop utility

Answer 3

To find out what is loaded right now in the mysql console, write
SHOW PROCESSLIST;
or
SHOW FULL PROCESSLIST;
To see the log of heavy queries, enable slow query log (by default, it is disabled on production, because it also creates some load).
This log can not only be read with your eyes, but also fed into the analyzer, of which there are many. There are even online analyzers quite decent.