AWS Database Blog
Use HammerDB to run performance tests on Amazon RDS for Db2
To ensure that you properly size your Amazon Relational Database Service (Amazon RDS) for Db2 instances and achieve comparable or better performance than your on-premises systems, you can use HammerDB. By using this tool, you can generate OLTP-type workloads using TPC-C tests, enabling you to compare performance between your on-premises Db2 and Amazon RDS for Db2 systems. By using HammerDB, you can evaluate how different instance types, network speeds, and Amazon Elastic Block Storage (Amazon EBS) characteristics such as IOPS and throughput impact your database performance. This approach helps you make informed decisions when migrating your Db2 workloads to the AWS cloud and helps ensure optimal performance in your RDS for Db2 environment.
This post guides you through running HammerDB tests on RDS for Db2. We provide a step-by-step process for creating an RDS for Db2 instance using an AWS CloudFormation template, setting up a Db2 client, and configuring HammerDB. You learn how to execute tests and interpret results to properly size your RDS for Db2 instances.
Solution overview
The solution architecture explains the use of a CloudFormation template to create an RDS for Db2 system, creation of an Amazon Elastic Compute Cloud (Amazon EC2) instance with Db2 client, and the use of HammerDB software to run the performance tests.
The following components are used in this solution:
- Amazon RDS for Db2 – This is a managed IBM Db2 environment provided by Amazon.
- Db2 client on Amazon EC2 – We need a client to connect to RDS for Db2. The solution shows installing a Db2 client on Amazon EC2 to connect to RDS for Db2.
- HammerDB – The HammerDB generates the datasets for different types of TPC-C workloads. We run tests by varying the size of the database and the number of users to generate the workload and identify performance bottlenecks arising out of either instance size limitations or insufficient IOPS for the storage.
- RDP client – You can use a Remote Desktop Protocol (RDP) client to connect to the Db2 client running on either Windows or on Ubuntu by installing support for XRDP.
The following diagram illustrates the architecture for implementing this solution.
The solution consists of the following steps:
- Create an RDS for Db2 instance using AWS CloudFormation
- Configure RDS for Db2 multi-Availability Zone with a standby database
- Creating an EC2 instance using CloudFormation for a Db2 client
- Connection to RDS for Db2 using the Amazon EC2 Db2 client
- Configuration of the Db2 client for connecting to RDS for Db2 instance
- Configuration of HammerDB in Amazon EC2 using a Db2 client
- Running the HammerDB tests
Prerequisites
For this solution to work, you must have the following:
- IBM Db2 license
- Bring your own license (BYOL) – You will need to specify IBM customer ID and IBM Site ID for creating an RDS for Db2 instance.
- AWS Market Place – You can subscribe to AWS Marketplace to obtain hourly usage licenses for Standard Editions and Advanced Editions. You can also make use of the IBM private offers to get discounts for bulk usage.
- IBM account – You can create a free IBM account to download the free IBM Db2 client to connect to RDS for Db2.
- AWS account – You need an AWS account
- Amazon EC2 key pair – You need to create public/private keys to connect to Amazon EC2 from your local machine. You can build a new key pair or use your own in your AWS account.
- RDP client program – This program can be based on either Apple MacOS or Microsoft Windows.
Note that this solution will incur costs for creating an RDS for Db2 instance. However, you can use a free tier of Amazon EC2 to create a Db2 client. You can the AWS Pricing Calculator to estimate costs.
Deploy RDS for Db2
We walk you through creating an RDS for Db2 instance and an EC2 instance using a CloudFormation template. You can download and preview the CloudFormation template .
You can modify or add any additional parameters such as VPC CIDR in the downloaded CloudFormation template. You can use the your modified CloudFormation template by uploading it to an Amazon Simple Storage Service (Amazon S3) bucket or by directly uploading the modified CloudFormation template in AWS Management Console for CloudFormation.
- To launch the CloudFormation template with pre-configured settings, choose Launch Stack.
- When you run the CloudFormation template, it will ask for several input parameters.
- For ClientIpCidr, enter your PC IP address by visiting whatismyipaddress.com and finding your IP. Enter the IP address using the format
xxx.xxx.xxx.xxx/32
. This CIDR will be to restrict access to Amazon EC2 to only your IP address. - For DBInstanceIdentifier, use
db2tpcc
as stated in the example to be consistent with scripts. You can use any other name, but you will need to modify the scripts to do so. - For Db2DbName, enter the database name in uppercase letters or use the default name.
- For Db2ServicePort, enter the database service port or use the default port, such as 50001.
- For IbmCustomerId, enter your IMB customer ID.
- For IbmSiteId, enter your IMB site ID.
- For MasterUsername, enter the primary username for your Db2 database or the default username, such as
db2inst1
. - For MasterUserPassword, enter the password for the primary username.
- For SshKeyPairName, choose your key pair for Amazon EC2.
- For ClientIpCidr, enter your PC IP address by visiting whatismyipaddress.com and finding your IP. Enter the IP address using the format
- Creating the stack will take approximately 15 minutes. When the stack creation is complete, you can view the output values on the Outputs tab. The output values might be different for each user than the values shown in the following screenshot.
- You can check the resources of RDS for Db2 on the Amazon RDS console. Choose Databases in the navigation pane and choose the database identifier that AWS CloudFormation created.
- On the Connectivity & security tab, you can find connection-related information such as endpoints and ports available in the database. You can also identify network-related information such as VPC, Availability Zone, and security groups.
- On the Configuration tab, you can find the database configuration information. This includes details such as database instance ID and engine version. Additionally, you can verify information such as vCPU and RAM allocated to the RDS for Db2 instance.
- When you view the parameter group details, you can check various Db2 parameter settings. You can also change the parameter values to your desired values.
Configure the Db2 client on Amazon EC2
In this section, we walk through the steps to set up the Db2 client in Amazon EC2.
Check the client Ubuntu EC2 instance
The CloudFormation template created an EC2 instance running Ubuntu Linux. On Outputs tab of the stack, you can see the access information. The Ubuntu Linux instance that was created supports RDP access. When configured through the CloudFormation template, it’s also configured to allow users to log in using port 3389. You can connect similar to connecting to Windows.
SSH login
Log in with the SSH command by using the connection information shown in the output of the CloudFormation stack. The IP address of each user might be different.
- To log in with SSH command, you need to prepare the private key pair file and set the file permission as 400 on your computer.
- Then, you can log in as the Ubuntu user:
- After you’re logged in as the Ubuntu user, use the following command to log in as the root user:
Download the Db2 binary client packages
- Visit the IBM Support page and download the IBM Data Server Client Packages. If you have an IBM account, these client packages are free to download. As of the writing this post, the latest version available for download is IBM Data Server Client Packages Version 11.5 Mod 9 Fix Pack 0. We recommend downloading the driver for Linux-x64.
- You can download the client driver for Db2 by selecting the options FTPS/SFTP, HTTPS, or Download Director. Among those options, you can download by using the FTPS/SFTP option.
You can also download it from the Ubuntu environment that was created using the CloudFormation template.
- To use HammerDB with the GUI, you need to configure HammerDB and the Db2 client to the root user. Thus, you need to log in using the
sudo -i
command for downloading and installing Db2 client: - Unzip the
*.gz
file for installation:
Install the Db2 client
Use the following command to install the client. You are prompted with [yes/no]
two times. Each time, enter yes
to proceed.
Connect to RDS for Db2 using the Db2 client
In this section, we walk you through the steps to connect to RDS for Db2 using the Db2 client.
Create a db2profile for Ubuntu and root users
- Create a
db2profile
for the Ubuntu user. While logged in as the root user, navigate to the directory where Db2 client is installed. Use the following command to registerubuntu
as the Db2 user: - The output should be similar to the following.
- Add the following configuration to the end of root’s
.profile
file. The Ubuntu user’s.profile
file has been added with the following code. This process adds the profile configuration to the root’s.profile
file so the root user can also use thedb2profile
configuration.x - While logged in as the root user, run the
.profile
file with the following command: - Run the
db2
command to verify that the Db2 client is working as the root OS user: - The following is the expected output:
Test the Db2 connection using the root user
- Use the following command to catalog the node for Db2. The DNS of RDS for Db2 and port should be used by checking the values from the output of the CloudFormation stack.
- The following is the expected output:
- Catalog the database using the following command:
- The following is the expected output:
- When the cataloging is complete, run the connection test using the following command. The primary username and password can be found by checking the values in the output of the CloudFormation.
- The following is an example of the expected output:
- After you connect to the Db2 database,
TPCC
, check whether the connection is successful by performing the following SELECT query:
If you want to monitor the TPCC database, we recommend the db2top utility. With db2top
, you can monitor various Db2 database metrics and check database status in real time.
- Install the required packages to install db2top:
- Run db2top with the following command:
- To check the functions and shortcut keys of
db2top
, press h on the db2top page.
- You can save the db2top run parameter environment by pressing w. It’s created as a
~/.db2toprc
file. You can run it with just thedb2top
command without any options:
Configure HammerDB on the Db2 client
In this section, we walk through the steps to configure HammerDB on the Db2 client.
Install the HammerDB program
- Connect to the Ubuntu client EC2 machine with an SSH command if not already connected, then log in as root and install HammerDB. The IP address of the EC2 instance is different for each user.
- The following is the expected output:
Run the HammerDB program
Connect to the Ubuntu EC2 instance using Microsoft RDP on MacOS, or using remote desktop on Windows. The following example uses Microsoft RDP on MacOS.
- Choose Add PC.
- Enter the public IP of the Ubuntu EC2 instance for PC name and the root user for User account.
You can check the public IP through the output of the CloudFormation stack. - Double-click the created remote PC to open it. When you connect to Ubuntu using RDP, a login page appears.
- Enter
root
as the username and use the password that you provided when you created the CloudFormation stack, then choose Next. - When you log in for the first time, the basic OS usage guide page appears. Choose Activities to open the search menu.
- Search for and launch the Terminal program.
- At a prompt, run the following command to confirm that the
db2profile
configuration is applied under the root user: - Use the following commands to move the HammerDB installation directory, then run the HammerDB program:
Configure HammerDB for RDS for Db2
To configure HammerDB for RDS for Db2, complete the following steps:
- Double-click Db2 in the navigation pane.
- Select Db2 and TPROC-C.
- Choose OK.
- Choose OK in the pop-up to confirm.
Build the schema for the TPC-C workload
Complete the following steps to set up the TPC-C workload.
- Choose the arrow to the left of TPROC-C. Choose the arrow to the left of Schema. Double-click Options under Schema in the navigation pane.
- For TPROC-C Db2 User, enter the username you specified when creating the CloudFormation stack.
- For TPROC-C Db2 User Password, enter the password you specified when creating the CloudFormation stack.
- In the same menu, choose the Settings
- For Number of Warehouses, enter a number high enough to accommodate your test. For example,
100
. - Enter the same number for Virtual users to Build Schema to simultaneously build the schema. For example,
100
. - Choose OK.
- Double-click Build and choose Yes to confirm.
- When all the Status icons of virtual users have changed to green as shown in the following figure, you can delete the virtual users.
- Choose the red stop icon (Destroy Virtual User) to end the build session.
Configure driver options
Complete the following steps to configure driver options:
- Choose the arrow to the left of Driver Script. Double-click Options under Driver Script in the navigation pane. In the popup dialog box, choose the Settings
- You can configure Db2 TPRC-C Driver options in this menu as you want. For test purposes, you can leave all the options at the default values and choose OK.
- Double-click the Load menu in the Driver Script By doing that, you can load generated scripts from Driver Options.
Configure virtual users
Complete the following steps to configure Virtual Users options:
- Choose the arrow to the left of Virtual User. Double-click Options under Virtual User in the navigation pane.
- Enter your desired number of virtual users, such as
100
, and choose OK.
- Double-click Create to create the users. By doing that, you can create the desired number of users and plus one more user because it requires a user for monitoring.
Run the TPC-C workload
Choose the green play icon to create a benchmark workload.
Monitor the benchmark workload with TPM graph
- Choose the graph icon to see the TPM graph.
- You will see the progress of the graph as shown in the following figure.
- When the benchmark workload is complete, the TPM value will drop.
Run HammerDB with the CLI
In addition to the GUI, HammerDB also provides the ability to use command-line (CLI) commands. You can use the CLI functionality to automate repetitive tests.
Enter the Hammerdbcli prompt
- To use HammerDB with the CLI, you need to use the
hammerdbcli
terminal. Navigate to the directory where HammerDB is installed and runhammerdbcli
. - The following is the expected output.
Configure the schema build setting
- Run the following command to set the database management system (DBMS) to be tested as
db2
. Then, run theprint db
command to check the DBMS name. - The following is the expected output.
- Run the following command to set the workload type to TPC-C.
- The following is the expected output.
- By using the
print dict
command, you can see the settings for the current workload. - You can change these settings with the
diset
command. Replace the password with your own password using the following command. Also, Set the number of warehouses and virtual users to the number you need for your test. - The following is the expected output.
- Run the
print dict
command again to verify that it changed correctly.
Build the schema
Build the schema using the preceding configuration information.
- Run the following command to build the schema.
- The following is the expected output.
- After this is done, delete the virtual user that was used during build. Run the following command to delete the user, and then check the status to make sure it was successfully deleted.
- The following is the expected output.
Configure the driver
Configure settings for the driver that performs the load. Set the details of the load transaction, such as rampup
and duration
.
- Use the following commands to modify existing settings, and
print dict
to verify that the settings are correct. - The following is the expected output.
- If the settings for the driver change, you need to reload the script with the changes. You can load the script with the following command.
- The following is the expected output.
Configure virtual users
You can set the number of virtual users to carry the load and make additional settings, such as whether to print to log.
- The following command is an example of a command to create five virtual users and set up log output.
- The following is the expected output.
- You can verify that the virtual users were successfully created by running the
vucreate
command. As you can see in the following result, in addition to the five virtual users that performed the creation request, there is one more virtual user for monitoring. - You can check the status of the current virtual users by running the
vustatus
command.
Run the workload
- The workload prepared by the setup so far can be run by using the
vurun
command. The following is the expected output. - As you can see from the preceding results, it will display what’s being done, and the final TPM will be displayed. After the process is complete, use the
vudestroy
command to delete thevu
. - The following is the expected output.
Perform a batch using a Tcl script
HammerDB provides a way to run workloads in batch using Tcl scripts. Tcl batch scripts are a convenient way to perform repetitive tasks with minor configuration changes.
- Use the following command to create a simple Tcl batch script. The following batch command is an example of running a test repeatedly while incrementing the
vu
number in the form of 1, 2, and 4. - Use the
auto
keyword inhammerdbcli
to run a Tcl batch file, as shown in the following example command. - The following is the expected output.
Monitoring the HammerDB workload
When you run benchmark tests, it’s possible that the tests aren’t performing at the desired level because of insufficient system resources. To check this, you can look at the OS metrics.
- You can view OS-related metrics of a database by going to the Amazon RDS console while the benchmark workload is running and choosing Databases from the navigation pane, selecting the database, and then choosing the Monitoring tab.
- You can use the monitoring menu to view various metrics related to Amazon RDS system resources.
By checking the OS metrics, you can see if CPU, Memory, IOPS, and Network have sufficient capacity. If you encounter a situation where these resources aren’t sufficient, you can adjust the instance type, storage type, and IOPS through the Modify function in the RDS console to resolve the resource shortage.
You can monitor the status of the database through the db2top
command. db2top
is a real-time monitoring tool and you can run it immediately before running the benchmark workload and then monitor while the benchmark workload is running.
- Run db2top with the following command.
- By entering
l
, you can see the status of the current session.
- By entering
B
, you can see the level of bottleneck of the database.
Configure Db2 parameters
Depending on what kind of workload you want to verify by using TPC-C, you need to configure the parameters of Db2. A parameter group in Amazon RDS is a collection of database configuration parameters that can be applied to one or more database instances.
If you have multiple different workloads, it might be better to create different custom parameter groups tailored for each workload type.
To create a parameter group, use the Amazon RDS console. Choose Parameter groups from the navigation pane, and then choose Create parameter group.
When using RDS for Db2, you can modify database manager parameters and registry variables through parameter groups. The following screenshot shows the BYOL parameter settings accepted in a parameter group.
- You can change database parameters through the management database. To use the management database, the first thing to do is to catalog
rdsadmin
and then connect tordsadmin
. Then, you can run therdsadmin
procedure to update parameters as shown in the following example: - The following is the expected output of the command:
RDS for Db2 instances performance testing
The HammerDB can be used to assess the performance of RDS for Db2 instances by executing the HammberDB TPROC-C workload—which emulates an OLTP workload—on RDS for Db2. The HammerDB workload can be also executed against Db2 on-premises for comparison.
After running the HammerDB benchmarking, you can analyze the result by considering the following aspects. When the benchmarking is also performed against an on-premises Db2 environment, the results can be compared with the results obtained from the RDS for Db2 instance, helping you to evaluate performance of the provisioned RDS for Db2 configuration compared to the on-premises Db2 configuration.
- TPROC-C workload performance
- Evaluate the throughput performance using the two key indicators such as the number of new order transactions per minute (NOPM) and the overall transactions per minute (TPM) at the end of the run.
- Capture and review the latency performance of the five TPROC-C transactions by enabling the Time Profile option available in the Driver Script Options. During the benchmark run, this feature will generate response time percentiles for all the virtual users executing these transactions on the system, logging the results to the
/tmp/hdbxtprofile.log
file for comprehensive comparison. - After the run, open the
/tmp/hdbxtprofile.log
file to review the response time percentiles of the five transactions of all the virtual users.
- Evaluate the throughput performance using the two key indicators such as the number of new order transactions per minute (NOPM) and the overall transactions per minute (TPM) at the end of the run.
- RDS for Db2 resource utilization.
- By using Amazon CloudWatch or enhanced monitoring, you can gain a comprehensive understanding of the system’s performance characteristics under the HammerDB benchmark workload. By using this approach, you can compare resource utilization across the cloud and on-premises environments along with the TPROC-C workload performance, and ensure that your RDS for Db2 instance is provisioned with a comparable size and configuration to your on-premises Db2 setup.
- The Cloudwatch metrics
CPUUtilization
,FreeableMemory
,ReadLatency
, andWriteLatency
provide valuable insights into resource utilization, specifically CPU usage, memory consumption, and disk I/O latency. This enables a comprehensive comparison with an on-premises Db2 database along with the TPROC-C workload performance metrics.
- Based on the preceding NOPM and TPM transaction response time comparison with on-premises and RDS for Db2 resource utilization metrics:
- To achieve the desired target of NOPM or TPM for RDS for Db2 compared to on-premises Db2, the performance bottlenecks coming from CPUs, memory, I/O or the database itself should be identified and mitigated by adjusting the RDS for Db2 configuration.
- The following chart shows an example of the HammerDB TPROC-C benchmark NOPM for RDS for Db2 instances with four vCPUs and eight vCPUs configurations. If the NOPM of RDS for Db2 with four vCPUs doesn’t meet the desired performance target, the instance can be scaled to as much as an eight vCPUs configuration until it meets the performance goal.
Clean up
If you no longer need this setup and want to avoid future charges, you can delete the resources that you created as part of this setup.
To delete all of the resources that were launched as part of the CloudFormation stack, complete the following steps:
- On the CloudFormation console, choose Stacks in the navigation pane.
- Select the stack you created, then choose Delete.
- Choose Delete stack when prompted.
For more information, see Deleting a stack on the AWS CloudFormation console.
Conclusion
In this post, we described how to configure the HammerDB benchmark tool and what considerations you should take into account when performing benchmark testing in an RDS for Db2 environment. There aren’t many tools that can benchmark Db2 compared to other popular database management services. HammerDB is a powerful benchmarking tool that streamlines the process of evaluating and configuring the performance of RDS for Db2 instances. This tool also enables competitive analysis between RDS for Db2 configurations and on-premises Db2 databases, helping organization make informed decisions for optimal resource allocation. In addition to HammerDB, there is an open source tool called Apache JMeter and a benchmark tool called DTW from IBM that you can use to compare performance.
Try out the HammerDB benchmark tool for yourself, and share your thoughts in the comments section.
About the authors
Byeong-eok Kang is a Solutions Architect at AWS. He has over 20 years of experience in building and consulting on IT systems for financial companies, helping customers configure and use the right cloud systems for their needs. In addition to that, he also has experience in various other technology areas such as databases, AI/ML, analytics, and SaaS. Outside of work, he likes bicycling, playing with his cat, and reading books.
YunCheol Ha is a Senior PostgreSQL and Db2 specialist Solutions Architect at AWS with over 25 years of experience in designing and implementing mission-critical operational systems and large-scale data warehouses. He is passionate about database performance optimization and delivering high-performance and scalable solutions.
SeongHee Kang, a Partner SA, has experience as both a database engineer and a Data Analytics platform engineer, having built and conducted pre-sales across various industries. He is currently supporting partner solutions businesses in the Industry & Partner Solution team.
Gyoubyoung Kim is an Analytics Sales Specialist at AWS. He has worked as a technology expert and consultant in the data and analytics field in the large enterprise markets in Korea. He has specialized experience with high-volume, high-availability Db2 environments for financial and enterprise customers.