Monitor NDB memory usage with Nagios

We are planning to put online a MySQL NDB Cluster soon (more on this in another post), and one thing you have to do before putting anything in production is to monitor it for problems. In the case of a NDB cluster, you should care about monitoring your limited resources – basically because is a in-memory database – and be alerted when your developers are filling up the dedicated tablespace.

You can do this in two ways: performing a SELECT on the ndbinfo database through the MySQL interface to NDB or parsing the ndb_mgm output. I prefer the latter because maybe I’m using another frontend to the data (native NDB API, memcached etc) and I don’t want to maintain a MySQL server frontend just to check how much space I still have free.

So, you can use this script on GitHub

https://github.com/vide/nagios-ndb/blob/master/check_ndb_usage.sh

to parse its output and know if your tablesapce is OK, warning or critical. Feel free to post any comment, fork it and send patches! :)

Advertisements

A handful of bash tips – part 1

During last weeks I’ve been doing lot of scripting (a user management system for our internal infrastructure which is formed by lots of services) and it ended being something like 3000 lines of bash code. Not too much but probably this is my greatest experience til now with bash :)

So, I’ve learnt a couple of things with this project and I’d like to share some tips and lessons learnt during the process. I’m not a bash guru, and if you find something could be improved, feel free to leave a comment.

Stand on the giant’s shoulders

You can find lot of docs on bash, like the Advanced Bash Scripting Guide, but these are better in my opinion

and the #bash IRC channel on irc.freenode.net. Just lurking the conversations will teach you lot of things

Use the latest and greatest bash version

This means right now to use Bash4. I mean, if you are using a shell to script things, use the latest version and take advantage of all its features! Really, I don’t care about portability, I script for my systems where I have full control. I’ve upgraded a Debian box from Lenny to Squeeze just to get bash4, go figure :) Obviously if you cannot update easily or you need to be portable, don’t fall in the bashisms trap, try to be as POSIX as possible and discard my tip(s).

Quote everything. I mean, really everything

If you are used to the simple $VARIABLE form, drop it right now and use always “${VARIABLE}”. It’s cleaner, it’s safer, it supports white spaces in the variable content etc.

Only the first quote matters

I’ve seen very often (and I was using this too) escaped quote chars when creating queries or strings to be passed somewhere. For example

QUERY="SELECT * FROM foo WHERE name=\"${VAR}\";"

because you fear that ${VAR} won’t be expanded if single quoted. But what really matters in this case is the first double quote. bash will interpret every other single quote before the closing double quote as a normal char, and pass it to the next hop. So this is perfectly right:

QUERY="SELECT * FROM foo WHERE name='${VAR}';"

${VAR} will be expanded as expected. This is quite useful if calling another script with parameters via ssh, and leave a cleaner syntax (I hate escaping chars)

ssh user@host "/path/to/script 'foobar goes first' 'second parameter'"

Use shift when receiving parameters

Probably you are already doing this, anyway it’s a lesson learnt in these days. If you’re are passing parameters to functions/other scripts instead of

PARAM1="${1}"
PARAM2="${2}"
# etc

use the power of shift. If you are going to change your mind about those parameters, you won’t need to rename everything.

PARAM1="${1}"
shift 1
PARAM2="${1}"
shift 1
# etc

Use input redirection instead of pipes when possible

A classic example

cat /path/to/mylst|while read foo
do
# do somtething with foo
done

should be

while read foo
# do something with foo
done < /path/to/mylist

why? because input redirection it is meant to do that!

Convert pwdLastSet to a human readable date

Here it is a simple (and a bit hacky, I know) one-liner for bash shell (even under Windows if you are using Cygwin) to convert the cryptic pwdLastSet timestamp of Active Directory (which represent when a user has changed the last time his/her AD password)


D=128457325992343750; date -d "01/01/1601 UTC $(let D=D/10000000; echo $D) seconds"

where the very large number after the first D= it’s your pwdLastSet value. This strange timestamp it’s a 1/100 of a nanosecond (so, it’s 1/10^7 seconds) and the ticks are counted from January 1st, 1601. Don’t ask me why, probably they didn’t like the Epoch time :)

Kill every MySQL SELECT older than X seconds

If you’re managing large MySQL installations where lot of queries (written by lot of people) are executed, this is going to sound familiar.  Sometimes due to some faulty query or to heavy traffic, your MySQL server could be overloaded and queries start to stall there, making your PROCESS LIST grow and grow and grow…

So, here it is a quick script that kills every SELECT (we don’t want to kill other query types) older than a certain amount of second and that can help you when you find yourself in one of these situations.

It’s rather quick’n’dirty, but for it’s small use case, it works. It expects GNU userland


#!/bin/bash
SEC=$1
IFS='|'
if [[ $SEC -lt 1 ]]; then
echo "Usage: $0 SECONDS"
exit 1
fi
mysqladmin proc -v|grep Query|grep -Evi "delete|update|insert|alter table" |while read dummy qid qusr qhost qdb qstat qsec qstat2 query
do
if [ $qsec -gt $SEC ]; then
echo "Killing query $qid..."
mysqladmin kill $qid
fi
done