Diagnosing MySQL High CPU Usage with Processlist and Performance Schema

Problem The author notices MySQL CPU usage spiking and sees an overwhelming number of rows in the processlist, prompting a need for systematic diagnosis.

Experiment A simple reproducible scenario is set up by creating a test database and inserting data using techniques from a previous experiment. A deliberately inefficient SQL statement is then executed repeatedly to generate high CPU load.

Using top -H , the thread consuming the most CPU (e.g., thread 17967) is identified. The thread’s details are examined in the MySQL processlist, revealing the query ID that can be killed.

Tips When using performance_schema , be aware of three different thread identifiers: 1. PROCESSLIST_ID – the ID shown in the processlist, usable with the KILL command. 2. THREAD_ID – MySQL’s internal thread identifier. 3. THREAD_OS_ID – the operating‑system thread ID. Distinguishing these prevents accidentally terminating the wrong query.

Additional useful information from the performance schema includes the query start time and whether a temporary disk table is being used. If the problematic query is clearly identified, it can be terminated with KILL PROCESSLIST_ID .

Related articles are linked for further reading on topics such as evaluating ALTER TABLE progress and understanding MySQL’s internal statistics.