RStoolKit - RedShift Health Check

If you are managing a single node Redshift cluster or a big giant multi node cluster, you are responsible for its performance. RedShift performance optimization starts from the table designing. We have an amazing RedShift Utility repo where we can get a bunch of SQL queries to check the cluster's status. But unfortunately many times we may end up with many queries but the results are positive, no need to optimize anything.

The goal of this project is to concentrate only on where is the bottleneck in your RedShift cluster and a complete health check recipe in one place. Once it identified the issues in the cluster, you don't need to run somewhere to find the solutions for them. This utility will take you to the documentation page where you'll find the cause for this issue and how to solve them. Also, these doc pages contain External links section where you can find scripts from other trusted repositions like AWS RedShift Utilities, AWS Blogs, and some documentation to learn and fix the issue.

:loudspeaker: What we are checking in your cluster?

1. Design - Find the wrong distribution and sort keys, compression issues and etc.
2. Table - Table related issues like without sort key, unused tables and etc.
3. WLM - Find the max connection hit, check the concurrency is good or not, number of queues.
4. Vacuum - Tables never performed vacuum, ghost rows, tombstone blocks and etc.
5. Performance - Disk based queries, High CPU queries and etc.

List of checks and their priority:

Before running the script:

We optimized this query as much as possible.

• We recommend running this on your non peak hours.
• If you have a single node cluster, then keep an eye on CPU (but it'll not go more than 50% or 60%).
• If your cluster is launched within 3 days(new cluster or if you did elastic resize), then the system tables may not have enough data to find out the bottleneck. So at least 3 days of data is recommended on STL, SVV tables to run this script.

Run the Health Check:

SQL script:

• You can just download the health-check.sql script from this repo and run this on your favourite UI tool like pgAdmin, DBeaver or Aginity workbench.
• If you are using command line tool psql client then you can import this via command line.

wget https://raw.githubusercontent.com/searceinc/RStoolKit/master/health-check.sql
psql -h redshift-endpoint -U user -p 5439 -d db_name < health-check.sql

Sample Report:

Scheduled Report via Lambda:

If you want to run this health check every week or some particular frequency, you can use AWS Lambda to run this health check and send the report over the email with SES. We have created a CloudFormation template to create this lambda function. RedShift and SES details can be passed through the Lambda environment variables. Please follow the below instructions to deploy this.

Changes in RedShift:

No changes on the cluster level, but lambda needs to access the RedShift cluster. So the subnets that you are going to use for Lambda those IP ranges needs to be whitelisted on RedShift's security group.

Changes in the VPC:

• We strongly recommend that to use SES with VPC endpoints for security reasons.
• Create a VPC Endpoint on SES supported Regions and attach to your RedShift VPC.
• While creating this Endpoint it will you to pick subnets where SES endpoints are available(Still many AZs is not supporting this).
• Create a Security Group for AWS Lambda and allow the 25, 465, 587, 2465, or 2587 port to the subnets IP Range that you in your previous step.
• More detailed information from the AWS Doc: https://docs.aws.amazon.com/ses/latest/DeveloperGuide/send-email-set-up-vpc-endpoints.html
• Create an AWS IAM user for SES to send emails. Follow this link for more information.

Resources will be created by the Cloud Formation:

• IAM role for Lambda.
• Custom policy for lambda [AWSLambdaVPCAccessExecutionRole and AWSLambdaBasicExecutionRole]
• Lambda function with Python 3.7

Things you need to take care:

Its an addition step for security. The function will work without this change, but recommend to enable the KMS encryption to this.

• This cloud formation stack will use the RedShift credentials and SES credentials as an Environment variables.
• Its all in plain text. To ensure the security you can encrypt them using KMS. For more information please follow this link.

Cloud Formation Parameters:

Deploy this Stack (Regions where SES is supported):

Region	URL (Click the Launch stack icon)
US East (N. Virginia)
US West (Oregon)
Asia Pacific (Mumbai)
Asia Pacific (Sydney)
Canada (Central)
Europe (Frankfurt)
Europe (Ireland)
Europe (London)
South America (São Paulo)

Manual Deployment (If your region is not listed on the above list)

The above list is prepared if SES is officially supported. If you want to use this lambda function, still you can deploy it on the other regions, but you need to take care of the Email part from the lambda code. (Still, we didn't test this option)

• Or use SES in another region.
  • Create a SES user on any supported region, and verify an email address.
  • Create NAT gateway and attach it to a new route table.
  • Create or choose any exsiting subnets to use this NAT gateway.
  • While launching the CF template select the subnet that has NAT gate way.
• Download the Lambda code
• Download CloudFormation template

Sample Report:

License & Contributing:

• RStooKit is free and open sourced under the MIT license.
• We appreciate and happy to accept the pull requests from anyone. Read the contributing guide before submit your pull request.
• If you want add your query or modify something on the documentation page, feel free to get the documents under the /src/doc/.