mytop (Monitor mysql process list with top like screen updates and adiditonal stats like cpu/io usage)

mytop.sh

#!/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