Help : Hammerdb setup with mysql instance

[noopur100]

Hello everyone..
I am new to setup hammerdb with mysql instance, and need help with step by step instructions if anyone can help...
I have setup an compute engine "ds-poc" and installed hammerdb on it.
then I have created and cloud sql instance "t-streamb" under this I have created a DB "tpcc"

Please help me with the instructions :
my print dict output is as follow:

hammerdb>print dict
Dictionary Settings for MySQL
connection {
mysql_host = (I have set this value to public IP of my mysql instance)
mysql_port = 3306
mysql_socket = /tmp/mysql.sock
mysql_ssl = false
mysql_ssl_two_way = false
mysql_ssl_linux_capath = /etc/mysql/certs
mysql_ssl_windows_capath = C:\mysql\certs
mysql_ssl_ca = ca-cert.pem
mysql_ssl_cert = client-cert.pem
mysql_ssl_key = client-key.pem
mysql_ssl_cipher = server
}
tpcc {
mysql_count_ware = 1000
mysql_num_vu = 20
mysql_user = username
mysql_pass = password
mysql_dbase = tpcc
mysql_storage_engine = innodb
mysql_partition = true
mysql_prepared = false
mysql_total_iterations = 10000000
mysql_raiseerror = false
mysql_keyandthink = false
mysql_driver = test
mysql_rampup = 2
mysql_duration = 5
mysql_allwarehouse = false
mysql_timeprofile = false
mysql_async_scale = false
mysql_async_client = 10
mysql_async_verbose = false
mysql_async_delay = 1000
mysql_connect_pool = false

[sm-shaw]

There is a lot of information in the HammerDB documentation and the blog, also using the GUI locally such as on a laptop can really help with understanding the CLI but as a quick CLI primer for MySQL:

Firstly, we will write a script to build a 30 warehouse schema with 5 virtual users, the most common beginning mistake is to create too many warehouses and too many virtual users expecting higher performance - see the blog here for how many to create.

We have the default user of root, password mysql and database tpcc.

more buildmysql.tcl

dbset db mysql
dbset bm TPROC-C
diset tpcc mysql_count_ware 30
diset tpcc mysql_num_vu 5
buildschema
waittocomplete

Then we will run it.

$ ./hammerdbcli auto buildmysql.tcl 
HammerDB CLI v4.5
Copyright (C) 2003-2022 Steve Shaw
Type "help" for a list of commands
Database set to MySQL
Benchmark set to TPROC-C for MySQL
Changed tpcc:mysql_count_ware from 10 to 30 for MySQL
Changed tpcc:mysql_num_vu from 1 to 5 for MySQL
Script cleared
Building 30 Warehouses with 6 Virtual Users, 5 active + 1 Monitor VU(dict value mysql_num_vu is set to 5)
Ready to create a 30 Warehouse MySQL TPROC-C schema
in host 127.0.0.1:/TMP/MYSQL.SOCK under user ROOT in database TPCC with storage engine INNODB?
Enter yes or no: replied yes
Vuser 1 created - WAIT IDLE
Vuser 2 created - WAIT IDLE
Vuser 3 created - WAIT IDLE
Vuser 4 created - WAIT IDLE
Vuser 5 created - WAIT IDLE
Vuser 6 created - WAIT IDLE
Vuser 1:RUNNING
Vuser 1:Monitor Thread
Vuser 1:CREATING TPCC SCHEMA
Vuser 1:Ssl_cipher 
Vuser 1:CREATING DATABASE tpcc
Vuser 1:CREATING TPCC TABLES
...

You will see progress such as the following showing the build is running:

Vuser 6:...1000
Vuser 2:...2000
Vuser 5:...2000
Vuser 4:...2000
Vuser 2:...3000
Vuser 3:...2000
Vuser 2:Orders Done
Vuser 2:Loading Orders for D=2 W=4
Vuser 5:...3000
Vuser 6:...2000
Vuser 5:Orders Done
Vuser 5:Loading Orders for D=3 W=22
Vuser 4:...3000
Vuser 4:Orders Done
Vuser 4:Loading Orders for D=3 W=16
Vuser 3:...3000
Vuser 3:Orders Done
Vuser 3:Loading Orders for D=3 W=10
Vuser 6:...3000
Vuser 6:Orders Done
Vuser 6:Loading Orders for D=3 W=28
Vuser 2:...1000
Vuser 5:...1000

and the following when it is complete

Vuser 2:End:Fri Sep 02 11:11:30 BST 2022
Vuser 2:FINISHED SUCCESS
Vuser 1:Workers: 0 Active 5 Done
Vuser 1:CREATING TPCC STORED PROCEDURES
Vuser 1:GATHERING SCHEMA STATISTICS
Vuser 1:TPCC SCHEMA COMPLETE
Vuser 1:FINISHED SUCCESS
ALL VIRTUAL USERS COMPLETE
waittocomplete called script exit

You can login to MySQL to check the newly built schema e.g.

Login to MySQL
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| tpcc               |
+--------------------+
5 rows in set (0.01 sec)

mysql> use tpcc;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_tpcc |
+----------------+
| customer       |
| district       |
| history        |
| item           |
| new_order      |
| order_line     |
| orders         |
| stock          |
| warehouse      |
+----------------+
9 rows in set (0.00 sec)

Now we will write a test script:
We will run a timed test with 1 minute rampup, 2 minutes test with 2 virtual users and show the transaction rate while it is running:

more testmysql.tcl

dbset db mysql
dbset bm TPROC-C
diset tpcc mysql_driver timed
diset tpcc mysql_rampup 1
diset tpcc mysql_duration 2
loadscript
vuset vu 2
vucreate
tcstart
vurun
runtimer 200
tcstop
vudestroy

We run it in the same way as the build with the following output:

$ ./hammerdbcli auto testmysql.tcl 
HammerDB CLI v4.5
Copyright (C) 2003-2022 Steve Shaw
Type "help" for a list of commands
Database set to MySQL
Benchmark set to TPROC-C for MySQL
Clearing Script, reload script to activate new setting
Script cleared
Changed tpcc:mysql_driver from test to timed for MySQL
Changed tpcc:mysql_rampup from 2 to 1 for MySQL
Changed tpcc:mysql_duration from 5 to 2 for MySQL
Script loaded, Type "print script" to view
Vuser 1 created MONITOR - WAIT IDLE
Vuser 2 created - WAIT IDLE
Vuser 3 created - WAIT IDLE
3 Virtual Users Created with Monitor VU
Transaction Counter Started
Vuser 1:RUNNING
Vuser 1:Ssl_cipher 
Vuser 1:Beginning rampup time of 1 minutes
0 MySQL tpm
Vuser 2:RUNNING
Vuser 2:Ssl_cipher 
Vuser 2:Processing 10000000 transactions with output suppressed...
Vuser 3:RUNNING
Vuser 3:Ssl_cipher 
Vuser 3:Processing 10000000 transactions with output suppressed...
5298 MySQL tpm
6102 MySQL tpm
5592 MySQL tpm
4080 MySQL tpm
5796 MySQL tpm
Vuser 1:Rampup 1 minutes complete ...
Vuser 1:Rampup complete, Taking start Transaction Count.
Vuser 1:Timing test period of 2 in minutes
3894 MySQL tpm
Timer: 1 minutes elapsed
6564 MySQL tpm
5238 MySQL tpm
5688 MySQL tpm
5784 MySQL tpm
5988 MySQL tpm
Vuser 1:1 ...,
6702 MySQL tpm
Timer: 2 minutes elapsed
6498 MySQL tpm
6270 MySQL tpm
7146 MySQL tpm
6048 MySQL tpm
6126 MySQL tpm
Vuser 1:2 ...,
Vuser 1:Test complete, Taking end Transaction Count.
Vuser 1:2 Active Virtual Users configured
Vuser 1:TEST RESULT : System achieved 2700 NOPM from 6269 MySQL TPM
Vuser 1:FINISHED SUCCESS
7272 MySQL tpm
Vuser 2:FINISHED SUCCESS
Timer: 3 minutes elapsed
Vuser 3:FINISHED SUCCESS
ALL VIRTUAL USERS COMPLETE
runtimer returned after 183 seconds
Transaction Counter thread running with threadid:tid0x7fdd810d7700
Stopping Transaction Counter
vudestroy success

This simple test on a latop gave a performance result of:
Vuser 1:TEST RESULT : System achieved 2700 NOPM from 6269 MySQL TPM

Try rerunning by increasing your virtual users vuset vu 4 until you top out on performance.

When you have finished, you can drop the database.

mysql> drop database tpcc;
Query OK, 9 rows affected (0.19 sec)

This should be enough to get started. To understand some of the more advanced parameters, see the presentation here and then the documentation and blog.

[noopur100]

thank you, I was able to setup tpcc with mysql using HammerDB.