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 - 08:46:04 up 62 days, 22:36, 1 user, load average: 6.25, 6.22, 6.19
Threads: 49 total, 6 running, 43 sleeping, 0 stopped, 0 zombie
%Cpu(s): 92.1 us, 2.4 sy, 0.0 ni, 4.6 id, 0.1 wa, 0.6 hi, 0.2 si, 0.0 st
MiB Mem : 31836.1 total, 793.0 free, 27163.8 used, 4298.5 buff/cache
MiB Swap: 16384.0 total, 12384.8 free, 3999.2 used. 4672.3 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
931809 mysql 20 0 28.7g 886796 16768 R 99.7 2.7 1106:37 connection
1444635 mysql 20 0 28.7g 886796 16768 R 98.7 2.7 491:06.94 connection
927397 mysql 20 0 28.7g 886796 16768 R 98.3 2.7 751:09.03 connection
931774 mysql 20 0 28.7g 886796 16768 R 97.3 2.7 827:58.07 connection
927974 mysql 20 0 28.7g 886796 16768 R 90.7 2.7 568:15.28 connection
927386 mysql 20 0 28.7g 886796 16768 R 70.3 2.7 1055:15 connection
823761 mysql 20 0 28.7g 886796 16768 S 0.3 2.7 24:29.11 ib_log_flush
927387 mysql 20 0 28.7g 886796 16768 S 0.3 2.7 825:40.81 connection
823746 mysql 20 0 28.7g 886796 16768 S 0.0 2.7 0:10.28 mysqld
823749 mysql 20 0 28.7g 886796 16768 S 0.0 2.7 0:05.52 ib_io_ibuf
823750 mysql 20 0 28.7g 886796 16768 S 0.0 2.7 0:42.89 ib_io_rd-1
823751 mysql 20 0 28.7g 886796 16768 S 0.0 2.7 0:42.99 ib_io_rd-2
823752 mysql 20 0 28.7g 886796 16768 S 0.0 2.7 0:43.00 ib_io_rd-3
823753 mysql 20 0 28.7g 886796 16768 S 0.0 2.7 0:42.90 ib_io_rd-4
thread pl_id user host database command state info conn_type
823746 NULL NULL NULL mysql NULL NULL NULL NULL
823783 NULL NULL NULL NULL NULL waiting for handler commit NULL NULL
823785 5 event_scheduler localhost NULL Daemon Waiting on empty queue NULL NULL
823789 7 NULL NULL NULL Daemon Suspending NULL NULL
927386 261161 redcapstg_user ourappserver123 redcap_stg Execute executing SELECT DISTINCT p.project_id F TCP/IP
927397 259909 redcapstg_user ourappserver123 redcap_stg Query executing select distinct record, event_ TCP/IP
927453 261163 root localhost NULL Query executing select thread_os_id, processli Socket
927974 260119 redcapstg_user ourappserver123 redcap_stg Query executing select distinct record, event_ TCP/IP
931774 260589 redcapstg_user ourappserver123 redcap_stg Query executing select distinct record, event_ TCP/IP
931809 259962 redcapstg_user ourappserver123 redcap_stg Query executing select distinct record, event_ TCP/IP
1444635 260225 redcapstg_user ourappserver123 redcap_stg Query executing select distinct record, event_ TCP/IP
1444927 260100 redcapstg_user ourappserver123 redcap_stg Sleep NULL NULL TCP/IP
The above shows that mysqld thread with ID (incorrectly called "PID" by "top") 931809 uses the most CPU, 99.7%, and it is a connection thread (this thread name would all be mysqld for old versions). It matches processlist_id 259962 inside MySQL, which you could see if you were to run "show processlist". The session comes from ourappserver123 and is running SQL "select distinct record, event_ ..." 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 first 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 top -H -p $(pgrep -x mysqld) -wbn2 | grep -A20 '^top -' | tail -21 >> 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,' '), left(processlist_host,15), processlist_db, processlist_command, processlist_state, left(processlist_info,30), connection_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.logChange 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 spacing on the line below echo for the output header and/or the select-list of the SQL (with rpad or left). It's hard to align all columns, though. 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 58 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 the 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 2025