for HammerDB/src/postgresql /pgolap.tcl, can the max thread be more than 256 if wanting to run tproc-h/tpc-h?

[ashkumar20]

plz help. i have my a server with 192 phyical cores/cpu (384 logical cores/cpus). i need to create vuser for it but due to hammerdb->src->pgolap.tcl file, it restricts to 256 vusers while running tpc-h/tproc-h and then it shows no data to create starting vusers 257 as below. can i edit the file to max thread 384 or in general above 256? if so, after making changes, do i need to edit any other file and or do i need restart the database/service?
snippet from the pgolap.tcl file in hammerdb
set max_threads 256
set sf_mult 1
set cust_mult 15
set part_mult 20
set ord_mult 150
if { [ string toupper $greenplum ] eq "TRUE"} { set greenplum 1 } else { set greenplum 0 }
if { [ string toupper $gpcompress ] eq "TRUE"} { set gpcompress 1 } else { set gpcompress 0 }
if { $num_vu > $max_threads } { set num_vu $max_threads }
if { $num_vu > 1 && [ chk_thread ] eq "TRUE" } {
set threaded "MULTI-THREADED"
set rema [ lassign [ findvuhposition ] myposition totalvirtualusers ]
switch $myposition {
1 {
puts "Monitor Thread"
if { $threaded eq "MULTI-THREADED" } {
tsv::lappend common thrdlst monitor
for { set th 1 } { $th <= $totalvirtualusers } { incr th } {
tsv::lappend common thrdlst idle
}
tsv::set application load "WAIT"
}
}
default {
puts "Worker Thread"
if { [ expr $myposition - 1 ] > $max_threads } { puts "No Data to Create"; return }

[sm-shaw]

256 is a soft limit put in place to prevent over-configuring of TPROC-H loads. A load is often limited not by the number of threads loading but the databases ability to manage the load and adding more virtual users can increase the load time, therefore:
set max_threads 256 was added so if the number of virtual users for the load is higher than this then they report they have no data to load.

And yes as a soft limit this setting can be modified in the file pg_olap.tcl and will increase the limit with no other changes needed.

Note that max_connections in postgresql.conf will also need to be increased beyond the default 100 limit.

Also by default there is a 500ms delay i.e. 1/2 a second between each VU starting, (User Delay(ms) under the /Virtual user dialog) with a larger number of VUs you may want to bring this down to say 100ms.

I recommend experimenting and try changing the limit and seeing if this improves your build times. If it does you can submit a pull request (or provide feedback on your findings) to change the value and it will modified at the next release.
After the builds you can run the schema check in the GUI or checkschema command in the CLI to check the consistency to show that the build is fine after increasing the max_threads (which it should be).

[ashkumar20]

thank u so much sir for the handy info. i have a few more questions, plz help with the same.

1. for the tpch .tcl file, what should i consider for setting up the values for these two or how do i set the optimum values for the below parameters:?
   diset tpch pg_total_querysets 1
   diset tpch pg_degree_of_parallel 2

2. in the postgresql.conf file, what should i consider for editing the values of these? also, the values will vary for tpcc and tpch test runs? if so, how do i know what value needs to be edited/provide for these parameters:

shared_buffers = ??
max_prepared_transactions = ??
max_files_per_process = ??
effective_io_concurrency = ??
max_worker_processes = ??
wal_buffers = ??
max_wal_size = ??
min_wal_size = ??
max_wal_senders = ??
effective_cache_size =??
autovacuum = ??
max_parallel_workers = ??
max_parallel_maintenance_workers = ??

[sm-shaw]

The answer for all this depends on a number of factors such as the PostgreSQL version, the server CPU, memory, I/O etc. This is what HammerDB is for to find your optimal settings through benchmarking, you adjust settings and see how performance differs as a result of the changes you made. For example, for TPROC-H pg_degree_of_parallel sets the number of parallel processes https://www.postgresql.org/docs/current/how-parallel-query-works.html.
For autovaccum see this discussion here #760.

[ashkumar20]

thanks Steve, i see your point here. i should have included these details. i was basically looking for a generic formula if any or generic baseline/ basis that one should keep in mind for assigning values to the above parameters. for my trial runs, i have a 384 threads cpu ( 96 physical core , 2 sockets board and multi thread enabled), mem is 1.5 TB, disk is 16TB, psql-17 version and on rhel 9.5.

[sm-shaw]

Here is a postgresql.conf I have found from a laptop/desktop environment. In particular adjust your shared buffers accordingly and consider huge pages.

listen_addresses = '*'              # what IP address(es) to listen on;
port = 5432                             # (change requires restart)
max_connections = 16384                   # (change requires restart)
shared_buffers = 3096MB                        # min 128kB
huge_pages = off                # on, off, or try
temp_buffers = 128MB                   # min 800kB
work_mem = 128MB                               # min 64kB
maintenance_work_mem = 128MB            # min 1MB
autovacuum_work_mem = -1                # min 1MB, or -1 to use maintenance_work_mem
max_stack_depth = 7MB                   # min 100kB
dynamic_shared_memory_type = posix      # the default is the first option
max_files_per_process = 8000            # min 25
effective_io_concurrency = 32           # 1-1000; 0 disables prefetching
wal_level = minimal                     # minimal, archive, hot_standby, or logical
synchronous_commit = off                # synchronization level;
wal_sync_method = open_datasync
wal_buffers = 16MB                     # min 32kB, -1 sets based on shared_buffers
#checkpoint_segments = 256              # in logfile segments, min 1, 16MB each
checkpoint_timeout = 1h         # range 30s-1h
checkpoint_completion_target = 0.9        # checkpoint target duration, 0.0 - 1.0
checkpoint_warning = 1          # 0 disables
log_min_messages = error                # values in order of decreasing detail:
log_min_error_statement = error # values in order of decreasing detail:
logging_collector = on
autovacuum = on                        # Enable autovacuum subprocess?  'on'
autovacuum_max_workers = 4
autovacuum_vacuum_cost_limit = 3000
cpu_tuple_cost = 0.03
random_page_cost = 1.1
datestyle = 'iso, dmy'
lc_messages = 'en_US.UTF-8'                     # locale for system error message
lc_monetary = 'en_US.UTF-8'                     # locale for monetary formatting
lc_numeric = 'en_US.UTF-8'                      # locale for number formatting
lc_time = 'en_US.UTF-8'                         # locale for time formatting
default_text_search_config = 'pg_catalog.english'
max_locks_per_transaction = 64          # min 10
max_pred_locks_per_transaction = 64     # min 10
archive_mode=off
max_wal_senders=0
min_wal_size=1024
max_wal_size=16384
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

Note that here I am using the HammerDB monitoring based on pg_sentinel to monitor performance. https://www.hammerdb.com/blog/uncategorized/how-to-analyze-postgresql-benchmark-performance-with-hammerdb/

With PostgreSQL autovaccuum is key, don't turn this off.
Also we have set:

wal_level = minimal                     # minimal, archive, hot_standby, or logical
synchronous_commit = off 

This is often misunderstood, firstly try and achieve the optimal the platform can achieve and tune for this. Then look at more 'real world' settings and compare the difference. As an analogy, consider MPG/WLTP for vehicle mileage, this is the best possible achievable measurement in lab conditions, in the 'real world' you are unlikely to achieve this, however you are not likely to exceed it, therefore this sets the bar for potential performance and should be the first thing you aim to measure - then reduce performance from here for your desired settings. A solution with a higher optimal configuration setting is likely to give you better 'real world' performance than one with a lower one (just like MPG/WLTP) although both will be lower in the 'real world' scenario.

Then tune your parameters for your desired configuration e.g. hammerpost can help you automate this
https://github.com/dineshkumar02/hammerpost

There are solutions for you that can automatically tune PostgreSQL such as DBtune https://www.dbtune.com/

Finally, don't forget the OS/platform tuning below the database - e.g. you have a 2 socket server, don't assume that 2 sockets are better than one for database performance you should also test optimal CPU/BIOS/memory/I/O settings etc e.g. memory DIMMS in incorrect slots can reduce performance and is difficult to check without physical server access. Also, performance in the cloud is highly variable.

That should be enough to get you started. You should be in the millions of NOPM with your hardware:
https://www.hammerdb.com/blog/uncategorized/how-to-understand-tpc-c-tpmc-and-tproc-c-nopm-and-what-is-good-performance/

[sm-shaw]

set max_threads 256 has been updated to 1024 for the next release of HammerDB.

[ashkumar20]

thank u sir for all these updates.