PostgreSQL performance deviation on multiple trails on HammerDB benchmark environment

PostgreSQL
1

PostgreSQLv14 performance(NOPM)values deviation for trail-1 vs trail-2.
NOPM values captured with same hammerdbv4.3 scripts(schema and test run) on multiple trails.
The expected behavior the performance deviation should be less than 2 % on multiple trail

Hardware configuration 
Architecture        x86_64 
CPU op-mode(s)      32-bit, 64-bit 
Byte Order:          Little Endian 
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):        8 
L1d cache:           32K 
L1i cache:           32K  
L2 cache:            512K 
L3 cache:            16384K 
OS: RHEL8.4 
RAM SIZE:512 
SSD:1TB 



Postgresql.conf 

autovacuum_max_workers = 16 
autovacuum_vacuum_cost_limit = 3000 
checkpoint_completion_target = 0.9 
checkpoint_timeout = '15min' 
cpu_tuple_cost = 0.03 
effective_cache_size = '350GB' 
listen_addresses = '*' 
maintenance_work_mem = '2GB' 
max_connections = 1000 
max_wal_size = '128GB' 
random_page_cost = 1.1 
shared_buffers = '128GB' 
wal_buffers = '1GB' 
work_mem = '128MB' 
random_page_cost = 1.1 
effective_io_concurrency = 200  

HammerDB Scripts 
    >>cat schema.tcl 
    #!/bin/tclsh 
    dbset db pg 
    diset connection pg_host localhost 
    diset connection pg_port 5432 
    diset tpcc pg_count_ware 400 
    diset tpcc pg_num_vu 50 
    print dict 
    buildschema 
    waittocomplete 

RUN TEST on i.e. start with 1VU then 2, 4 etc 
      Virtual Users   Trail-1(NOPM) Trail-2(NOPM)	%diff	 
        12		99390	           92913	              6.516752  
        140		561429	           525408	             6.415949  
        192		636016	          499574	             21.4526  
        230		621644	           701882	           12.9074
1 Like
2

So what is your question ?

I often see quite a difference between benchmark runs which could depends from stare of the database SSD performance variance, CPU scheduling etc.

If you want to discover what is driving the difference I’d check what is different between the runs - CPU usage ? IO volume ? IO Latency etc

1 Like
3

my question here , what causes the performance deviation on different between the runs . Kindly help me out on commands or tools to check CPU usage/IO volume/IO latency.

1 Like