Load testing MariaDB: factors affecting read write ratio

[ananthu-dev]

I am performing TPROC-C benchmarks on MariaDB. I have 12 MariaDB VMs running on a vSphere cluster with 4 ESXi hosts; 3 VMs per host. This is a non-clustered setup; each MariaDB instance is standalone. Each VM has 8 vCPUs and 32 GB RAM. First I tried with 200GB Database and read write ratio in storage (IOPS) observed is almost 50:50. Then I increased the DB size to 1 TB. Here, workload is mostly read. Read to write ratio is almost 90:10 now.
I would like to whether HammerDB adjusts test parameters based on DB size or anything else for that matter.

Also TPMs achieved while testing on 1TB DB is 1/3 of what is achieved on 200GB DB. Could you help me figure out why this is happening?
Is it because larger DB means more data is outside RAM? I have set MariaDB bufferpool size as 26GB.

Please let me know if additional info is needed to answer my question.

[sm-shaw]

This is a frequent question and if you have not done, so already it is worth watching the introductory video here https://www.hammerdb.com/about.html, in particular the section on How many warehouses? at around 30 mins in.

So by default most of the workload is on a virtual users home warehouse - so if you create a schema of 100 warehouses or 1000 warehouses and run 10 virtual users most of the activity will be on 10 warehouses - regardless of how many you have created.

There is an option "use all warehouses" that then means a virtual user will swap its home warehouse for each transaction and therefore use more I/O than with the default method. Also the event driven scaling gives you more options to scale up the number of user https://www.hammerdb.com/blog/uncategorized/hammerdb-v3-3-event-driven-scaling/ (You would probably need maxscale for this).

Make sure you check your settings for innodb_flush_method - O_DIRECT will bypass the file system cache - otherwise you have an additional cache layer below MariaDB.

If a larger schema is performing more poorly, make sure you have selected the partition open when creating the schema. innoDB uses clustered indexes (like SQL Server or an Oracle IOT) meaning the data is stored in the index. Partitioning can reduce contention and improve performance as the indexes get larger.

[ananthu-dev]

I have already enabled "use all warehouses" option and O_DIRECT flush method.

If a larger schema is performing more poorly, make sure you have selected the partition open when creating the schema.

Does this mean setting diset tpcc maria_partition true in CLI?

[sm-shaw]

Yes, that is correct. This partitions the ORDER_LINE table.