DimitriK's (dim) Weblog

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 ;-))

Read more...

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

Read more...

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 ;-))

Read more...

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..

Read more...

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.

Read more...

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)

Read more...

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 :

• the DBLWR is no more part of "system tablespace", and can be placed anywhere you like (so, if you have a possibility to use a different storage for DBLWR files, you can totally get a rid of DBLWR impact on your main storage) -- but by default, DBLWR is stored in the same directory as your DATA
• you can configure how many DBLWR files you want to use
• and also how many pages per DBLWR files to have (which is also directly related to final DBLWR file size)

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..

Read more...

Generally you have much bigger benefit with dim_STAT when you're collecting and analyzing your data live (online). However, there maybe still many situations when this is simply not possible (due security restrictions, wide remote distance, or simply externally inaccessible hosts, etc.) -- for such cases dim_STAT has special tool EasySTAT allowing you to collect all the needed stats locally on the given machine, and then later to upload them to your dim_STAT server for post-analyze. To simplify overall setup, EasySTAT is integrated into STAT-service of dim_STAT and can be directly involved from there, also following the same rules for MySQL access and so on (e.g. if your STAT-service is already operational, then your EasySTAT will be too ;-))

The following article is explaining how to deploy and start EasySTAT on your Linux server from scratch, even if you never hear about..

Read more...