Inconsistent TPM PostgreSQL database

[coenvk]

As a continuation of this question:

I have been able to run a benchmark and measure the throughput. The following chart shows the TPM over time (measured every second). The first 2 minutes is the ramp-up period.

I was wondering if anyone knows why these results fluctuate so much. Is there any way to solve this to ensure a more consistent TPM?

[sm-shaw]

This tends to be a typically frequent question. You can see a number of other questions in discussions with a similar topic of 'why am I not getting consistent result's but do not post any details on the system hardware, OS, database release, database configuration and performance metrics. So without having done any investigation and provided the data it is almost impossible to answer the question.

To start troubleshooting, try running the GUI transaction counter at a time interval of 10 seconds, the graph should be flat https://www.hammerdb.com/docs/ch06s07.html.

Also, as per the earlier question try running the GUI PostgreSQL metrics e.g. https://www.hammerdb.com/blog/uncategorized/hammerdb-v4-3-new-features-pt1-graphical-metrics-for-postgresql/ - what is the top wait event in the central window? If you can't use the GUI use the PostgreSQL command line to query the performance metrics, so you can see what the database is waiting on.

Having said it is almost impossible to answer the question: invariably when this type of question is asked the top event is the WAL/redo/transaction log so here you would be looking for the WALWrite wait event in the GUI or when querying the PostgreSQL statistics manually. You would then look into the I/O configuration where the WAL is installed.

[coenvk]

Hi! Sorry for not including any system information:

OS Name ============= Microsoft Windows 11 Home
Version ============== 10.0.22000 Build 22000
OS Manufacturer ======== Microsoft Corporation
System Name ========== MSI
System Manufacturer ===== Micro-Star International Co., Ltd.
System Model ========= GP65 Leopard 10SEK
System Type ========== x64-based PC
System SKU =========== 16U7.1
Processor ============ Intel(R) Core(TM) i7-10750H CPU @ 2.60GHz, 2592 Mhz, 6 Core(s), 12 Logical Processor(s)

Postgres information:
PostgreSQL 14.1, compiled by Visual C++ build 1914, 64-bit

[coenvk]

Also, running the transaction counter at a time interval of 10 seconds fluctuates less, but there are still fluctuations.

[sm-shaw]

I would definitely recommend installing pg_sentinel and running the PostgreSQL GUI metrics as described. You can then see exactly what the database is waiting for in the event window. As above, what disk is installed for the WAL file?
Every transaction has to write to WAL this is why more often than not the disk is the bottleneck - this would show as WALWrite in the GUI.