Monday, November 16, 2015

Script for checking database connections and queries taking maximum time

If you want to monitor the database connections , you can use this script. The script checks for the  connections count every minute and sends an email whenever the connections crosses the specified threshold. Same can be done via some monitoring tool(Nagios) also.

But you can customise the script to check the queries running at the time your database connections were high. This will help you to find the rogue query which might be causing the issue and will present you the better results for the troubleshooting the problem.

 #####check the number of connections established  
 mysql --user="db_user" --password='db_password' --host="" --execute="show status like 'Threads_connected'" > /tmp/db.log;  
 conn_count=`cat /tmp/db.log | grep -i threads | awk '{print $2}'`  
 #####Compare the established connections with the threshold in our case its 1500  
 if [ $conn_count -gt 1500 ]  
 #### Create a file with all the queries running at that time  
 mysql --user="db_user" --password='db_password' --host="" --execute="show full processlist \G" > /tmp/dbquery.txt  
 #### Create a file isolating the queries only in descending order of there execution time with the query taking most time to execute coming first  
 mysql --user="db_user" --password='db_password' --host="" --execute="show full processlist \G" | grep -v "row" | grep -v Id | grep -v User | grep -v Host | grep -v db | grep -v Command | sed 's/  Info://g' | sed 's/  Time://g' | sed 'N;s/\n/ /' | grep -v NULL > /tmp/maxtime-db-query.txt  
 #### Sent an Alert mail with connection count and attaching the Queries with the mail for troubleshooting and establing root cause  
 echo "High DB connections($conn_count) on Database_name at $date, DB Queries attached " | /bin/mail -v -s "Alert: High DB connections" -a /tmp/dbquery.txt -a /tmp/maxtime-db-query.txt [email protected]  
 #### Remove all the temporary files generated during the script execution  
 rm -rf /tmp/db.log  
 rm -rf /tmp/dbquery.txt  
 rm -rf /tmp/maxtime-db-query.txt  


Post a Comment