Small Datum

InnoDB uses a lot more CPU per query in MySQL 8.0 which makes it a lot slower for low-concurrency workloads that are CPU-bound. That is offset by improvements which reduce mutex contention which means it might not be slower for high-concurrency workloads? It would be great to get the reduction in mutex contention without the increase in CPU overhead. Alas, I don't think that is ever going to happen and this makes me sad. I doubt the performance regressions for InnoDB in MySQL 8.0 will ever be fixed and the workarounds are: MyRocks, MariaDB, MySQL 5.7 forever, Postgres. I explained in a recent post that the problems are code bloat: MySQL 8.0 uses more instructions per query MySQL 8.0 wastes more time on cache and TLB activity per query  Another recent post shows that the MySQL binary is growing at an alarming rate. This post has more details with a focus on InnoDB. Again, this makes me sad. InnoDB was very good to me for a long time but that time is ending. I have spent much time

This post is inspired by a comment about a recent blog post I shared. While I don't focus on MySQL vs Postgres comparisons, sometimes I share them and in my benchmarks with CPU-bound workloads, and fsync disabled (even more CPU-bound) the general result was that insert throughput was much better with Postgres than MySQL+InnoDB. The commenter stated that Postgres and MySQL+InnoDB get similar insert rates when the MySQL binlog is disabled. I rarely test such a config because I never ran MySQL in production without the binlog enabled. Regardless, the commenter is a smart person so I decided to run a few more tests. Some of the configurations I test here are great for performance but lousy for durability so don't take anything here as tuning advice. tl;dr MySQL+InnoDB suffers more tha Postgres from updates that require index maintenance For MySQL with the binlog disabled, insert throughput gets close to Postgres results but not many deployments run primaries with the binlog disabl

I have been documenting performance regressions over time in MySQL. The regressions mean that some workloads get less throughput because the server uses more CPU per SQL operation. From perf stat  I see there is more instruction cache and TLB activity per query. I also see that it takes more instructions per query. A recent blog post from me has more details. This is bloat if you are a pessimist and a side-effect of innovation if you are an optimist. I must repeat that the issue is more serious for low-concurrency workloads because there has been much great work to reduce mutex contention. The table below shows the size of the mysqld binary both as-is (not stripped, includes debug symbols) and stripped. The size of the stripped binary (almost) doubled from the last 5.6 release (5.6.51) to the last 5.7 release (5.7.44). It doubled again from 5.7.44 to a somewhat recent 8.0 release (8.0.28). Assuming the binary size is a proxy for innovation then there is much innovation (2X per major r

As I search to explain why MySQL is getting (too much) slower over time I collect a lot of data from many servers and then get overwhelmed trying to summarize it. Here I focus on one microbenchmark (table scan) on one server (ax162-s from Hetzner) using upstream MySQL with InnoDB and FB MyRocks. While my primary goal is to explain the regressions in MyRocks, many of them are inherited from upstream MySQL so I must first explain them in upstream MySQL with InnoDB. My focus is on a test that does full table scans so the ratio of (time spent in storage engine / time spent elsewhere) is much larger for that test than many of the other tests I use with sysbench. The basic problem is that the table scan done by my tests is ~1.5X faster for InnoDB in MySQL 5.6.51 vs 8.0.39 ~1.5X faster for MyRocks in FB MySQL 5.6.35 vs 8.0.32 The problem is spread over many releases, but the summary is: Code bloat - more instructions are executed per SQL statement InnoDB 8.0.28 uses ~1.3X more instructions/qu

This is a summary of interesting performance issues I have had with the HWE kernels (6.5.x) on Ubuntu 22.04, mostly this year. This is mostly a note to my future self. Use ext4 instead of XFS I have used XFS since pre-2010 because it avoided a few perf problems that show up in the ext family. But I finally found a reason to switch to ext4. The problem is that with 6.5 kernels (when HWE is used) there are perf problems and many error messages (WQ_UNBOUND, CPU hogging). Some details are here . I am curious if XFS needs a few updates to match whatever changed in the 6.5 kernels. Switch from schedutil to performance CPU frequency governor This issue hurt MyRocks a lot more than Postgres or InnoDB. I don't know the root cause but MyRocks perf was lousy with the schedutil CPU frequency governor.  See here for more details from me, and this LWN post might be relevant. readahead requests are dropped RocksDB changed how readahead is done when O_DIRECT is not used and the change uses the r

This has benchmark results for MySQL and Postgres vs the Insert Benchmark on a large server. My intent is to document how performance changes over time more so than start a Postgres vs MySQL argument. MySQL has accumulated large regressions from 5.6 to 8.0 that are obvious on low-concurrency benchmarks. While they are less obvious on high-concurrency benchmarks, and there have been significant improvements to make MySQL 8 better at high-concurrency, the regressions that hurt low-concurrency results also reduce throughput at high-concurrency. tl;dr For Postgres 17rc1 vs 15.8 Performance is mostly the same For MySQL 8.0.39 vs 5.6.51 Writes are much faster in 8.0.39 Reads throughput is mixed,  8.0.39 is slower than, similar to and faster than 5.6.51 depending on the context For MySQL vs Postgres MySQL is faster on point queries Postgres is faster on writes and range queries Builds, configuration and hardware I compiled from source: Postgres versions 17rc1 from source using  -O2 -fno-omit-

For a few years I have only used sysbench and the Insert Benchmark for work but now I need an HTAP benchmark. There are several to choose from but I will start with HATrick. The source is here . It uses C++ and ODBC. I prefer Java (and JDBC), golang or Python but lets see how this goes. My current OS is Ubuntu 22.04 and I install MySQL in custom locations so I assume that I need to install MySQL's Connector/ODBC from source. That depends on an ODBC driver manager, and for Ubuntu I will try iodbc. I have no experience with ODBC and now I am worried about pointless complexity (I am looking at you Log4J, CORBA and EJB). The benchmark looks interesting and useful but I'd rather not deal with setting up ODBC again, nor with core dumps. So I will move on and try CH-benchmark from benchmark (Java + JDBC). The details So the first step is to install iodbc via: sudo apt install libiodbc2 libiodbc2-dev Then configure MySQL Connector/ODBC. I didn't expect to need ODBC_INCLUDES= becaus

This has benchmark results for MySQL 5.6.51, 5.7.44 and 8.0.39 using sysbench on a large server. I also add a few results comparing Postgres 17rc1 with MySQL 8.0.39. My goal with that is to highlight things that can be made better in MySQL (and in Postgres). One of the responses to my claims that MySQL is getting too many performance regressions over time is that this is true for low-concurrency tests but not for high-concurrency tests. Alas, that claim is more truthy than true and fixing some of these regressions would help make modern MySQL not look so slow when compared to modern Postgres. tl;dr MySQL 8.0 is faster than 5.6 for point queries and writes but slower for range queries PostgresSQL 17rc1 is a lot faster than MySQL 8.0 for point queries and writes. For range queries Postgres was still faster but the difference was usually not as large. Builds, configuration and hardware I compiled  Postgres versions 17rc1 from source using  -O2 -fno-omit-frame-pointer . MySQL versions 5.6.