Postgres vs MySQL Sysbench Latency
What open source database is faster? Postgres or MySQL? It's surprisingly hard to find latency benchmark comparisons between these two databases.
Here at DoltHub, we're building the world's first version controlled SQL database, Dolt. Dolt is MySQL compatible. Last year we began development of Doltgres, a Postgres compatible version of Dolt. One of the main questions we get is, how much slower is Dolt than MySQL? We've spent the past five years perfecting our benchmarking approach to answer this question. With the release of Doltgres, we've ported these benchmarks over to Postgres. This gives us the unique opportunity to compare MySQL to Postgres using our latency benchmarking suite.
So which database is faster, MySQL or Postgres? This blog will answer.
The Results
Let's start with the results. We'll explain the benchmarking approach after. Below find the latency comparison of Postgres 15.5 to MySQL 8.0.35 with vanilla configuration on an EC2 host with network attached EBS storage using an extended suite of Sysbench benchmark tests.
| Sysbench Test | Postgres 15.5 Median Latency (ms) |
MySQL 8.0.35 Median Latency (ms) |
Raw Difference (ms) | Multiple |
|---|---|---|---|---|
| covering_index_scan | 1.79 | 2.07 | 0.28 | 1.2 |
| groupby_scan | 5.28 | 14.46 | 9.18 | 2.7 |
| index_join | 1.96 | 1.37 | -0.59 | 0.7 |
| index_join_scan | 0.69 | 1.27 | 0.58 | 1.8 |
| index_scan | 18.28 | 34.33 | 16.05 | 1.9 |
| oltp_point_select | 0.14 | 0.18 | 0.04 | 1.3 |
| oltp_read_only | 2.52 | 3.49 | 0.97 | 1.4 |
| select_random_points | 0.21 | 0.34 | 0.13 | 1.6 |
| select_random_ranges | 0.41 | 0.39 | -0.02 | 1.0 |
| table_scan | 18.61 | 34.95 | 16.34 | 1.9 |
| types_table_scan | 44.98 | 75.82 | 30.84 | 1.7 |
| Read Average | 1.6 | |||
| oltp_delete_insert | 2.07 | 8.13 | 6.06 | 3.9 |
| oltp_insert | 1.03 | 3.82 | 2.79 | 3.7 |
| oltp_read_write | 4.33 | 8.58 | 4.25 | 2.0 |
| oltp_update_index | 1.06 | 3.89 | 2.83 | 3.7 |
| oltp_update_non_index | 1.04 | 3.89 | 2.85 | 3.7 |
| oltp_write_only | 1.67 | 5.47 | 3.80 | 3.3 |
| types_delete_insert | 2.14 | 7.84 | 5.70 | 3.7 |
| Write Average | 3.4 | |||
| Overall Average | 2.3 |
Postgres is approximately 60% faster on read benchmark tests and approximately 3.5 times faster on write benchmark tests. Overall, this works out to Postgres being approximately 2.3X faster than MySQL on this benchmark suite on this hardware configuration. MySQL performs materially better on a single benchmark test, index_join and slightly better on another, select_random_ranges. Otherwise, on a wide range of benchmarks, Postgres is faster than MySQL.
The Set Up
Here is a detailed explanation of the methodology used to gather these benchmarks.
Frequency
We run Sysbench benchmarks of MySQL vs Dolt and Postgres vs Doltgres nightly and on every Dolt and Doltgres release. The MySQL and Postgres latency returned is fairly stable, within 2% variance run to run. We actively monitor these metrics because we use them to detect latency regressions in Dolt and Doltgres.
Benchmark Host
For each benchmark run, we procure an m5a.xlarge EC2 instance running Ubuntu with 4 2.5 GHz AMD EPYC 7000 series processors and 16 GB of RAM. We attach an EBS GP3 disk with 30GB of storage. EBS GP3 is backed by solid state drive (SSD) disk.
We think this hardware set up is representative of typical MySQL and Postgres deployments for workloads of much larger magnitude than the Sysbench benchmark tests. As you will see, the Sysbench benchmark tests operate on tables of less than 1 million rows so the host is under no memory or disk pressure. Moreover, the entire table can fit in memory so caching is highly effective for these benchmarks.
MySQL Set Up
The MySQL version we benchmark against against is 8.0.35. The my.cnf is what ships with MySQL. There is no special configuration set for the benchmark.
Postgres Set Up
The Postgres version we benchmark against is 15.5. There is no special configuration set for the benchmark.
Sysbench Tests
The original Sysbench testing tool is open source.
sysbench is a scriptable multi-threaded benchmark tool based on LuaJIT. It is most frequently used for database benchmarks, but can also be used to create arbitrarily complex workloads that do not involve a database server.
Sysbench itself is a program used to run a configurable set of tests and report results. Sysbench ships with a suite of database tests written in Lua. As you can see, they are relatively simple tests where a single table is created and then a single query is executed repeatedly. For instance, the select_random_points test, runs the following prepared SQL query:
SELECT id, k, c, pad
FROM sbtest1
WHERE k IN (?)We have forked these benchmark Lua scripts and added some additional read and write tests to enhance the benchmark coverage for a wider variety of SQL use cases. Notably, we've added indexed join benchmarks and some additional types coverage. Types coverage tests text and json type columns.
We run the each of the tests for 120 seconds and report median latency in milliseconds.
Future Work
Sysbench database benchmark tests are rather simple. There is no concurrency, only a single table, and a single query per test. These tests are not necessarily indicative of a production database workload. The standard OLTP database benchmark is TPC-C. TPC-C has multiple, larger tables and tests concurrent reads and writes in transactions.
We currently run TPC-C against Dolt and MySQL but not Doltgres and Postgres. When we get TPC-C running against Postgres, we will follow up with a blog post outlining results there. We expect Postgres to perform approximately 2X better on TPC-C, similar to our suite of Sysbench tests.
Conclusion
Postgres is over 2X faster than MySQL on a standard suite of Sysbench benchmark tests. Sysbench tests are simple tests. We wonder how Postgres and MySQL compare on the more complicated, TPC-C benchmark. Curious about Dolt, the world's first version controlled database, or database performance? Come chat with us on our Discord.