Performance Monitoring of MySQL

This is a simple performance monitoring script for MySQL, combining the `top -H' output with MySQL session threads so they can be matched (provided you leave the system variable thread_handling as default, 'one-thread-per-connection'). The output of the script is as follows. 

top - 09:27:05 up 58 days, 23:17,  1 user,  load average: 1.16, 1.20, 1.23
Threads:  38 total,   1 running,  37 sleeping,   0 stopped,   0 zombie
%Cpu(s): 23.4 us,  4.6 sy,  0.0 ni, 70.3 id,  0.1 wa,  1.1 hi,  0.5 si,  0.0 st
MiB Mem :  31836.1 total,   1210.6 free,  28306.9 used,   2715.1 buff/cache
MiB Swap:  16384.0 total,  14334.5 free,   2049.5 used.   3529.2 avail Mem

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
 620542 mysql     20   0   27.5g   1.9g  34432 R  81.3   6.1  50:55.22 /usr/sbin/mysqld
 621019 mysql     20   0   27.5g   1.9g  34432 S  56.7   6.1  40:43.76 /usr/sbin/mysqld
 620456 mysql     20   0   27.5g   1.9g  34432 S   1.7   6.1  66:18.34 /usr/sbin/mysqld
 620418 mysql     20   0   27.5g   1.9g  34432 S   0.3   6.1   0:07.90 /usr/sbin/mysqld
 620419 mysql     20   0   27.5g   1.9g  34432 S   0.3   6.1   0:19.25 /usr/sbin/mysqld
 620421 mysql     20   0   27.5g   1.9g  34432 S   0.3   6.1   0:13.31 /usr/sbin/mysqld
 620396 mysql     20   0   27.5g   1.9g  34432 S   0.0   6.1   0:00.82 /usr/sbin/mysqld
 620407 mysql     20   0   27.5g   1.9g  34432 S   0.0   6.1   0:00.23 /usr/sbin/mysqld

thread  pl_id   user            host            database        command state           info    conn_type
620443  5       event_scheduler localhost       NULL    Daemon  Waiting on empty queue  NULL    NULL
620447  7       NULL    NULL    NULL    Daemon  Suspending      NULL    NULL
620542  10272   redcapstg_user  ourappserver123 redcap_stg      Execute executing       SELECT record FROM redcap_ddp_  TCP/IP
621019  10484   redcapstg_user  ourappserver123 redcap_stg      Prepare starting        NULL    TCP/IP

The above shows that mysqld thread (incorrectly called "PID" by "top") 620542 uses the most CPU, 81.3%. It matches processlist_id 10272 inside MySQL, which you could see if you were to run "show processlist". The session comes from ourappserver123 and is running SQL "SELECT record FROM redcap_ddp_ ..." in database redcap_stg. What's important to me is the correlation between the OS thread and the MySQL thread or session. A natural question any performance analyst may ask is, What is the top CPU hogging process doing? Since MySQL runs in thread mode by default, change the word "process" in the question to "thread" (and run `top -H' instead of `top'). My monitoring script answers exactly that question.

To implement, save the following to a file called, say, perfmon.sh: 

#!/bin/bash
#perfmon.sh: Performance monitoring for MySQL. To correlate the two parts in the output, the first column PID (actually thread ID) in the first part (from `top -H') may match the first column "thread" in the second part (from performance_schema.threads inside MySQL, almost the same as `show processlist'). So you know which thread uses the most CPU.

cd /u03/app/mysql/scripts/perfmon

LINES=15 COLUMNS=132 top -H -p $(pgrep -x mysqld) -wcbn2 | grep -A14 '^top -' | tail -15 >> perfmon.log

echo "
thread  pl_id   user            host            database        command state           info                            conn_type" >> perfmon.log

#make sure ~/.my.cnf has client credentials
mysql -N <<EOF >> perfmon.log
select thread_os_id, processlist_id, rpad(processlist_user,15,' '), substr(processlist_host,1,15), processlist_db, processlist_command, processlist_state, substr(processlist_info,1,30), connection_type from performance_schema.threads where processlist_id!=(select connection_id()) and  processlist_command is not null and processlist_state is not null order by 1;
EOF
echo "" >> perfmon.log
Make sure you have 
[client]
user=root
password=MySecretPassword123~!@#$
in ~/.my.cnf to avoid putting the password in the script, and chmod 600 ~/.my.cnf as it has the password. Alternatively, you can pass the password to the mysql command in the script. Adjust spacing on the line below echo for the output header and/or the select-list (with rpad or substr). It's hard to align all columns, though. Then schedule a cron job to run it once per minute, rotate the log every day, and keep the logs for a month: 
* * * * * /u03/app/mysql/scripts/perfmon/perfmon.sh &> /tmp/perfmon.out
58 23 * * * mv /u03/app/mysql/scripts/perfmon/perfmon.log /u03/app/mysql/scripts/perfmon/perfmon.log.$(/bin/date +'\%d') &> /tmp/rotateperfmonlog.out

Quite often one or two threads using a lot of CPU are not found inside MySQL performance_schema.threads or "show processlist". They're probably some sort of background threads. I'm still researching it. The where-clause for my query of performance_schema.threads may not be ideal. I want to keep it restrictive enough to only capture essential threads but don't want to miss anything important.

(The idea of this script comes from my Perfmon script for Oracle, which our shop has been running since 2008.)

August 2025