Significant performance regression since MySQL 8.0.30 (up to 8.2.0 so far)

Before migrating from MySQL 5.7 to 8.0 I had created automated benchmarking framework which proved to give us repeatable results. It is based on sysbench and we perform set of runs with different threads from 16 to 256 with the step of 16, using OLTP Read Write workload and Percona’s TPCC-like one. I may write the other time a bit more on all the details and the process we are using, but in the context of this post the most important thing to know is that we can rely on its results and between different runs with the same conditions we were achieving less than 0.7% difference (averaged across runs for all threads, in terms of transactions per second).

While working on the project to evaluate which MySQL version we should use as our next go-to version, performance testing was an important part. That’s why I ran bunch of tests, this time only with 32 and 64 threads, to quickly compare new versions to Percona 8.0.28 glibc2.17. I was focusing mostly on Percona releases (P prefix), but for the sake of curiosity, I have tested some of the Oracle ones too (O prefix). Additionally, as the tests were done on Rocky 8.8, I was using glibc2.28, but did also few runs with 2.17. Please find below our findings:

It looks that:

  • Starting from 8.0.30 there is quite significant performance degradation, at least looking from TPS perspective
  • glibc2.17 versions performs better than glibc2.28 ones, even though glibc2.28 is the one to go on Rocky 8.8
  • Oracle distribution seems to behave better, at least for 8.0.34 (8.0.35 and 8.2.0 are not yet available at the time of writing)

Above findings seem to be confirmed on Mark Callaghan’s Small Datum blog, which does amazing work on benchmarking MySQL and PostgreSQL, for example here or here.

Finally, as choosing new MySQL version is not only about performance I recommend reading this post from Jean-François Gagné where several interesting points about new MySQL release strategy are raised.

7 thoughts on “Significant performance regression since MySQL 8.0.30 (up to 8.2.0 so far)”

  1. There is another one here Szymon relating to performance schema which will be fixed in 8036 according to the latest update. It appeared from 8029 onwards, gradually getting worse. The example in the bug report is pretty extreme for demonstration purposes, but it can be easily observed on tpcc like workloads. In my case, one which implement stored procedures. (hammerdb for example)

    https://bugs.mysql.com/bug.php?id=112621

    Liked by 1 person

    Reply

  3. Could you, please, also share your my.conf file and “lscpu” output from your server to better understand which MySQL config you’re using and on which HW you’re running.

    Like

    Reply

    1. Unfortunately I cannot share our full config or detailed CPU information publicly (I’ll send masked version to you directly), but I can say a bit more about the server and MySQL instance where the benchmarks were done:

      * 1x AMD EPYC 64-core CPU with SMT enabled
      * Rocky Linux 8.8 with 4.18 kernel
      * 128 GB Buffer Pool, but dataset size is only around 100 GB (Buffer Pool preloaded before each test from BP map, ~90 GB for TPCC and ~92 GB for OLTP)

      Hope this helps! And please let me know in case you had some specific my.cnf parameters in mind or you have some additional questions/remarks.

      Like

      Reply

      1. Sure, understand. But generally when you share some results publicly, it’s always better to provide system and config details, otherwise its not fair.

        Like

      2. Honestly, I don’t see anything unfair in not sharing my.cnf/CPU details here in such comparison, knowing that:

        * We are using our tool internally since about 2 years and it proved to give us consistent and repeatable results
        * All the tests were performed with the same conditions, on the same machine and on the same dataset (up to single block of Buffer Pool block loaded into memory before runs)

        The only differences where:

        * Different DB binaries
        * Small differences in my.cnf settings between versions related to some deprecations/already using the same defaults/functionality changes in that versions

        Now, to be fully transparent, please find below the changes vs. Percona 8.0.28 baseline (cumulative):

        — 8.0.28 -> 8.0.29
        REMOVED:
        replica_parallel_type = LOGICAL_CLOCK

        — 8.0.29 -> 8.0.3{0,1,2}
        REMOVED:
        innodb_log_file_size = 8G
        innodb_log_files_in_group = 5

        ADDED:
        innodb_redo_log_capacity = 40G

        — 8.0.32 -> 8.0.33 and above
        REMOVED:
        replica_preserve_commit_order = 1
        binlog_format = ROW

        ADDED:
        log_error_suppression_list=’MY-013360′

        If you see anything here (or elsewhere) which might lead to performance degradation, feel free to share, as I will be more than happy to be able to migrate to newer version without taking performance hit. If Oracle needs more details regarding our tests and the environment in order to pinpoint the reason of regressions, we are also ready to cooperate.

        Like

Leave a comment