tpcc: enable setting percentage of workload

[ZhouXing19]

I am using HammerDB to run TPCC against my oracle container and I'd like to run insert only workload. However, I found the percentage of each workload type seems to be not configurable.

Ideally, we support such syntax in the tcl file:

    # Set workload to only perform NEW_ORDER transactions (insert only)
    diset tpcc driver timed
    diset tpcc keyandthink false
    diset tpcc neword 100    ;# Set 100% of the workload to NEW_ORDER (insert operations)
    diset tpcc payment 0     ;# 0% for payment (update)
    diset tpcc orderstat 0   ;# 0% for order status (select)
    diset tpcc delivery 0    ;# 0% for delivery (update)
    diset tpcc slev 0        ;# 0% for stock level (select)

[sm-shaw]

This would not be practical change, although it is a common question so converted to a discussion topic.

We have seen it before, where users run benchmarks derived from TPC-C and change the transaction mix. For example, having a higher mix of delivery (eg 80%) that does mostly deletes.

However, in this scenario, the available data is soon deleted and the query to identify the rows to delete returns 'no rows found'. The user then observes high CPU on all the threads/virtual users and draws conclusions about the system performance, when in fact it is not exercising the database at all, just running empty queries. (But the user sees CPU running at 100% and incorrectly thinks that this is measuring database performance).

The transaction mix is intentional so we are doing a balanced mix of selects, inserts, updates and deletes.

Having said this, HammerDB is intentionally scripted, and allows experts who are fully able to diagnose system performance to change the transaction mix if they wish. At the end of the script, the variable choice is set to a number from 1 to 23. So if for example you only wanted to run a new order transaction then the following would do this. (However, expect your database to grow more rapidly than with the normal transaction mix. You can change the choice value to achieve any transaction mix.

      #set choice [ RandomNumber 1 23 ]
       set choice 1
            if {$choice <= 10} {
                if { $KEYANDTHINK } { keytime 18 }
                neword $neword_st $w_id $w_id_input $RAISEERROR
                if { $KEYANDTHINK } { thinktime 12 }
            } elseif {$choice <= 20} {
                if { $KEYANDTHINK } { keytime 3 }
                payment $payment_st $w_id $w_id_input $RAISEERROR
                if { $KEYANDTHINK } { thinktime 12 }
            } elseif {$choice <= 21} {
                if { $KEYANDTHINK } { keytime 2 }
                delivery $delivery_st $w_id $RAISEERROR
                if { $KEYANDTHINK } { thinktime 10 }
            } elseif {$choice <= 22} {
                if { $KEYANDTHINK } { keytime 2 }
                slev $slev_st $w_id $stock_level_d_id $RAISEERROR
                if { $KEYANDTHINK } { thinktime 5 }
            } elseif {$choice <= 23} {
                if { $KEYANDTHINK } { keytime 2 }
                ostat $ostat_st $w_id $RAISEERROR
                if { $KEYANDTHINK } { thinktime 5 }
            }
        }

With this understood, it is also worth pointing out that if you want to run a 100% insert only workload then a schema build does exactly that, 100% inserts. Again the build is a script so you can modify it as you wish, such as creating indexes before you load the tables.

Also, as you are using Oracle HammerDB includes the feature to trace Oracle workloads and then replay them as described here https://www.hammerdb.com/docs/ch14s01.html

So e.g. create a logon trigger (as sysdba)

create or replace trigger logon_trigger
after logon on database
begin
if (user = 'TPCC') then
execute immediate
'alter session set events ''10046 trace name context forever, level 4''';
end if;
end;

then do a build with 1 VU and 1 WH and in your trace directory is a file such as follows:

-rw-r-----. 1 oracle oracle 117596350 Sep 6 04:51 RAZCDB1_ora_5880.trc

Load that into HammerDB and convert it (button at the far left side on the bottom of the menu). Note it will take a while (there are a lot of potential enhancements for this #718)

When it is complete, you can see the converted trace in a form that HammerDB can re-run against the database.

So e.g. you can grab the section on inserting into a table and have a template to build your own insert test.

set curn139638646161808 [oraopen $lda ]
set sql139638646161808 "INSERT INTO item (i_id, i_im_id, i_name, i_price, i_data) values (:i_id, :i_im_id, :i_name, :i_price, :i_data) "
orasql $curn139638646161808 $sql139638646161808 -parseonly
orabindexec $curn139638646161808 :i_name {GB7evLH5cRDA2koMptU} :i_id {1} :i_data {ZNjocAeMdH6awDESXEtHlrI0n07JT0xFW4lUQ0C9x8fQenVWgk} :i_im_id {313} :i_price {72.21} 
orabindexec $curn139638646161808 :i_name {8HKqRVgSiALbnG} :i_id {2} :i_data {v66qJPGj1AQ8e3PT8cRikH81bT1DrgiK2EMZxnpk} :i_im_id {2570} :i_price {28.27} 
orabindexec $curn139638646161808 :i_name {QmzfJUVxx8zc54p1U} :i_id {3} :i_data {0biQuGFnaMHYKY18Lh76CX8originalNGMJNqk9T04A} :i_im_id {6485} :i_price {29.59} 
orabindexec $curn139638646161808 :i_name {EBK4deyJHw8DXcOaXa} :i_id {4} :i_data {lCZQkpqoQ0dtuRcyJa7pU9yW2M4TfM1hcDK0JYpSojvEorG0t} :i_im_id {4830} :i_price {56.08} 
orabindexec $curn139638646161808 :i_name {f9Uwq1GZjVeG0ZEg43py6UP} :i_id {5} :i_data {x6OJhlfwvD7cyKq7xWIfzBATkflIJ5} :i_im_id {8073} :i_price {48.34} 
orabindexec $curn139638646161808 :i_name {kqB0qL3WG7yfAqy} :i_id {6} :i_data {gNXqKpj2m4ZSrL8ihpQIJ7wGFK2fqHYcxj2Kfo3fB6Ch1} :i_im_id {6321} :i_price {41.05} 
orabindexec $curn139638646161808 :i_name {2M6spqKnw6aQTliszyeZ7F} :i_id {7} :i_data {EhjLKlEu9xe3VvSHiEYlDAbUPkuD6V2PC0aAW8aA6ocp} :i_im_id {3972} :i_price {91.40} 
orabindexec $curn139638646161808 :i_name {KsoLGTS94SQccddbNHTWGRz} :i_id {8} :i_data {V8bK6sWntmGPSeONrmUzKOXfv7rRyDwloyZasPQEVhLD0FJSx} :i_im_id {8355} :i_price {67.19} 
orabindexec $curn139638646161808 :i_name {YSn1cbUBxyJHS9TA} :i_id {9} :i_data {wqLUY99YlXMGe0Qi6ogh6ktxYNYGifKRFhRU9erEMztVOa} :i_im_id {4813} :i_price {66.88} 
orabindexec $curn139638646161808 :i_name {jX6PMgHNdOqxoVT1c} :i_id {10} :i_data {uHUSzVxdfGL2kHDIyWHBxiV8KgW8Vvp} :i_im_id {8718} :i_price {14.63} 
....

Finally, don't forget the oracle metrics, this will give you a breakdown of the statistics for the insert based workload and the related wait events. Especially if you are changing the transaction mix, this will then help you make sure that the workload you are running is sensible (and not VUs running empty queries at 100% CPU).

So no, we don't want to allow changing the transaction mix by default, however it is straightforward for experts to do and there are a number of options for creating bespoke insert based tests and analysing them.