How to analyze AWS RDS Slow Query

Amazon RDS has the feature in which we can log the slow queries and than analyze them to find the queries which are making our Database slow.
There are few prerequisites to this:
1. Slow log query should be enabled in RDS.
2. AWS RDS CLI should be present.
3. Percona Toolkit should be present.

Once you have all the prerequisites than following script will help us in rest.
[root@ip-10-0-1-220 ravi]# cat rds-slowlog.sh 
#!/bin/bash
#Script to Analyze AWS RDS MySQL logs via Percona Toolkit
#By Ravi Gadgil

#To get list of all slow logs available.
/opt/aws/apitools/rds/bin/rds-describe-db-log-files --db-instance-identifier teamie-production --aws-credential-file /opt/aws/apitools/rds/credential | awk '{print $2 }' | grep slow > /home/ravi/slowlog.txt

logfile=$(echo -e "slowlog-`date +%F-%H-%M`")
resultfile=$(echo -e "resultlog-`date +%F-%H-%M`")

for i in `cat /home/ravi/slowlog.txt` ; do

#To download Slow Log files and add them to single file.
/opt/aws/apitools/rds/bin/rds-download-db-logfile teamie-production --log-file-name $i --debug --connection-timeout 3600 --aws-credential-file /opt/aws/apitools/rds/credential >> /data/rds-logs/$logfile

done

#To run analysis on slowlog file.

pt-query-digest /data/rds-logs/$logfile > /data/rds-logs/$resultfile

rm -rf /data/rds-logs/$logfile

Note: I have not set the system environment paths for RDS commands and credentials file so using the whole relative path in command, If you have set system environment variable than whole relative path is not needed.





Comments

Popular posts from this blog

Script to create daily AMI of AWS server and delete AMI older than n no of days..

How to delete multiple user in linux

How to create users from ansible with public key and password.