Client Logging of TCL Script Output Used for Tests

[SeanDaniels]

My ultimate goal is to view the SQL statements that are executed on the SUT, but I don't want to use HammerDB stat viewer (or whatever it's called). My understanding is that HammerDB randomly generates those statements. Is it possible to log the result of that random generation on the client?

HammerDB with GUI isn't a reasonable tool for my use, so I'm limited to CLI. I also don't want to spend a bunch of time manually inspecting the SQL statements on the DB. To me, a simple alternative is to log the statements at generation. Open to any feedback on this.

[sm-shaw]

Really basic and easy to do:

e.g. server side - use simple commands provided by your database for example MariaDB

MariaDB [(none)]> show processlist;
+----+------+-----------+------+---------+------+--------------+------------------------------------------------------------------------------------------------------+----------+
| Id | User | Host      | db   | Command | Time | State        | Info | Progress |
+----+------+-----------+------+---------+------+--------------+------------------------------------------------------------------------------------------------------+----------+
|  3 | root | localhost | NULL | Query   |    0 | starting     | show processlist |    0.000 |
|  4 | root | localhost | tpcc | Sleep   |    9 |              | NULL |    0.000 |
|  5 | root | localhost | tpcc | Query   |    0 | Updating     | UPDATE district SET d_next_o_id = d_next_o_id + 1 WHERE d_id = NAME_CONST('no_d_id',2) AND d_w_id = |    0.000 |
|  6 | root | localhost | tpcc | Query   |    0 | Sending data | CALL PAYMENT(8,2,5,1,@p_c_id,1,4432,@p_c_last,@p_w_street_1,@p_w_street_2,@p_w_city,@p_w_state,@p_w_ |    0.000 |
+----+------+-----------+------+---------+------+--------------+------------------------------------------------------------------------------------------------------+----------+
4 rows in set (0.000 sec)

MariaDB [(none)]> show processlist;
+----+------+-----------+------+---------+------+----------+------------------------------------------------------------------------------------------------------+----------+
| Id | User | Host      | db   | Command | Time | State    | Info | Progress |
+----+------+-----------+------+---------+------+----------+------------------------------------------------------------------------------------------------------+----------+
|  3 | root | localhost | NULL | Query   |    0 | starting | show processlist |    0.000 |
|  4 | root | localhost | tpcc | Sleep   |   22 |          | NULL |    0.000 |
|  5 | root | localhost | tpcc | Query   |    0 | Updating | UPDATE order_line SET ol_delivery_d = NAME_CONST('timestamp',TIMESTAMP'2023-06-03 12:29:50')
       |    0.000 |
|  6 | root | localhost | tpcc | Query   |    0 | Update   | INSERT INTO order_line (ol_o_id, ol_d_id, ol_w_id, ol_number, ol_i_id, ol_supply_w_id, ol_quantity,  |    0.000 |
+----+------+-----------+------+---------+------+----------+------------------------------------------------------------------------------------------------------+----------+
4 rows in set (0.000 sec)

MariaDB [(none)]>
 

client side modify the driver script to print the exec statements for the database you are using e.g. again for MariaDB

catch { mariaexec $maria_handler "CALL NEWORD($no_w_id,$w_id_input,$no_d_id,$no_c_id,$ol_cnt,@disc,@last,@credit,@dtax,@wtax,@next_o_id,str_to_date($date,'%Y%m%d%H%i%s'))" }
puts "mariaexec $maria_handler CALL NEWORD($no_w_id,$w_id_input,$no_d_id,$no_c_id,$ol_cnt,@disc,@last,@credit,@dtax,@wtax,@next_o_id,str_to_date($date,'%Y%m%d%H%i%s'))"

For a beginner it is much easier to use the GUI to modify your script, save it and then load it into the CLI with customscript.
Choose log to temp and then the call parameters are all logged.

Vuser 3:mariaexec maria0 CALL NEWORD(6,10,6,705,13,@disc,@last,@credit,@dtax,@wtax,@next_o_id,str_to_date(20230603123412,'%Y%m%d%H%i%s'))
Vuser 2:mariaexec maria0 CALL NEWORD(4,10,3,831,10,@disc,@last,@credit,@dtax,@wtax,@next_o_id,str_to_date(20230603123412,'%Y%m%d%H%i%s'))
Vuser 2:mariaexec maria0 CALL NEWORD(4,10,1,734,6,@disc,@last,@credit,@dtax,@wtax,@next_o_id,str_to_date(20230603123412,'%Y%m%d%H%i%s'))
Vuser 2:mariaexec maria0 CALL NEWORD(4,10,1,310,7,@disc,@last,@credit,@dtax,@wtax,@next_o_id,str_to_date(20230603123412,'%Y%m%d%H%i%s'))

Note although it may be obvious, HammerDB is fully multi-threaded - once you start writing lots of output it becomes single threaded as you only have 1 console to write to - so messages are passed to the master thread for writing. This is why the timed scripts do not print output but the test ones do.

[SeanDaniels]

Thank you for the response.

The server side example is executed while a test is running, right? I'm testing PostgreSQL, and I have tried some different logging options to capture the data I'm looking for with limited success. Maybe I am not configuring the DB with the correct logging parameters (pg_stat?).

For the client side solution, limiting HammerDB to a single threaded process would limit load generated, but would provide a high level view of what is happening during the test, right?

Could I reasonably expect the same(ish) statements to be generated once I disabled the logging (just more of them, but same ratio of instructions)?

[sm-shaw]

Fortunately with PostgreSQL you have pg_stat_statements 'The pg_stat_statements module provides a means for tracking planning and execution statistics of all SQL statements executed by a server'.
I would also recommend pg_sentinel this gives you the ability to look back to see what was running at a particular time. Once both are installed an example from postgresql.conf shows how to enable them.

shared_preload_libraries = 'pg_stat_statements,pgsentinel'
track_activity_query_size=2048
pg_stat_statements.save=on
pg_stat_statements.track=all
pgsentinel_pgssh.enable = true
pgsentinel_ash.pull_frequency = 1
pgsentinel_ash.max_entries = 1000000

Then you can use SQL statements to query all of the SQL in your database over time. Although you said you didn't want to use the GUI, the GUI takes exactly the approach you describe. Once enabled, hit the metrics button and then drag out the window and expand it. An example is shown below. So drag the grey box in the AAS window to the time you are interested in and you can see the SQL running at that time. You can also see the associated SQL statistics by clicking on the right tabs.

Note that the example above is when functions are selected. When stored procedures are selected, you instead see the calls to the stored procedures as shown. This matches up to the "puts" statement if you modify the script.

Otherwise you will need to query pg_stat_statements manually.