One Way to Quickly Locate SQL with High CPU Usage in MySQL

When the CPU usage of a MySQL database rises abnormally, it's necessary to quickly identify the problematic SQL statements. In this post, we try to provide one way to achieve this. 

Below are the actual steps.

1. Use the top command to find the threads with the highest CPU usage in the MySQL process.

# Find the MySQL process ID
ps -ef | grep mysql

# Use the process ID to find the thread IDs with the highest CPU usage:
top -H -p 

In top, press P to sort by CPU usage.

Note the thread ID, for example, 39449.

2. Log in to the database to query performance_schema and information_schema.

-- Query the threads in the performance schema:
SELECT * FROM performance_schema.threads;

-- Query the current process list:
SELECT * FROM information_schema.processlist;

Use the following SQL statement to find specific thread information, including the operating system thread ID thread_os_id and the executing SQL statement. Replace [specific thread ID] with the thread ID noted earlier:

SELECT
    a. USER,
    a. HOST,
    a.db,
    b.thread_os_id,
    b.thread_id,
    a.id processlist_id,
    a.command,
    a.time,
    a.state,
    a.info
FROM
    information_schema.PROCESSLIST a,
    performance_schema.threads b
WHERE
    a.id = b.processlist_id
AND b.thread_os_id = [specific thread ID];

The output would show the SQL statement. 

Do you have other suggestions to locate high CPU usage statements or slow query etc? Leave a comment.

Reference: https://www.toutiao.com/article/7392838998437200434/