I was doing TPCC testing with HammerDB 3.1 and with mysql as a backend. By looking at the resources consumption during testing, I don't think it does in-memory testing, as there is a lot of IO activity.
So my question is if with mysql server it is possible to do in-memory hammerdb testing, or how can I achieve that?
Using the default mysql configuration otherwise, based on the hammerdb documentation.
For TPCC testing on relational databases the I/O activity will be divided into 2 major areas, the data area and the redo/transaction log or WAL and both of these will be buffered in memory but with a key difference. For the data area you will have a buffer cache or pool into which you read the data blocks or pages, for MySQL and the InnoDB storage engine this is set by for example innodb_buffer_pool_size=64000M. At a basic level during a test rampup you will read most of your data blocks from disk into this buffer pool from where all of the operations on the blocks will take place in memory. Periodically the modified blocks will be written out to disk. To prevent data being lost as result of a failure all of the changes are written out to the redo log and this is flushed to disk on commit. There is an in memory buffer where changes will be queued and potentially flushed to disk together however this buffer will be small as all the changes need to reach persistent media when they happen (so a log buffer larger than 10s of MB will not be filled before it is flushed). Therefore for the TPCC test you will see a lot of write activity to the redo log. If the persistent media (HDD or SDD) cannot keep up with the writes this will be a bottleneck preventing a higher transaction rate from adding more virtual users and therefore need less memory in the data area as by default one virtual user will work mostly on 1 warehouse. If you want to increase the data area activity the "use all warehouses" check-box will increase the number of warehouses that each virtual user will use.