Tuesday, August 16, 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 
#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


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

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 package: percona-toolkit-2.2.18-1.noarch
--> Running transaction check
---> Package perl-DBD-MySQL55.x86_64 0:4.023-5.23.amzn1 will be installed
---> Package perl-DBI.x86_64 0:1.627-4.8.amzn1 will be installed
--> Processing Dependency: perl(RPC::PlClient) >= 0.2000 for package: perl-DBI-1.627-4.8.amzn1.x86_64
--> Processing Dependency: perl(RPC::PlServer) >= 0.2001 for package: perl-DBI-1.627-4.8.amzn1.x86_64
--> Running transaction check
---> Package perl-PlRPC.noarch 0:0.2020-14.7.amzn1 will be installed
--> Processing Dependency: perl(Net::Daemon) >= 0.13 for package: perl-PlRPC-0.2020-14.7.amzn1.noarch
--> Processing Dependency: perl(Net::Daemon::Test) for package: perl-PlRPC-0.2020-14.7.amzn1.noarch
--> Processing Dependency: perl(Net::Daemon::Log) for package: perl-PlRPC-0.2020-14.7.amzn1.noarch
--> Running transaction check
---> Package perl-Net-Daemon.noarch 0:0.48-5.5.amzn1 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

 Package               Arch        Version                  Repository                   Size
 percona-toolkit       noarch      2.2.18-1                 percona-release-noarch      1.7 M
Installing for dependencies:
 perl-DBD-MySQL55      x86_64      4.023-5.23.amzn1         amzn-main                   149 k
 perl-DBI              x86_64      1.627-4.8.amzn1          amzn-main                   855 k
 perl-Net-Daemon       noarch      0.48-5.5.amzn1           amzn-main                    58 k
 perl-PlRPC            noarch      0.2020-14.7.amzn1        amzn-main                    39 k

Transaction Summary
Install  1 Package (+4 Dependent packages)

Total download size: 2.7 M
Installed size: 4.1 M
Is this ok [y/d/N]: y
Downloading packages:
warning: /var/cache/yum/x86_64/latest/percona-release-noarch/packages/percona-toolkit-2.2.18-1.noarch.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Public key for percona-toolkit-2.2.18-1.noarch.rpm is not installed
(1/5): percona-toolkit-2.2.18-1.noarch.rpm                             | 1.7 MB     00:01     
(2/5): perl-DBD-MySQL55-4.023-5.23.amzn1.x86_64.rpm                    | 149 kB     00:00     
(3/5): perl-DBI-1.627-4.8.amzn1.x86_64.rpm                             | 855 kB     00:00     
(4/5): perl-Net-Daemon-0.48-5.5.amzn1.noarch.rpm                       |  58 kB     00:00     
(5/5): perl-PlRPC-0.2020-14.7.amzn1.noarch.rpm                         |  39 kB     00:00     
Total                                                         1.3 MB/s | 2.7 MB  00:00:02     
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-Percona
Importing GPG key 0xCD2EFD2A:
 Userid     : "Percona MySQL Development Team <mysql-dev@percona.com>"
 Fingerprint: 430b df5c 56e7 c94e 848e e60c 1c4c bdcd cd2e fd2a
 Package    : percona-release-0.1-3.noarch (@/percona-release-0.1-3.noarch)
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-Percona
Is this ok [y/N]: y
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : perl-Net-Daemon-0.48-5.5.amzn1.noarch                                      1/5 
  Installing : perl-PlRPC-0.2020-14.7.amzn1.noarch                                        2/5 
  Installing : perl-DBI-1.627-4.8.amzn1.x86_64                                            3/5 
  Installing : perl-DBD-MySQL55-4.023-5.23.amzn1.x86_64                                   4/5 
  Installing : percona-toolkit-2.2.18-1.noarch                                            5/5 
  Verifying  : percona-toolkit-2.2.18-1.noarch                                            1/5 
  Verifying  : perl-DBD-MySQL55-4.023-5.23.amzn1.x86_64                                   2/5 
  Verifying  : perl-PlRPC-0.2020-14.7.amzn1.noarch                                        3/5 
  Verifying  : perl-Net-Daemon-0.48-5.5.amzn1.noarch                                      4/5 
  Verifying  : perl-DBI-1.627-4.8.amzn1.x86_64                                            5/5 

  percona-toolkit.noarch 0:2.2.18-1                                                           

Dependency Installed:
  perl-DBD-MySQL55.x86_64 0:4.023-5.23.amzn1       perl-DBI.x86_64 0:1.627-4.8.amzn1          
  perl-Net-Daemon.noarch 0:0.48-5.5.amzn1          perl-PlRPC.noarch 0:0.2020-14.7.amzn1      


