The following report is covering performance evaluation of the currently available OpenSSL releases when they are used by MySQL in CPU-intensive OLTP workloads. However, the main focus is on OpenSSL-3.5.5, which will be used by default in the next MySQL releases.
As promised, my slides from FOSDEM25 and Pre-FOSDEM MySQL Belgian Days :
And since I was asked several times about MySQL test case demonstrating glibc-malloc memory fragmentation / leaks -- here are all the details :
In MySQL 8.0 we introduced a totally new design for InnoDB REDO Log management. The main difference was about implementing a lock-free solution for user threads, and use dedicated REDO threads for all background IO write work.
for more details, see an excellent and very detailed article by Pawel : https://dev.mysql.com/blog-archive/mysql-8-0-new-lock-free-scalable-wal-design/
However, over a time we also added an option to let users to switch REDO threads=OFF to enforce REDO log processing efficiency in some particular cases. Unfortunately this feature created a lot of confusions for MySQL users, and many ones interpreted this in different ways, providing different and sometimes opposite advices, etc..
My main advice will be always : test each feature yourself and within your own Production environment !! -- and at the end, this is all what matters ! -- regardless what you'll read on the Net (including the following post ;-))
In case you don't have Production workloads for testing (like me), or afraid to break Production and want to do some initial probe experiments -- there are many generic benchmark workloads available around MySQL, so you can play with this to help you get a better understanding of what is going on.
And this is what I'll try to do here -- to give you yet-one-more-advice about REDO Log threads ;-))
The following is a short HOWTO about deployment and use of Benchmark-kit (BMK-kit). The main idea of this kit is to simplify your life in running various MySQL benchmark workloads with less blood and minimal potential errors.
Generally as simple as the following :
$ bash /BMK/sb_exec/sb11-Prepare_50M_8tab-InnoDB.sh 32 # prepare data $ for users in 1 2 4 8 16 32 64 128 256 512 1024 2048 do # run OLTP_RW for 5min each load level.. bash /BMK/sb_exec/sb11-OLTP_RW_50M_8tab-uniform-ps-trx.sh $users 300 sleep 15 done
the latest public online version of the following HOWTO is always available from here : http://dimitrik.free.fr/blog/posts/mysql-perf-bmk-kit.html
UPDATE : May.2021 -- workaround fixes for A1 ARM64 systems
UPDATE : Sep.2021 -- dedicated Sysbench binary for MySQL 8.0 & SSL
UPDATE : Oct.2021 -- new args --mysql-session-options=...
UPDATE : Oct.2021 -- new args --update-range-size=N
UPDATE : Dec.2021 -- moving INTEGER to BIGINT in OLTP tests to allow bigger data
UPDATE : Jun.2022 -- new args :
--extra-cols=N : add N extra columns to table(s)--extra-cols-type=TYPE : data type to use for extra columns (ex. VARCHAR(32))--extra-cols-default=value : default value to use for extra columns--select-star=on/off : use SELECT * ... in point-selects and ranges (def.: off)UPDATE : Jun.2022 -- new args --trx-retry=on/off in TPCC (def: off)
UPDATE : Jun.2022 -- dbSTRESS HOWTO
UPDATE : Aug.2022 -- added mixed Point-Select + UPDATEs workloads
UPDATE : Sep.2022 -- added Point-Selects re-connect workloads !
UPDATE : Oct.2022 -- added option --mysql-query-hint=...
UPDATE : Oct.2022 -- added env. variable option MYSQL_SESSION_OPTIONS
UPDATE : Mar.2023 -- new option --sync-file=/path/to/filename
UPDATE : Apr.2023 (v.1.43) -- new option --mysql-table-partitions=N
UPDATE : Apr.2023 (v.1.44) -- new option --mysql-check-charset=1/true
UPDATE : Apr.2023 (v.1.45) -- new option --sleep-before-commit=N (usec)
UPDATE : Jun.2023 (v.1.47) -- new option --sleep-after-query=N (usec)
UPDATE : Aug.2023 (v.1.50) -- new option --extra-cols-options=...
UPDATE : Nov.2023 (v.1.54) --
you can use now TEXT/BLOB data types for extra-cols and provide default values containing # or @ to increase randomizing (each slash will be replaced by a random number from 0-9, and each @ by random a-z letter) -- this will also work for other data types as well to get more randomness in data
more simplified setup -- using only one single Sysbench binary according to platform (x64 or arm64), compiled with MySQL 8.0 dynamic client lib and OpenSSL-1.1.1L -- no more need to explicitly create users with "native" passwords, etc..
UPDATE : 2023-11-12 (v.1.55) -- replacing --select-star=1/0 option by --select-cols="..." option to allow replace "default" c column in SELECT queries for point-select and simple/ordered range queries by something else:
--select-cols="id,k"
--select-cols="*" (to get SELECT * from ...)--select-cols="JSON_OBJECT(id,k,c,pad)"
--select-star="JSON_ARRAY(id,k,c,pad)"
UPDATE : Nov.2023 (v.1.56) -- fixed floor size for partitions
UPDATE : Nov.2023 (v.1.57) -- added OLTP-loop.lua
UPDATE : Jan.2024 (v.1.58) -- added 10TB OLTP/TPCC scripts
UPDATE : Mar.2024 (v.1.62) -- new options for extra SQL queries :
--extra-query-before="..."--extra-query-after="..." example: --extra-query-before="select WAIT_FOR_EXECUTED_GTID_SET( @@global.gtid_executed );"
UPDATE : Sep.2024 (v.1.67) -- print result reports in CSV format by setting the following environment variables:
SYSBENCH_REPORT_CSV=yes -- print intermediate reports in CSVSYSBENCH_FINAL_REPORT_CSV=yes -- print final report in CSVUPDATE : Sep.2024 (v.1.68) -- added scripts using transaction enclosures for single-query test scenarios ("generally" it's always more efficient to use them as auto-commit statements, but there were many requests for testing / investigations purposes to also have them with transaction enclosure)
UPDATE : Oct.2024 (v.1.69) -- new option --by-secidx=true/false (def: false) to allow WHERE criteria use secondary index k column instead of default primary key id column. By default it's set to "false" to be compatible with all initial test scenarios. Switching this to "true" will make all SELECT and non-index-UPDATE queries to use secondary index column k in WHERE condition criteria, however mind that k values in Sysbench are assigned randomly, so your test results may vary in the same test just because each time you'll have a different values distribution for kcolumn.
UPDATE : Oct.2024 (v.1.70) -- adding "pareto" access pattern to TPCC
UPDATE : Oct.2024 (v.1.71) -- adding verbose output for OLTP workloads to let check all required SQL queries are really considered during test execution time (hunting rare Sysbench / Lua issue)
UPDATE : Oct.2024 (v.1.72) add --opt-debug=1 option to debug Sysbench options settings
UPDATE : Oct.2024 (v.1.74) add test workloads using Secondary Index (SecIDX)
UPDATE : Jan.2025 (v.1.75) add --mysql-index-type option (def : BTREE) to OLTP tests
UPDATE : Mar.2025 (v.1.77) add --mysql-table-partitions=N option to TPCC workload
UPDATE : Jun.2025 (v.1.79) add --mysql-index-options to OLTP and TPCC workloads
UPDATE : Sep.2025 (v.1.85) add more data sizes (100Mx5tab, 40Mx256tab, 250Mx32tab, 125Mx64tab)
UPDATE : Nov.2025 (v.1.88) auto-adaptive threads number during "prepare" step to allow true parallel data load (number of threads -vs- number of tables should multiple one of other, and if it's not so, then number of threads involved in parallel load will be auto-reduced to match "multiple" condition and not misbalance parallelism of data load)..
UPDATEs :
v.1.89 : Nov.2025 | allow multi-queries for extra-before/after.. v.1.90 : Nov.2025 | add #TID (sysbench.tid) and #THD (threads) in extra-query & session-ops.. v.1.91 : Nov.2025 | add #ID (last_id) in extra-query & session-ops.. v.1.92 : Nov.2025 | cover 1053 error code (MySQL shutdown) and loop in re-connect.. v.1.93 : Jan.2026 | add --extra-query-ratio (def:1) option.. v.1.94 : Jul.2026 | add --extra-cols-from-file=/path/to/filename.sql option..
Just realized I did not post any notes about dim_STAT CoreUpdates during the last 3 years, so will try to fix it now with the following short summary ;-))
Few weeks ago with a big curiosity I was reading several articles published by Percona about TPCC Benchmark results and MySQL 8.0 "checkpointing" issues..
Unfortunately, in these articles there was no any explanation nor any tentative to understand what is going on, an probably at least try and validate some "first coming in mind" tuning / troubleshooting options.. (And even no any try to show in action so often advertised PMM, and see on what it'll point ?)..
All in all, in the following article I'll try to feel up the "white holes" left in this TPCC testing..
The TPCC workload "mystery" exposed in the following post was already clarified the last year, and I've presented explanations about the observed problem during PerconaLIVE-2019. But slides are slides, while article is article ;-)) So, I decided to take a time to write a few lines more about, to keep this post as a reference for further TPCC investigations..
The "mystery" is related to observed scalability issues on MySQL 8.0 under the given TPCC workload -- just that on the old aged DBT-2 workload (TPCC variation) I was getting much higher TPS when running on 2 CPU Sockets, comparing to1 CPU Socket, which is was not at all the case for Sysbench-TPCC.
This post was remaining in stand-by for a long time, specially that I was expecting that observed issues will be fixed soon. But time is going, and the problems are remaining. And I'm constantly asked "why, Dimitri, you're suggesting now to use XFS, while in the past you always suggested EXT4 ??" -- hope the following article will clarify you the "why" and maybe motivate you to do your own evaluations to see how well the things are working for you on your own systems under your own workloads..
NOTE : this will also clarify why the new Double Write did not appear in MySQL 8.0 in 2018, as it was planned, but only recently (http://dimitrik.free.fr/blog/posts/mysql-80-perf-new-dblwr.html)
The new MySQL-8.0.20 release is coming with re-designed InnoDB Double Write Buffer (DBLWR), and, indeed, it's one huge historical PITA less.. -- why it was so painful and cost us much blood in the past, I could not better explain than already done it in the following article yet from 2018 about MySQL on IO-bound workloads.. The story is not complete, as it's missing the 2019's chapter (will tell it later, np) -- but if you'll (re)read the mentioned above article first, you'll better understand the next ;-))
But at least the current post is only about good news now -- the new DBLWR and how it helps to solve historical MySQL performance problems ! -- and as one picture is better than million words, I'll try to save 3M words here (as there are 3 pictures in this article ;-))
Well, I'll also skip all new design details (I think Sunny will better explain them all himself "from the first hands") -- I'll only mention the following :
For more details about config options you can check MySQL 8.0 doc about DBLWR explaining this all in details.
Enough words, let's go to the test results..
Here are my slides about MySQL 8.0 Scalability & Benchmarks from Percona LIVE 2019 in Austin, TX. (and I also realized I forgot to publish my slides from MySQL pre-FOSDEM 2019 Day about MySQL Performance Tuning, so fixing it now ;-))
P.S. Percona LIVE was "just awesome" ! ;-))
Rgds,
-Dimitri