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 - 23:42:05 up 85 days, 15:37,  1 user,  load average: 6.03, 6.67, 6.83
Threads:  61 total,   5 running,  56 sleeping,   0 stopped,   0 zombie
%Cpu(s): 15.1 us,  1.6 sy,  0.0 ni, 80.1 id,  2.5 wa,  0.5 hi,  0.2 si,  0.0 st
MiB Mem : 128268.4 total,   1794.7 free, 109256.4 used,  18673.4 buff/cache
MiB Swap:  16384.0 total,   6172.9 free,  10211.1 used.  19012.1 avail Mem

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
2149424 mysql     20   0  113.9g 102.3g  18020 R  99.9  81.7 374:21.18 connection
2087574 mysql     20   0  113.9g 102.3g  18020 R  99.3  81.7 288:18.76 connection
2175715 mysql     20   0  113.9g 102.3g  18020 R  58.7  81.7 101:19.03 connection
2061457 mysql     20   0  113.9g 102.3g  18020 R  50.0  81.7 176:12.61 connection
2269894 mysql     20   0  113.9g 102.3g  18020 D  26.0  81.7   1:18.47 connection
2079706 mysql     20   0  113.9g 102.3g  18020 R  17.7  81.7 159:41.62 connection
2099073 mysql     20   0  113.9g 102.3g  18020 S   8.7  81.7 142:40.14 connection
2087351 mysql     20   0  113.9g 102.3g  18020 S   2.3  81.7 166:35.90 connection
2104853 mysql     20   0  113.9g 102.3g  18020 S   2.3  81.7 154:18.86 connection
3406454 mysql     20   0  113.9g 102.3g  18020 S   2.0  81.7 595:07.30 ib_log_flush
3406483 mysql     20   0  113.9g 102.3g  18020 S   1.7  81.7 172:18.95 ib_srv_wkr-3
3406456 mysql     20   0  113.9g 102.3g  18020 S   1.3  81.7 502:12.16 ib_log_writer
3406453 mysql     20   0  113.9g 102.3g  18020 S   1.0  81.7 181:24.94 ib_log_fl_notif
3406480 mysql     20   0  113.9g 102.3g  18020 S   0.7  81.7 366:04.34 ib_srv_purge
+---------+---------+-----------------+-----------------+--------+---------+----------------------------+--------------------------------+-----------+
| thread  | pl_id   | user            | host            | db     | command | state                      | info                           | conn_type |
+---------+---------+-----------------+-----------------+--------+---------+----------------------------+--------------------------------+-----------+
| 2014901 | 8683116 | redcap_owner    | theappserver123 | redcap | Sleep   | NULL                       | NULL                           | TCP/IP    |
| 2038869 | 8684638 | root            | localhost       | NULL   | Query   | executing                  | select thread_os_id thread, pr | Socket    |
| 2061457 | 8684634 | redcap_owner    | theappserver123 | redcap | Query   | Opening tables             | select ss_id from redcap_surve | TCP/IP    |
| 2079706 | 8684540 | redcap_owner    | theappserver123 | redcap | Query   | waiting for handler commit | INSERT INTO redcap_data2 (proj | TCP/IP    |
| 2087574 | 8682169 | redcap_owner    | theappserver123 | redcap | Query   | executing                  | SELECT `normalized_instance` F | TCP/IP    |
| 2099073 | 8683561 | redcap_owner    | theappserver123 | redcap | Sleep   | NULL                       | NULL                           | TCP/IP    |
| 2133067 | 8684629 | redcap_owner    | theappserver12. | redcap | Sleep   | NULL                       | NULL                           | TCP/IP    |
| 2149423 | 8684482 | redcap_owner    | theappserver123 | redcap | Sleep   | NULL                       | NULL                           | TCP/IP    |
| 2149424 | 8648098 | redcap_owner    | theappserver123 | redcap | Query   | executing                  | SELECT `normalized_instance` F | TCP/IP    |
| 2175715 | 8682541 | redcap_owner    | theappserver123 | redcap | Query   | executing                  | select r.completion_time from  | TCP/IP    |
| 2269894 | 8684397 | redcap_owner    | theappserver123 | redcap | Query   | waiting for handler commit | INSERT INTO redcap_data2 (proj | TCP/IP    |
| 3406387 |    NULL | NULL            | NULL            | mysql  | NULL    | NULL                       | NULL                           | NULL      |
| 3406479 |    NULL | NULL            | NULL            | NULL   | NULL    | waiting for handler commit | NULL                           | NULL      |
| 3406480 |    NULL | NULL            | NULL            | NULL   | NULL    | waiting for handler commit | NULL                           | NULL      |
| 3406484 |       5 | event_scheduler | localhost       | NULL   | Daemon  | Waiting on empty queue     | NULL                           | NULL      |
| 3406488 |       7 | NULL            | NULL            | NULL   | Daemon  | Suspending                 | NULL                           | NULL      |
+---------+---------+-----------------+-----------------+--------+---------+----------------------------+--------------------------------+-----------+

The above shows that mysqld thread with ID (incorrectly called "PID" by "top") 2149424 uses the most CPU, 99.9%, and it is a connection thread (this thread name would all be mysqld for old versions of MySQL). It matches processlist_id 8648098 inside MySQL, which you would see if you were to run "show processlist" with mysql client. The session comes from theappserver123 (name changed for innocence) and is running SQL "SELECT `normalized_instance` F ..." in the database redcap. 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 first is, What is the top CPU hogging process doing? Since MySQL runs in the 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

top -H -p $(pgrep -x mysqld) -wbn2 | grep -A20 '^top -' | tail -21 >> perfmon.log

#make sure ~/.my.cnf has client credentials
mysql -t <<EOF >> perfmon.log
select thread_os_id thread, processlist_id pl_id, rpad(processlist_user,15,' ') user, substr(processlist_host,1,15) host, processlist_db db, processlist_command command, processlist_state state, substr(processlist_info,1,30) info, connection_type conn_type from performance_schema.threads where coalesce(processlist_id,processlist_user,processlist_host,processlist_db,processlist_command,processlist_state,processlist_info,connection_type) is not null order by 1;
EOF

echo "" >> perfmon.log
Change the directory on the cd line to where this script is. 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 to limit read access. (Alternatively, you can pass the password to the mysql command in the script.) Adjust the where-clause as needed. 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
59 23 * * * mv /u03/app/mysql/scripts/perfmon/perfmon.log /u03/app/mysql/scripts/perfmon/perfmon.log.$(/bin/date +'\%d') &> /tmp/rotateperfmonlog.out

Sometimes 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 not accounted for by mysqld, or they just finished using much CPU by the time this Perfmon query is run. The current where-clause for my query of performance_schema.threads is not ideal. For now, I'm just excluding the rows with NULL in all columns.

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

August,September 2025