Clarification on Aggregating TPM/NOPM from Multiple HammerDB Clients

[razupro]

I’m currently benchmarking database performance on the OpenShift platform using HammerDB, focusing on TPCC workloads across databases like PostgreSQL deployed in OpenShift containers.

In my methodology, I use multiple HammerDB clients connecting to a single database instance to measure peak TPM (Transactions Per Minute). Typically, I begin with one HammerDB client configured with 40 virtual users (VUs), then incrementally increase the number of clients (e.g., 2, 3, 4, etc.) while maintaining the same VU configuration per client. The goal is to observe how performance scales and identify the maximum TPM achievable. Key parameters such as VUs per client, warehouse count, and other test settings are adjusted to optimize performance.

To measure performance, I aggregate the TPM and NOPM (New Orders Per Minute) metrics reported by all active HammerDB clients. For instance, if six clients are connected to the same database during a test run, I sum the TPM/NOPM values from each client to get the total performance for that database instance.

I’m seeking confirmation on whether this approach—aggregating TPM/NOPM across multiple HammerDB clients targeting the same database—is valid. I attempted to cross-check HammerDB's reported metrics with actual transaction counts at the database level, but encountered inconsistencies.

Specifically, while HammerDB (combined across all clients) reported approximately 2.4 million TPM / 1.1 million NOPM, querying the database directly yielded about 9.7 million transactions during the same test. I'm unsure how to reconcile these figures and would appreciate clarification on how HammerDB calculates TPM/NOPM versus what the database logs.

postgres=# -- During benchmark, check actual database transaction rate
SELECT
    datname,
    xact_commit + xact_rollback as total_transactions,
    (xact_commit + xact_rollback) / EXTRACT(EPOCH FROM (now() - stats_reset)) * 60 as tpm_database
FROM pg_stat_database
WHERE datname = 'tpcc_db';
 datname | total_transactions | tpm_database
---------+--------------------+--------------
 tpcc_db |            9760054 |
(1 row)

Can someone please help me on this. Thanks

Raja

[sm-shaw]

Hi,
So if I understand your methodology correctly, then unfortunately the approach does not sound correct, however there are definitely ways to achieve what you want to do.

So the NOPM metric is retrieved from the database schema and the TPM metric is retrieved from the database instance as you show. So you say single database instance and this is where it is not entirely clear, as I am assuming you mean both a single database instance and database, eg so everything is connecting to one database eg called TPCC, or alternatively do you have one PostgreSQL cluster with the correct terminology but multiple databases such as TPCC1, TPCC2, TPCC3 etc?

If you are connecting to just the one database (just TPCC as an example) then regardless of the number of HammerDB clients connecting you should only retrieve the NOPM and TPM values from the one connection for it to be valid. If however you are connecting to multiple databases then aggregating NOPM is correct as NOPM is database specific but not TPM which is across the entire database instance.
Instead HammerDB provides the primary and replica modes https://www.hammerdb.com/docs/ch08.html for this scenario. This works in both the GUI and CLI. Here you configure one primary and then connect multiple replicas. You then run the workload from the primary and this will trigger and coordinate the replicas and only collect the NOPM and TPM values from the primary connection. This feature was originally designed to test Oracle RAC clusters by configuring HammerDB clients to connect to specific database nodes to balance the load across the cluster (note with Oracle RAC you have one physical database on shared storage but multiple database instances).

Now, there is another connection specific option whereby one instance of HammerDB can create a pool of connection to connect to multiple databases at the same time https://www.hammerdb.com/blog/uncategorized/hammerdb-v4-0-new-features-pt4-connect-pooling-for-clusters/. Again this is for database clusters but is intended for when you want one instance of HammerDB to connect to different database instances and run different stored procedures on each e.g. if you had clustered database you can run read/write transactions on the primary instance and read only on the standby. You configure the multiple connections in the XML configuration and decide which transactions to run against each connection.

Now again in this case the NOPM and TPM is only valid from the primary connection but there is nothing to stop you connecting to multiple separate unconnected databases instances, therefore with this feature HammerDB will also report client side transactions as well as retrieving the standard NOPM and TPM values just from the primary instance. Because of this you can also use this feature in any scenario you wish to capture and report client side transactions.

So, with both these features you can configure one HammerDB client to connect to multiple database instances or configure multiple HammerDB clients to connect to one of more databases or use both to test pretty much any database multi-instance configuration. However NOPM and TPM are database (not client side) metrics so querying the same data from multiple locations will report the same thing.

[razupro]

Thanks for the detailed response, Steve. Your explanation cleared up my confusion. In my case, multiple HammerDB clients were connecting to a single database (tpcc_db), which led to some uncertainty about how to calculate the TPM.

[razupro]

Hi Steve,
I'm trying to determine the maximum database performance I can achieve from my OpenShift cluster. I've observed mixed performance results when testing different container configurations—such as 16 CPU / 64 GB memory, 32 CPU / 128 GB memory, and 8 CPU / 32 GB memory.

The reason for experimenting with these configurations for my PostgreSQL container is that I'm not sure what a typical production setup looks like. In particular, I'm unclear about how many database containers or VMs are typically deployed per OpenShift cluster or per worker node. Additionally, I’m uncertain about the recommended number of replicas for a database within the same cluster—especially in terms of balancing performance, availability, and resource usage.

Unfortunately, the information available online hasn’t provided any solid answers. Do you have any suggestions or best practices for these scenarios? For context, I'm using PostgreSQL containers from the official Postgres image.

[sm-shaw]

So, generally this is a 'it depends' answer and in particular on the actual hardware configuration you are running.

https://www.hammerdb.com/blog/uncategorized/how-to-analyze-postgresql-benchmark-performance-with-hammerdb/

I would also recommend setting up the HammerDB metrics based on pg_sentinel https://hammerdb.com/docs/ch07s07.html - that allows you to look at the performance of specific database and the wait events that impact performance. You can then look more into the resource allocation across the VMs.

Also note that there is this post from Red Hat about running databases on OpenShift with HammerDB tests, and although not PostgreSQL this post may contain some suitable references:
https://www.redhat.com/zh-tw/blog/running-microsoft-sql-server-2019-openshift-using-red-hat-openshift-container-storage

[razupro]

Thanks, Steve. The information you shared is invaluable. It definitely helps identify the bottleneck and fine-tune it. Thanks again!