Quickly Find the Highest CPU‑Consuming SQL in MySQL

Overview

If you need to locate the SQL statements that are driving the highest CPU usage in a MySQL database, you can use the THREAD_OS_ID column added to performance_schema.threads (available from MySQL 5.7) together with OS‑level monitoring tools. The method works for cases where a specific CPU core is overloaded by database queries.

Identify the OS threads consuming CPU

Run pidstat -t -p <mysqld_pid> 1 5 to sample the MySQL server process. The output lists thread IDs (e.g., 802, 4445) that are using significant CPU time. Verify that the high‑CPU usage is consistent across multiple samples.

Map the OS thread to a MySQL session

SELECT * FROM performance_schema.threads WHERE thread_os_id = <thread_id>;
SELECT * FROM information_schema.`PROCESSLIST` WHERE id = threads.processlist_id;

The first query finds the MySQL thread that corresponds to the OS thread ID; the second query retrieves the SQL statement associated with that MySQL thread.

Examine the execution plan

Run EXPLAIN FORMAT=JSON <problematic_sql>; (or use the GUI “Explain” feature) to view the execution plan. The plan reveals why the query is CPU‑intensive, often due to missing indexes.

In most cases, creating an appropriate index on the involved columns resolves the high CPU consumption.