Understanding CPU Utilization for TPROC-C

[mamiyaakira]

I run Mysql benchmarks using hammerdb's TPROC-C.
The result of the top command when running the benchmark shows that the CPU usage is only around 10% as shown below.
I would like to put more load (more than 50% CPU utilization) in terms of benchmarking, but is there any way to put load?

%Cpu24 : 13.5 us, 2.6 sy, 0.0 ni, 79.2 id, 4.6 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu25 : 17.2 us, 2.9 sy, 0.0 ni, 75.3 id, 4.5 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu26 : 11.5 us, 2.4 sy, 0.0 ni, 79.9 id, 6.2 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu27 : 12.2 us, 2.0 sy, 0.0 ni, 80.6 id, 5.1 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu28 : 15.5 us, 2.6 sy, 0.0 ni, 76.9 id, 5.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu29 : 12.4 us, 3.3 sy, 0.0 ni, 78.9 id, 5.4 wa, 0.0 hi, 0.0 si, 0.0 st

[mamiyaakira]

The current parameters for hammerdb are:.

dbset db mysql
dbset bm TPROC-C
diset connection mysql_host localhost
diset connection mysql_socket /var/lib/mysql/mysql.sock
diset tpcc mysql_user root
diset tpcc mysql_pass password
diset tpcc mysql_dbase tpcc
diset tpcc mysql_count_ware 100
diset tpcc mysql_driver timed
diset tpcc mysql_timeprofile true
loadscript
vuset vu 24
vuset logtotemp 1
vuset unique 1
vuset timestamps 1
vucreate
puts "RUNNING BENCHMARK TPROC-C"
vurun
waittocomplete

[sm-shaw]

Hi, you are starting to get some of the first insights about database benchmarking and performance testing, and in particular the different characteristics between different databases.

Firstly, note that the HammerDB GUI CPU metrics can help you visualize CPU utilization across multiple cores and the user/system utilization as sometimes the workload may not be balanced across cores or NUMA nodes.

Secondly, the key question is what is your database waiting on? This is where your performance analysis should start, for example if you are waiting on redo or binlog I/O then no amount of additional load will improve the CPU utilization as you are not waiting on CPU.

Thirdly, you may hit internal database contention through locking and latching. At this point, you will find that adding load can in fact increase the contention and thereby reduce the throughput. To test this, once you have resolved any I/O bottlenecks, compare the latest releases of MySQL 5.7 and MySQL 8.0 - you will find that MySQL 8.0 scales better and will reach higher CPU utilization. Also, if you have a dual socket system, compare single and dual socket performance.

It is reasonable to observe that at the current time the commercial databases are able to scale to higher core and socket counts that the open source ones and therefore reach higher CPU utilization and performance as these databases have been optimized for the TPC-C workload over many years. This also demonstrates that HammerDB itself is capable of very high throughput due to its fully parallel architecture, and therefore the performance bottlenecks are in the database being tested rather than HammerDB itself.