Last active
October 24, 2019 13:26
-
-
Save tundeaoni/689c80b5770e427ccd3ea1e59ebdfaa7 to your computer and use it in GitHub Desktop.
get_rds_slow_queries.sh
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/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() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment