Small Datum

  Update  - the regression isn't as bad as I have been reporting. Read  this post  to understand why. The good news is that I hope to begin debugging this problem next week. After fixing a few problems to reduce variance I am repeating tests to document the performance regression from MySQL 5.6 to 8.0. The first problem was fixed by disabling turbo boost on my Intel NUC servers to avoid thermal throttling. The other problem was the impact from mutex contention for InnoDB purge threads and I repeated tests with it set to 1 and 4. This is part of my series on  low-concurrency CPU regressions  for  bug 86215 . tl;dr for in-memory sysbench on a small server with a fast SSD most of the regression is from 5.6.35 to 5.7.17, much less is from 5.7.1 to 8.0.1 innodb_purge_threads=4 costs 10% to 15% of the QPS for write-heavy tests QPS is 30% less for 5.7.17 & 8.0.1 vs 5.6.35 on write-only tests QPS is 30% to 40% less for 5.7.17 & 8.0.1 vs 5.6.35 on read-write tests QPS is

Update  - the regression isn't as bad as I have been reporting. Read  this post  to understand why. I am a big fan of Intel NUC servers but recently noticed that CPU performance varied frequently from thermal throttling  for my NUC7i5bnh .  To prevent this I disabled turbo boost for the CPUs. My old NUC servers ( NUC5i3ryh ) don't suffer from this because  that CPU  does not have turbo boost. Now I need to repeat many tests. Today I share results for a quick run of sysbench using MyISAM and InnoDB in MySQL 5.6, 5.7 and 8.0. This is part of my series on  low-concurrency CPU regressions  for  bug 86215 . tl;dr for in-memory sysbench: the QPS regression is from MySQL 5.6 to 5.7 the QPS regression is similar for MyISAM and InnoDB the default value for innodb_purge_threads, which is 4, can cause too much mutex contention and a loss in QPS on small servers. For sysbench update-only I lose 25% of updates/second with 5.7.17 and 15% with 8.0.1 when going from innodb_purge_thr

Update  - the regression isn't as bad as I have been reporting. Read  this post  to understand why. I used Linux perf to get more details on system performance while running the point-query test with modern sysbench. This is for an in-memory workload and part of my series on low-concurrency CPU regressions  for bug 86215 . tl;dr I have more explaining to do The increase in instructions/query explains the decrease in queries/second from MySQL 5.6 to 5.7 to 8.0. Configuration I tested MySQL using upstream 5.6.35, 5.7.17 and 8.0.1. For 8.0.1 I used the latin1 charset and latin1_swedish_ci collation. My servers are  described here  and are named i3 NUC and i5 NUC . My usage of sysbench, including the tests run, and the my.cnf used for each database engine is  described here  for MySQL 5.6, 5.7 and 8. The my.cnf files I share were for the i3 NUC. For the i5 NUC the InnoDB buffer pool and IO capacity options were increased using  these values . The my.cnf files are here

Update  - the regression isn't as bad as I have been reporting. Read  this post  to understand why. As I document the low-concurrency CPU regressions in MySQL 5.7 and 8 one of the questions is whether the performance schema is part of the problem. I don't think it is as the worst-case overhead I measure is about 7% and the typical overhead is less than 5% for a single-threaded & in-memory workload. Eventually I will repeat this test for workloads with more concurrency because there are overheads that won't be apparent at low-concurrency. The overhead is larger for simple queries, like sysbench point-query, and larger for complex queries. The overhead is also larger for in-memory workloads. tl;dr - it isn't the perf schema Configuration I tested MySQL using upstream 5.6.35, 5.7.17 and 8.0.1. For 8.0.1 I used the latin1 charset and latin1_swedish_ci collation. My servers are  described here  but I only share results for the i5 NUC. I enabled suppor

