[question] Read intensive benchmark

[MrM40]

It seems the TPC-C benchmark is a mixed test (50% read / 50% Write ?).
I have a very read-intensive database which I would like to optimize and therefor benchmark.
Would like to do a more read-intensive benchmark using HammerDB and it's warehouse database.

Any recommendation on how to do this best way?

[sm-shaw]

Firstly there is also the TPC-H based workload that you can run as entirely read, however that does depend on exactly the nature of the workload you want to read as these are more complex queries.
With the TPC-C workload there are 5 transactions (implemented as stored procedures) so with the transaction mix (ie how often they are selected 92% of the time a RW transaction is chosen and 8% a RO is chosen. Fortunately HammerDB has a script editor for exactly this reason so to change the transaction mix to all RO just change the line as follows:

set choice [ RandomNumber 22 23 ]

In the GUI change in the interface. in the CLI save and load a new script.
Note that by default each virtual user will select one home warehouse so it will be re-running similar queries on the same data so will likely be cached. If you are looking to increase the reads from disk create more warehouses and select the "Use All Warehouses" option so a new warehouse will be selected each time and therefore the data less likely to be cached.
Finally of course doing this the NOPM value will be 0 because that measures the New Orders per minute and by doing this you are not running any of them.

The section will look as follows and to only run the 2 read only transactions.

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

[sm-shaw]

Closed no further update

[MrM40]

Thanks for you quick answer, it was very helpful :-)