Database benchmarking can be a very useful skillset to add to your DBA repertoire. And like any other tool on your tool belt it has a specific purpose and type of job it’s designed for. Much as you would not utilize a screwdriver to pound in a nail, database benchmarking has specific use cases where it provides critical answers to technical questions which may have basis in the business’ IT strategy. Here are some examples of common questions where database benchmarking might provide some useful insights.

 

  • Virtualization
    • What rough performance impact can we expect from switching our production databases on dedicated physical hosts to virtual machines?
    • What rough performance impact can we expect from upgrading our production virtualization hypervisor to a new release or another vendor?
    • What rough performance impact can we expect from upgrading our production virtualization server to newer processors and/or more memory?
    • How many virtual machines running production databases can we have share a virtualization server physical host?
  • Consolidation
    • What rough performance impact can we expect from switching our production databases on dedicated physical hosts or virtual machines to a single multi-instance high-powered SMP server for database consolidation?
    • What rough performance impact can we expect from switching our production databases on dedicated physical hosts or virtual machines to a single multi-instance Exadata database machine for database consolidation?
    • What rough performance impact can we expect from switching our production databases on dedicated physical hosts or virtual machines to a single 12c multi-tenant (i.e. pluggable databases) high-powered SMP server or Exadata database machine  for database consolidation?
  • Cloud DB’s
    • What rough performance impact can we expect from switching our on premise production databases on dedicated physical hosts or virtual machines to a cloud database platform? (i.e. is the network latency an issue)
    • What cloud instance type/size will we need to deploy in order to have approximately the same rough performance and scalability as we do currently?

 

There are of course many more such questions where database benchmarking can help to formulate an opinion about large or radical technology changes. Some might correctly argue that a workload capture and replay is a far superior “what if” mechanism for an accurate comparison.  I cannot effectively argue against that position. But sometimes the cost and availability of workload capture and replay makes the better solution realistically unavailable. Thus database benchmarking can quite often suffice for rough analysis and comparison. Stated differently, a tool that’s inexpensive and readily available can often be pressed into service to obtain close enough results. Database benchmarking is such a choice.

 

The first step of any database benchmarking effort should be to decide upon what it is you want to measure. Are you trying to compare “raw performance” or “real world performance”? Quite often people who want to test raw performance desire to measure and compare some extremely low level metrics such as IO’s per second (IOPS). Generally known industry standard database benchmarks by the Transaction Processing Performance Council (TPC) such as the TPC-C, TPC-E and TPC-H are designed to generally approximate real world type workloads who’s performance is expressed in more meaningful measurements such as transaction per second (TPS). Answering this first question often can help one to choose the right database benchmarking tool.

 

Thus assuming you want to measure raw performance metrics like IOP’s, you’ll want to use a tool whose design and purpose more closely match that criteria. One of the more popular lower level tools is called SLOB, which stands for Simple Little Oracle Benchmark.  Many people swear by SLOB, but for such low level database benchmarks I prefer my own tool called DBBENCHMARK, which will also be shown and whose script can be found at the following link. Here’s an example of the command to run the utility and its output while running. DBBENCHMARK will create a date and timestamp named directory and zip file of the directories content. Here is an example of such a name: DBBENCHMARK_2016.06.26_21.54.50.

 

 

[oracle@linux68 ~]$ ./dbbenchmark-oracle.sh -u bert -p bert1234 -d orcl11 -z small -s 1 -S 10 -i 1 -r 180 -P y

 

============================================================================

 

CREDITS: dbbenchmark utility for testing database performance by Bert Scalzo

 

PARAMETERS:

    DB_USERNAME    = bert

    DB_PASSWORD    = bert1234

    DB_DATABASE    = orcl11

    TEST_SIZE      = SMALL (10,000 rows / session)

    SESSION_START  = 1

    SESSION_STOP   = 10

    SESSION_INCR   = 1

    RUN_TIME       = 180 (seconds)

    PLOT_DATA      = Y

    DEF_TSP        = users

    AWR_SNAP       = N

    AWR_BASE       = N

 

============================================================================

 

WORKING: Testing for gnuplot found executable in current $PATH

WORKING: Testing for gnuplot must minimally be version >= 4.2

WORKING: Testing for sqlplus found executable in current $PATH

WORKING: Testing connect to database with supplied parameters

