The history of low-concurrency performance regressions in MySQL 5.6, 5.7 and 5.8

Update - the regression isn't as bad as I have been reporting. Read this post to understand why.

What is a reasonable goal for performance regressions between major releases of MySQL (5.6, 5.7, 5.8)? Some regressions are expected because more features means longer code paths. But what if MySQL 5.7 and 5.8 only get 2/3 of the QPS versus MySQL 5.6 at low concurrency?

The low-concurrency performance regressions from MySQL 5.6 to 5.7 continue in MySQL 8. That isn't a surprise for two reasons. First, more features usually make a DBMS slower. Second, all of the performance marketing for new MySQL releases focuses on high-concurrency workloads. One example of the regression occurs for in-memory sysbench where MySQL 5.7 and MySQL 8 get 60% to 70% of the QPS compared to 5.6, which is the same as writing that 5.6 gets 1.43X to 1.67X more QPS than MySQL 5.7 and MySQL 8.

A deployment that I care about uses MySQL 5.6 and we want to use MySQL 8. Alas, we need to reduce the performance regressions for that to happen. I am excited that the community has another chance to make MySQL better because I don't think this will be fixed without us. I have been writing about this problem since 2013. I am also happy to note that the performance schema isn't the problem. I rediscovered this problem when using MySQL 8 on my home test servers and filed bug 86215. While I have published results showing the regression from earlier releases to MySQL 5.6 -- at this point I just want to get back to the low-concurrency QPS we get from MySQL 5.6. I have good memories from MySQL 4.0, 5.0 and 5.1, but I can do without the excitement of running those releases at web-scale.

I first wrote about this problem in 2013 when comparing MySQL 5.6 to previous releases and filed bugs 68825 and 69236. My favorite low-concurrency performance regression will always be bug 29921. I still remember debugging that over a weekend. Parsers are hard to profile when code is generated and everything is in one function. Fortunately I was able to use rdtsc.

Bugs for low-concurrency performance regressions

Bugs filed by me:

• 68825 - April 2013 -- performance regressions for single-threaded workloads
• 69236 - May 2013 -- performance regressions for single-threaded workloads, part 2
• 74325 - October 2014 (fixed) -- updates to indexed column much slower in 5.7.5
• 74342 - October 2014 -- InnoDB disk reads at 1 thread much slower in 5.7.5
• 86215 - May 2017 -- MySQL is much slower in 5.7 than 5.6

Bugs not filed by me:

• 71130 - December 2013 -- 5.6 SQL thread is much slower than 5.1/5.5
• 78176 - August 2015 -- 5.6.20 is almost twice as slow as 5.0.96

Finally, there are two interesting bugs for high-concurrency regressions in InnoDB that I want to revisit: bug 74280 (open) and bug 74283 (fixed). I wrote about this in a post on range scan performance.

Content for low-concurrency performance regressions

Reports from me

• March 2013 - MySQL 5.6: single-threaded, read-only
• April 2013 - MySQL 5.6: single-thread, update-only
• May 2013 - MySQL 5.6 versus 4.0 for a read-only workload
• May 2013 - MySQL 5.6: single-threaded performance regressions
• September 2013 - MySQL 5.7.2 single threaded performance needs improvement
• September 2013 - MySQL 4.1 forever
• October 2014 - Single thread performance in MySQL 5.7.5 versus older releases via sql-bench
• October 2014 - Low-concurrency performance for point lookups: MySQL 5.7.5 vs previous releases
• October 2014 - Low-concurrence performance regressions for range queries: MySQL 5.7 vs previous releases
• October 2014 - Low-concurrency performance for updates and the Heap engine: MySQL 5.7 vs previous releases
• October 2014 - Low-concurrency performance for updates with InnoDB: MySQL 5.7 vs previous releases
• October 2014 - Page read performance: MySQL 5.7 vs previous releases
• October 2014 - Sysbench cached updates: MySQL 5.7 vs previous releases
• October 2014 - Sysbench IO-bound updates: MySQL 5.7 vs previous releases
• October 2014 - Updates with secondary index maintenance: 5.7 vs previous releases
• August, 2015 - Single-threaded linkbench performance for MySQL 5.7, 5.6, WebScale and MyRocks
• February 2017 - Using modern sysbench to compare MyRocks and InnoDB on a small server
• February 2017 - Part 2 - sysbench, MyRocks, InnoDB and a small server
• May 2017 - Sysbench, in-memory & Intel NUC
• May 2017 - Sysbench, IO-bound & Intel NUC for MySQL 5.6, 5.7 & 8
• June 2017 - Insert benchmark, in-memory & Intel NUC for MySQL 5.6, 5.7 and 8
• June 2017 - Insert benchmark, IO-bound & Intel NUC for MySQL 5.6, 5.7 & 8
• June 2017 - Linkbench, in-memory & Intel NUC for MySQL 5.6, 5.7 & 8
• June 2017 - Linkbench, IO-bound & Intel NUC for MySQL 5.6, 5.7 & 8
• June 2017 - Sysbench for MySQL 5.0, 5.1, 5.5, 5.6, 5.7 and 8
• June 2017 - Impact of perf schema on sysbench at low concurrency
• June 2017 - MyISAM, small servers and sysbench at low concurrency
• June 2017 - One more time with sysbench, a small server & MySQL 5.7, 5.7 and 8.0
• June 2017 - Linux perf and the CPU regression in MySQL 5.7

Reports from others:

• March 2013 - Why MySQL Performance at Low Concurrency is Important
• December 2013 - Single thread performance regression in 5.6 - Replication