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.
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.
#!/bin/bash
date=`date`
#####check the number of connections established
mysql --user="db_user" --password='db_password' --host="rds-name.ap-southeast-1.rds.amazonaws.com" --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 ]
then
#### Create a file with all the queries running at that time
mysql --user="db_user" --password='db_password' --host="rds-name.ap-southeast-1.rds.amazonaws.com" --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="rds-name.ap-southeast-1.rds.amazonaws.com" --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]
fi
#### 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
0 comments:
Post a Comment