PostgreSQLv14-TPC-H-HammerDBv4.2 performance deviation between iterations(Number of trails of same test on same workload) too high

[arjunshetty955]

Captured TPC-H benchmarks on PostgreSQLv14 on HammerDBv4.2 in client-server model i.e client machine-HammerDBv4.2 ,server machine-PostgreSQLv14.
Postgres backup(like schema) for restarting the benchmark after each run. This helped remove any unknown factor (like caching).
But even observed the deviation between the iterations is high. please find below results.

|Virtual Users |GCC_11 |Deviation BTW iterations %
|------------- |-----Itr1 | itr2 | Itr3 |--------------------------|
|1 |10416 | 10493 | 14064 |35.02304147 |
|2 |12520 | 11349 | 15195 |33.88844832 |
|3 |10622 | 5602 |14309 |155.426633

Parallelism enabled in Postgres.conf

parallel query

max_parallel_workers_per_gather = 16
max_parallel_maintenance_workers = 16
max_worker_processes = 32
max_parallel_workers = 32

Example: 10416 Avg time QPS of Q1 to Q22(completed query sets(s) in seconds)
Kindly let me know is this expected behavior ? is any suggestion to reduce the deviation btw iterations.

schema_tpcc.tcl

#!/bin/tclsh
puts "SETTING CONFIGURATION"
dbset db pg
dbset bm TPROC-H
diset connection pg_host localhost
diset connection pg_port 5432
diset tpch pg_scale_fact 100
diset tpch pg_num_tpch_threads 64
diset tpch pg_degree_of_parallel 4
print dict
buildschema

[sm-shaw]

Like the previous questions around OLTP, you really need to analyze the data to determine the difference in performance.
For a TPC-H workload try looking at the queries in isolation and the associated wait events. e.g graph out the execution times for individual queries in a spreadsheet.
Also, are the explain plans the same between different runs for the same queries? Note that the hammerdb client parallelism setting is pg_degree_of_parallel.
Similar to the OLTP workload more parallelism does not necessarily equal faster performance for all queries. Try running with a lower degree of parallel.

Note you may be able to improve performance by partitioning the schema so it uses partition pruning https://www.postgresql.org/docs/13/ddl-partitioning.html#DDL-PARTITION-PRUNING. This is not something done by default as it is difficult to know exactly which feature is supported by each database, however this is the sort of thing often done in improving TPC-H query performance.

[arjunshetty955]

by tuning following parameters the deviation reduced between the iteration and current deviation is 2-4%
diset tpch pg_degree_of_parallel (schema file)
postgres.conf file
#max_parallel_maintenance_workers
#max_parallel_workers_per_gather
#max_parallel_workers
In my environment(postgresql)SET enable_partition_pruning = on by default. need to check on this enable_partition_pruning on/off on performance wise.

I would like to know how to capture tpch-meters like Power,Throughput、QphH via HammerDB command line interface.
Example: Vuser 3:Completed 1 query set(s) in 161 seconds --> is QphH i.e.(Geometric Mean -vuser3-ofQ1 to Q22-query response time).
Vuser 3:FINISHED SUCCESS
ALL VIRTUAL USERS COMPLETE

[sm-shaw]

Great, so that does serve to illustrate that it is a database configuration issue rather than a HammerDB one.
for partition pruning to work, you would manually need to partition your schema.
for the Power, Throughput and QphH calculations this was included in older versions and a spreadsheet provided to make the calculations easier, however this was removed at the request of the TPC as QphH calculations are a trademark violation.
Also note that the official calculations include build times, by default HammerDB loads with inserts to not require staging files and therefore if you were to try and calculate the correct metrics manually then you would likely use the datagen feature to create flat files and then do a bulk load for performance.

[arjunshetty955]

Yes mostly i think server exemplify with those parameters in database configuration issue.
And observed error while building schema
error:
^MVuser 1:CREATING TPCH INDEXES
^MError in Virtual User 1: FATAL: terminating connection due to administrator command
CONTEXT: SQL statement "SELECT fk."l_partkey", fk."l_suppkey" FROM ONLY "public"."lineitem" fk LEFT OUTER JOIN ONLY "public"."partsupp" pk ON ( pk."ps_partkey" OPERATOR(pg_catalog.=) fk."l_partkey" AND pk."ps_suppkey" OPERATOR(pg_catalog.=) fk."l_suppkey") WHERE pk."ps_partkey" IS NULL AND (fk."l_partkey" IS NOT NULL AND fk."l_suppkey" IS NOT NULL)"
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

^MVuser 1:FINISHED FAILED
^MALL VIRTUAL USERS COMPLETE

of course the schema error (the above) is independent on data base configuration and system Hardware settings . even the schema issue was observed with default/basic configuration of PostgreSQLv14 with HammerDBv setup. Is this error can be ignored ?

[sm-shaw]

The key output is here:
FATAL: terminating connection due to administrator command
This means your monitor virtual user connection was terminated with a SIGTERM signal.
For the schema build the monitor virtual user creates the tables and then waits for all virtual users to finish the loading before creating the indexes. While it was waiting it was terminated. The first thing to check is the timeout values.