Troubleshooting High CPU in MySQL

Products

• VMware Tanzu Application Service for VMs

Issue/Introduction

Percona added support to map processlist ids to OS thread ids through column TID of the information_schema.processlist table starting on Percona Server for MySQL 5.6.27. With the release of 5.7, MySQL followed with its own implementation by extending the PERFORMANCE_SCHEMA.THREADS table and adding a new column named THREAD_OS_ID, which Percona Server for MySQL adopted in place of its own, as it usually does remain as close to upstream as possible.

The following approach is useful for cases where there is a query overloading one particular CPU while other cores are performing normally.

To troubleshoot CPU issues, we can use several tools, such as top or pidstat (requires the sysstat package). However, with TAS, mysqld is under bpm which will run a PID namespace. So the THREAD_OS_ID will map to a different PID than you might see if you run pidstat on the Host VM.

Environment

Product Version: 2.10

Resolution

To make those PIDs line up you might run a command like this instead:

# nsenter --target "$(pidof mysqld)" -m  pidstat -t

And then the THREAD_OS_ID’s should line up with the pidstat output

Once you identify the thread TID that is consuming the most CPU, you can log into the database and use the following query to find out which MySQL thread is the culprit.

Login

mysql/b73967e5-5720-4b64-b6aa-d226f2fa4cdd:~# mysql --defaults-file=/var/vcap/jobs/pxc-mysql/config/mylogin.cnf

Query

mysql> select * from performance_schema.threads where THREAD_OS_ID = 74 \G