Small Datum

This has results for the  Insert Benchmark  using MyRocks 5.6 and 8.0 using a large server and cached workload.  tl;dr performance between MyRocks 5.6.35, 8.0.28 and 8.0.32 are similar, unlike what I see with InnoDB where there are large changes, both good (writes) and bad (for reads) using the same setup Build + Configuration I tested MyRocks 5.6.35, 8.0.28 and 8.0.32. These were compiled from source. All builds use CMAKE_BUILD_TYPE =Release. MyRocks 5.6.35 compiled with clang on Dec 21, 2023 at git hash 4f3a57a1, RocksDB 8.7.0 at git hash 29005f0b MyRocks 8.0.28 compiled with clang on Dec 22, 2023 at git hash 2ad105fc, RocksDB 8.7.0 at git hash 29005f0b MyRocks 8.0.32 compiled with clang on Dec 22, 2023 at git hash 76707b44, RocksDB 8.7.0 at git hash 29005f0b The my.cnf files are here for 5.6.35 ( d1 ), 8.0.28 ( d1 and d2 ) and 8.0.32 ( d1 and d2 ). For MyRocks 8.0 the d1 configs enable the performance_schema and the d2 configs disable it. The full names for the configs are my.cnf.

This has results for the Insert Benchmark using InnoDB from MySQL 5.6 to 8.0, a large server and a cached workload.  tl;dr MySQL 8.0 is faster than 5.6 for writes because the improvements for concurrent workloads are larger than the regressions from new CPU overheads. With 8.0 the throughput is ~2X larger than 5.6 for l.i0 (inserts with no secondary indexes) and ~3X larger for l.i1/l.i2 (random inserts, random deletes). MySQL 8.0 is slower than 5.6 for reads because there are new CPU overheads. For range queries with 8.0 the throughput is ~13% to ~40% less than 5.6 and for point queries it is ~20% less. With MySQL 5.7 the write throughput is not as large as MySQL 8.0 but the read throughput is better.  Context matters as the results here for MySQL 8.0 are better than results on a small server. The difference is that I use a workload with high concurrency (24 clients) on the large server and low concurrency (1 client) on the small server. On both servers MySQL 8.0 pays a price from new

This describes the Insert Benchmark after another round of changes I recently made. Past descriptions are here and here . Source code for the benchmark is here . It is still written in Python and I hope that one day a Python JIT will arrive to reduce the overhead of the benchmark client. The changes include: switched from float to int for the price column changed the marketsegment index from (price, customerid) to (productid, customerid). The other indexes are unchanged -- registersegment is on (cashregisterid, price, customerid) and pdc is on (price, dateandtime, customerid) added a mode for the read-write benchmark step to do point queries on the PK index changed some of the benchmark steps to do a delete per insert to avoid growing the table size. I made this change a few months ago. add the l.i2 benchmark step that modifies fewer rows per transaction compared to l.i1 added code to reduce checkpoint (InnoDB) and compaction (RocksDB) debt that runs between the l.i2 and qr100 benchm

This post has results for performance regressions in all versions of 8.x from 8.0.0 to 8.9.2 using a large server. In a  previous post  I shared results for RocksDB 8.0 to 8.8 on the same hardware. tl;dr The regressions from 8.0 to 8.9 are small. For the non read-only benchmark steps, QPS from 8.9.2 is at most 3% less than 8.0.0 with leveled and at most 1% less with universal. I focus on the benchmark steps that aren't read-only because they suffer less from noise.  These benchmark steps are fillseq, revrangewhilewriting, fwdrangewhilewriting, readwhilewriting and overwriteandwait. I also focus on leveled more so than universal, in part because there is more noise with universal but also because the workloads I care about most use leveled. Builds I compiled with gcc RocksDB 8.0.0, 8.1.1, 8.2.1, 8.3.3, 8.4.4, 8.5.4, 8.6.7, 8.7.3 and 8.8.1 and 8.9.2 which are the latest patch releases. Benchmark All tests used a server with 40 cores, 80 HW threads, 2 sockets, 256GB of RAM and many TB