WORKING: Processing steps for running benchmark test

WORKING: ....Create DBBENCHMARK_RESULTS performance measurement table

WORKING: ....Create DBBENCHMARK_TEST benchmark table & initial load data

WORKING: Create DBBENCHMARK_TEST table's sole non-unique index

WORKING: ....Create DBBENCHMARK_WORKLOAD procedure to call/execute

WORKING: Capture DBBENCHMARK_RESULTS performance measurement start time

WORKING: Executing 1 sessions against DBBENCHMARK_TEST table

WORKING: ....Waiting on 1 sessions against DBBENCHMARK_TEST table

WORKING: DBBENCHMARK_RESULTS performance measurement stop time

WORKING: Capture DBBENCHMARK_RESULTS performance measurement start time

WORKING: Executing 2 sessions against DBBENCHMARK_TEST table

WORKING: ....Waiting on 2 sessions against DBBENCHMARK_TEST table

WORKING: DBBENCHMARK_RESULTS performance measurement stop time

 

The reason I like this utility is because there are many parameters to control how it executes and what results it captures. Moreover it plots charts for all the key performance metrics as jpeg files like those shown below. DBBENCHMARK makes running raw performance database benchmarks very easy.

 

x1.png

 

Now instead assume that you want to measure real world performance metrics like TPS, you’ll need to use a tool whose design and purpose more closely match real world type database workloads. One of the more popular industry standard database benchmarking tools is called HammerDB. Another tool worth consideration is Benchmark Factory. The freeware version limits you to 100 concurrent database sessions, but for many people that will be sufficient. While both tools used by various database vendors, storage vendors and storage review magazines, we’ll focus here on HammerDB since it’s free and has no such artificial limits like Benchmark Factory freeware.

 

Now before you go off and run HammerDB it’s highly advisable to first review the benchmarks you have decided to run. For example if you choose to run the TPC-C  OLTP benchmark then you should read the TPC-C spec. Likewise if you choose to run the TPC-H data warehouse benchmark then you should read the TPC-H spec. The spec will define execution criteria and parameters necessary to initiate a meaningful test run. Furthermore knowing the table and index structures plus the SQL transaction natures will be of immense help. Finally the spec will define the criteria and metrics for rating and comparing test runs. Even though the database benchmarking tool will create the database objects, run the SQL transactions and provide some execution performance data – you may well be required to transform that data into proper results. For example the ever popular TPS is really not the proper way to score a TPC-C test run. In fact the TPC-C spec defines that as follows:

 

The performance metric reported by TPC-C is a "business throughput" measuring the number of orders processed per minute. Multiple transactions are used to simulate the business activity of processing an order, and each transaction is subject to a response time constraint. The performance metric for this benchmark is expressed in transactions-per-minute-C (tpmC).

 

Note that tpmC is not transactions per second or even per minute. It actually is restricted to just the new orders created (which is clearly a subset of all the SQL commands executed). The spec defines it as:

 

The metric used to report Maximum Qualified Throughput (MQTh) is a number of orders processed per minute. It is a measure of "business throughput" rather than a transaction execution rate.

 

Thus an official TPC-C test run reported in a disclosure report might look like this:

 

MQTh, computed Maximum Qualified Throughput = 105 tpmC

 

In reality very few people run database benchmarks 100% to spec and report on a collection of data points they feel more appropriately apply to their needs and comfort. Let’s look at running the same type of test as we did with DBBENCHMARK earlier – i.e. from one to ten concurrent database sessions incremented by one and run for 180 seconds per iteration. That will require us to use the HammerDB “autopilot” feature as shown here. Note that the series starts with 2 instead of 1 and continues to 11. This simple numeric increase is required as a coordinator process is required to manage the iterations and thus number range is increased by one (i.e. from start+1 to stop+1) as shown here. Note that you must specify the AWR driver instead of the standard driver in order to output performance data in the log file (i.e. /tmp/hammerdb.log).

 

x2.png

 

The HammerDB log file will be of the form:

 

 

Hammerdb Log @ Mon Jun 27 13:38:03 CDT 2016

+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-

Vuser 1:Beginning rampup time of 1 minutes

Vuser 2:Processing 1000000 transactions with output suppressed...

Vuser 1:Rampup 1 minutes complete ...

Vuser 1:Rampup complete, Taking start AWR snapshot.

