Benchmarking MySQL x PostgreSQL with HammerDB

[lrweck]

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 was expecting both to perform about the same, honestly.

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.

[sm-shaw]

Hi,
If new to databases in general, the first thing I would recommend is getting familiar with database architecture. All of the relational databases that HammerDB tests are similar in many respects so having this grounding can help you tune them. There is an introductory presentation linked here https://www.hammerdb.com/about.html and also a number of blog posts the include links to example parameters and potential performance.
https://www.hammerdb.com/blog/uncategorized/hammerdb-best-practice-for-postgresql-performance-and-scalability/
https://www.hammerdb.com/blog/uncategorized/hammerdb-mysql-and-mariadb-best-practice-for-performance-and-scalability/
https://www.hammerdb.com/blog/uncategorized/testing-mysql-8-0-16-on-skylake-with-innodb_spin_wait_pause_multiplier/
https://www.hammerdb.com/blog/uncategorized/how-to-improve-redo-transaction-log-and-wal-throughput-for-hammerdb-benchmarks/

So key things to learn about are how data is cached in the buffer cache, how checkpoints work to write dirty data out to the data area of the disk, how the redo/WAL/transaction logs work and how the database will wait for transactions to be written to disk if using synchronous commit and how the log size can affect the checkpointing. Also you will want to learn about MVCC (multi version concurrency control) and how this impacts undo. You will also want to consider locking and latching and how this impacts scalability. Performance at one virtual user may not be the same as you ramp up to multiple virtual users. See the blog posts of examples for "performance profiles" for measuring performance right across the system and getting the peak performance. As you scale-up especially on a server with multiple sockets the database software will limit how much CPU you can use due to locking and latching and some CPU will remain idle. The commercial databases can deliver higher performance as the software scales better because the software vendors have worked specifically in this area.

Once you have this grounding then you will be able better to understand the parameters you are setting and how they impact performance eg make sure your redo/WAL files are on your SSD and gives good performance, size the buffer cache to 8GB (1/2 of your RAM and also put the CPU in performance mode. Here one thing that you do not mention is the OS you are running. If running Linux please check the pstate and cstate drivers and run the single threaded performance calibration https://www.hammerdb.com/docs/ch04s01.html just to make sure that you are not running in powersave mode - this is the default in for example Ubuntu and many people do not change it. You should be able to run this test and for your CPU see a frequency of 4.7GHz with a tool such as turbostat. also be aware of HWP (hardware managed pstates) you may want to check your BIOS settings for HWP or speed shift to make sure it is enabled. In this case the CPU can manage throttling itself.

Also if comparing performance I would recommend starting on bare metal ie the base OS without containers, virtualization etc. The approach is to start at the point of maximum performance and work your way up by adding layers to then determine the performance impact they have.

For simple single vu performance tests see the introductory presentation on how many warehouses you need as each virtual user does 90% of its work on the home warehouse (I created 20 for a simple single vu test but you need more as you scale up). I would also recommend testing for a shorter period of time (eg 2 to 5 minutes) to start and using the GUI transaction monitor to observe any variability in performance.
For comparison and as a baseline, this is what I measured on a much older Linux PC (Kaby Lake 2 core / max turbo 3.1GHz). So yes PostgreSQL performs better than MySQL for this type of single-threaded test. In particular, PostgreSQL supports more advanced stored procedures and has an update returning operation whereas MySQL needs to use 'select for update'. However be aware that in a server environment PostgreSQL can bloat more and the autovacumming process can impact performance at higher levels.

I hope this gives you a good starting point for tuning as you should be able to exceed these values with your hardware configuration.

[lrweck]

Hello @sm-shaw

Thank you for replying so quickly!

I'm not new with databases, just new to MySQL.

About the links you've shared, some of them recommend disabling features that would compromise data safety in case of a crash, so I'm purposefully not changing those. I was looking at the main outliers when it comes to MySQL tuning to do just the basic, for example buffer cache.

About the performance with increasing virtual users, this is what I have now:

That is why I think there's something amiss. Both of these databases are supposed to compete neck in neck.

I thought innodb_buffer_pool_size = 8G means changing MySQL buffer cache. Maybe I got that wrong?

I need to check whether powersave mode is on and will come back to you.

The point to use containers was to emulate today's common work condition for databases. Since both of them are running containerized, I think it's a fair test. It's not a test about absolute performance, after all (I'm not willing to disable security features in order to just show bigger numbers).

I was testing with 100 warehouses for all my tests. Ran each three times and each time I created a new database instance from scratch.

[sm-shaw]

Hi,
yes, the whole aim of HammerDB is to test exactly what you want to test, so there is no particular right or wrong way to approach it. However, a common error is to test a configuration and then draw conclusions about just one particular component, which is then not valid because the other parts are not considered. I would always recommend starting with a baseline where everything is turned off as much possible, this then gives you a theoretical 'top speed' from the database software with the CPU and memory from which you can measure the impact of the additional components. There is no cost to running the tests, so the more insights you have, the better.
So for your data, no, MySQL and PostgreSQL are not meant to be exactly the same in performance for this type of enterprise test based on the TPC-C specification. For example, as noted, PostgreSQL supports more advanced stored procedures and has features that mean it can run more efficiently. So on your laptop with the additional overhead eg for MySQL with the doublewrite buffer and possibly binlog then yes this could be realistic - however do note that PostgreSQL 13.X and MySQL 8.0.20+ introduced performance changes so depending on the software version the landscape changes frequently. However, be aware that the real test is in a server environment - this is in the millions of NOPM/TPM and here the gap is closer than what you show here.