IBM Books

Administration Guide

Executing the Benchmark Tests

One type of database benchmark involves choosing a configuration parameter and running the test with different values for that parameter until the maximum benefit is achieved. A single test should include executing the application through several iterations (for example, 10 times) with the same parameter value to get an average timing, which will better show the effect of parameter changes.

When running your benchmark, the first iteration should be considered a separate case from the subsequent iterations. This is because the results from the first iteration will include some start-up activities (such as initializing the buffer pool). Consequently, this iteration will take somewhat longer than the others. Although the information from this iteration may be realistically valid, it will not be statistically valid. Therefore, when calculating the average timing for a specific set of parameter values, use the timings from the second and subsequent iterations.

You may want to consider using the Performance Configuration SmartGuide to create the first iteration of the benchmark. The questions asked as part of the Performance Configuration SmartGuide will provide insight into some of those things to consider when adjusting the configuration of your environment for subsequent iterations during your benchmark activity. To use the Performance Configuration SmartGuide, enter db2cc to get into the Control Center and proceed from there.

If you are benchmarking using individual queries, you need to ensure that you minimize the potential effects of previous queries. This can be accomplished by flushing the buffer pool which can be done by reading a number of pages (irrelevant to your query) to fill the buffer pool.

After completing the iterations for a single set of parameter values, a single parameter can be changed. However, between each iteration, the following tasks should be performed to restore the benchmark environment to its original state:

Output from the benchmark program should include an identifier for each test, the iteration of the program execution, the statement number, and the timing for the execution. A summary of benchmarking results after a series of measurements might look like the following:

Figure 65. Benchmark Sample Results

     Test     Iter.   Stmt    Timing         SQL Statement
     Numbr    Numbr   Numbr   (
      002      05      01     00:00:01.34    CONNECT TO SAMPLE
      002      05      10     00:02:08.15    OPEN cursor_01
      002      05      15     00:00:00.24    FETCH cursor_01
      002      05      15     00:00:00.23    FETCH cursor_01
      002      05      15     00:00:00.28    FETCH cursor_01
      002      05      15     00:00:00.21    FETCH cursor_01
      002      05      15     00:00:00.20    FETCH cursor_01
      002      05      15     00:00:00.22    FETCH cursor_01
      002      05      15     00:00:00.22    FETCH cursor_01
      002      05      20     00:00:00.84    CLOSE cursor_01
      002      05      99     00:00:00.03    CONNECT RESET

Note:The data in the above report is shown for illustration purposes only. It does not represent measured results.

Examining this report would indicate that the CONNECT (statement 01) took 1.34 seconds, the OPEN CURSOR (statement 10) took 2 minutes and 8.15 seconds, the FETCHES (statement 15) returned seven rows with the longest delay being .28 seconds, the CLOSE CURSOR (statement 20) took .84 seconds, and the CONNECT RESET (statement 99) took .03 seconds.

It might be beneficial for your program to output your data in a delimited ASCII format so that it could later be imported into a database table or a spreadsheet for further statistical analysis.

Sample output for a benchmark report might be:

Figure 66. Benchmark Sample Timings Report

       TEST NUMBER      001     002     003    004     005
       locklist         63      63      63      63      63
  >>   buffpage         1000    1175    1250    1325    1400     <<
       maxappls         8       8       8       8       8
       applheapsz       48      48      48      48      48
       dbheap           128     128     128     128     128
       sortheap         256     256     256     256     256
       maxlocks         22      22      22      22      22
       stmtheap         1024    1024    1024    1024    1024
       SQL STMT         AVERAGE TIMINGS (seconds)
         01             01.34   01.34   01.35   01.35   01.36
         10             02.15   02.00   01.55   01.24   01.00
         15             00.22   00.22   00.22   00.22   00.22
         20             00.84   00.84   00.84   00.84   00.84
         99             00.03   00.03   00.03   00.03   00.03

Note:The data in the above report is shown for illustration purposes only. It does not represent any measured results.

Examining the data in this example shows that changing the buffpage parameter successively lowered the OPEN CURSOR times from 2.15 seconds to 1.00 second. (The assumption is that there is only one (1) buffer pool with the size (NPAGES) set to -1. This means the size of the buffer pool is controlled by the buffpage parameter.)

In summary, the following steps/iterations may be followed to benchmark a database application:

Step 1
Leave the database and database manager tuning parameters at their default values except for:

Run your set of iterations for this initial case and calculate the average timing.

Step 2
Select one and only one tuning parameter to be tested, and change its value.

Step 3
Run another set of iterations and calculate the average timing.

Step 4
Depending on the results of the benchmark test, do one of the following:

You can write a driver program to help you with your benchmark testing. This driver program could be written using a language such as REXX or, for UNIX-based platforms, using shell scripts.

This driver program would execute the benchmark program, pass it the appropriate parameters, drive the test through multiple iterations, restore the environment to a consistent state, set up the next test with new parameter values, and collect/consolidate the test results. These driver programs can be flexible enough that they could be used to run the entire set of benchmark tests, analyze the results, and provide a report of the final and best parameter values for the given test.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]