AWS Database Web log

Benchmark Amazon RDS SQL Server performance using HammerDB

For many organizations, performance is a cardinal priority for databases that demand to handle a loftier load. Amazon Relational Database Service (Amazon RDS) is a managed service with a toll-efficient, reliable, and highly scalable database offering which makes it a suitable utilise case for such requirements.

In this post, I walk y'all through benchmarking Amazon RDS for SQL Server performance using HammerDB. HammerDB is an open up-source database load testing and benchmarking tool, where you lot create test schema and simulate the transactional load using virtual users. You can analyze the test results in order to right-size your database instance and so that information technology matches your workloads. Although HammerDB helps you simulate generic TPROC-C (derived from TPC-C), you should always criterion your database based on your own workload to become accurate performance results.

Prerequisites

To get started, complete the following prerequisites:

  1. Provision Amazon RDS for SQL Server to benchmark the performance testing. For instructions, see Creating a Microsoft SQL Server DB example and connecting to information technology. Take a note of your username and countersign every bit y'all will apply them later to connect to the database.
  2. Launch Amazon EC2 Windows instance to install HammerDB and generate the load. I recommend running the example in the same Virtual Private Cloud (Amazon VPC) next to your RDS DB instance, to eliminate network latency.

Solution overview

Running the RDS for SQL Server in a private subnet ensures that only resources provisioned within the Amazon VPC communicate with the RDS DB instance.

Brand sure y'all add an inbound dominion to allow port 1433 in the security group assigned to RDS to be accessible from the Windows Server EC2 instance. For more than details about security groups, encounter Security groups for your VPC.

We walk you through the post-obit high-level steps:

  1. Install HammerDB.
  2. Build your schema.
  3. Run your functioning test.

Install HammerDB

After connecting to the Amazon EC2 Windows instance, download and install the latest version of HammerDB. As of this writing, the latest version is 4.2.

Open up hammerdb.bat located nether C:\Programme Files\HammerDB-X.X.

Build the schema

To build your schema, consummate the post-obit steps:

  1. On the Options menu, choose Benchmark.
  2. Choose SQL Server and TPROC-C.
  3. In the Benchmark panel, expand TPROC-C, Schema Build, and cull Options.
  4. For SQL Server, enter the DNS proper name for your RDS DB instance. To find your RDS DB instance DNS open RDS console, select Databases and click on DB instance proper name, on the Connectivity tab re-create the endpoint. The Endpoint looks something like this: sample-case.123456789012.us-due east-2.rds.amazonaws.com.
  5. For SQL Server User ID, enter your SQL Server user ID (if you selected SQL Server Authentication).
  6. For SQL Server User Password, enter your password.
  7. For TPROC-C SQL Server Database, enter a proper noun for the database that is created equally function of the schema build.
  8. For Number of Warehouses, choose how big yous want your testing data to be.
  9. For Virtual Users to Build Schema, enter your preferred number of virtual users. A higher number speeds upwards test data creation. We recommend a value closer to the full vCPU.
  10. Choose OK.
  11. Under Schema Build¸ cull Build.
  12. Ostend by choosing Yeah.
  13. On the Virtual User Output tab, monitor the schema creation progress for each virtual user.
  14. When the status of all virtual users changes to Consummate, choose the red traffic light icon.

Run a functioning test

We're now set to run our operation test.

  1. Expand Driver Script and choose Options.
  2. For SQL Server, enter the domain proper noun for your RDS DB example.
  3. Select SQL Server Authentication and enter your user ID and countersign.
  4. For TPROC-C SQL Server Database, enter a name for the database that is created.
  5. For TPROC-C Driver Script, select Timed Commuter Script.
  6. Select Use All Warehouses.
  7. Leave the other fields with the default value.
  8. Cull OK.
  9. Under Driver Script, choose Load.
  10. Expand Autopilot and choose Options.
  11. Make certain you select Autopilot Enabled.
  12. For Minutes per Test in Virtual User Sequence, enter 8.
  13. For Active Virtual User Sequence, enter four 8 16 32 64 128 256.
  14. Select Show Virtual User Output.
  15. Cull OK.
  16. Choose Autopilot to launch the simulation.

Autopilot is an like shooting fish in a barrel-to-employ automated way to simulate the aforementioned test with a dissimilar number of virtual users. For more information, encounter Autopilot for Automated Testing.

When Autopilot is consummate, you get a result similar to the following:

NOPM (new orders per infinitesimal) records how many new orders per minute the database can achieve. TPM (transactions per minute) records all transactions, both commits and rollbacks, during the measuring period on the database.

For farther understanding of how your database example is performing while nether load testing, go to the Databases page on the Amazon for RDS console, and navigate to the Monitoring tab. This provides you lot with additional monitoring capabilities similar retentivity consumption, read/write IOPS, network throughput, and more. These metrics allow you to make up one's mind key factors in database performance, which you can easily calibration vertically or horizontally using Amazon RDS so you tin go on up with the increasing need of your application.

Amazon RDS offers Performance Insights, a powerful, easy-to-apply, congenital-in tool with which you tin can visualize database load and filter by unlike criteria. For more information, meet Monitoring DB load with Performance Insights on Amazon RDS.

Amazon RDS also offers great integration with Amazon CloudWatch, allowing you to monitor near real-time metrics and take automatic actions accordingly using alarms. Equally an example you tin can monitor high CPU or RAM consumption using CPUUtilization, FreeableMemory and change the instance class of your DB instance, or you might demand to alter the number of DB instances and read replicas. For a complete listing of available metrics, see Overview of monitoring Amazon RDS.

Clean up

When you lot're done with your functioning testing, make clean up your resources by deleting the RDS instance and stopping your EC2 example.

Conclusion

In this post, I showed you how to run performance testing on Amazon RDS for SQL Server using HammerDB. This criterion tells yous how well your database performs, and allows you to compare different instance types. If you have whatsoever comments or questions, leave them in the comments section.

Nearly the Author

Ramy Zbeidy is an App Migrations Consultant at Amazon Web Services. He is experienced in helping clients migrate to AWS using compute, database, storage, migration, and DevOps services. With a strong evolution and architectural background, he has helped a wide range of verticals achieve a successful deject journey.