Building useful command lines, step by step

Posted by Chris on September 28th, 2009 filed in sysadmin

1- I tend to have tons of things running at a time and want to watch out of a corner of my eye.  Since I’m doing some pretty heavy stat stuff that can end up killing a machine’s memory in a short amount of time, I want to watch that too.

Enter ‘watch’.  Here’s how it goes – watch -n<number of seconds to wait before refreshing> <command to do this to>

And here’s my big watch command (which I’ve aliased to watcher).  In this I’m looking at perl and python processes, the amount of free memory and the cpu usage, and any mysql processes going on.

watch -n1 ' ps aux | egrep "(perl|python)" | grep -v grep;uptime;free -m; echo "show processlist" | mysql -uroot | grep -v Sleep'

This shows details on any perl or python processes (without the grep command itself), the load, the memory state, and any non-sleeping mysql processes.  Every second.   I just keep it up in a corner of the monitor.

2. Semi-related – killing a bunch of mysql processes.

Sometimes I’ll fire up a bunch of processes and decide they should go away.  Easy – killall perl works wonders.  But the mysql processes remain – enter another one:

echo "show processlist" | mysql |egrep -v "(Id|processlist)" | awk {'print "kill "$1";"'} | mysql

What it does:  'echo "show proesslist" ' just echoes “show processlist” to standard output.  That is then piped to the input of mysql.

#echo "show processlist" | mysql
Id User Host db Command Time State Info
455093 root localhost NULL Query 0 NULL show processlist

Next, it reverse-greps (as I like to call it) for ‘Id’ because I don’t want to see that.

# echo "show processlist" | mysql |grep -v Id
455095 root localhost NULL Query 0 NULL show processlist

Next, it inputs it into a very short awk program – what this does is split it up by spaces, and set each of those to a $<number> variable.  So we print "kill $1;" to standard out – that’ll be the command we want to send to mysql to kill them all. So we end up with:

# echo "show processlist" | mysql |grep -v Id| awk {'print "kill "$1";"'}
kill 455098;

Finally, pipe that into mysql, like so:

# echo "show processlist" | mysql |grep -v Id| awk {'print "kill "$1";"'} | mysql
ERROR 1094 (HY000) at line 1: Unknown thread id: 455101

What happened there?  Well, since I’ve been killing threads for a good two minutes now while working on this shortcut the only thread left is the “show processlist” thread – which ends as soon as the processlist is shown.  Which makes sense.  So cheat and either add another grep -v to get rid of it or  egrep with a simple regex: egrep -v "(Id|processlist)"

#echo "show processlist" | mysql |egrep -v "(Id|processlist)" | awk {'print "kill "$1";"'} | mysql

Yes, both of these could be done in better ways!   But in my defense, part of doing these things is to share them with others and help them be better.  Just like using simple perl is better a lot of the time than really complex code, simple things that are easy to handle are easier to show others and let them modify.  Plus, this is actually how I go about helping someone solve one of these problems so they can do it themselves in the future.

Oh, and now I can just send this link…

Leave a Comment