Skip to content

Instantly share code, notes, and snippets.

@detain
Last active August 16, 2020 13:59
Show Gist options
  • Save detain/11354276 to your computer and use it in GitHub Desktop.
Save detain/11354276 to your computer and use it in GitHub Desktop.
mytop (Monitor mysql process list with top like screen updates and adiditonal stats like cpu/io usage)
#!/bin/bash
# mytop.sh - (Monitor mysql process list with top like screen updates and adiditonal stats like cpu/io usage)
#
# Sample Output:
#
# CPU Usage 3.29% user 1.60% system 3.37% io wait 0.00% steal 91.59% idle
# Up 17 days 10 hours Load Avg 0.43 0.36 0.35 Processes 1(running) 408(total) Last PID 17825
# +----------+------+-----------+----+---------+------+-------+------------------+
# | Id | User | Host | db | Command | Time | State | Info |
# +----------+------+-----------+----+---------+------+-------+------------------+
# | 49117793 | root | localhost | | Query | 0 | | show processlist |
# +----------+------+-----------+----+---------+------+-------+------------------+
# Uptime: 1507802 Threads: 1 Questions: 880170604 Slow queries: 4631149
# Opens: 17561 Flush tables: 117 Open tables: 204 Queries per second avg: 583.744
# (q)uit updating
#
# alternative to using this shell script is creating an alias that does it so its stored in memory instead of read from script file
# i suggest adding this to like /etc/bashrc
#alias mytop=' delay=1; stop=no; while [ "$stop" = "no" ]; do clear; iostat -c|grep -v "^$" |tail -n 1 | awk "{ print \" CPU Usage \" \$1 \"% system \" \$4 \"% io wait \" \$5 \"% steal \" \$6 \"% idle\" }"; upsec=$(cut -d\. -f1 /proc/uptime); alias o="echo -n "; o " Up "; [ $upsec -lt 120 ] && o $upsec seconds || [ $upsec -lt 7200 ] && o $(($upsec / 60)) minutes || [ $upsec -lt 172800 ] && o $(($upsec / 3600)) hours || o $(($upsec / 86400)) days $(($(($upsec % 86400)) / 3600)) hours; o " "; awk "{ print \" Load Avg \" \$1 \" \" \$2 \" \" \$3 \" Processes \" \$4 \"(total) Last PID \" \$5 }" /proc/loadavg | sed s#"/"#"(running) "#g; mysqladmin processlist; mysqladmin status | sed s#"Opens:"#"\nOpens:"#g; read -p "(q)uit updating" -n 1 -t $delay stopnow; [ "$stopnow" = "q" ] && stop=yes; done'
# then just type mytop to run
delay=1;
stop=no;
while [ "$stop" = "no" ]; do
clear;
iostat -c|grep -v "^$" |tail -n 1 | awk "{ print \" CPU Usage \" \$1 \"% system \" \$4 \"% io wait \" \$5 \"% steal \" \$6 \"% idle\" }";
upsec=$(cut -d\. -f1 /proc/uptime);
echo -n " Up ";
[ $upsec -lt 120 ] && echo -n $upsec seconds || [ $upsec -lt 7200 ] && echo -n $(($upsec / 60)) minutes || [ $upsec -lt 172800 ] && echo -n $(($upsec / 3600)) hours || echo -n $(($upsec / 86400)) days $(($(($upsec % 86400)) / 3600)) hours;
echo -n " ";
awk '{ print " Load Avg " $1 " " $2 " " $3 " Processes " $4 "(total) Last PID " $5 }' /proc/loadavg | sed s#"/"#"(running) "#g;
mysqladmin processlist;
mysqladmin status | sed s#"Opens:"#"\nOpens:"#g
read -p "(q)uit updating" -n 1 -t $delay stopnow;
[ "$stopnow" = "q" ] && stop=yes;
done
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment