Troubleshooting High CPU in MySQL

tags: `linux` `dba` `mysql`

Overview

With MySQL 5.7, the PERFORMANCE_SCHEMA.THREADS table added THREAD_OS_ID. This column helps identify sessions consuming high CPU resources, especially when one query overloads a CPU core.

To troubleshoot, use pidstat from the sysstat package. The -t option shows thread-level usage, and -p with the MySQL process ID filters results to MySQL threads:

1. Using `pidstat`

The command is pidstat -t -p <mysqld_pid> 1:

shell> pidstat -t -p 31258 1
03:31:06 PM   UID      TGID       TID    %usr %system  %guest    %CPU   CPU  Command
[...]
6.00    23  |__mysqld
03:31:07 PM 10014         -     32052    5.00    1.00    0.00    6.00    14  |__mysqld
03:31:07 PM 10014         -     32053   94.00    0.00    0.00   94.00     9  |__mysqld
03:31:07 PM 10014         -     32055    4.00    1.00    0.00    5.00    10  |__mysqld
03:31:07 PM 10014         -      4275    5.00    1.00    0.00    6.00    10  |__mysqld

2. Query `performance_schema.threads`

The thread 32053 is consuming the most CPU, verified consistently across multiple pidstat samples. To identify the MySQL thread responsible, log into the database and execute select * from performance_schema.threads where THREAD_OS_ID = 32053 G

Example result:

mysql > select * from performance_schema.threads where THREAD_OS_ID = 32053 G
*************************** 1. row ***************************
          THREAD_ID: 686
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 590
   PROCESSLIST_USER: msandbox
   PROCESSLIST_HOST: localhost
     PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Query
   PROCESSLIST_TIME: 0
  PROCESSLIST_STATE: Sending data
   PROCESSLIST_INFO: select * from test.joinit where b = 'a a eveniet ut.'
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: SSL/TLS
       THREAD_OS_ID: 32053
1 row in set (0.00 sec)

The high CPU usage is traced to a query on the joinit table, executed by the msandbox user from localhost on the test database.

3. Analyze Query

Use EXPLAIN to analyze the query execution plan and identify potential optimizations:

mysql > explain select * from test.joinit where b = 'a a eveniet ut.' G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: joinit
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7170836
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

4. Apply Fix

In this example, it was an index that was missing. Please adjust according to your results:

mysql > alter table test.joinit add index (b) ;
Query OK, 0 rows affected (15.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

5. Verify

Recheck pidstat to confirm reduced CPU usage:

shell> pidstat -t -p 31258 1
03:37:53 PM   UID      TGID       TID    %usr %system  %guest    %CPU   CPU  Command
[...]
03:37:54 PM 10014         -     32042   25.00    6.00    0.00   31.00    20  |__mysqld
03:37:54 PM 10014         -     32047   25.00    4.00    0.00   29.00    23  |__mysqld
03:37:54 PM 10014         -     32048   25.00    7.00    0.00   32.00    22  |__mysqld
03:37:54 PM 10014         -     32049   23.00    6.00    0.00   29.00     4  |__mysqld
03:37:54 PM 10014         -     32052   23.00    7.00    0.00   30.00    14  |__mysqld
03:37:54 PM 10014         -     32053   10.00    2.00    0.00   12.00    11  |__mysqld
03:37:54 PM 10014         -     32055   24.00    6.00    0.00   30.00     1  |__mysqld

Additional Notes on IO and Memory Troubleshooting

IO Issues: Use pidstat -d or iostat -H to measure IO operations by thread.
Memory Issues: Use performance_schema memory instrumentation available from MySQL 5.7 onwards.

A highly IO-consuming thread may be detectable, but results can be misleading due to background operations.

Memory usage is harder to gauge at the OS level since MySQL manages memory internally, making thread-level consumption transparent to the OS. Use performance_schema memory instrumentation (available from MySQL 5.7) for more accurate insights.

Correctly understanding the true cause of database performance problems allows for a quick and efficient resolution – yet enterprises often lack this crucial information.

Without it, your solution could require more time and resources than necessary, or inefficiently address the issue. And contrary to popular belief, the problem is not always the database itself!

josephbolus/mysql-high-cpu.md