Posts

Showing posts from August, 2016

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/ap…

How to install Percona MySQL tools

Persona has lots of tools to analyses MySQL data and very useful information can be extracted from them.

Installing via Yum:
[root@ip-10-0-1-220 ravi]# yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm Retrieving http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm Preparing... ########################################### [100%] 1:percona-release ########################################### [100%] [root@ip-10-0-1-220 ravi]# yum install percona-toolkit Loaded plugins: auto-update-debuginfo, priorities, update-motd, upgrade-helper 1123 packages excluded due to repository priority protections Resolving Dependencies --> Running transaction check ---> Package percona-toolkit.noarch 0:2.2.18-1 will be installed --> Processing Dependency: perl(DBD::mysql) >= 1.0 for package: percona-toolkit-2.2.18-1.noarch --> Processing Dependency: perl(DBI) >= 1.13 for…

How to setup AWS RDS CLI

AWS provides the its CLI tools but few of RDS functionalities don't work on them so in order to make them work AWS RDS CLI is very helpful.

Download the RDS CLI:
[root@server downloads]# wget http://s3.amazonaws.com/rds-downloads/RDSCli.zip
Unzip the downloaded file and copy it in desired location:
[root@server downloads]# unzip RDSCli.zip[root@server downloads]# cp -r RDSCli-1.19.004 /opt/aws/apitools/rds [root@server downloads]# cd /opt/aws/apitools/rds/bin
Check RDS CLI version:
[root@server bin]# ./rds-version Relational Database Service CLI version 1.19.004 (API 2014-10-31)
These commands can be added in system environment so you can run them from anywhere:
[root@server bin]# export PATH=/sbin:/bin:/usr/sbin:/usr/bin:/opt/aws/apitools/rds/bin:/usr/local/bin
The credential file is also need to be created so that these commands can be run taking permissions in to consideration from that file and file can be added in system environment so we don't need to pass every time in co…

Script to add logs in Logentires in case of Shared hosting Web Server.

Logentries is a very good tool to store and analyze logs in central location but when we are storing logs from a  Nginx/Apache shared hosting environment it gets complex as we need to tag each log to which host it belong. I am using rsyslog to forward to send my logs to Logentries as it gives me more flexibility as all rsyslog functionality works.

First need to create a main configuration file which we will use to create corresponding rsyslog files. It will have your Logentries secret key to whom log need to be followed.

For access log:
[root@ip-10-0-1-220 ravi]# cat access-vanila $Modload imfile $InputFileName access-log-location $InputFileTag access-tag $InputFileStateFile filestate-tag $InputFileSeverity info $InputFileFacility local7 $InputRunFileMonitor # Only entered once in case of following multiple files $InputFilePollInterval 1 $template filestate-tag,"6fb8xxxxxxxxxxxxxxxxxxxxxxe8ed %HOSTNAME% %syslogtag% %msg%\n" if $programname == 'access-tag' then @@…