Following my article Everything fails, Monitor Everything , and some inquiries, I’ve made some small modifications to my initially hourly script. This script is still a quick and dirty trial of what I’m wanting to develop, but in true Guy Kawasaki terms “5. Don’t worry, be crappy”. It works for now, and enables me to determine what works and what doesn’t.
My goals are Data Collection, Data Analysis and Data Presentation. This is the start of Data Collection. So now I get the following files:
- os.vmstat.070524.122054.log
- os.ps.070524.122054.log
- mysql.innodbstatus.070524.122054.log
- mysql.processlist.070524.122054.log
- mysql.status.070524.122054.log
- mysql.tablestatus.070524.122054.log
- mysql.tablestatus.vertical.070524.122054.log
- mysql.variables.070524.122054.log
#!/bin/sh # Name: hourly # Purpose: Script to 'cron' hourly to run for monitoring # Author: Ronald Bradford error() { echo "ERROR: $1" exit 1 } MYSQL_AUTHENTICATION=".mysql.authentication" [ ! -f $MYSQL_AUTHENTICATION ] && error "You must specific MySQL Authentication in $MYSQL_AUTHENTICATION" [ -z `which mysqladmin` ] && error "mysqladmin must be in the PATH" DATETIME_FORMAT="+%y%m%d.%H%M%S" DATETIME=`date $DATETIME_FORMAT` DATABASE="test" AUTHENTICATION=`cat $MYSQL_AUTHENTICATION` # run vmstat every second for 1 hour # normally this is an overkill 5 seconds is acceptable # but need to monitoring any spike VMSTAT_OPTIONS="1 3600" LOG_FILE="os.vmstat.$DATETIME.log" echo "INFO: Logging vmstat $VMSTAT_OPTIONS to $LOG_FILE" vmstat $VMSTAT_OPTIONS > $LOG_FILE 2>&1 & LOG_FILE="os.ps.$DATETIME.log" echo "INFO: Logging ps to $LOG_FILE" ps -ef > $LOG_FILE 2>&1 & LOG_FILE="mysql.variables.$DATETIME.log" echo "INFO: Logging mysqladmin variables to $LOG_FILE" echo "| date_time | $DATETIME |" > $LOG_FILE mysqladmin $AUTHENTICATION variables >> $LOG_FILE 2>&1 & LOG_FILE="mysql.tablestatus.vertical.$DATETIME.log" mysql $AUTHENTICATION $DATABASE -e "SHOW TABLE STATUSG" > $LOG_FILE 2>&1 & LOG_FILE="mysql.tablestatus.$DATETIME.log" mysql $AUTHENTICATION $DATABASE -e "SHOW TABLE STATUS" > $LOG_FILE 2>&1 & COUNT=0 MAX_COUNT=60 SLEEP_TIME=60 LOG_FILE1="mysql.status.$DATETIME.log" LOG_FILE2="mysql.processlist.$DATETIME.log" LOG_FILE3="mysql.innodbstatus.$DATETIME.log" > $LOG_FILE1 > $LOG_FILE2 > $LOG_FILE3 echo "INFO: Logging mysqladmin extended-status per $SLEEP_TIME secs for $MAX_COUNT times to $LOG_FILE1" echo "INFO: Logging mysqladmin processlist per $SLEEP_TIME secs for $MAX_COUNT times to $LOG_FILE2" echo "INFO: Logging mysql show innodb status per $SLEEP_TIME secs for $MAX_COUNT times to $LOG_FILE3" while [ $COUNT -lt $MAX_COUNT ] do NOW=`date $DATETIME_FORMAT` echo "| date_time | $NOW |" >> $LOG_FILE1 echo $NOW >> $LOG_FILE2 echo $NOW >> $LOG_FILE3 mysqladmin $AUTHENTICATION extended-status >> $LOG_FILE1 mysqladmin --verbose $AUTHENTICATION processlist >> $LOG_FILE2 mysql $AUTHENTICATION $DATABASE -e "SHOW INNODB STATUS\G" >> $LOG_FILE3 2>&1 & COUNT=`expr $COUNT + 1` sleep $SLEEP_TIME done exit 0
So from here, I need to:
- Put into my standard sh script framework which provides correct logging, message management and true parameterization.
- Additional pre-checks for the correct security requirements
- Revised Parameterised settings including database
- Host and Instance logging
- Additional file parsing for later Data Analysis and Data Presentation.