Sysbench: MySQL vs Postgres for an IO-bound workload

This compares results for MySQL/InnoDB and Postgres via IO-bound sysbench using the data I shared for Postgres and MySQL. I previously shared a comparison for an in-memory workload with sysbench. My goal is to document how performance and efficiency have changed from MySQL 5.6 to 8.0 and from Postgres 11 to 13. I try to avoid benchmarketing but I am curious about how system software evolves over time. I share many numbers here (performance is a vector not a scalar) and am happy that one DBMS isn't strictly faster or more efficient than the other.

Summary:

• On average, old Postgres was slower than old MySQL while new Postgres is faster than new MySQL courtesy of CPU regressions in new MySQL (for this workload, HW, etc)
• The InnoDB clustered PK index hurts throughput for a few queries when the smaller Postgres PK index can be cached, but the larger InnoDB PK index cannot.
• The InnoDB clustered PK index helps a few queries where Postgres must to extra random IO to fetch columns not in the PK index

Overview

Much more detail on the benchmarks is here for Postgres and here for MySQL/InnoDB. The summary is that I used 1 table with 400M rows and small servers. Tests were run for Postgres (11.10, 12.4 and 13.1) and MySQL (5.6.49, 5.7.31, 8.0.22) but my focus here is comparing Postgres 11.10 with MySQL 5.6.49 and then Postgres 13.1 with MySQL 8.0.22.

Results

The tests are in 5 groups based on the sequence in which they are run: load, read-only run before write-heavy, write-heavy, read-only run after write-heavy and insert/delete. 

I have scripts that generate 3 summaries -- absolute throughput, relative throughput and HW efficiency. Absolute throughput is the QPS or TPS for a test. Relative throughput is the QPS or TPS relative to the base case. The HW efficiency report has absolute and relative results for CPU and IO per operation.

I use ratios (relative throughput & relative HW efficiency) to explain performance. For this post the denominator (the base case) is MySQL and the numerator is Postgres. A throughput ratio < 1 means that Postgres is slower. For HW efficiency, CPU and IO per operation, a ratio > 1 means that Postgres uses more CPU or IO per operation.

I did two comparisons: one for Postgres 11 vs MySQL 5.6, another for Postgres 13 vs MySQL 8.0 and the files are in github. Files that end in old are for Postgres 11 vs MySQL 5.6 while files that end in new are for Postgres 13 vs MySQL 8.0. There are files for absolute throughput (old and new), relative throughput (old and new) and HW efficiency (old and new).

I am still figuring out how to present this data and balance both the ease of writing reports while making it not too hard to read. In this case I put the results for throughput and HW efficiency into a spreadsheet. The spreadsheet has many rows and 10 columns. The rows are the order in which the tests were run. The columns are:

• Test - the name of the test
• Range - the value of the range argument for Lua scripts that determines the number of rows processed for some of the tests.
• qps.new, qps.old - throughput ratios
• cpu/o - new, cpu/o - old  - ratios for CPU/operation
• r/o - new, r/o - old - ratios for storage reads/operation
• rKB/o - new, rKB/o - old - ratios for storage read KB/operation

The ratios have (Postgres value / MySQL value), new means this is for Postgres 13.1 vs MySQL 8.0.22 and old means this is for Postgres 11.10 vs MySQL 5.6.49. For the HW efficiency values (/operation) the operation is one of queries, statements, transactions.

In the spreadsheet I highlighted entries in gold when the throughput for Postgres improved by more than ~5% relative to MySQL from qps.old to qps.new (done for 19 of 43 tests). I also highlighted entries in silver when CPU/operation for Postgres improved by more than ~5% relative to MySQL from cpu/o - old to cpu/o - new (done for 26 of 43 tests). This makes it easier to understand the impact from CPU regressions.

New Postgres has improved relative to new MySQL. The geometric mean for throughput ratios is 0.95 for qps.old and 1.04 for qps.new. CPU efficiency is the reason for the improvement as the geometric mean for cpu/o is 1.12 for cpu/o - old and 0.88 for cpu/o - new. Neither MySQL nor Postgres dominate on these tests, MySQL is faster for some and Postgres is faster for others. But the advantage has shifted towards Postgres thanks to CPU efficiency. On average, old Postgres was slower than old MySQL while new Postgres is faster than new MySQL courtesy of CPU regressions in new MySQL. 

There were a few outliers where MySQL is 2X faster than Postgres or vice versa and I will explain these using the names from the Test column in the spreadsheet.

• points-covered-pk.pre (row 10) - Postgres is more than 17X faster. The PK index is covering for the query used by this test. However, InnoDB has a clustered PK index that includes all columns for the table and is much larger than the Postgres PK index. The r/o columns from the spreadsheet show that InnoDB does ~30X more storage reads/query than Postgres and that would explain the throughput difference. This also explains why Postgres is much faster for:
• range-covered-pk.pre (row 14) - Postgres is more than 3X faster
• points-covered-pk (row 34) - Postgres is more than 20X faster
• range-covered-pk (row 38) - Postgres is more than 3X faster
• update-inlist, update-nonindex (rows 18 & 20) - these do updates that don't require secondary index maintenance and MySQL gets ~2X more updates/second. MySQL modifies leaf pages of the clustered PK index while Postgres modifies both PK index leaf pages and heap-organized table pages. That might explain why Postgres uses more CPU and random IO.
• insert (row 44) - InnoDB is ~3X faster and I suspect the change buffer is the reason

Finally, there are tests that benefit from the InnoDB clustered PK index, although the benefit there is not as extreme as the impact above where it hurts InnoDB. One example are the read-only tests (rows 8-10 and 26-28). Those queries are also included in the read-write tests (rows 24, 25) that represent the traditional sysbench test. The benefit is that InnoDB does a PK index range scan to evaluate the query predicate and get all columns, while Postgres must fetch rows via random page reads from the heap-organized table to get columns not in the PK index.