If you want to install via RPM go to following link and download the latest version  and install via rpm -ivh package name.

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 command.
[root@server  rds]# cat credential
[root@server  rds]# export AWS_CREDENTIAL_FILE=/opt/aws/apitools/rds/credential

If you don't want to add credentials in environment variable than can be passed in command it self with following parameter followed by the path of credentials file: --aws-credential-file.

Wednesday, August 10, 2016

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

# Only entered once in case of following multiple files
$InputFilePollInterval 1

$template filestate-tag,"6fb8xxxxxxxxxxxxxxxxxxxxxxe8ed %HOSTNAME% %syslogtag% %msg%\n"
if $programname == 'access-tag' then @@data.logentries.com:10000;filestate-tag
& ~

For error log:
[root@ip-10-0-1-220 ravi]# cat error-vanila 
$Modload imfile

$InputFileName error-log-location
$InputFileTag error-tag
$InputFileStateFile filestate-tag
$InputFileSeverity info
$InputFileFacility local7

# Only entered once in case of following multiple files
$InputFilePollInterval 1

$template filestate-tag,"4f71xxxxxxxxxxxxxxxxxxxxxxx138 %HOSTNAME% %syslogtag% %msg%\n"
if $programname == 'error-tag' then @@data.logentries.com:10000;filestate-tag
& ~

Now use the following script to create rsyslog configurations to send logs to Logentries.
[root@ip-10-0-1-220 ravi]# cat logscript.sh 
#Script to add hosts to Logentries
#By Ravi Gadgil

#Path where nginx hosts configurations are placed /etc/nginx/sites-enabled/sites/
for j in `ls /etc/nginx/sites-enabled/sites/`; do cat /etc/nginx/sites-enabled/sites/$j | grep -m 1 access; cat /etc/nginx/sites-enabled/sites/$j | grep -m 1 error ; done | uniq  | grep 'teamieapp\|theteamie' > /home/ravi/data.txt

#Creating files having entries of access and error log, if want to avoid any log place instead of avoid in grep command.
cat data.txt | grep access.log | grep -v 'avoid' | awk '{ print $2 }' | cut -d';' -f1 > /home/ravi/accessfiles.txt
cat data.txt | grep error.log | grep -v 'avoid' | awk '{ print $2 }' | cut -d';' -f1 > /home/ravi/errorfiles.txt

#To create unique Filestate Tag in rsyslog.

#To create access log entries.
for i in `cat /home/ravi/accessfiles.txt`
logfileaccess=$( echo -e "$i" )
echo -e "Access Log file is : $logfileaccess"

accesstag=$(echo -e $logfileaccess | cut -d'/' -f5 | sed 's/.log//g' | sed "s/_/-/g" | sed "s/\./-/g")
echo -e "Access Tag is : $accesstag"


filestate=$(echo -e "nginx$COUNTER")
echo -e "File state is : $filestate"

cp /home/ravi/access-vanila /home/ravi/tempconf
sed -i "s#access-log-location#$logfileaccess#g" /home/ravi/tempconf
sed -i "s/access-tag/$accesstag/g" /home/ravi/tempconf
sed -i "s/filestate-tag/$filestate/g" /home/ravi/tempconf
mv /home/ravi/tempconf /home/ravi/conf/$accesstag.conf


#To create error log entries.
for i in `cat /home/ravi/errorfiles.txt`

logfileerror=$( echo -e "$i" )
echo -e "Error Log file is : $logfileerror"

errortag=$(echo -e $logfileerror | cut -d'/' -f5 | sed 's/.log//g' | sed "s/_/-/g" | sed "s/\./-/g")
echo -e "Error Tag is : $errortag"


filestate=$(echo -e "nginx$COUNTER")
echo -e "File state is : $filestate"

cp /home/ravi/error-vanila /home/ravi/tempconf
sed -i "s#error-log-location#$logfileerror#g" /home/ravi/tempconf
sed -i "s/error-tag/$errortag/g" /home/ravi/tempconf
sed -i "s/filestate-tag/$filestate/g" /home/ravi/tempconf
mv /home/ravi/tempconf /home/ravi/conf/$errortag.conf


1. Your log format should be in site_access.log and site_error.log if not in this format do adjust in script.
2. Script was working in /home/ravi directory so adjust script according to your paths.
3. Once all configurations are created add them to your rsyslog directory and restart service.
4. Rsyslog can only forward 100 files so do keep that in mind and check /var/log/messages for
 any errors if any.

Setup fully configurable EFK Elasticsearch Fluentd Kibana setup in Kubernetes

In the following setup, we will be creating a fully configurable Elasticsearch, Flunetd, Kibana setup better known as EKF setup. There is a...