How to quickly locate the SQL statements that consume the most CPU in the current database?

How to quickly locate the SQL statements that consume the most CPU? Check out the introduction below.

An overview of the

If you are using an Oracle database, you can easily locate CPU consuming statements in the current database using SQL. How can you locate CPU consuming statements in mysql database? Here is a simple example to illustrate…

The main idea is to understand how to position, see the official website introduction..

Reference: www.percona.com/blog/…

Percona has added support for mapping processList IDS to OS thread ids through the TID column of the information. MySQL has added a new THREAD_OS_ID column to the Performance_schema. THREADS table since version 5.7. The following method applies to the situation where a particular CPU is overloaded with queries when other cores are running normally.

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

Locate the thread

pidstat -t -p <mysqld_pid> 1 5

Using this command, we can find out that threads 802, 4445, etc. are consuming a lot of CPU, and try to make sure that the consumption is constant across multiple samples of Pidstat. Based on this information, we can log into the database and use the following query to find out which MySQL thread is the culprit.

Fault Locating SQL

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

The corresponding session can be found in the processList table based on the operating system ID, as follows:

View the problem SQL execution plan

Here the corresponding execution plan can determine why the current database CPU consumption is so high…

As for the optimized points, you only need to build an index on the dock, which will not be introduced here.