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.

[Ayabuya]

Hi Steve
Im not sure if this is inscope, I was hoping you would point me in the right direction.
HammerDB can't seem to create stored procedures on MS Azure under MySQL flexible server but it can do it on my local Mysql server. I suspect that there's a setting I have to enable on Azure, but I cannot seem to find it. Below is a sample error I get from hammerdb

Vuser 4:mysqlexec/db server: PROCEDURE tpcc.NEWORD does not exist
Vuser 3:mysqlexec/db server: PROCEDURE tpcc.SLEV does not exist
Vuser 6:mysqlexec/db server: PROCEDURE tpcc.PAYMENT does not exist
Vuser 4:mysqlexec/db server: PROCEDURE tpcc.PAYMENT does not exist

Are you able to assist me please

[sm-shaw]

I'm not entirely sure what the issue is here, as stored procedures are a standard feature in MySQL.
However, just to note, HammerDB for MySQL/MariaDB has a "no stored procedures" option that uses client SQL instead and will therefore work without them, although this will be slower as described here https://www.hammerdb.com/blog/uncategorized/why-you-should-benchmark-your-database-using-stored-procedures/

[Ayabuya]

Hi Steve
Thank you once again for the assistance I managed successfully perform a TPROC-C benchmark comparison between INNODB and Memory storage engine.
Just for anyone reading this thread what caused the issue was not waiting for "Complete" on the schema build even though the virtual users had finished loading data.
Upon performing the benchmark comparison I received odd results, that INNODB storage engine was at least 10x faster that the Memory storage engine in both Azure & my local MySQL database.
Are these results close to some you have seen?

[sm-shaw]

This is an excellent finding and shows exactly why we do benchmarks to prove/disprove any preconceptions we may have.
So if you look in the MySQL manual https://dev.mysql.com/doc/refman/8.4/en/table-locking.html it says:

MySQL uses table locking (instead of page, row, or column locking) for all storage engines except InnoDB. The locking operations themselves do not have much overhead. But because only one session can write to a table at any one time, for best performance with these other storage engines, use them primarily for tables that are queried often and rarely inserted into or updated.

So the memory storage engine locks the entire table for every DML statement. This is what you have observed. Also note that InnoDB although disk-based has an in-memory cache (as do all the databases HammerDB tests) so disk based databases are working "in-memory" as well with the redo log logging transactions to disk.

For MySQL and MariaDB InnoDB is the best performing storage engine e.g. https://mariadb.org/how-intel-helps-mariadb-become-even-faster/