Benchmarking OLTP on mySQL using the Inno and Memory engine

[Ayabuya]

I would like to find out is one able to test OLTP workloads on mySQL using RDBMS (Inno) and In-memory (Memory) engines ?

[sm-shaw]

Yes, if you see the entry field "Transactional Storage Engine" this enables you to define the storage engine for your schema build for both MySQL and MariaDB

Note however that storage engines have different characteristics: e.g. https://dev.mysql.com/doc/refman/8.0/en/internal-locking.html

MySQL uses row-level locking for InnoDB tables to support simultaneous write access by multiple sessions, making them suitable for multi-user, highly concurrent, and OLTP applications.
...
MySQL uses table-level locking for MyISAM, MEMORY, and MERGE tables, permitting only one session to update those tables at a time. This locking level makes these storage engines more suitable for read-only, read-mostly, or single-user applications.

Therefore as the HammerDB TPROC-C workload is highly, concurrent OLTP InnoDB is the default as the best performing storage engine for this workload. MyRocks supported by MariaDB has also been tested.

[Ayabuya]

Thank you for the prompt response Steve.
I have tried to run hammerDB (v4.3) against Mysql (version 8.0) but I get the below error. Please note I tried this on 3 different windows machines but still no success 😔. Please assist

[sm-shaw]

This is in the documentation here https://www.hammerdb.com/docs/ch01s08.html
You need to have the MySQL 8.0 client library installed, follow the steps in the documentation and then run the librarycheck command in the CLI.

HammerDB CLI v4.3
Copyright (C) 2003-2021 Steve Shaw
Type "help" for a list of commands
The xml is well-formed, applying configuration
hammerdb>librarycheck
...
Checking database library for MySQL
Success ... loaded library mysqltcl for MySQL
...

hammerdb>

[b-anup]

I am also facing the same issue while running the hammerdb client on windows machine and the mariadb server hosted on linux machine.
I have set the environment variable PATH to the C:\Program Files\HammerDB-4.3\lib\mariatcl0.1 and restarted the windows machine but still no luck. It is still giving me following error message : --
couldn't load library "libmariatcl.dll": this library or a dependent library could not be found in library path

it gives the same error message for % package require mariatcl

I am using windows server 2016 Standard version

[sm-shaw]

OK the problem is here:

I have set the environment variable PATH to the C:\Program Files\HammerDB-4.3\lib\mariatcl0.1

The error message says "this library or a dependent library" so it is the dependant library you are missing. (see the documentation on this).

As an example, I type environment variables into the Windows search bar and this shows me the Path, in my case as follows where the MariaDB Windows client is installed, not HammerDB (HammerDB already knows where to find its own libraries).

E:\MariaDB\mariadb-10.5.10-winx64\lib

Then running librarycheck shows it can be found.

HammerDB CLI v4.3
Copyright (C) 2003-2021 Steve Shaw
Type "help" for a list of commands
The xml is well-formed, applying configuration
hammerdb>librarycheck
...
Checking database library for MariaDB
Success ... loaded library mariatcl for MariaDB

hammerdb>

[Ayabuya]

Thank you Steve, I managed to get it working 😊
I have been waiting for the application to finish running the virtual user section for over 24 hours, is this possible on a 32 GB ram i7 CPU @2.9GHZ having assigned 1 virtual user and 1 warehouse?

[sm-shaw]

It is not entirely clear what you mean by the virtual user section - there is a quick start guide here: https://www.hammerdb.com/docs/ch02.html
But basically the steps are: (shown with MySQL on Windows)

1. Build the schema (this took 1 min 17 secs for 1 WH with 1 VUser on a test PC with the database on a slow non-database HDD)
   
2. Check the schema built OK with the test workload if you want, or check it with SQL (as shown below)
   
3. Run a timed workload - in this case 1 minute rampup and 1 minute test - Looking at Task Manager it was clear that the disk the database was on was at 100% and the my.cnf was not optimized so this limited the performance.
   

[Ayabuya]

Thank you Steve I managed to successfully run a timed workload using the INNODB storage engine however, I am struggling to duplicate the same test using a MEMORY engine and get either the error "The table stock is full" when I'm running more than 1 virtual users and "The table 'item' is full" when im running a single user. Do you perhaps have a guide on how use use the Memory engine for MySQL?

[sm-shaw]

This is now getting out of scope for assistance from HammerDB as the tool is working correctly. You need to ask MySQL resources about how to configure the memory engine. Unfortunately we are not able to answer every configuration question about every database.

Having said that, if you are trying to compare InnoDB with the memory storage engine - the following is key:
MySQL uses table-level locking for MyISAM, MEMORY, and MERGE tables - table level locking is not going to be good for performance.

InnoDB should be the default storage engine of choice for transactional workloads with MySQL/MariaDB.

[Ayabuya]

Hi Steve
I had to re-perform the same exercise and I had no issues with the run this time 😊. However, I was hoping you can help me interpret what I received from the results. I set the users to a maximum of 4 and choosing 100 000 transactions for TPC-C after for testing both Inno and Memory storage engine, I received 0 NOPM for InnoDB from 2 -4 users.
What does this 0 mean?

[sm-shaw]

This 0 NOPM means that because you set the total transactions to 100,000 it completed these before you started the timed part of the test and therefore recorded no transactions. The ability to set the total number of transactions allows you to run test scenarios to test connectivity where users are logging on and off during the test. In your case you need to leave this total number of transactions at a higher value.

[Ayabuya]

Thank you Sir. I will increase the total number of transactions as advised 😊

[Ayabuya]

Hey Steve,

I've submitted my results through my technical report. However, a professor has suggested that I should conduct the same experiment on a cloud platform with better computing resources, even if it means paying a bit extra. Do you have any recommendations for a cloud platform that would able me to perform the a similar test?

[sm-shaw]

The 3 biggest Cloud Service Providers are AWS, Microsoft Azure and Google and therefore choosing one of these 3 will bring you most relevance to a wider audience. Be aware that the CPU and I/O configuration in particular will impact the over results you get.

Also note that results in the cloud can be variable. In discussion with a top CSP we found up to 10% variability in results entirely due to cloud resource allocation rather than HammerDB. The CSP cross-referenced the results in a lab environment and confirmed that HammerDB variability was < 1%, however variability from their cloud was up to 10% - therefore account for this and make sure you run multiple tests.