I made parallel create index for InnoDB up to 2.4X faster by doing one of: performance_schema=0 removing this line of code I filed bug 113505 for this. The problem is that the parallel create index threads all call that function and there is too much contention on the counter that is incremented. The problem wasn't obvious from Linux perf when using the default metric (cycles). I then tried perf stat and IPC (instructions/cycle) was about 2X larger during create index with performance_schema=0. Finally, I repeated the perf measurements using memory system counters (cache-misses, etc) and with perf record , perf report and perf annotate the problem was obvious. I am still deciding whether this 2X faster bug fix is more exciting then the one I found for Falcon. Results The results here are from a server with 2 sockets, 12 cores/socket, 64G of RAM running Ubuntu 22.04 and one m.2 device with 2TB and XFS. All tests use MySQL 8.0.35 and all of the my.cnf files  are here . The test

This post has results from the  Insert Benchmark  for some MySQL 8.0.35 with InnoDB using the Insert Benchmark, a small server and a cached workload. The goal is to document the overhead of the performance schema. tl;dr The perf schema in 5.7 costs <= 4% of QPS The perf schema in 8.0 costs ~10% of QPS, except for index create where the cost is much larger. I will try to explain that later.  Build + configuration I used InnoDB with MySQL 8.0.35 and 5.7.44. The cmake files for each of the builds are here for 5.7.44 and for 8.0.35 .  By default the my.cnf files I use for MySQL 5.7 and 8.0 have performance_schema=1 but I don't otherwise enable instruments. So the tests here document the overhead from using whatever is enabled by default with the perf schema. For 8.0.35 there were 5 variants of the build and 2 my.cnf files. I didn't test the full cross product, but there are 7 different combinations I tried that are listed below. The 8.0.35 builds are:  my8035_rel MySQL 8.0.35 a

This post has results from the  Insert Benchmark  for some MySQL 5.6 releases, all 5.7 releases and all 8.0 releases using a small server and a cached workload. It follows up on work I published in October. The differences are that I updated the my.cnf files to make them more similar across releases and I added results for pre-GA releases from 5.7 and 8.0. tl;dr For 4 of the 6 benchmark steps, MySQL 8.0.35 gets about 60% of the throughput relative to 5.6.51 with simple queries and a cached database. The regressions for random writes (l.i1) is less and index create (l.x) is faster in 8.0 than in 5.6. For MySQL 5.7 about half of the perf regressions occur between the last 5.6 release (5.6.51) and the first 5.7 release that I was able to compile (5.7.5). The rest are spread evenly across 5.7. For MySQL 8.0 almost all of the perf regressions are spread across 8.0 releases from 8.0.0 to 8.0.35.  For InnoDB there is at least one significant regression arriving in 8.0.30+ related to which co

This is part 4 of my series on using HW counters from  Linux perf  to explain why MySQL gets slower from 5.6 to 8.0. Refer to  part 1  for an overview. What happened in MySQL 5.7 and 8.0 to put so much more stress on the memory system? tl;dr It looks like someone sprinkled magic go slower dust across most of the MySQL code because the slowdown from MySQL 5.6 to 8.0 is not isolated to a few call stacks. MySQL 8.0 uses ~1.5X more instructions/operation than 5.6. Cache activity (references, loads, misses are frequently up 1.5X or more. TLB activity (loads, misses) is frequently up 2X to 5X with the iTLB being a bigger problem than the dTLB. innodb_log_writer_threads=ON is worse than I thought. I will soon have a post on that. Too long to be a tl;dr I don't have much experience using Linux perf counters to explain performance changes. There are huge increases for data TLB, instruction TLB and L1 cache counters. From the Xeon CPU (socket2 below) the change from MySQL 5.6.21 to 8.0.34 me

This is part 3 of my series on using HW counters from  Linux perf  to explain why MySQL gets slower from 5.6 to 8.0. Refer to  part 1  for an overview. What happened in MySQL 5.7 and 8.0 to put so much more stress on the memory system? tl;dr It looks like someone sprinkled magic go slower dust across most of the MySQL code because the slowdown from MySQL 5.6 to 8.0 is not isolated to a few call stacks. MySQL 8.0 uses ~1.5X more instructions/operation than 5.6. Cache activity (references, loads, misses are frequently up 1.5X or more. TLB activity (loads, misses) is frequently up 2X to 5X with the iTLB being a bigger problem than the dTLB. innodb_log_writer_threads=ON is worse than I thought. I will soon have a post on that. Too long to be a tl;dr I don't have much experience using Linux perf counters to explain performance changes. There are huge increases for data TLB, instruction TLB and L1 cache counters. From the Xeon CPU (socket2 below) the change from MySQL 5.6.21 to 8.0.34 me