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.
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.
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.