I compare Postgres and MySQL using an in-memory and low-concurrency workload via sysbench. This reuses the data from my previous posts (here and here).
For this workload Postgres used to be slower than MySQL and now it is faster. I hesitate to do direct comparisons because I like both DBMS and don't want to start pointless debates but I care about efficiency and hope that more is done to prevent regressions in MySQL going forward. I also worry that Postgres is faster than MySQL for this workload gets truncated to Postgres is faster than MySQL when this post is discussed elsewhere.
This post compares performance between MySQL 5.6.49 and Postgres 11.10 and then between MySQL 8.0.21 and Postgres 13.1 to document what has changed over the past 3 major versions: MySQL 5.6, 5.7, and 8.0 and then Postgres 11, 12 and 13. I used MySQL 8.0.21 rather than 8.0.22 to avoid the impact from bug 102037. The workload is in-memory & low-concurrency sysbench on a small server. The sysbench tests are microbenchmarks. Context matters so be careful about projecting the conclusions here to your use case.
Summary for this workload:
- MySQL 5.6 does better than Postgres 11.10 for most tests because it uses less CPU/operation
- MySQL 8.0 does worse than Postgres 13.1 for most tests because it uses more CPU/operation
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/TPS that a test gets for 1, 2, and 3 clients. Relative throughput is the QPS/TPS relative to the base case. The HW efficiency report has absolute and relative results for CPU and IO per operation. In this post the base case is the result for MySQL 5.6.49 or 8.0.21.
I use ratios to explain performance and provide two sets below. One uses Postgres 11.10 as the numerator and MySQL 5.6.49 as the denominator. The other uses Postgres 13.1 as the numerator and MySQL 8.0.21 as the denominator. A QPS 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.
The results are in github for absolute throughput, relative throughput and HW efficiency. I annotate the relative throughput and HW efficiency results below. I use vs My56 for the Postgres 11.10 vs MySQL 5.6.49 results and vs My80 for the Postgres 13.1 vs MySQL 8.0.21 results.
Postgres 11.10 was usually slower than MySQL 5.6. Now that has changed and Postgres 13.1 is usually faster than MySQL 8.0.21. The reason for this change is new CPU overhead in MySQL 5.7 and 8.0. You can see that below where the throughput ratios (QPS, insert/s, transactions/s) increase from the comparison with MySQL 5.6 to the comparison with MySQL 8.0 and most of the ratios are now greater than 1.
Postgres 11.10 was usually slower than MySQL 5.6. Now that has changed and Postgres 13.1 is usually faster than MySQL 8.0.21. The reason for this change is new CPU overhead in MySQL 5.7 and 8.0. You can see that below where the throughput ratios (QPS, insert/s, transactions/s) increase from the comparison with MySQL 5.6 to the comparison with MySQL 8.0 and most of the ratios are now greater than 1.
Load:
Read-only before write-heavy:
- For the first 4 tests that do point queries
- QPS ratios are 0.98, 0.92, 0.63, 0.71 for My56
- QPS ratios are 1.41, 1.20, 0.83, 0.76 for My80
- CPU/query overhead explains the change from My56 to My80
- The next 3 tests have range scans from oltp_read_write.lua with ranges of size 10, 100 & 10,000
- QPS ratios are 0.98, 1.09, 0.92 for My56
- QPS ratios are 1.40, 1.10, 0.71 for My80
- CPU/query overhead explains the change from My56 to My80
- The next 2 tests do point queries via in-lists that are covering and not covering for the PK index
- QPS ratios are 0.70, 0.62 for My56
- QPS ratios are 0.91, 0.81 for My80
- CPU/query overhead explains the change from My56 to My80
- The next 2 tests are similar to the previous but use the secondary index
- QPS ratios are 1.10, 1.27 for My56
- QPS ratios are 1.41, 1.66 for My80
- CPU/query overhead explains the change from My56 to My80
- The next 2 tests do range queries that are covering and not covering for the PK index
- QPS ratios are 0.90, 0.77 for My56
- QPS ratios are 1.21, 1.12 for My80
- CPU/query overhead explains the change from My56 to My80
- The next 2 tests are similar to the previous but use the secondary index
Write-heavy
- For the next 5 tests that are update-only
- QPS ratios are 0.96, 6.79, 1.28, 1.13, 1.28 for My56
- QPS ratios are 1.48, 3.93, 1.87, 1.98, 1.89 for My80
- The largest difference is for the 2nd test (update-index) that requires index maintenance. For Postgres that looks like an insert internally and the old index entry becomes an old version. For InnoDB that is a delete-mark of the old entry and an insert for the new entry (see my notes on MVCC GC).
- CPU/statement overhead explains the change from My56 to My80. Write-amp (wKB/o) is worse for MySQL especially for the update-index test.
- The next test is write-only that has the writes from oltp_read_write.lua
- QPS ratio is 1.41 for My56
- QPS ratio is 1.81 for My80
- CPU/transaction overhead explains the change from My56 to My80. Write-amp (wKB/o) is also worse for MySQL.
- The next 2 tests are the traditional sysbench tests with ranges of size 10 & 100
Read-only after write-heavy includes tests that were run before write-heavy.
- The next 3 tests have range scans from oltp_read_write.lua with ranges of size 10, 100 & 10,000
- QPS ratio is 0.88, 0.94, 0.67 for My56
- QPS ratio is 1.26, 0.96, 0.53 for My80
- CPU/query overhead explains the change from My56 to My80
- The next 5 tests do point queries
- QPS ratios are 0.98, 0.80, 0.60, 0.67, 0.73 for My56
- QPS ratios are 1.40, 1.17, 0.77, 0.72, 1.15 for My80
- CPU/query overhead explains the change from My56 to My80
- The next 2 tests do point queries via in-lists that are covering and not covering for the PK index
- QPS ratios are 0.68, 0.58 for My56
- QPS ratios are 0.88, 0.76 for My80
- CPU/query overhead explains the change from My56 to My80
- The next 2 tests are similar to the previous test but use the secondary index
- QPS ratios are 1.09, 1.23 for My56
- QPS ratios are 1.38, 1.61 for My80
- CPU/query overhead explains the change from My56 to My80
- The next 2 tests do range queries that are covering and not covering for the PK index
- QPS ratios are 0.80, 0.57 for My56
- QPS ratios are 1.07, 0.83 for My80
- CPU/query overhead explains the change from My56 to My80
- The next 2 tests are similar to the previous but use the secondary index
Insert/delete
%20_%20(QPS%20for%208.0.34).png)
Hi!
ReplyDeleteWhen you say:
QPS ratios are 1.41, 1.20, 0.83, 0.76 for My80
does this mean that Postgres was 1.41 times better in the first run, while MySQL was 1.0/0.76 = 1.32 times better the 4th run?
If so, what is causing the big variance? Is it MySQL or Postgres that varies the most?
When my usage of this stabilizes I should do a full writeup on how I run sysbench to avoid confusing readers.
DeleteFor each type of test there can be more than one variant. The numbers you cite are from read-only tests that do point queries and there are 4 variants:
The "driver" for the tests is here. The sequence in which I run these tests is meaningful to me:
https://github.com/mdcallag/mytools/blob/master/bench/sysbench.lua/all_small.sh
It invokes run.sh:
https://github.com/mdcallag/mytools/blob/master/bench/sysbench.lua/run.sh
Read-only tests run before and after the write-heavy tests. The numbers you cite are from before, so:
https://github.com/mdcallag/mytools/blob/master/bench/sysbench.lua/all_small.sh#L26
to:
https://github.com/mdcallag/mytools/blob/master/bench/sysbench.lua/all_small.sh#L32
To map that to a specific Lua file, for point-query.* see this
https://github.com/mdcallag/mytools/blob/master/bench/sysbench.lua/run.sh#L54
... and for read-only.* see
https://github.com/mdcallag/mytools/blob/master/bench/sysbench.lua/run.sh#L28
My Lua files are here:
https://github.com/mdcallag/sysbench/tree/1.0/src/lua
So after all of that the 4 numbers are from:
point-query.pre - Postgres gets 1.41x more QPS
read-only with --range-size = 10, 100 and 10,000 and Postgres gets 1.20, 0.83 and 0.76 of the QPS relative to MySQL.
As you can see, at least for in-memory, MySQL uses less CPU than Postgres for longer range scans
Thanks, for the explanation.
DeleteBased on my experience from some earlier investigations into MySQL performance regressions, I think part of MySQL's problem is that instruction cache misses are more frequent when more code is added. For example, I have seen cases where pushing code that is not executed by a query, causes the query to run 15% slower. That large ranges performs better may be an indication of this, since for larger ranges, much of the execution will be in a tighter loop.
IIUC, you are running your tests on pretty modest machines. Maybe the issue is more visible here, than if the tests where run on machines with larger cache sizes.
It is a good idea to run on a larger CPU but that will have to wait. It will take some time for me to get a cloud setup with a configuration that I trust to be reasonably tuned for performance. I am sure there are great blog posts from others on that topic, but I have to find them.
DeleteBut if true, why is that an issue for MySQL and not for Postgres?
DeleteAlso, I have many results from a few years ago when I had free & easy access to fancy Intel servers and today's results are similar to the old results.
Deletehttps://smalldatum.blogspot.com/2017/05/the-history-of-low-concurrency.html
Bugs I have filed, not sure I am going to document all of them:
Delete* optimizer overhead, https://bugs.mysql.com/bug.php?id=102037
* redo log writer CPU overhead, https://bugs.mysql.com/bug.php?id=102238
Hi Mark,
ReplyDeleteThere is another thing that needs to be considered: in the latest versions of MySQL the throughput per thread is not the highest when a single thread is used.
It may not apply to the specific hardware you are using, but on larger machines you can get 30% more throughput at 4-8 threads, and then drop again as the number of threads grows.
You may also consider using the innodb_log_writer_threads=OFF option that came out in MySQL 8.0.22, as that avoids some extra latency from the parallel redo log work.
Regards
Vitor
I am not sure what you mean by this?
ReplyDelete"in the latest versions of MySQL the throughput per thread is not the highest when a single thread is used."
Do you mean:
1) Modern MySQL suffers less from mutex contention
2) Modern MySQL does parallel query
Both of these would be good news. I suspect #1 is true but my HW is too small to evaluate that. Hopefully parallel query will be supported in a few years and #2 isn't true today.
Regardless, we should be able to get #1 (better support for concurrency) without suffering from what I have been reporting for years (CPU regressions at low-concurrency) and my existence proof of that is Postgres.
Also, let me correct my original post that suggest that it always happens, which is far from the true, what I meant is that it happens in some specific circumstances.
ReplyDeleteThe point I wanted to make is that, while code-path increase and instruction cache reuse are good reasons for some performance degradation, in some cases there may be also impact from Innodb being optimised for high concurrency, and that some of those optimisations are not beneficial for single-threaded performance.
ReplyDeleteOne particular example is the parallel redo log, which transfers the redo log write task from the user threads to dedicated log writer threads (in MySQL 8 before 8.0.22 and from there on when innodb_log_writer_threads=ON).
It works well at very high throughput, but for the single-threaded case there is additional context switch from the user thread to the log writer thread, which adds to latency.
Adding to that, to avoid using too much cpu when there is little work on the server, the writer thread has to decide to stop busy waiting for more redo to write and go to sleep, in which case the commit will be further delayed.
That threshold of when the server is considered lightly loaded, and avoids busy waiting, is controlled by innodb_log_spin_cpu_abs_lwm option. This can be set to 0 to always spin waiting for work, you may want to try that.
Not surprisingly, the success of the heuristics depends a lot on the specific hardware and workload, and on the low-level fine tuning that explores it properly.
Many of the regressions I point out are just as bad for high concurrency workloads as they are for low concurrency. CPU overhead doesn't magically disappear.
DeleteI assume this is a great feature for benchmarks and lousy for production given the amount of tuning required and the need for spare CPU.