How to quickly locate the SQL statement that consumes the most CPU in the current database?

How to quickly locate the sql statement that consumes the most CPU, and how to do it? Take a look at the introduction below.

Overview

If it is an Oracle database, we can easily locate which statements in the current database that consume high CPU sql Here is a simple example to illustrate...

Mainly to understand the idea of how to locate, see the official website for details..

Reference: https://www.percona.com/blog/2020/04/23/a-simple-approach-to-troubleshooting-high-cpu-in-mysql/

The main meaning is that for CPU , Percona added support for mapping processlist ID to OS thread ID through the TID column of the information, and MySQL added a THREAD in the PERFORMANCE\_SCHEMA.THREADS table after version 5.7 \_OS\_ID new column to achieve, the following method is suitable for the case of a specific CPU query overload when other cores are running normally.

find out which session is using the most CPU resources in my database?

Locating thread

pidstat -t -p <mysqld_pid> 1  5

Through this command, we can locate that threads such as 802 and 4445 consume a lot of CPU. Try to ensure that the consumption is constant in multiple samples of pidstat. Based on this information, we can log in to the database and use the following query to find out which MySQL thread is the culprit.

Locate the problem sql

select * from performance_schema.threads where thread_os_id = xx ;
select * from information_schema.`PROCESSLIST` where  id=threads.processlist_id

According to the operating system id, you can find the corresponding session in the processlist table, as follows:

View the problem sql execution plan

Here, by looking at the execution plan, you can basically judge why the current database CPU consumption is so high...

As for the optimization point, you only need to build an index in the dock, which will not be introduced here.

Original: https://www.toutiao.com/i6923526305795293707/