TPC-H configuration HammerDB

[ajithkumarg987]

I perceive TPC-H little puzzle compared to TPC-H on CPUs/Cores and CPU utilization in HammerDB envirnoment.

Here the test TPC-H(PostgreSQL)

schema_tpcc.tcl

diset tpch pg_scale_fact 30
diset tpch pg_num_tpch_threads 10 /schema buid with Vusers as n+1/
diset tpch pg_degree_of_parallel 2 /*
print dict
buildschema

Test_run.tcl

vu set 4 /* why cannot test more vu's*/

Question1:

if vu increased in Test_run.tcl got an error like "Virtual User 1: FATAL: terminating connection due to administrator command" and postgresql restart.
(ab)In schema build Vuser's used more than 4 i.e pg_num_tpch_threads and test run why can't user more vu's.?

here is the limit for vu wrt scale-factor as suggested as HammerDB document"
The number of Virtual Users is based upon the following table where each Stream is processed by a Virtual User in HammerDB"?
https://www.hammerdb.com/docs/ch11s02.html

(b)Here what is best way more number virtual users wrt CPU cores?
Is Virtual users limited in TPC-H and CPU Utilization or cores related to this Parameters
max_worker_processes
max_parallel_workers_per_gather
max_parallel_workers
max_parallel_maintenance_workers

Question2:

As HammerDB document as "For PostgreSQL the Degree of Parallelism sets the max_parallel_workers_per_gather parameter for the sessions executing the queries"
if the parameters max_parallel_workers_per_gather set in postgres.conf can be ignored to set in schema build i.e pg_degree_of_parallel.
Mostly PostgreSQL read from the configuration.

[sm-shaw]

For Q1. pg_num_tpch_threads this is the number of virtual users to build the TPC-H schema. It will automatically set the number of virtual users for the build based on this value, you do not need to use vu set to set it manually. This can be any value you wish however you should not set it higher than the core count and also note that the virtual users will create and insert data into the same tables at the same time, therefore you may find setting this value too high can reduce the build time. Only testing will show what is right for your environment.

For Q2. This is what HammerDB runs (look at the driver script)
set result [ pg_exec $lda "set max_parallel_workers_per_gather=$degree_of_parallel"]
and here is the PostgreSQL documentation for what this setting does:
https://www.postgresql.org/docs/current/how-parallel-query-works.html
HammerDB is completely open source and transparent, you can always look at the code to see exactly what it is doing. This setting has the same impact as running the same at the command line.

[sm-shaw]

Hi,
Q4. You should think of the Virtual Users differently between TPROC-C and TPROC-H. For TPROC-H (derived from TPC-H) there is the potential for 1 Virtual User to use all system resources with parallel query. Therefore, think of the approach as power test - one virtual user and throughput test, stream value based on scale factor, you set these with vuset. (There is no monitor virtual user).
Q6. Yes, you cannot just extract the query from the source code, as the query predicates are generated at run time. To extract a query as it ran to re-run from the PostgreSQL command line run the HammerDB client with verbose output and out to the logfile and then run the actual query captured in the logfile.
Q7. All the changes to HammerDB are recorded in the Changelog. These refer to the pull requests for you to see the code changes made.

[ajithkumarg987]

TPC-H still questionable compared to TPC-H on CPU utilization.
On go through the HammerDB documents not helpout on CPU uitlization.

Example:
schema.tcl
diset tpch pg_scale_fact 30
diset tpch pg_num_tpch_threads 10
diset tpch degree_of_parallel/max_parallel_workers_per_gather/

test_run.tcl
vu set ->4

Question 1:
TPC-H:CPU utilization calculated on scale_factor 30 on enabled parallel Querry(below configuration parameters)

max_worker_processes-> No of CPU's( total number of workers to the number of CPU cores)
max_parallel_workers=same for the number of parallel workers i.e number of CPU's
max_parallel_workers_per_gather= workers an executor i.e the parallel execution of a planner node(Join/Hash/Merge)?
max_parallel_maintenance_work=No of cores like physical cores(socket(s):2) or Numa nodes(s):2.?

(a)is PostgreSQL has an internal logic to calculate the maximum number of parallel workers it is ready to use for a table
max_parallel_workers_per_gather=log3(table size / min_parallel_table_scan_size(8MB)

CPU(s): 256
On-line CPU(s) list: 0-255
Thread(s) per core: 2
Core(s) per socket: 64
Socket(s): 2
NUMA node(s): 2

max_worker_processes=256
max_parallel_workers=256
max_parallel_workers_per_gather=?/log3(table size / min_parallel_table_scan_size(8MB)/
max_parallel_maintenance_work??
** Total Number of CPU's/Process utilized on Scale factor 30?**

[sm-shaw]

You are out of scope for HammerDB support. HammerDB TPROC-H is working as expected. Your questions are focused on how PostgreSQL manages parallel query and should be directed to a PostgreSQL discussion forum.

[ajithkumarg987]

How to evaluate the TPC-H results on Multiple test runs on same configuration and schema
(a)is it may be better to compare the execution times of the queries individually?
Example:
Vuser 1:Executing Query 22 (12 of 22)
Vuser 1:query 22 completed in 0.999 seconds

(b)better to compare the execution time of completed Query sets/Geometric mean of query
user 1:Completed 1 query set(s)(Q1 to Q22) in 99 seconds
Vuser 1:Geometric mean of query times returning rows (22) is 0.99999

In (a) noticed the deviation is high(>20%) between multiple test runs for few query's.
In (b) noticed the deviation is low(<3%) btw multiple test run.

[sm-shaw]

Yes, you can compare the query times individually or use the geomean whichever works best for your environment. If query times are short then just comparing the times will be the best approach. The TPC prefers that you do not calculate the QphH metric yourself for derived workloads from TPC-H.