2. Running the Power and Throughput Test and Calculating QphH

2. Running the Power and Throughput Test and Calculating QphH

The Composite Query-per-Hour Performance Metric (QphH@Size) is calculated as follows:

Figure 9.3. QphH Calculation

QphH Calculation

To calculate QphH it requires 3 aspects of the capability of the system to process queries:

  1. Database size.

  2. Query processing power of queries in a single stream.

  3. Total query throughput of queries from multiple concurrent users.

For the multiple concurrent user tests the throughput test always follows the power test and the number of Virtual Users is based upon the following table where each Stream is processed by a Virtual User in HammerDB.

Table 9.1. Query Streams and Scale Factors

SF ( Scale Factor )S (Streams)

There is also a requirement for a simultaneous data refresh set. HammerDB provides full capabilities to run this refresh set both automatically as part of a Power test and concurrently with a Throughput test. Note however that once a refresh set is run the schema is required to be refreshed and it is prudent to backup and restore a HammerDB TPC-H based schema where running a refresh set is planned.

In order to calculate your QphH value you need to measure the following:

  1. The Refresh Times for one set of the Refresh Function (run as part of a Power test).

  2. The Query times for the Power test.

  3. The Query Time of the longest running virtual user for the throughput test.

Therefore once you have done your pre-testing run the tests detailed above and capture the values for the optimal configuration for your system ensuring that the configuration is the same for both the Power and Throughput tests. It is important that you read the documentation for running a DSS workload for your chosen database as this explains in detail how with HammerDB you can simulate the running of a Power test including both the Refresh Function and Query test and how you should configure the throughput test also with the refresh function. The next stage is to prepare your calculations in order to be able to capture your query performance. As we have seen the calculation of QphH @ Size is taken as the Square Root of the Power @ Size metric multiplied by the Throughput @ Size metric. Consequently we must calculate the Power @ Size metric from the Power test and Throughput @ Size metric from the Throughput test.

The Power @ Size metric is the inverse of the geometric mean of the timing intervals and shown as follows:

Figure 9.4. Power @ Size

Power @ Size

Here QI(i,0) is the timing interval, in seconds of the query stream and RI(j,0) is the timing interval, in seconds, of the refresh function and SF is the scale factor. An alternative calculation is shown where ln(x) is the natural logarithm of x

An alternative calculation is shown where ln(x) is the natural logarithm of x.

Figure 9.5. Natural Logarithm

Natural Logarithm

For strict adherence to TPC-H you should also be aware of the clause that specifies if the ratio between the longest running and shortest running query is greater than 1000 than all of the queries that took less than the maximum query time / 1000 should be increased to the maximum query time / 1000. Using a spreadsheet to do the calculations for us makes the calculation of Power @ Size relatively straightforward requiring only the entry of the Scale Factor, Query Times and Refresh times.

To calculate Throughput@Size the following function is used.

Figure 9.6. Throughput @ Size

Throughput @ Size

Here SF is the scale factor and S the number of streams.And the timing interval is the time taken between the first stream starting and the last stream completing. For simplicity because all of the query streams start at the same time this value can be taken as the longest time it takes for any of the query to complete .i.e. The time of the slowest query stream.

Given the Power @ Size Calculation and Throughput @ Size Calculation it is then simple to calculate the QphH as the Square of these two values multiplied.

Figure 9.7. QphH @ Size

QphH @ Size

Your completed spreadsheet will do this automatically for you resemble the following where the necessary input values are shown in red.

Figure 9.8. QphH @ Size

QphH @ Size

And your QphH value in this case 176881 @ 300GB has been calculated for you. In the example spreadsheet note that the sample values are not intended to represent the performance of an actual system and are simply to demonstrate the calculation of QphH. You should also calculate your price/performance by dividing the QphH value by the total system cost.