Large variation in HammerDB TPC-C TPM/NOPM results across runs with same configuration

[vijivignesh15112013-crypto]

I'm running HammerDB benchmarks on PostgreSQL and observing large variation in TPM and NOPM across test runs, even with the same configuration on the same server .

Below are the configuration:

PostgreSQL 17.4 on Ubuntu 22.04

16 vCPU, 62 GB RAM

HammerDB TPC-C benchmark

20 virtual users, 100 warehouses, 3h test duration.

Same config same VM same duration

Below is the out put:

Run TPM NOPM

1 308659 134092
2 298998 129852
3 474245 206136
4 456699 198488
5 283805 123287
6 449005 195082

Questions:

Why do TPM/NOPM vary so much between runs?

How can I make the results more stable or consistent?

What amount of variation is considered normal in HammerDB tests?

Thanks

[sm-shaw]

That is a good insight and exactly the sort of findings that HammerDB is there to investigate. HammerDB has been designed from the outset to be repeatable and for all supported databases can deliver runs within 1%/test noise variation including PostgreSQL. So you have identified a configuration issue in your environment.

Firstly note that there is variation between the runs, but there is also likely a variation within your runs. Look at the transaction counter (https://www.hammerdb.com/docs/ch06s06.html) statistics for a run, you can use the web service for this https://www.hammerdb.com/docs/ch10s02.html - you are likely to see the transaction count for an individual run rising and falling or even stalling i.e. dropping to 0, whereas a good measurement should show a straight transaction counter graph.

Then your next step is to drill down into the database statistics and match them up to the transaction counter, when the transaction count falls or even stalls to answer whats happening?

Fortunately HammerDB has tools exactly for this purpose with the Active Session History Viewer. (The HammerDB GUI can also be run in a browser if running remotely) Follow the instructions here https://www.hammerdb.com/docs/ch07s07.html to install pg_sentinel and then run the HammerDB active session history tool. Note that this will record statistics over time, so you can then drag the window over a particular time period when your transaction rate drops and see exactly what your PostgreSQL database was doing at that time to cause it. Below is an example from the documentation where we are seeing a lot of CPU which is a good sign. Look for variations over time.

Having done this from what you find, you will then most likely want to start looking into PostgreSQL checkpoint and vacuuming configuration to improve the consistency of the throughput.

[vijivignesh15112013-crypto]

Thank you for the response . will check