TPM sudden drops

[dineshtessell]

Hi Team,

While running the hammerdb benchmark, we observe there is a sudden tpm drop in the output.
During this time, we also observe that there is no load on the database side too.

It seems, sometimes(not sure the interval) the database is not getting enough load, and we observe a sudden tpm drop.

Vuser 1:1 ...,
132 PostgreSQL tpm.          --->> Here
187386 PostgreSQL tpm
373452 PostgreSQL tpm
132 PostgreSQL tpm           --->> Here
673050 PostgreSQL tpm
271956 PostgreSQL tpm.    ---->> Here
Vuser 1:2 ...,
985824 PostgreSQL tpm
934848 PostgreSQL tpm
958890 PostgreSQL tpm
240960 PostgreSQL tpm   -->> Here

Here is the run .tcl

dbset db pg
dbset bm TPC-C
diset connection pg_sslmode disable
diset connection pg_host xxxx.xxxxx
diset connection pg_port 5432
diset tpcc pg_superuser master
diset tpcc pg_superuserpass Tessell123
diset tpcc pg_storedprocs true
diset tpcc pg_total_iterations 100000000
diset tpcc pg_driver timed
diset tpcc pg_rampup 0
diset tpcc pg_duration 5
diset tpcc pg_count_ware 55000
diset tpcc pg_allwarehouse false
vuset logtotemp 1
loadscript
tcstart
vuset vu 100
vuset delay 0
vucreate
vurun
vudestroy
tcstop

The target database is having 200GB RAM, and 24 CPU and 7TB storage.
Trying to understand what is causing these sudden tpm.
Please let us know if we miss something here.

Thanks,
Dinesh

[sm-shaw]

Hi,
you need to look at your database metrics. hammerDB includes a PostgreSQL statistics viewer as described here: https://www.hammerdb.com/docs/ch07s05.html

Find your top wait events to see what you are waiting for (if you can't use the GUI query PG sentinel manually).

It is almost certainly a configuration issue related to buffer cache, redo log, checkpoints and/or vacuuming so also look at your PostgreSQL logs as well.

[dineshtessell]

Thank you @sm-shaw

You said right, the PostgreSQL database was doing CHECKPOINT of 50GB data,
during that time period we got very low TPM.

After tuning some of the database parameters, TPM got stabilised