I am trying to benchmark MySQL with HammerDB using their TPROC-C benchmark. This is part of a research I'm doing where I am benchmarking MySQL and PostgreSQL. After running through all benchmarks with PostgreSQL, I started to benchmark MySQL by was surprised at the difference!
According to HammerDB's site, NOPM should be comparable between RDBMSs, but the numbers were so low that got me thinking about my methodology was wrong or my tuning parameters were hurting performance.
My intention was to run their benchmark with 100 warehouses and varying number of virtual users.
With PostgreSQL I go around 14000 NOPM with a single virtual user, but with MySQL, I get around 3800.
I AM NOT TRYING TO START A WAR
What would be really helpful is if someone can point me in the right direction.
I am running both MySQL and PostgreSQL on Docker, with the latest images.
Both images were limited to 12GB of RAM and HammerDB is running on the host machine.
MySQL is running on InnoDB.
These are the settings I changed from MySQL:
innodb_buffer_pool_size = 8G
innodb_buffer_chunk_size = 1G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 2G
innodb_stats_on_metadata = OFF
innodb_file_per_table = ON
innodb_write_io_threads = 24
innodb_read_io_threads = 24
innodb_thread_concurrency = 0
innodb_io_capacity = 20000
key_buffer_size = 128M
thread_stack = 256K
thread_cache_size = 16
max_heap_table_size = 256M
I am a complete MySQL noob, but I want to get these tests right, that's why I've come here
I am running these on a laptop though, which is less than ideal. Here are the configs:
i7-1165G7 - 4 cores 8 threads HT 16GB RAM M.2 nvme SSD
As for HammerDB options:
Timed Driver Script (same as PG)
1000000 total txs per user (same as PG)
Prepared statements
1m ramp-up time (same as PG)
30m test time (same as PG)
Use all warehouses (same as PG)
Time profile (same as PG)
I'll be more than happy to answer any questions!
I'm not a native speaker, so forgive me for any mistakes.
The advice on the HammerDB website that the NOPM (New Orders per Minute) value is comparable between databases means that NOPM can be used to compare between different databases and systems rather than meaning all the databases should achieve the same throughput. For example, the commercial databases can achieve higher performance and scalability than the open source ones, however this is to be expected as the commercial databases have been tuned over many decades to perform well in the TPC-C tests which the HammerDB OLTP test is derived from. Additionally, databases have been designed to run multiple sessions on multiple CPUs concurrently, and therefore little can be determined about the capability of an individual database by a single user test. On an up-to-date 2 socket server in 2021 expect both MySQL and PostgreSQL to perform at peak in the range of 1,000,000 to 3,000,000 NOPM with multiple sessions depending on the hardware and software configuration. Versions MySQL 8.0.20 and PostgreSQL 13.0 upwards are recommended for higher performance. The HammerDB discussions forum on github are the best place for specific HammerDB tuning advice.