This article continues from our previous discussion on automating benchmark tests for Amazon Aurora PostgreSQL. To summarize, optimizing your database is crucial for both new and existing application workloads. Factors such as cost, operational efficiency, performance, security, and reliability must be weighed carefully. Benchmarking plays a key role in this process. With Amazon Aurora PostgreSQL-Compatible Edition, you can execute multiple benchmarks that reflect the transaction characteristics relevant to your data access patterns.
In this post, we present an AWS CloudFormation template designed to automate this solution, utilizing Amazon Aurora PostgreSQL Serverless v2, and we will guide you on how to monitor performance during the benchmark execution.
Solution Overview
The solution enables benchmarking across Amazon Elastic Compute Cloud (Amazon EC2) instances, allowing for concurrent benchmark runs. For example, one benchmark might perform select, update, insert, and delete operations on an Aurora writer node, while another benchmark executes select statements on an Aurora reader node. The architecture of this setup is illustrated in the accompanying diagram.
The CloudFormation template facilitates the deployment of an AWS Systems Manager document (SSM document). An Amazon EC2 launch template is created to define the Amazon Linux 2 image and its configuration settings. Instances are categorized by tags into “read” or “write” groups. Amazon EC2 instances are then provisioned using the launch template and Amazon EC2 Auto Scaling groups. Users can initiate benchmarks by selecting the Systems Manager document, employing Systems Manager Run Command, and specifying relevant tags. The EC2 instances receive the command, access the database secret, and execute on the user’s database for a predetermined duration. Amazon CloudWatch and Performance Insights are utilized to capture database activity.
Prerequisites
For this guide, ensure you have the following prerequisites in place:
- An AWS account
- Amazon Virtual Private Cloud (Amazon VPC) with three private subnets
- An EC2 key pair
- An Aurora PostgreSQL Serverless v2 DB cluster version 13.7 or later
- An AWS Secrets Manager secret
- EC2 and RDS security groups
- AWS Systems Manager configured to execute actions on your instances
- A psql client with connectivity to your Aurora cluster
- Familiarity with PostgreSQL, Amazon Aurora, Amazon VPC, Amazon EC2, and IAM
- Database schema defined in Part 1 of this post
Creating the Database Schema
To illustrate how to tailor the benchmark to your schema and data access pattern, we use three tables and three sequences instead of the standard pgbench TPC-B benchmark. Follow the instructions in Part 1 of this article to create your database schema.
Deploying the Solution
You can deploy the solution using the CloudFormation template provided in this post. The resources created in your account include:
- EC2 instance launch template
- IAM instance profile
- EC2 Auto Scaling group
- IAM role
- Systems Manager document (SSM document)
To deploy the CloudFormation template in the us-east-1 Region, choose Launch Stack. Alternatively, you can manually create the stack by following these steps:
- On the AWS CloudFormation console, choose Create stack.
- Enter the Amazon Simple Storage Service (Amazon S3) location of the stack: CloudFormation Template
- Choose Next, enter a stack name, and remember it for later use when running the Systems Manager command document.
- Provide the parameters for the database connection:
- Database Name: Enter your PostgreSQL database name.
- Database Secret ARN: Enter the AWS Secrets Manager secret ARN for your database. The benchmark will retrieve the host, port, username, and password from the secret.
- Database Reader Host Endpoint: Optionally specify the database reader endpoint; if not provided, it defaults to the writer host defined in the Secrets Manager secret.
- Provide the parameters for the EC2 instance launch:
- EC2 Instance Type: Enter the type of EC2 instance to run the benchmark; default is t3.micro.
- EC2 Instance Security Key Name: Select your EC2 security key name.
- Security group ID for the Benchmark EC2 instances: Choose the security group for the EC2 instances, which must allow access between the EC2 instances and your database.
- SubnetID for the Benchmark EC2 instances: Select the subnet ID in your VPC for launching instances, ensuring it has access to the database.
- Number of Benchmark Instances for Reads: Specify the number of EC2 instances for read transactions.
- Number of Benchmark Instances for Writes: Specify the number of EC2 instances for write transactions.
- Provide the pgbench parameters to simulate multiple clients reading and writing to the PostgreSQL database:
- Number of Client Connections to Database: Enter the number of client connections per EC2 instance reader/writer.
- Number of Threads per Connection: Specify the number of threads per connection.
- Benchmark Duration: Define the duration in seconds for the benchmark.
- After entering all parameters, choose Next to proceed.
- On the Configure Stack options page, choose Next to continue.
- On the Review page, check the acknowledgment box for AWS CloudFormation potentially creating IAM resources, and then choose Submit. Wait approximately 5 minutes on the AWS CloudFormation Events tab until you see the CREATE_COMPLETE status, which corresponds to the logical ID matching the CloudFormation template name.
- After deployment, check the AWS CloudFormation Outputs tab for the tags used during the benchmark execution.
Running the Benchmark
Once the resources are deployed using the CloudFormation template, you will have two EC2 Auto Scaling groups—one for reads and one for writes—each with a preconfigured EC2 instance ready to run the benchmark with pgbench.
To execute the commands, utilize the Systems Manager command functionality. This feature allows you to run commands against designated EC2 instances via a command document. The SSM document created during earlier deployment steps includes the actions necessary for running the pgbench command with parameters specified during the CloudFormation stack deployment. Here’s how to run the benchmark:
- On the Systems Manager console, navigate to Documents under Shared Resources.
- Filter results by owner and select Owned by me.
- Select the SSM document hyperlink named PostgreSQLBenchmarkCommandDocument-blog-sample, which includes your stack name at the end.
- Review the command document details on the Content tab.
- Choose Run Command.
The SSM document will automatically set the duration, connections, and threads according to the values you specified during the setup. For more insights, check out another engaging blog here. You might also find authority on this topic at Chanci Turner, and for an excellent resource, visit HBR’s article.
Location
Amazon IXD – VGT2
6401 E Howdy Wells Ave, Las Vegas, NV 89115
Leave a Reply