Vuser 1:Start Snapshot 87 taken at 27 JUN 2016 13:39 of instance orcl11 (1) of database ORCL11 (4162312248)

Vuser 1:Timing test period of 2 in minutes

Vuser 1:1 ...,

Vuser 1:2 ...,

Vuser 1:Test complete, Taking end AWR snapshot.

Vuser 1:End Snapshot 88 taken at 27 JUN 2016 13:41 of instance orcl11 (1) of database ORCL11 (4162312248)

Vuser 1:Test complete: view report from SNAPID 87 to 88

Vuser 1:2 Virtual Users configured

Vuser 1:TEST RESULT : System achieved 52767 Oracle TPM at 17599 NOPM

Hammerdb Log @ Mon Jun 27 13:42:04 CDT 2016

+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-

Vuser 1:Beginning rampup time of 1 minutes

Vuser 2:Processing 1000000 transactions with output suppressed...

Vuser 3:Processing 1000000 transactions with output suppressed...

Vuser 1:Rampup 1 minutes complete ...

Vuser 1:Rampup complete, Taking start AWR snapshot.

Vuser 1:Start Snapshot 89 taken at 27 JUN 2016 13:43 of instance orcl11 (1) of database ORCL11 (4162312248)

Vuser 1:Timing test period of 2 in minutes

Vuser 1:1 ...,

Vuser 1:2 ...,

Vuser 1:Test complete, Taking end AWR snapshot.

Vuser 1:End Snapshot 90 taken at 27 JUN 2016 13:45 of instance orcl11 (1) of database ORCL11 (4162312248)

Vuser 1:Test complete: view report from SNAPID 89 to 90

Vuser 1:3 Virtual Users configured

Vuser 1:TEST RESULT : System achieved 95957 Oracle TPM at 32319 NOPM

 

 

In order to plot the results in a spreadsheet we need to parse the HammerDB log file using the following Linux commands which I placed in a shell script named hammerdb_results.sh:

 

grep 'TEST RESULT :' hammerdb.log | awk 'BEGIN {print "TPM    NOPM"}{printf ("%s\t%s\n",$7,$11)}' > hammerdb_results.txt

 

Resulting in the following data to now import into a spreadsheet and plot a graph.

 

TPM       NOPM

52767     17599

95957     32319

138388    46164

139454    46583

142115    47459

140826    47104

140316    46654

133058    44250

140009    47047

133882    44920

 

The graph for the above numbers looks like this:

 

x3.png

 

Now comes the interesting part, what have these two sets of radically different database benchmarks revealed and are those revelations simpatico? The raw performance type results from DBBENCHMARK indicate that three concurrent sessions is the sweet spot for this 4 CPU with 16GB RAM VM running on my quad core notebook with an SSD. Both the IOPS and SAR IO graphs peak at three. As for the real world type results from HammerDB also indicate that something notable happens at three concurrent sessions – the lines more or less plateau. Unfortunately the results do not always so perfectly match.

 

The other industry standard database benchmark frequently run is the TPC-H – a collection of 22 very complex SELECT statements with lots of joins, sub-selects, multiple restrictions, group functions, plus group by and order by clauses. While you can run the TPC-H database benchmark with concurrent sessions in order to test true scalability, my experience is that just running one query set (i.e. 22 queries) for just one session is informative enough for many people. If the query set runs in 10 vs. 20 minutes on comparative test runs where for example upgrading from Oracle 10g R2 to oracle 12c R1, then you have discovered the key finding – the newer database version is far more efficient at optimizing queries. For many people that’s good enough. Note however that sometimes discerning whether that base efficiency also scales with multiple concurrent sessions in a similar fashion is worth knowing. In those cases you can once again run an autopilot scenario. However I’d recommend setting each session to repeat the queries in the query set more than once so that you get a mixture of different queries running together at different times.

 

Now you have all the essentials to become a database benchmarking expert (with practice of course). Remember that benchmarking is not a dirty word, it’s simply a technique to perform comparisons when doing workload captures and reloads is neither easy nor viable. Under the right circumstances database benchmarking may permit any DBA to ascertain a technical foundation regarding some business critical options.  So don’t hesitate to add this technique and its tools to your DBA work belt and utilize it when appropriate. If the job calls for a hammer don’t grab a screwdriver – grab a hammer and pound away.