Small Datum

This wraps up another round of benchmarks. My goal was to determine whether there were CPU regressions in MySQL and Postgres. I found more in MySQL than Postgres.  I used the Insert Benchmark and sysbench . I also ran tests with TSBS but ran out of time to write up the results. Tests were run on  small servers  that I have at home. It would be nice to repeat these tests using larger servers in the cloud, perhaps someone else will do that. The interesting result is that CPU regressions in MySQL reduce throughput by 10% to 20% between each major release. While my tests were limited to 5.6, 5.7 and 8.0 I saw similar problems years ago when I was able to compile older releases from source. Low-concurrency CPU regression s have been a thing with MySQL, and they still are a thing. Fortunately Postgres is the existence proof that the regressions can be avoided. Sysbench results: IO-bound summary that compares Postgres and MySQL IO-bound for Postgres , MySQL/InnoDB and MySQL/MyRocks In-mem

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 i

This has results for IO-bound sysbench with InnoDB in MySQL 5.6, 5.7 and 8.0. The test is similar to  what I used  for in-memory sysbench except the table has 400M rows instead of 10M and the test table is much larger than memory. The goal is to understand how performance and efficiency change from MySQL 5.6 to 8.0. I also have posts for Postgres and MyRocks . Summary: MySQL 8.0 on read-heavy tests is mixed. Sometimes is is better than 5.6 and when worse the loss is mostly at most 10%. MySQL 8.0 on write-heavy tests does worse. Many of these are CPU bound and throughput in 8.0 is frequently 20% to 30% worse than 5.6. But for some 8.0 does much better than 5.6. MySQL 8.0 for full scans is ~17% slower than 5.6 New CPU overhead explains most of the regressions There are large regressions on a few tests that are logically index-only and extra read IO is the problem. See slide 7 in Explaining MVCC GC. Overview I use my  sysbench fork . I have yet to change the code but added  Lua scripts

This has results for IO-bound sysbench with Postsgres versions 11.10, 12.4 and 13.1. The test is similar to  what I used  for in-memory sysbench except the table has 400M rows instead of 10M and the test table is much larger than memory. The goal is to understand how performance and efficiency change over time. I also have posts for InnoDB and MyRocks . Summary: Many tests get between 5% and 10% more throughput in 13.1 relative to 11.10 Tests that do covering queries on secondary indexes do ~20% less read IO/query and I am not sure whether this is a benefit from index deduplication There aren't significant regressions from 11.10 to 13.1 Overview I use my  sysbench fork . I have yet to change the code but added  Lua scripts  for new tests. Tests are run in a sequence (prepare, pause to let write back & compaction catch up, read-only, write-heavy, pause again, read-only, delete, insert) via  all_small.sh  which calls another helper script,  run.sh , to run tests and collect HW m

I added a test that does a full scan of the test table(s) to the sequence of tests I use with sysbench and repeated the full sequence with an in-memory workload, but I only share the results for scan here.  Summary For in-memory scans, InnoDB was fastest, then Postgres and MyRocks was the slowest. MyRocks was also reading from storage (SSD) on this test for a table that should fit in cache. Throughput decreases by ~15% for InnoDB in MySQL 8.0 vs 5.6 (see CPU overhead) Throughput increases by ~16% for Postgres 13.1 vs 11.10 (see CPU overhead) I am wary of having strong conclusions for a test that takes < 10 seconds Overview As explained in a previous post I share numbers for absolute throughput, relative throughput and HW efficiency. The HW efficiency numbers are computed as metric / throughput and the rows scanned/second is the value for throughput in the denominator. The files that have all of the results are in github . Tests were run using 1 table with 10M rows and 1 client that

This has results for IO-bound sysbench with MyRocks in FB MySQL 5.6.35 and 8.0.17. The test is similar to what I used for in-memory sysbench except the table has 400M rows instead of 10M and the test table is much larger than memory. The goal is to understand how performance and efficiency change from MySQL 5.6 to 8.0. I also have posts for InnoDB and Postgres . Summary: While it varies, throughput for SELECT is frequently ~10% less in 8.0.17 vs 5.6.35 Throughput for insert and update is 20% to 30% less in 8.0.17 vs 5.6.35 CPU overhead is the reason for regressions, as it was for in-memory sysbench with MyRocks. It is more of an issue for insert and update because they are CPU-bound while the read-only tests do more read IO per query and are less CPU-bound. Overview I use my sysbench fork . I have yet to change the code but added  Lua scripts  for new tests. Tests are run in a sequence (prepare, pause to let write back & compaction catch up, read-only, write-heavy, pause again,

This has results for the  insert benchmark  on a  small server  with a low-concurrency workload using InnoDB in MySQL 5.6.49, 5.7.31 and 8.0.22. My previous posts are for the same workload with MyRocks and Postgres . Overview The test is run with 1 client and 1 table. In this case, client means there is at most 1 connection for writes and 1 for reads. For some tests the write and read connections do things concurrently. My test server has 4 CPU cores and hyperthreads are disabled. The test is run for 3 table sizes: 20M, 100M and 500M rows. The test table fits in memory for the 20M and 100M sizes and is larger than memory for the 500M size. For the read+write test steps the working set is not cached for the 500M size with InnoDB while it is cached for MyRocks and almost cached for Postgres. The insert benchmark has several steps (explained  here  and  here ): load data, create secondary indexes, load more data, read+write and the read+write step is repeated using increasing rate limits

This has results for the  insert benchmark  on a  small server  with a low-concurrency workload using Postgres in versions 11.10, 12.4 and 13.1 My previous posts are for the same workload with MyRocks and InnoDB . Boring is good news. It means I haven't found serious regressions. Overview The test is run with 1 client and 1 table. In this case, client means there is at most 1 connection for writes and 1 for reads. For some tests the write and read connections do things concurrently. My test server has 4 CPU cores and hyperthreads are disabled. The test is run for 3 table sizes: 20M, 100M and 500M rows. The test table fits in memory for the 20M and 100M sizes and is larger than memory for the 500M size. For the read+write test steps the working set for queries is mostly cached by Postgres, cached by MyRocks and not cached by InnoDB. The insert benchmark has several steps (explained  here  and  here ): load data, create secondary indexes, load more data, read+write and the read+writ

This has results for the insert benchmark on a small server with a low-concurrency workload using MyRocks in MySQL 5.6.35 and 8.0.17. I also have posts for InnoDB and Postgres . Overview The test is run with 1 client and 1 table. In this case, client means there is at most 1 connection for writes and 1 for reads. For some tests the write and read connections do things concurrently. My test server has 4 CPU cores and hyperthreads are disabled. The test is run for 3 table sizes: 20M, 100M and 500M rows. The test table fits in memory for the 20M and 100M sizes and is larger than memory for the 500M size. For the read+write test steps the working set is cached for the 500M size with MyRocks (but not with all DBMS engines). The insert benchmark has several steps (explained  here  and here ): load data, create secondary indexes, load more data, read+write and the read+write step is repeated using increasing rate limits for the writer (100/s, 200/s, 400/s, 600/s, 800/s and 1000/s). Results