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.
- 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
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.
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).
- 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
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.
- 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