How to run command from oraoltp.tcl in hammerdbcli?

[alfer-aws]

Hello all,

I'm building a custom automation that should be able to run the create schema in steps, eg: create tables, load/generate data, create indexes.

I find the file oraoltp.tcl perfect for my use case as all the operation are split and it can be customized with wh_count, partitioning, etc..

I tried to find a way to call those functions right away from hammerdbcli but i am not familiar with TCL and I believe i can only call the functions included into gencli.tcl via hammerdbcli.

Thanks for your help in advance

Alessandro

[sm-shaw]

Hi,
This is timely as it fits well with some of the Issues planned for v5.0 around usability. In particular this study https://www.hammerdb.com/blog/uncategorized/why-tcl-is-700-faster-than-python-for-database-benchmarking/ verifies why Tcl is irreplaceable at the backend however we appreciate that not everyone knows it so there are plans to add Python functionality at the front - precisely for reasons such as this.
So yes you are on the right track on saying you can only access what is in gencli from the CLI. These functions are the front-end and what runs in the master thread which is the equivalent of the GUI front-end - whereas the virtual users are all additional threads (running in parallel) that get sent the configuration from the master thread - for the build they all work out their own 'chunk' from their own thread-id but every virtual user runs the same build with the first virtual user doing the user/table creation then sending a notification for the other threads to build and waiting to the end to complete. So to break it down into steps it would need the main build script run by the virtual users to accept an additional parameter such as a dict of steps to take as true or false and then to check if the flag is set before doing each step. This would be fairly straightforward to do because as you say all of the steps are there and it is easy to do manually be modifying the script and adding comments to bypass various sections, so these comments could be replaced with a simple true and false.

[alfer-aws]

Hi Steve,
Thanks for the heads up. I started working on the implementation by adding a section at engine's xml level to get the new dict included in the init cli phase:

Dictionary Settings for Oracle
connection {
 system_user     = abcdf
 system_password = ghijk
 instance        = oracle
 rac             = 0
}
tpcc       {
 count_ware       = 8
 num_vu           = 8
 tpcc_user        = tpcc
 tpcc_pass        = tpcc
 tpcc_def_tab     = tpcctab
 tpcc_ol_tab      = tpcctab
 tpcc_def_temp    = temp
 partition        = false
 hash_clusters    = false
 tpcc_tt_compat   = false
 total_iterations = 10000000
 raiseerror       = false
 keyandthink      = false
 checkpoint       = false
 ora_driver       = timed
 rampup           = 0
 duration         = 1
 allwarehouse     = false
 ora_timeprofile  = false
 async_scale      = false
 async_client     = 10
 async_verbose    = false
 async_delay      = 1000
 connect_pool     = false
}
builddb    {
 create_tables     = true
 create_indexes    = true
 create_storeprocs = true
 load_tables       = true
 create_users      = true
 gather_stats      = true
}

Then i isolated the builddb section into a dict to pass to "do_tpcc" proc but i did not find where it is called (yet). My idea was to put some logic into this proc instead of each procedures building the schema, basically i check the dict key and if true i proceed with the step (eg: creating users, creating indexes, etc) otherwise i skip to the next step.

[alfer-aws]

Thanks Steve, i'll have a look into it.

Yes in my test I had the dict at the same level as tpcc but just for testing purposes, I was thinking to include it for all engines probably having it in the generic config (haven’t looked at the structure yet). I'll give you an update when i have something solid in place.

[sm-shaw]

A quick thought, probably the easiest way to do this is to use the in-place editing as above but just to append to the end of the script new empty procs for the functions you don't want to use. To be absolutely correct you would also rename the existing proc (although Tcl will always use the newest definition). So for example if you had a proc called "CreateTables" you would use the in-place editing to append to the end of the build script (or just before the do_tpcc proc) something like:

rename CreateTables _CreateTables
proc CreateTables { args } { ; } 

This way you don't have to modify any of the existing build scripts or how it is loaded. You just edit it afterwards how is already done for the driver script options and now in the example the CreateTables section does nothing and replaces the existing one. This would be best in the tpcccommon module.

[greenhal]

From what I could tell.. in gencli, build_schema, calls build_[db]tpcc, which loads all of the Create*,Load* procedures and the do_tpcc call, into _ED array, then calls run_virtual which executes it.

If we wanted to replace the procedure call, we'd have to do it before the do_tpcc call, in the build_[db]tpcc function. Correct?

I was thinking we could pass additional parameters to the do_tpcc call, then use them to determine if the proc should run or not.

Alternatively we could also just add a section above the line:

.ed_mainFrame.mainwin.textFrame.left.text fastinsert end "do_tpcc $pg_host $pg_port $pg_count_ware $pg_superuser $pg_superuserpass $pg_defaultdbase $pg_dbase $pg_tspace $pg_user $pg_pass $pg_oracompat $pg_storedprocs $pg_partition $pg_num_vu"

That does the proc rename as you mentioned. This may be easier, since we don't have to mess with the parameters passed to do_tpcc, and could possibly be a common proc.

[sm-shaw]

Yes, that is the idea as if the build script is the default then nothing changes. In the GUI fastinsert is built into the ctext command, in the CLI you can see that this command has been written to mimic what is supported in the GUI in gencli. So you have one command that searches for the insert index, in this case looking for the line with do_tpcc - 1 and then you do the fastinsert to add the additional lines needed. This is the say that various combinations of use all warehouses, asynchronous clients and timing can be added without having to store multiple copies of the driver script for every combination of options.

[alfer-aws]

Hey Steve,

I was able to do implement the change by adding a section into each engine's xml and loading a build schema dict from the config$key (engine) variable. Then i just added an if block where i check what is true/false and i rename procedures accordingly.

I tried to build a generic proc but the routines differ from engine to engine so probably oltp.tcl / olap.tcl is the best place to store it.