get_rds_slow_queries.sh

get_rds_slow_queries

#!/usr/bin/env python3
# run download rds log and extract slow queries
#  usage example: 
# ./get_rds_slow_queries.py --region eu-central-1 --rds-instance rds-instance-name --since 24

import boto3
import sys
import argparse
import datetime
import time
import json
import base64

from botocore.exceptions import ClientError

MAX_SINCE = 48
MAX_RECORDS = 99
LOG_FILE_TEMPLATE = '/tmp/{}_{}.log'
SLOW_LOG_GREP_TEMPLATE = "cat {} | grep -o '.\{{0,200\}}duration:.\{{0,200\}}' | sort -k5r"

parser = argparse.ArgumentParser(description='Extract slow queries')
parser.add_argument('--region', default='us-west-2')
parser.add_argument('--rds-instance', required=True, help='The RDS name')
parser.add_argument('--since', type=int, default=3, help='Start log in hours')

args = parser.parse_args()
region = args.region
rds_instance = args.rds_instance
since = args.since


# being lazy here
if since > MAX_SINCE:
    since  = 48
    print("Specified more than the max hours of: {}".format(MAX_SINCE))

since_in_hours = args.since
since = datetime.datetime.now() - datetime.timedelta(hours=args.since)

rds = boto3.client('rds', region)

log_file_details = rds.describe_db_log_files(
    DBInstanceIdentifier=rds_instance,
    FileLastWritten=int(since.timestamp()) * 1000,
    MaxRecords=MAX_RECORDS,
)

# maybe enforce download once using result hash to name log storage file
log_files = log_file_details['DescribeDBLogFiles']
local_log_file = LOG_FILE_TEMPLATE.format(rds_instance,time.time())

print("Downloaded rds log files will be saved in {}".format(local_log_file))

with open(local_log_file, 'w+') as f:
    token = '0'
    for log_file_item in log_files:
        log_file = log_file_item['LogFileName']
        print('downloading {rds} log file {file}'.format(rds=rds_instance, file=log_file))
        try:
            response = rds.download_db_log_file_portion(
                DBInstanceIdentifier=rds_instance,
                LogFileName=log_file,
                Marker=token)
            while response['AdditionalDataPending']:
                f.write(response['LogFileData'])
                token=response['Marker']
                response = rds.download_db_log_file_portion(
                    DBInstanceIdentifier=rds_instance,
                    LogFileName=log_file,
                    Marker=token)
            f.write(response['LogFileData'])
        except ClientError as e:
            print(e)
            sys.exit(2)

print()
print("Slow queries from instance: {} over the last {} hours".format(rds_instance,since_in_hours))
print("====================================================")
os.system(SLOW_LOG_GREP_TEMPLATE.format(local_log_file))
print("====================================================")
print()