Random warehouses for each iteration

[dineshtessell]

Hi @sm-shaw,

We have been using HammerDB to get some benchmark numbers, and we are doing it from last few weeks.
We are getting very good NOPM and TPM, but we are struggling to get more IOPS from the benchmark.
While we run the HammerDB benchmarks, most of the time the OS system cache is getting utilised, and unable to
force the HammerDB to go to disk rather the cache.

I understand that, tpcc is not a iops benchmark but why can't we use HammerDB to get this IOPS benchmark too.

By tweaking the PostgreSQL OLTP behaviour as below, we are able to hit the disk truly hard, and able to get the IOPS along with TPM and NOPM.

@@ -3201,8 +3201,12 @@ switch $myposition {
         set abchk 1; set abchk_mx 1024; set hi_t [ expr {pow([ lindex [ time {if {  [ tsv::get application abort ]  } { break }} ] 0 ],2)}]
         for {set it 0} {$it < $total_iterations} {incr it} {
             if { [expr {$it % $abchk}] eq 0 } { if { [ time {if {  [ tsv::get application abort ]  } { break }} ] > $hi_t }  {  set  abchk [ expr {min(($abchk * 2), $abchk_mx)}]; set hi_t [ expr {$hi_t * 2} ] } }
-            set choice [ RandomNumber 1 23 ]
-            if {$choice <= 10} {
+
+set w_id  [ RandomNumber 1 $w_id_input ]
+set stock_level_d_id  [ RandomNumber 1 $d_id_input ]

Here, as you see we are hitting a random warehouse and random district id for each iteration, rather
hitting the same warehouse and same district id for the whole iteration. By making only above changes, we are able to generate 100,000 to 150,000 IOPS along with the NOPM and TPM numbers.

By sharing this idea with you, would like to know your suggestions/inputs on this.
If you are okay with this idea "diset use_random_warehouse true" (something like this), we are happy to submit the PR.

Kindly let us know.

Thanks,
Dinesh Kumar

[sm-shaw]

Hi,
HammerDB already has something similar to this but more advanced with the "use all warehouses feature"
This is described here https://www.hammerdb.com/docs/ch04s06.html

Use All Warehouses: By default each Virtual User selects a home warehouse at random from at the start of a test and remains with that home warehouse. Therefore for example if there are 100 warehouses created and 10 virtual users selected to run the Driver Script then most of the activity will take place on 10 warehouse only. This option means that the Virtual Users select a new warehouse for each transaction from an available list divided between all Virtual Users at the start of the test therefore ensuring greater I/O activity.

Rather than being purely random it divides the available warehouses out between the virtual users and then each virtual user selects from the allocated set. When you set this it prints the allocated warehouses at the start for each virtual user.

[dineshtessell]

Hi @sm-shaw ,

We ran a simple benchmark(Read Only - 15000 warehouses) as below with and without said changes.

dbset db pg
dbset bm TPC-C
diset connection pg_sslmode disable
diset connection pg_host xxxx.xxxx
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 1000000
diset tpcc pg_driver timed
diset tpcc pg_rampup 0
diset tpcc pg_duration 30
diset tpcc pg_allwarehouse True
vuset logtotemp 1
loadscript
tcstart
vuset vu 200
vuset delay 0
vucreate
vurun
vudestroy

Without Random Warehouse for each iteration
Max IOPS: 44145.00
Avg IOPS: 15950.01

With Random Warehouse for each iteration (With the proposed change)
Max IOPS: 109863.00
Avg IOPS: 95928.80

Disk IOPS are calculated for duration 30 minutes with the help of iostat command.
Please let me know if you need any further details.
Looking forward for your inputs.

Thank You,
Dinesh Kumar

[sm-shaw]

Hi,
Many thanks for the additional insights. Firstly, it is useful feedback that the use all warehouses took 5-8 minutes to distribute the warehouses, this will be because of the output of the distribution rather than the actual calculation itself, and therefore this is something we could look at about whether this needs a verbose/non-verbose option to suppress the output for the distribution when there is a large warehouse count.

Also, you are right that the main goal of HammerDB traditionally is not IOPs. In fact, HammerDB's initial design challenge was to devise a database benchmark that did not depend on storage performance when this required a storage array of spinning drives, this is why the default workload is intentionally cached. However, since then as I/O performance and capacity has improved with SSDs we see more questions around increasing I/O. The key question is whether this is a valid goal at the expense of NOPM and TPM?

Before adding such a feature, the most important consideration is the impact across all databases on both windows and Linux rather than just PostgreSQL on Linux. You mention the file system cache, but Oracle for example uses Oracle ASM and does not use the file system cache, so the impact may be different on different databases on different OS's.

Also, it should be considered whether it is not easier to increase I/O by reducing the buffer cache size and/or recovery time settings if the main goal is to increase I/O throughput.

[dineshtessell]

Hi @sm-shaw,

As you mentioned Oracle has its own file system "ASM", which is derived especially for the oracle database engines.
Oracle users use SLOB to test their ASM disk IOPS.

In this cloud era, there are many storage engines emerged into the market, and some of the storage engines especially designed for the databases. For example, consider "ceph", "longhorn" which provides a clustered storage engine for the stateful applications like
databases. I strongly believe we should have something related to disk benchmark too.

Also, you are right that the main goal of HammerDB traditionally is not IOPs. In fact, HammerDB's initial design challenge was to devise a database benchmark that did not depend on storage performance when this required a storage array of spinning drives, this is why the default workload is intentionally cached. However, since then as I/O performance and capacity has improved with SSDs we see more questions around increasing I/O. The key question is whether this is a valid goal at the expense of NOPM and TPM?

Aren't the NOPM,TPM numbers are the results of stressing only CPU/MEMORY without stressing the DISK (The true source of the database)?

Before adding such a feature, the most important consideration is the impact across all databases on both windows and Linux rather than just PostgreSQL on Linux. You mention the file system cache, but Oracle for example uses Oracle ASM and does not use the file system cache, so the impact may be different on different databases on different OS's.

You are right @sm-shaw. But Oracle users do use SLOB to stress the ASM, which do the random disk lookups as I said.
The author of SLOB wrote this PG disk benchmark tool.

Also, it should be considered whether it is not easier to increase I/O by reducing the buffer cache size and/or recovery time settings if the main goal is to increase I/O throughput.

@sm-shaw , we tried this option, but databases just submit their I/O request to filesystem.
In general, I don't think we would be having an option to always do "Cache miss" rather the "Cache hit".
Because, databases are always love to do "Cache hit" than "Cache miss" which is what we are trying to propose.

If my proposed idea is not good or not making any sense then kindly guid me. Also, please suggest me how we can squeeze the disk with the help of HammerDB. Will try to contribute as much as I can.

Thank you again for the wonderful tool.

[sm-shaw]

Hi, Yes SLOB is a very good example (and a very good tool) for testing I/O. In contrast HammerDB is designed to test the entire database.

It is a misunderstanding to think that testing the database is only testing the CPU and or memory, the hammerdb test is holistic, and you need a memory and I/O configuration to match the throughput that the CPU can deliver to achieve maximum performance. One aspect that was identified early on was that HammerDB with a correctly configured database would deliver a very close performance ratio between 2 systems as would a full audited TPC-C test (i.e. if system A outperformed system B by 1.25X in a full TPC-C test then the HammerDB result would also be close to 1.25X), because the full test would configure a highly performant disk subsystem to ensure that CPU utilisation is maximised. HammerDB was deliberately designed to do this without needing the high cost of the disk configuration.

Relational databases are designed to cache data in memory and therefore trying to deliberately bypass this to derive an IOPs based database is somewhat artifical and tool like SLOB is much better for this with both tools being complementary.

Of course being open-source and scripted you can change things as you wish (this is deliberate in HammerDB as well) however this was discussed in the TPC-OSS and for the time being adding an IOPs based configuration is not desired in addition to the use all warehouses feature. (although it could make sense adding a non-verbose option here).

[dineshtessell]

Hi @sm-shaw ,

Thank you for the inputs, I see it as a good discussion, which is helpful to me.

I see, HammerDB is a simulation of an e-commerce application,
which forks the required number of db connections, and run it's load.

I also see HammerDB is a pure database engine benchmarking, which evaluates
the database engine capacity. If we want to use HammerDB to get the best hardware utilisation,
then we have that numbers from the database engines.

As I said, database likes to do cache the data to give you the best response time.

I am trying to understand here, why can't we provide the complete benchmarking solution to do the both.
I respect the community decision, but why should I need to use separate disk benchmark tool
as I have a single HammerDB benchmark, which is holistic.

Can't we have strategies like "Cache Hit/Cache Miss" benchmarks. Aren't they both relate to database ?

As HammerDB is open source, I can maintain the said changes in a fork, but trying buy more suggestions from you.

[sm-shaw]

Hi, HammerDB TPROC-C is a derived workload based on the TPC-C specification from the TPC more details are here https://www.tpc.org/tpcc/. It is important to remember that any additions need to support and be tested on all databases, SQL Server, Oracle, Db2, MySQL, MariaDB & PostgreSQL all both on Windows and Linux. There are essentially 2 approaches

1. Cached - the default workload we are discussing to determine the highest performance.
2. Scaled - this uses the asynchronous scaling option and is closer to the workload in the specification. Here 1 connection will do appx 1 NOPM (so can be used for fixed throughput) and therefore you need a large configuration of virtual users and warehouses for high throughput. This will need middleware to handle the volume of connections setup and a good level of expertise to optimize, however when done correctly this will generate higher levels of I/O as designed in the specification.
   Most people use the cached workload as it is quicker, lower cost and gives the same result as a fully optimized scaled workload. (As we found with HammerDB performance ratios compared to the full tests originally).

[dineshtessell]

Hi @sm-shaw ,
Thank you for the suggestions/inputs.

Agreed that most people loves to get more performance, and they don't see whether the data is coming from cache or disk.
I try to test this functionality in other database engines too, and will let you know the behaviour.

We just tested this with Readonly workloads, which increased the IOPS in greater.
Will do more test and will update the thread here.