Loading...

Automatic Restart of MySQL Server on High Load

There was an instance of a server running a Java webapp bringing down the whole system causing SQL Exceptions. Upon checking of the server, it was found out that the MySQL server was running at high load and was unresponsive and unable to accept any further requests. A restart of the database server was necessary. However, since this webapp has a tendency to run lots of transactions per second and cause the database to strain under the load (most likely some of the code needs some optimizations to avoid hogging the db connections), and the source code was unavailable to debug, I had to think of a way to force a restart of the MySQL server when it reaches over a 100% CPU resource load.

This is what I came up with, a bash script to check MySQL CPU load and restart the server if necessary:

#!/bin/bash
if [ ! -f /<path to>/cpuload.txt ]; then
echo "cpuload.txt not found, creating one"
echo 0 > /<path to>/cpuload.txt
fi

cpuload=`ps -eo uname,nlwp,pcpu,pmem |grep mysql|awk ‘{print $3}’`
cpuload=`echo “$cpuload*100″|bc`

echo “Current MySQL load at $cpuload%”

# This one converts to integer
cpuload=${cpuload/.*}
oldcpuload=`cat /<path to>/cpuload.txt`

echo “Last recorded MySQL load at $oldcpuload%”

if [ $cpuload -gt 100 ] && [ $oldcpuload -gt 100 ]; then
echo “above cpu load, restarting MySQL server”
/etc/init.d/mysqld restart
fi

echo $cpuload > /<path to>/cpuload.txt

You’ll have to add a cron job to this script to make it run either every minute or every 3 minutes:

# crontab -e
For every 3 minutes:
*/3 * * * * /[path to automated script] > /dev/null
For every minute:
* * * * * /[path to automated script] > /dev/null

Please take note that is should be a stop gap measure and the best way of avoiding this is to do the necessary code optimizations, clustering mysql for high load scenarios, etc (in my case, I didn’t have any choice as I didn’t have access to the source code). This bash script is very crude and made in a straightforward manner and may or may not work for you (this was running in a CentOS server).

 
 

Leave a Reply

Your email address will not be published. Required fields are marked *