TPCC-How to simulate the same workload for multiple trails wrt MySQL?

[anilbommareddy]

As shown below tpcc -scheambuild and work load.
Observed the result values/throughput NOPM varies ~3-4% for multiple trials
For reference the table as shown below NOPM values on each trail ,similar behavior observed with single virtual user(NOPM values varies for each trail).

Schemabuild.tcl

#!/bin/tclsh
puts "SETTING CONFIGURATION"
dbset db mysql
diset connection mysql_host localhost
diset connection mysql_port 3306
diset tpcc mysql_count_ware 800
diset tpcc mysql_partition true
diset tpcc mysql_num_vu 64
diset tpcc mysql_storage_engine innodb
print dict
buildschema

Workload.tcl or test run.tcl

puts "SETTING CONFIGURATION"
dbset db mysql
diset connection mysql_host localhost
diset connection mysql_port 3306
diset tpcc mysql_driver timed
diset tpcc my_rampup 2
diset tpcc my_duration 5
vuset logtotemp 1
loadscript
puts "SEQUENCE STARTED"
foreach z {1 2 4 8 12 16 20 24 28 } {
puts "$z VU TEST"
vuset vu $z
vucreate
vurun
runtimer 600
vudestroy
after 5000
}

virtual User	NOPM-Trail-1	NOPM-Trail-2
1	24050	24243
2	45329	45606

In scenario, how to simulate the same values of NOPM for multiple trails with same workload i,e shcemabuild.tcl and workload.tcl.
PS:CPU usage purposeful for only MySQL operations.

[sm-shaw]

Question converted to discussion from issue.
The scripts look fine. Typically, you should expect a variation of up to 1% in HammerDB workloads, however this does vary depending on the specific hardware, OS and database engine rather than in HammerDB itself. (The commercial databases for example will vary less).
As a starting point you do not mention your test system? For example a bare metal system in a lab environment with dedicated hardware, high performance SSD storage and enough memory to cache the schema will perform more consistently than a non-dedicated system. In the cloud you would expect more variability when using shared systems. You should also look at the database and OS performance statistics to identify the bottlenecks. I would also recommend graphing the entire performance profile to peak performance. MySQL performance can be in the range of 1-2M NOPM on a higher performing MySQL system at the peak of the curve (again commercial databases will be much higher showing HammerDB itself is not the cause), if you are not seeing that then identifying the bottleneck is a good place to start looking for causes of variation.
Also if not already doing so MySQL does benefit from a restart after the build before running the test to flush the cache.

[anilbommareddy]

The test system is bare metal system in a lab environment with dedicated hardware, high performance SSD storage(1TB) and OS-RHEL8.
Once the schema was built I shutdown and restarted the database and collected mysql results(throghput NOPM) with configured a test script(workload.tcl)
In test system, observed MySQL performance up to range or greater than 5M NOPM. Refer the table.
I would like to know which performance/iterations to be considered as baseline to identify bottlenecks and do performance enhancement?

Virtual  Users	Trail 1	Trail 2	Trail 3
1	24742	24858	25362
2	44662	44659	44204
4	84980	84489	80339
8	150329	152174	152794
12	216484	216854	218760
16	278296	282201	277631
20	341568	343390	330147
24	399709	401499	377000
28	445772	454767	433616
32	492706	501473	483404
36	530145	530202	509024
40	558594	567306	521795
44	574790	571467	526408
48	574418	581421	520865
52	585765	584384	543608
56	582266	590755	533152
60	581982	577282	526879
64	544024	532437	516146
68	558583	554581	518343
72	555806	559978	522228

[sm-shaw]

You are seeing up to 590K NOPM rather than M. In current tests MySQL has been measured in the range of 1-2M NOPM so 3X of what is seen here. OLTP database is based on a number of factors, hardware, OS, database version and configuration and so on. The key factor of course will be the system and in particular the CPU and number cores closely followed by the redo log disk. Then it will be the MySQL version and configuration. These type of questions are best directed to your system/os/database/vendor on tuning for OLTP performance as there are a lot of variables here.