Database monitoring

Apart from Nagios, Cacti, AWS graphs, several shell scripts been executed on the databases to gather stats timely.

gatherDBStats.ksh - Once every 1 hr
echo "Select Global status"
SELECT CONCAT(VARIABLE_NAME,'$del',VARIABLE_VALUE) FROM information_schema.GLOBAL_STATUS;

echo "Select Global Variables"
SELECT CONCAT(VARIABLE_NAME,'$del',VARIABLE_VALUE) FROM information_schema.GLOBAL_VARIABLES;

echo "Select DB Size"
SELECT CONCAT(sum(ROUND(data_length/1024/1024,2)),'$del',sum(ROUND(index_length/1024/1024,2)))
  FROM   information_schema.TABLES
  WHERE  table_schema = '$DB_NAME';

monitorDBStatus.ksh - Once every 5 minutes
echo "Select PROCESSLIST"
SELECT * FROM information_schema.PROCESSLIST;

echo "Select INNODB TRX"
SELECT * FROM information_schema.INNODB_TRX;

echo "Select INNODB TRX Locks"
SELECT * FROM information_schema.INNODB_LOCKS;

echo "Select INNODB TRX Lock Waits"
SELECT * FROM information_schema.INNODB_LOCK_WAITS

echo "Select InnoDB status"
SHOW ENGINE INNODB STATUS\G

echo "Checking Free memory"
free -m

echo "Checking MySQL Process Memory"
ps aux | grep -i %CPU | grep -v grep
ps aux | grep -i mysqld | grep -v grep

echo "Checking OS Memory"
top -n 1 -b | head -5

echo "Checking iostat"
iostat -d -x 5 1