Update  - the regression isn't as bad as I have been reporting. Read  this post  to understand why. After sharing results for in-memory sysbench with MySQL 5.6, 5.7 and 8 I was curious about older releases and here I have results for MySQL 5.0, 5.1 and 5.5 in addition to 5.6, 5.7 and 8. This is one more result in my series on low-concurrency performance regressions . tl;dr MySQL 4.1 and 5.5 weren't great releases for performance. I skipped both in production. The biggest drop in QPS between releases is from 5.6 to 5.7 and that drop often exceeds the drop from 5.0 to 5.6. What happened?  Bug 86215 is open for this. Configuration I tested MySQL using upstream 5.0.96, 5.1.72, 5.5.51, 5.6.35, 5.7.17 and 8.0.1. For 8.0.1 I used the latin1 charset and latin1_swedish_ci collation. My servers are  described here  but I only share results for the i5 NUC. I was able to compile and run MySQL 4.1.22 on the same server but have yet to share the results. The results weren'

Update  - the regression isn't as bad as I have been reporting. Read  this post  to understand why. Next up in my  series on low-concurrency performance  is  Linkbench  on Intel NUC servers with an IO-bound workload. The working set is larger than RAM and there many reads from and writes to storage. This is part of my work on  bug 86215  and will be my last blog post on the topic for a few weeks. For more on Linkbench read the Facebook  post  and  paper  and my  latest post . tl;dr For the i5 NUC at least 2/3 of the regression is from MySQL 5.6 to 5.7 and the remainder from 5.7 to 8. Most of this is explained by more CPU overhead in 5.7 and 8. For the i3 NUC there is more variance, but that has a slower and older CPU that is less interesting to me. At this point I think the i3 NUC is useful to test builds and run MTR but not to test performance. Configuration I tested MySQL using upstream 5.6.35, 5.7.17 and 8.0.1. For 8.0.1 I repeated the test: first with the default c

Update - the regression isn't as bad as I have been reporting. Read this post to understand why. I have been reporting on low-concurrency performance regressions in MySQL for a few years and recently published many reports to compare MySQL 5.6.35, 5.7.17 and 8.0.1 using Intel NUC servers. This is tracked by bug 86215 . My summary of the recent tests is: The problem is not that there is a CPU regression from MySQL 5.6 to 5.7 to 8 as that is expected. The problem is that the regression is too large. In the worst case, I have MySQL 5.6 gets up to 2X more QPS than 5.7 and 8 using sysbench. In a typical case MySQL 5.6 gets 1.2X to 1.3X more QPS than 5.7 and 8. Most of the slowdown is from MySQL 5.6 to 5.7 and less of the problem is from 5.7 to 8. I think this is good news. This is based on my results from the i5 NUC . My team is committed to making this better. I hope that upstream is too. One day the big Percona Live presentation from upstream will include benchmark results for

Update  - the regression isn't as bad as I have been reporting. Read  this post  to understand why. Next up in my  series on low-concurrency performance  is Linkbench on Intel NUC servers with an in-memory workload. The database fits in RAM and there are no reads from but many writes to storage. This is part of my work on  bug 86215 . One day I will stop running tests and start working on code improvements but not yet. For more on Linkbench read the Facebook post and paper  and my latest post . tl;dr MySQL 5.7 and 8 get about 80% of insert rate and 60% of the transaction rate compared to MySQL 5.6 in the worst case at low concurrency. This is explained by an increase in the CPU cost per insert and per transaction. I assume more code & more features is the cause. For the i3 NUC some of the regression is from MySQL 5.6 to 5.7 and some is from 5.7 to 8. For the i5 NUC most of the regression is from MySQL 5.7 to 8. Configuration I tested MySQL using upstream 5.6.35,

Linkbench is a benchmark for social graph transaction processing. It lead to a paper , a post , benchmark client in Java and many blog posts from me. As a bonus, I also got to know the person who did the work for it - thanks Tim. The benchmark client has moved to my Github account because the upstream project has been archived (no comment). The repo is not active but someone recently added support for Postgres , someone else added support for multiple schemas and I fixed it to work with MySQL 8. Percona forked it to add support for MongoDB but that really needs proper support for multi-document transactions in MongoDB. I run Linkbench via helper scripts . These include files to create tables for MyRocks and InnoDB . There are two phases Linkbench: load and transaction. My helper scripts run the load phase and then transactions. From past experience results become stable after a few hours of transactions so I run it for 24 hours in 1-hour loops and compute metrics per hour to und