Using Primary/Replica modes to conduct tests on multiple instances of DB

[ananthu-dev]

I read in HammerDB documentation that it is possible to run tests on multiple instances of separate standalone Databases using Primary Replica modes or connect pooling. I am able to create multiple instances of HammerDB and connect replicas to a primary instance.

Here is my confusion, when i load driver script from primary it gets copied to all replicas. My database instances are all running on separate VMs with different IP addresses. In the loading script I can provide only 1 IP address (IP address of 1 DB instance). How can I provide a different IP address to each HammerDB replica instance so that I can run tests on all of my instances in parallel?

[sm-shaw]

Connect pooling can be thought of as a way for one HammerDB instance to connect to multiple database instances at the same time. This is typically for clusters so for example if you have a standby replica you can direct write traffic to the primary and read to the standby.

Primary and replica is when you want separate instances of HammerDB to connect to the same or different instances.

You don't mention whether you are using the GUI or CLI?

In the GUI the distribute button and distributescript in the CLI will send the primary script to all of the replicas.
In the GUI just modify the scripts in the Windows. In the CLI use customscript to load a separate script on each of the replicas rather than taking the default primary one to do the same thing.

In the CLI there is also an additional command to send one command to a specific replica, this is what is used by the step workload feature - see the example here:
https://www.hammerdb.com/blog/uncategorized/hammerdb-v4-1-step-workloads/
Sending "diset tpcc rampup 0" to 20182 razorbill.home

[ananthu-dev]

You don't mention whether you are using the GUI or CLI?

I am running the workload from CLI.

In the CLI use customscript to load a separate script on each of the replicas rather than taking the default primary one to do the same thing.

Does this mean I can modify default loadscript to provide different IPs to each replica? Or do I need different loadscripts altogether that can be loaded one by one to each replica?

If you don't mind, Is there any resource / example I can refer for this purpose?

Also, once we accomplish this, is it possible to get a cumulative TPM measurement from all replicas in the primary?

[sm-shaw]

Here is an example:
The system has 2 MariaDB instances running locally so I'm using sockets to connect so one is on /tmp/mariadb.sock and the other /tmp/mariadb2.sock.
I've used the GUI locally to configure and save 2 driver scripts with my options mdbprim.tcl and mdbrep.tcl, this is the difference between them:

diff mdbprim.tcl mdbrep.tcl 
13c13
< set socket "/tmp/mariadb.sock" ;# Maria Socket for local connections
---
> set socket "/tmp/mariadb2.sock" ;# Maria Socket for local connections

I also have a file called mariarun.tcl as follows:

vuset vu 2
vucreate
vurun
runtimer 480
vudestroy

I've then started 2 hammerdb instances and connected the replica to the primary and loaded our customscripts

Primary

./hammerdbcli 
HammerDB CLI v4.4
Copyright (C) 2003-2022 Steve Shaw
Type "help" for a list of commands
The xml is well-formed, applying configuration
hammerdb>switchmode primary
Switch from Local
to Primary mode?
Enter yes or no: replied yes
Setting Primary Mode at id : 14727, hostname : CRANE
Primary Mode active at id : 14727, hostname : CRANE

hammerdb>Received a new replica connection from host 127.0.0.1
New replica joined : {14750 CRANE}

hammerdb>customscript mdbprim.tcl
Loaded mdbprim.tcl

Replica

./hammerdbcli 
HammerDB CLI v4.4
Copyright (C) 2003-2022 Steve Shaw
Type "help" for a list of commands
The xml is well-formed, applying configuration
hammerdb>switchmode replica 14727 CRANE
Switch from Local
to Replica mode?
Enter yes or no: replied yes
Setting Replica Mode at id : 14750, hostname : CRANE
Replica connecting to CRANE 14727 : Connection succeeded
Primary call back successful

hammerdb>customscript mdbrep.tcl
Loaded mdbrep.tcl

I now run mariarun.tcl on the Primary. This runs the commands in the script on both the Primary and the Replica (or Replicas), they connect to the different instances at the same time and run their workload. You can then see the performance on each instance from the Primary and Replica output.

Primary

hammerdb>source mariarun.tcl
Vuser 1 created - WAIT IDLE
Vuser 2 created - WAIT IDLE
2 Virtual Users Created
Vuser 1:RUNNING
Vuser 1:Beginning rampup time of 1 minutes
Vuser 2:RUNNING
Vuser 2:Processing 10000000 transactions with output suppressed...
Timer: 1 minutes elapsed
Vuser 1:Rampup 1 minutes complete ...
Vuser 1:Rampup complete, Taking start Transaction Count.
Vuser 1:Timing test period of 2 in minutes
Timer: 2 minutes elapsed
Vuser 1:1 ...,
Timer: 3 minutes elapsed
Vuser 1:2 ...,
Vuser 1:Test complete, Taking end Transaction Count.
Vuser 1:1 Active Virtual Users configured
Vuser 1:TEST RESULT : System achieved 13453 NOPM from 41621 MariaDB TPM
Vuser 1:FINISHED SUCCESS
Vuser 2:FINISHED SUCCESS
ALL VIRTUAL USERS COMPLETE
runtimer returned after 181 seconds
vudestroy success

Replica

hammerdb>Vuser 1 created - WAIT IDLE
Vuser 2 created - WAIT IDLE
2 Virtual Users Created
Vuser 1:RUNNING
Vuser 1:Beginning rampup time of 1 minutes
Vuser 2:RUNNING
Vuser 2:Processing 10000000 transactions with output suppressed...
Vuser 1:Rampup 1 minutes complete ...
Vuser 1:Rampup complete, Taking start Transaction Count.
Vuser 1:Timing test period of 2 in minutes
Vuser 1:1 ...,
Vuser 1:2 ...,
Vuser 1:Test complete, Taking end Transaction Count.
Vuser 1:1 Active Virtual Users configured
Vuser 1:TEST RESULT : System achieved 13731 NOPM from 41604 MariaDB TPM
Vuser 1:FINISHED SUCCESS
Vuser 2:FINISHED SUCCESS
ALL VIRTUAL USERS COMPLETE
vudestroy success

The connectpool option also reports client-side transaction rates. This is because some clusters (such as Oracle RAC) will correctly report cross-cluster transaction rates whereas others will not.

[ananthu-dev]

Hello Steve,
Thank you for the detailed example. I tried running the example myself and I can start the test from a single place. That is good. I still need to collect and add up TPM from all the tests to get cumulative TPM. Is it possible to also calculate cumulative TPM on primary itself?

The connectpool option also reports client-side transaction rates. This is because some clusters (such as Oracle RAC) will correctly report cross-cluster transaction rates whereas others will not.

I tried connect pooling also. I filled up details of three standalone mariadb instances in xml and run the test from one of those systems. One output log was generated and in that I see TPM and client side TPM. I believe TPM value is collected from the system the test was run on and client side TPM is from other remote systems? Is that correct?

[sm-shaw]

As you found in the documentation, whether the cumulative TPM is reported correctly is entirely down to the database under test. HammerDB will report the TPM from the connect string specified, some database clusters such as Oracle RAC will report the cumulative TPM from a cluster other databases won't. The client side TPM is therefore reported as well to report the transactions from the client side as well. Note that this also includes the number of transactions processed during the rampup.