Tom Roehr, Senior Consultant

How much work can your database server do?

That’s a question that’s difficult to answer.  After all, unless you’ve run performance tests against a known standard with the database server, any answer you can give is just an educated guess.

An analogy  – ask a car performance enthusiast how much horsepower his cars’ engine produces. Knowledgeable enthusiasts use a device called a dynometer to measure the horsepower output of their modified engines. The dynometer is a tool that quantifies how much horsepower was gained or lost after making changes to the engine.

The equivalent to a dynometer for an OLTP database server is the TPC-C Benchmark.

TPC-C OLTP Benchmark

The TPC-C benchmark was developed by the Transaction Processing Council, a non-profit organization formed in 1988. TPC-C is based on the business model of a wholesale parts supplier (aka the “Company”) that operates out of a number of warehouses and associated sales districts. The benchmark is scaled by adding new warehouses; with the following parameters remaining constant:

• Each warehouse maintains stock for 100,000 items from the Company catalog
• Each warehouse supplies ten sales districts
• Each district serves 3,000 customers

Operators from the sales districts can perform five transaction types at any given time. These transactions types are:

• Entering a new order
• Recording a customer payment
• Reviewing an order’s status
• Processing a batch of ten orders for delivery
• Checking stock levels at a warehouse for shortages

Transaction Type Frequency
A remote terminal emulator (RTE) is used to generate transactions over the performance measurement period. The transactions will cover the complete business processing required for an order. Specifically, the transactions will cover:

• Equal number of New Order and Payment transactions.
• One each of Order-Status, Delivery, and Stock-Level for every ten New Order transactions.

The RTE is used to measure the response time of transactions and can simulate data entry keying and thinking time. This simulates an operator reading the results of a transaction before requesting another transaction, in order to simulate a real world workload.

TPC-C measurement metric, the tpm-C
The number of New-Order transactions that are processed per minute is what is known as the tpm-C. The tpm-C metric is considered the measurement of business throughput because the mix of transactions used in the test simulates a complete business process.

HammerDB’s benchmarking tool

HammerDB is an Open Source database load testing and benchmarking tool that provides both the TPC-C and TPC-H benchmarks. It is multi-platform and can be configured to use with the following databases:

• Oracle Database
• Oracle TimesTen
• SQL Server
• MySQL
• Postgres
• GreenPlum
• Redis

HammerDB automates the creation of the warehouses and the population of test data for the benchmarks. It also manages the testing, with generation of the required mix of transactions across the range of terminals.
 

Preparation for using HammerDB

Sizing the warehouse requirements
HammerDB recommends 200 to 250 warehouses per CPU socket for testing. Time will need to be allowed for the creation and population of the warehouses with data for the benchmark.

Transaction Generator
The Transaction Generator drives the benchmark load on the database server. In the ideal situation, a separate system, or group of systems, would be used to generate the test load. Minimally, the transaction generator can be run directly on the database server to generate the load.

Software Requirements
The Transaction generator requires the appropriate software download from HammerDB. Additional software, such as X-Windows packages, may need to be installed in order to run the HammerDB benchmark tool.

Database Requirements
The HammerDB documentation recommends creating a separate table space for Oracle benchmarking on the same disk storage as the production database. The table space should be at least 100 GBs in size. The sizing may need to be adjusted based on how many warehouses are being used in the benchmark.

Creating the Warehouses
This step only needs to be done once as prep work, unless you need to change the number of warehouses used in the benchmark testing.

Launch the HammerDB benchmark tool.

Expand out the Oracle->TPC-C-> Schema Build and double-click on Options

Verify the entries and click Ok.

Double-click on the Build selection.

Click on Yes.

HammerDB will then create the warehouses and populate them with test data for the benchmark.

Setting the TPC-C Driver Script Options

The TPC-C Driver Script is the script that is used by the Virtual Users for generating transaction traffic.

Expand out the Oracle->TPC-C->Driver Script and double-click on Options.

The Driver Script Options window will be displayed:

Those options that are of importance in the benchmarking process are:

• Standard Driver Script or AWR Snapshot Driver Script
• Keying and Thinking Time

Standard Driver Script or AWR Snapshot Driver Script
Using the Standard Driver Script you can run the benchmark in a straight-forward fashion. Selecting the AWR Snapshot Driver script coordinates taking AWR performance snapshots in conjunction with load testing to provide detailed statistics. HammerDB documentation provides the necessary changes that need to be made the AWR Driver Script in order to use StatsPack performance monitoring if AWR is not available or licensed.

Keying and Thinking Time
The Keying and Thinking Time option allows the options to simulate human interaction timing in the generation of transactions. Leaving the option unchecked essentially processes transactions as fast as the Transaction Generator can make them.

Confirm the Driver Script Options are set correctly, and then click on Ok.

Next double-click on the Load option to load the script into the benchmark tool.

Setting the TPC-C Transactions Options

The Transaction Options set the Oracle login parameters used to obtain the TPC_C tpm-C metric statistics.

Expand out the Oracle->TPC-C->Transactions and double-click on Options.

The Transaction Options window will be displayed.

Verify the Connect String is set correctly and if on a RAC database, that the RAC Global Transactions option is selected. Click on OK when finished.

Setting the TPC-C Virtual User Options

This is where you set the options on the Virtual Users for the benchmark.

Expand out Oracle->TPC-C->Virtual Users and double-click on Options.

The Virtual User Options window will be displayed.

HammerDB recommends adding one to the number of Virtual Users used for benchmarking for processing overhead.

So to run one Virtual User for a benchmark test, set the number of Virtual Users to 2. If running ten Virtual Users for a benchmark, set the number of Virtual Users to 11. Click on OK to save the changes.

You are now ready to start the TPC-C benchmark test.

Running the TPC-C benchmark

Start the TPC-C benchmark by expanding out Oracle->TPC-C->Virtual Users and double-click on Create. The Virtual User Output screen will be displayed as the benchmark is run.

You can view the tpm-C statistics as the benchmark is being processed by clicking on the Traffic Counter (pen) icon in the upper middle of the HammerDB window.

The tpm-C metric and graph will be displayed and updated as the benchmark progresses.

You can stop monitoring the tpm-C statistics by clicking on the Traffic Counter (now a red traffic light) icon.

Stopping the TPC_C benchmark

To stop the TPC-C benchmark, click on the red traffic light icon that is labeled Destroy Virtual Users.

Using the Autopilot Option

Autopilot can run the HammerDB benchmark over a range of Virtual Users for a specified time interval. To configure Autopilot, expand Oracle->TPC-C->Autopilot and double-click on Options.

The Autopilot Options window will be displayed.

Enable Autopilot by selecting Autopilot Enabled. Note that the Virtual User Sequence has N+1 number of Virtual Users for overhead as recommended by HammerDB. The sequence shown is for running 1, 2, 4, 8, 12, 16, 20, and 24 Virtual Users with one additional Virtual User for processing overhead.

After making your changes, click on OK to save.

Start Autopilot by clicking on the Autopilot icon.

Autopilot will proceed to run the benchmarks for the sets of Virtual Users for the specified duration.

To stop Autopilot, click on the red traffic light icon.

Conclusion

Measuring a database server’s performance is a complex undertaking. HammerDB’s benchmark tool provides a simplified method of running the industry standard TPC-C benchmark to obtain metrics. This provides a baseline for server performance that can be reassessed after any hardware or database configuration changes.

References

Transaction Processing Performance Council – http://tpc.org

HammerDB database load testing tool – http://hammerdb.com