DimitriK's (dim) Weblog

My Slides from FOSDEM25 and Pre-FOSDEM Belgian Days 2025

Dimitri — Fri, 28 Feb 2025 19:15:00 GMT

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 :

Read more... (4 min remaining to read)

MySQL Performance : Switching InnoDB REDO Threads=OFF/ON

Dimitri — Thu, 11 Jul 2024 16:15:00 GMT

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... (114 min remaining to read)

MySQL Performance : Benchmark kit (BMK-kit)

Dimitri — Mon, 20 Jun 2022 17:45:00 GMT

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

dim_STAT : v.9.0 CoreUpdate-20-12

Dimitri — Thu, 31 Dec 2020 10:45:00 GMT

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

MySQL Performance : Understanding InnoDB IO Internals & "Checkpointing"

Dimitri — Thu, 13 Aug 2020 22:45:00 GMT

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

MySQL Performance : TPCC "Mystery" [SOLVED]

Dimitri — Tue, 30 Jun 2020 21:45:00 GMT

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.

MySQL Performance : XFS -vs- EXT4 Story

Dimitri — Wed, 13 May 2020 20:15:00 GMT

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)

MySQL Performance : The New InnoDB Double Write Buffer in Action

Dimitri — Thu, 30 Apr 2020 20:10:00 GMT

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... (3 min remaining to read)

My Slides about MySQL 8.0 Scalability & Benchmarks from #PerconaLIVE 2019 Austin, TX

Dimitri — Fri, 31 May 2019 21:55:00 GMT

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

MySQL 8.0 Benchmarks [PDF]
MySQL 8.0 Performance Tuning [PDF]

P.S. Percona LIVE was "just awesome" ! ;-))

Rgds,
-Dimitri

dim_STAT : Collect MySQL & System stats @Linux locally

Dimitri — Sun, 28 Apr 2019 18:15:00 GMT

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

My Slides about MySQL 8.0 Performance from #OOW18 and #PerconaLIVE 2018

Dimitri — Wed, 07 Nov 2018 15:00:00 GMT

As promised, here are slides about MySQL 8.0 Performance from my talks at Oracle Open World 2018 and Percona LIVE Europe 2018 -- all is combined into a single PDF file to give you an overall summary about what we already completed, where we're going in the next updates within our "continuous release", and what kind of performance issues we're digging right now.. ;-))

Also, I'd like to say that both Conferences were simply awesome, and it's great to see a constantly growing level of skills of all MySQL Users attending these Conferences ! -- hope you'll have even more fun with MySQL 8.0 now ;-))

MySQL Performance : 8.0 on IO-bound OLTP_RW vs Percona Server 5.7

Dimitri — Tue, 14 Aug 2018 13:00:00 GMT

This article is inspired by Percona blog post comparing MySQL 8.0 and Percona Server 5.7 on IO-bound workload with Intel Optane storage. There are several claims made by Vadim based on a single test case, which is simply unfair. So, I'll try to clarify this all based on more test results and more tech details..

But before we start, some intro :

InnoDB Parallel Flushing -- was introduced with MySQL 5.7 (as a single-thread flushing could no more follow), and implemented as dedicated parallel threads (cleaners) which are involved in background once per second to do LRU-driven flushing first (in case there is no more or too low amount of free pages) and then REDO-driven flushing (to flush the oldest dirty pages and allow more free space in REDO). The amount of cleaners was intentionally made configurable as there were many worries that these threads will use too much CPU ;-)) -- but at least configuring their number equal to number of your Buffer Pool (BP) Instances was resulting in nearly the same as if you have dedicated cleaner-per-BP-instance.

Multi-threaded LRU Flusher -- was introduced in Percona Server 5.7, implementing dedicated LRU cleaners (one thread per BP instance) independently running in background. The real valid point in this approach is to keep LRU cleaners independent to so called "detected activity" in InnoDB (which was historically always buggy), so whatever happens, every LRU cleaner remains active to deliver free pages according the demand. While in MySQL 5.7 the same was expected to be covered by involving "free page event" (not what I'd prefer, but this is also historical to InnoDB). However, on any IO-bounded workload I've tested with MySQL 5.7 and 8.0 by configuring 16 BP instances with 16 page cleaners and with LRU depth setting matching the required free page rate -- I've never observed lower TPS comparing to Percona..

Single Page Flushing -- historically, in InnoDB when a user thread was not able to get a free page for its data, it was involving a "single page flush" itself, expecting to get a free page sooner -- the motivation behind such an approach was "better to try to do something than just do nothing". And this was blamed so often.. -- while, again, it's largely exaggerated, because the only real problem here is coming due a historical "limited space" for single page flush in DoubleWrite Buffer, and that's all. To be honest, making this option configurable could allow anyone to evaluate it very easily and decide to keep it ON or not by his own results ;-))

DoubleWrite Buffer -- probably one of the biggest historical PITA in InnoDB.. -- the feature is implemented to guarantee page "atomic writes" (e.g. to avoid partially written pages, each page is written first to DoubleWrite (DBLWR) place, and only then to its real place in data file). It was still "good enough" while storage was very slow, but quickly became a bottleneck on faster storage. However, such a bottleneck you could not observe on every workload.. -- despite you have to write your data twice, but as long as your storage is able to follow and you don't have waits on IO writes (e.g. not on REDO space nor on free pages) -- your overall TPS will still not be impacted ;-)) The impact is generally becomes visible since 64 concurrent users (really *concurrent*, e.g. doing things on the same time). Anyway, we addressed this issue yet for MySQL 5.7, but our fix arrived after GA date, so it was not delivered with 5.7 -- on the same time Percona delivered their "Parallel DoubleWrite", solving the problem for Percona Server 5.7 -- lucky guys, kudos for timing ! ;-))

Now, why we did NOT put all these points on the first priority for MySQL 8.0 release ?

there is one main thing changes since MySQL 8.0 -- for the first time in MySQL history we decided to move to "continuous release" model !
which means that we may still deliver new changes with every update ;-))
(e.g. if the same was possible with 5.7, the fix for DBLWR would be already here)
however, we should be also "realistic" as we cannot address fundamental changes in updates..
so, if any fundamental changes should be delivered, they should be made before GA deadline
and the most critical from such planned changes was our new REDO log implementation !
(we can address DBLWR and other changes later, but redesigning REDO is much more complex story ;-))
so, yes, we're aware about all the real issues we have, and we know how to fix them -- and it's only a question of time now..

So far, now let's see what of the listed issues are real problems and how much each one is impacting ;-))

For my following investigation I'll use :

the same 48cores-HT 2S Skylake server as before
x2 Optane drives used together as a single RAID-0 volume via MDM
same OL7.4, EXT4
Sysbench 50M x 8-tables data volume (same as I used before, and then Vadim)
Similar my.conf but with few changes :

trx_commit=1 (flush REDO on every COMMIT as before)
PFS=on (Performance Schema)
checksums=on (crc32)
doublewrite=off/on (to validate the impact)
binlog=off/on & sync_binlog=1 (to validate the impact as well)

Test scenarios :

Concurrent users : 32, 64, 128
Buffer Pool : 128GB / 32GB
Workload : Sysbench OLTP_RW 50Mx8tab (100GB)
Config variations :

1) base config + dblwr=0 + binlog=0
2) base config + dblwr=1 + binlog=0
3) base config + dblwr=0 + binlog=1
4) base config + dblwr=1 + binlog=1
where base config : trx_commit=1 + PFS=on + checksums=on

NOTE : I did not build for all the following test results "user friendly" charts -- all the graphs are representing real TPS (Commit/sec) stats collected during the tests, and matching 3 load levels : 32, 64, and 128 concurrent users.

128GB BUFFER POOL

So far, let's start first with Buffer Pool =128GB :

OLTP_RW-50Mx8tab | BP=128G, trx=1, dblwr=0, binlog=0

Comments :

with Buffer Pool (BP) of 128GB we keep the whole dataset "cached" (so, no IO reads)
NOTE : PFS=on and checksums=on -- while TPS is mostly the same as in the previous test on the same data volume (where both PFS & checksums were OFF), they are not impacting here
as well from the previous test you can see that even if the data are fully cached in BP, there is still an impact due used storage -- the result on Intel SSD was way lower than on Intel Optane
so, in the current test result you can see that MySQL 8.0 also getting better benefit from a faster storage comparing to Percona Server, even if the given test is mostly about REDO related improvements ;-))

And now, let's switch DobleWrite=ON :

MySQL Performance : 8.0 GA on IO-bound TPCC

Dimitri — Thu, 05 Jul 2018 20:55:00 GMT

This post is mainly inspired by findings from the previous testing of MySQL 8.0 on TPCC workload(s) and observations from IO-bound Sysbench OLTP on Optane -vs- SSD. But also by several "urban myths" I'm often hearing when discussing with users about their IO-bound OLTP performance problems :

Myth #1 : "if I'll double the number of my storage drives -- I'll get x2 times better TPS !"

this was mostly true during "HDD era", and again..
(ex.: a single thread app doing single random IO reads from a single HDD will not go faster by doing the same from 2x HDD -- similar like single thread workload will not run faster on 8CPU cores -vs- 2CPU cores, etc.)
all depends on your workload and how many parallel IO operations you're involving..
indeed, it is much more easier to saturate HDD, but it's much more harder to do it with modern SSD/NVMe
NOTE : we're speaking about OLTP (e.g. if you started to observe full table scans in your workload -- means you're already doing something wrong ;-))
simple rule : if you're not saturating your storage on any of its limits => you'll not see any gain by adding more drives, you'll probably just have a bigger storage space, that's all.

Myth #2 : "I'll go faster with flash drive which is claimed capable x2 times more IOps in specs than my current drive !"

if you're expecting to run OLTP workload, rather pay attention to IO latency first !
sometimes it may be not mentioned these x2 times more IOps were obtained with x4 times more IO threads ;-))
e.g. a drive capable of x2 times more IOps but with x4 times higher latency will still be x4 times slower than your current drive on 1 user load, and 2, and 4, and .. maybe up to 64 ? (depends on where your current drive the limit is reached) -- and if you don't have more than 64 concurrent users ? -- then you'll never see your x2 times more IOps, but rather x4 times worse TPS ;-))

Test it yourself - this is the only advice I could give you !

because "only a real test will give you a real answer" (and I'm repeating to say it again and again.. ;-))
testing your own workload will give you the best answer !
otherwise you may still use some generic benchmark workloads which are representative for you
for ex. your new flash drive may be look better from all points and passing very well all generic pure IO tests, but show x3 times worse results once used by MySQL -- and this is just because, for ex., every involved fsync() will take x3 times more time, etc. (based on real story, no kidding ;-))

So far, for the following story I'll use :

Sysbench-TPCC workload, 10x100W (x10 of 100 warehouses, ~100GB data, here is why)
Same Skylake server as before, same config, etc.
and the same Optane & SSD as in the previous testing, except that I'll also add to the game x2 Optane drives used as a single RAID-0 MDM volume !
EXT4 is used on top of each drive or volume

Starting Test scenario :

concurrent users : 1, 2, 4, .. 1024
trx_commit : 1 (flush REDO on every COMMIT)
Buffer Pool (BP) : 128GB (in-memory), 32GB (IO-bound)

Sorry, no "user friendly" graphs this time, but hope it'll still be easy to understand the results ;-))

On the first graph you'll see :

3 test results : with x2 Optaine first, then with single Optane, and then single SSD
on each test the load is going from 1, 2, 4 .. up to 1024 concurrent users
the Commits/sec curve is showing obtained TPS level

and the first result is with 128GB BP :

InnoDB Buffer Pool 128GB

Comments :

first of all, as you can see, TPS is not better with two -vs- one Optane
as we're not hitting any single limit of Optane drive, there is no any gain by using x2 ;-))
also, regardless the used data volume is ~100GB, we're not observing here such a big difference between Optane -vs- SSD as it was with Sysbench OLTP on a similar data size and still "in-memory"..
this because TPCC workload is much less aggressive on IO writes, so REDO flushing is less impacted..

now, how TPS will be impacted if BP size was smaller, just 32GB ?

MySQL Performance : 8.0 GA on IO-bound Sysbench OLTP with Optane -vs- SSD

Dimitri — Mon, 02 Jul 2018 20:05:00 GMT

MySQL Performance on IO-bound workloads is still extremely depending on the underlaying storage layer (thus is directly depending on your Storage Performance).. Indeed, flash storage is definitively changing the game, but even with flash there is, as usual, "flash and flash" -- all storage vendors are improving their stuff constantly, so every time you have something new to discover and to learn ;-)) During all my MySQL 8.0 GA tests I was very pleasantly surprised by IO performance delivered by Intel Optane SSD. However, what the storage device can deliver alone on pure IO tests is not at all the same to what you could observe when it's used by MySQL -- unfortunately, in the past I've observed many cases when with a device claimed to be x2 times faster we were even not observing 10% gain.. But MySQL 8.0 is probably the most best placed MySQL version today to re-visit all this IO-bound story (there are many "under-hood" changes in the code helping to use your storage more efficiently) -- well, we're definitively yet very far from "perfect" ;-)) -- but again, as usual -- "everything is relative".. And for the "relative" point for this article I'll propose you to get a look on 2 different flash drives from Intel :

Intel SSD: SSDSC2BA800G4 (800GB)
Intel Optane SSD: P4800X Series (375GB)

yes, both devices are from Intel, so it's only Intel -vs- Intel (e.g. no blame for "competition" post ;-))

so far, if you'll look on their specs, both drives are looking pretty good for avg. IO-bound workloads, except that Optane drive is claimed to be x5 times faster in all categories (and it's particularly impressive with its low IO latency -- see 1M IO-bound QPS with MySQL 8.0 -- so, can confirm at least for latency ;-)) -- however, will the x5 claim still be valid in different conditions / workloads ?

For my testing I'll use the same config as before (the same Skylake server as well), and the key config points are :

InnoDB page size : 16K
trx_commit : 1 (flush REDO on every commit)
single flash drive (just EXT4 on top of single drive, no RAID, no LVM, etc..)
InnoDB Buffer Pool (BP) : 128G / 64G / 32G (will vary along with test scenarios)

(no double write, no binlog for the moment -- these ones will be covered later, patience ;-))..

So far, let's start first with so called "in-memory" Sysbench OLTP_RW workload :

test workload : Sysbench OLTP_RW uniform
concurrent users : 1, 2, 4, .. 1024
data volume : 8 tables of 10M rows each (~20GB)
InnoDB Buffer Pool (BP) : 128GB
so, all the data will be fully cached in BP, thus NO any IO reads (the reason to be so called "in-memory")
however, there will be definitively IO writes (as there are writes in OLTP_RW ;-))
and in our case, if all is going well, there will be only 2 types of writes :

1) dirty pages flushing
2) REDO log writes

the 1) is going in background, and as soon as your flushing is fast enough -- your performance is not impacted
the 2) are pure sequential writes only mixed with periodic fsync(), and generally as soon as you use a good flash drive for your REDO files -- you're fine ;-))

Ok, what about results ?

Sysbench OLTP_RW 10Mx8-tables BP = 128GB : Intel SSD -vs- Intel Optane

Comments :

on the left side of the graph you can see TPS level of MySQL 8.0 running on SSD, and on the right side -- the same but on Optane
concurrent users level is progressing from 1 to 2, 4, 8, .. 1024
and from TPS results you can see that up to 16 users there is nearly no TPS difference between SSD and Optane
then Optane is slightly better on 64usr level, and definitively better on higher load
but you don't see any sign of expected x5 difference, right ?
and if you'll stop here, you could just say "all this Optane story is b*shit !", Dimitri, WFT ?.. ;-))

Indeed, on such a small data volume and having all the active data set cached in BP you'll not see much difference with a faster storage.. -- however, what will happen if your data will grow ?..

MySQL Performance : IP port -vs- UNIX socket impact in 8.0 GA

Dimitri — Fri, 15 Jun 2018 14:05:00 GMT

Generally, when I'm analyzing MySQL Performance on Linux with "localhost" test workloads, I'm configuring client connections to use IP port (loopback) to connect to MySQL Server (and not UNIX socket) -- this is still at least involving IP stack in the game, and if something is going odd on IP, we can be aware ahead about. And indeed, it already helped several times to discover such kind of problems even without network links between client/server (like this one, etc.). However, in the past we also observed a pretty significant difference in QPS results when IP port was used comparing to UNIX socket (communications via UNIX socket were going near 15% faster).. Over a time with newer OL kernel releases this gap became smaller and smaller. But in all such cases it's always hard to say if the gap was reduced due OS kernel / IP stack improvements, or it's just because MySQL Server is hitting new scalability bottlenecks ;-))

Anyway, I've still continued to use IP port in all my tests until now. But recently the same discussion about IP port impact -vs- UNIX socket came up again in other investigations, and I'd like to share the results I've obtained on different HW servers with MySQL 8.0 GA release.

The test workload is the most "aggressive" for client/server ping-pong exchange -- Sysbench point-selects (the same test scenario which initially already reported 2.1M QPS on MySQL 8.0) -- but this time on 3 different servers :

4CPU Sockets (4S) 96cores-HT Broadwell
2CPU Sockets (2S) 48cores-HT Skylake
2CPU Sockets (2S) 44cores-HT Broadwell

and see how different the results will be when exactly the same test is running via IP port / or UNIX socket.

Broadwell 2S 44cores-HT

- IP port :

- UNIX socket :

Comments :

wow, up to 18% difference !
and you can clearly see MySQL 8.0 out-passing 1.2M QPS with UNIX socket, and staying under 1.1M QPS with IP port..

Read more... (4 min remaining to read)

MySQL Performance : more in depth with latin1 and utf8mb4 in 8.0 GA

Dimitri — Tue, 05 Jun 2018 15:40:00 GMT

Looking on my previously obtained results on Read-Only (RO) tests for latin1 and UTF8 charsets, one question continued to turn in my mind :

- if MariaDB 10.3 is hitting a so deep drop on "distinct-ranges" workload :

- why then this is not impacting the "mixed" OLTP_RO workload results (which is containing "distinct-ranges" query too) :

The answer was within the test title :

I've missed one zero in my scripts while preparing initial tests.. ;-))
so, the "distinct-ranges" test was using range size=10 (instead of 100 by default)
while "mixed" OLTP_RO remained with default settings, and used range size=100 for all range tests..
was the use of a smaller range size which that much impacted MariaDB ?..
(generally people are expecting to see wider range queries to be more impacting than the smaller ones, right ?)..

To clarify this all -- I decided to replay RO tests with a more detailed analyze..

MySQL Performance : 8.0 GA and TPCC Workloads

Dimitri — Tue, 15 May 2018 00:51:00 GMT

Generally TPC-C benchmark workload is considered as one of the #1 references for Database OLTP Performance. On the same time, for MySQL users it's often not something which is seen as "the most compelling" for performance evaluations.. -- well, when you're still fighting to scale with your own very simple queries, any good result on something more complex may only look as "fake" ;-)) So, since a long time Sysbench workloads remained (and will remain) as the main #1 "entry ticket" for MySQL evaluation -- the most simple to install, to use, and to point on some sensible issues (if any). Specially that since new Sysbench version 1.0 a lot of improvements were made in Sysbench code itself, it really scales now, has the lowest ever overhead, and also allowing you to add your own test scenario via extended LUA scripts (and again, with lowest ever overhead) -- so, anyone can easily add whatever kind of different test scenarios and share with others ! (while I'd say "the most compelling test workload" for any given user should be the workload which is the most closely reproducing his production load -- and you can probably do it now with new Sysbench, just try it !).

However, from MySQL Dev side, every given benchmark workload is mostly seen asa yet one (or several ones) problem(s) to resolve. Some of problems are common for many workloads, some are completely different ones, but generally it's never about "something cool" -- and we're just progressing in this long road by fixing one problem after another (to hit yet another one again). So, TPC-C workload for MySQL is just yet another problem to resolve ;-))

Historically the most popular TPC-C implementations for MySQL were :

DBT-2 : an open source version of TPC-C
TPCC-mysql : another open source version of TPC-C developed by Percona

MySQL Performance : 1M IO-bound QPS with 8.0 GA on Intel Optane SSD !

Dimitri — Mon, 14 May 2018 12:15:00 GMT

Historically, Random I/O Reads were always a major PITA for any OLTP workload.. If Random I/O Writes you could yet "delay" via controller's caches (or any kind of other battery-protected caches -- specially if Writes are coming in bursts), there is no way to "predict" I/O Reads if they are fully Random (so you cannot "cache" or "prefetch" them ahead and have to deliver the data directly from storage, read by read.. -- which is hitting a huge "rotation penalty" on HDD).

Indeed, things changed dramatically since arriving of Flash Storage. You don't need to spend any particular attention if your I/O Reads are Random or Sequential. However, you still need to keep in mind to not hit the overall throughout limit of your Flash Device. As the result, reading by smaller I/O blocks allowing you to do more I/O operations/sec than with bigger blocks.

And what about InnoDB ? -- InnoDB is using by default 16KB page size (so by default all Random I/O Reads are of 16KB) :

And with 16KB Random Reads you definitively will reach your throughput limit sooner than with 8KB or 4KB Reads. Many users are seeing "compression" as the best matching solution here. And, indeed, if you're very lucky and can compress your data, say, by x4 times => you'll Read then only 4KB from storage to deliver 16KB of data. And, yes, you'll be able to deliver x4 times more Reads/sec :

Read more... (4 min remaining to read)

MySQL Performance : 8.0 RW & Binlog impact

Dimitri — Tue, 08 May 2018 22:32:00 GMT

In the previous article I've intentionally skipped the topic related to Binlog impact on MySQL 8.0 Performance, because it's not a short story, nor a simple one..

In fact, for most of people Binlog in MySQL is generally representing and additional overhead, and historically it was true. Since MySQL 5.6 there is Binlog Group Commit (BGC) feature available, and it was rather doing well, decreasing the gap between "binlog=OFF" and "binlog=ON sync_bin=1". However, storage vendors are making flash drives more and more better from year to year.. And when we delivered MySQL 5.7 the scope of Binlog impact moved with code and flash improvements -- the main impact was no more coming from the I/O operations related to Binlog, but to the Binlog code itself ! -- indeed, this may sound odd initially, but let's go to "pictures" to see it better in details ;-))

So far, I'll reuse the same Skylake server as before, but will reduce it to 1S (1CPU Socket, 24cores-HT) -- so, you don't believe it's all because I'm using a "big HW" (while 2S HW is the most common server config in all data centers where people are running MySQL, and having 16-24cores per CPU Socket is what is todays "commodity HW") -- but well, let's stay with 24cores-HT for the following experiment ;-)) And as the storage it'll be the same Optane drive with EXT4 filesystem.

I'll now replay the same Sysbench OLTP_RW and Update-NoKEY tests as before, but using only 1S (24cores-HT) and with the same my.conf but :

1) binlog=OFF
2) binlog=ON sync_bin=0
3) binlog=ON sync_bin=1000
4) binlog=ON sync_bin=1

and the results are here, each graph is representing a given test workload growing with number of concurrent user sessions from 1, 2, 4, .. to 1024 :

Sysbench OLTP_RW 10Mx8-tables @MySQL-5.7

Comments :

as you can see, on a higher load enabling Binlog is helping a higher TPS !
(well, in fact it's helping not to gain, but rather not to loose TPS on high load)
but in any case, you're seeing here a positive impact ! ;-))
and you can understand that in such a case it was difficult to blame Binlog code along MySQL 5.7 time ;-))

Specially that even more important "positive impact" happens on much more aggressive writes with Update-NoKey workload :

Read more... (3 min remaining to read)

MySQL Performance : 8.0 and Sysbench OLTP_RW / Update-NoKEY

Dimitri — Tue, 08 May 2018 01:37:00 GMT

This post is following previously published OLTP_RO results for MySQL 8.0 ( latin1 and utf8mb4 charsets), and now is focusing on Sysbench RW workloads, particularly "mixed" OLTP_RW and Update-NoKey :

OLTP_RW : while this workload has writes, it's mainly driven by reads (OLTP_RO + 2 updates + delete + insert)
Update-NoKey : aggressively bombarding UPDATE queries (but with no changes on indexed columns)

The same 2S Skylake server was used as in previous tests :

Server configuration :

OS : Oracle Linux 7.4
CPU : 48cores-HT Intel Skylake 2.7Ghz (2CPU sockets (2S), Intel(R) Xeon(R) Platinum 8168 CPU)
RAM: 172GB
Storage : x2 Intel Optane flash drives (Intel (R) Optane (TM) SSD P4800X Series)

volume : RAID-0 via MDADM
filesystem : EXT4

And I'm following mostly the same test conditions as previously explained for MySQL 5.7 GA -- similar variations in options (spin delay = 6;24;96 / thread concurrency = 0;64;128 / taskset = 1S/2S, etc.) to let each Engine to show its best possible TPS/QPS results.

However, as running the test with all these config variations is taking a significant time, I've slightly reduced the scope of investigation to the following :

trx_commit = 1 : along with our work on InnoDB REDO re-design we not only fixed the biggest related bottleneck, but also discovered and partially fixed several other issues around REDO (also mostly historical, but still) -- keeping all this in mind, I'd rather suggest you today to use "1" (flushing REDO log on COMMIT) whenever possible -- specially that with all the progress we're seeing on HW/Storage improvement last years -- the penalty of "1" with 8.0 becomes much less dramatic than before -vs- "2" (flush REDO log once per second), and also a big enough total size for the whole REDO space (currently I'm using x16 or x32 log files of 1GB each), more about later..

Please, don't miss also Pawel's article about how exactly the new REDO log was implemented in MySQL 8.0 GA and why it was not a simple story..
PFS = off : I'm intentionally now switching Performance Schema OFF just because it's not a bottleneck, but a pure "overhead" (as many other things as well) -- my main target in all benchmark investigations is "to see what is our next bottleneck", and as HW resources are always limited, any additional overhead will help to "hide" the real problem.. While PFS overhead is part of MySQL QA testing, and every overhead higher than 5% for "default instrumentation" is considered as a bug (mind to file a bug if you see it bigger in your case!) -- while from the other side many users are asking to see more an more instrumentation enabled by default regardless overhead (and this "balance" between overhead and benefit from built-in instrumentation is generally can be observed only case by case).
Checksums = off : this is also a pure "overhead" and not a bottleneck, while since CRC32 is supported, generally you'll not hit any problem..
Charset = latin1 : while most of interest is moving to UTF8, I'm continuing to test with "latin1" for the same reasons as UTF8 -vs- latin1 "overhead" which may hide you more important problems (while using UTF8 in 8.0 is giving you a direct gain -vs- any previous MySQL release, but I'm rather looking to point on problems than hide them)..
DoubleWrite = off : this, however, is a big problem and a big bottleneck, but the fix was already developed by Sunny since 2 years now, we worked on this together, and I can confirm you you'll not see any TPS drop as soon as your storage is able to follow (as you "writing twice", e.g. x2 times more) -- but the code is still NOT part of 8.0 because "there is always something more important to do" ;-)) -- please feel free to urge Sunny to push re-designed DoubleWrite code to 8.0 asap !! (Sunny's twitter : @sunbains) -- while for my part I need to see "what is after" once the new code is delivered..
Binlog = off : this is another big problem, and on the same time both bottleneck and overhead.. -- but this one rather need a very particular attention, so I'll skip it here to say you more later..

The full list of all config options you may always find at the end of the article, while here are the final results :

Sysbench OLTP_RW 10Mx8-tables TPS

Comments :

over 45K TPS with MySQL 8.0 !
around 35K TPS with MySQL 5.7 -- interesting that similar result was obtained in the past with 5.7 on 4S 72cores-HT Broadwell server, and now 2S Skylake 48cores-HT is just enough to get the same ;-))
NOTE : and we're still far from the max possible TPS to get from this HW ! => work in progress..

While looking on the same result expressed in QPS we can see that we're more and more close to 1M QPS obtained on the same server with pure OLTP_RO :

Read more... (3 min remaining to read)

MySQL Performance : 8.0 and UTF8 impact

Dimitri — Wed, 25 Apr 2018 22:58:00 GMT

The world is moving to UTF8, MySQL 8.0 has utf8mb4 charset as default now, but, to be honest, I was pretty surprised how sensible the "charset" related topic could be.. -- in fact you may easily hit huge performance overhead just by using an "odd" config settings around your client/server charset and collation. While to avoid any potential charset mismatch between client and server, MySQL has from a long time an excellent option : "skip-character-set-client-handshake" which is forcing any client connection to be "aligned" with server settings ! (for more details see the ref. manual : https://dev.mysql.com/doc/refman/8.0/en/server-options.html#option_mysqld_character-set-client-handshake) -- this option is NOT set by default (to leave you a freedom in choose of charsets used on client and server sides). However, in my sense, it's still better to align clients according to the server settings to avoid any potential client misconfig..

As well if you wish to use UTF8, please use "utf8mb4" as first of all it's the most complete for any kind of characters (and probably the only one which makes sense as of today), and second -- its related code was yet more improved in MySQL 8.0 for better efficiency. How much more efficient ? -- let's see from the following test results.

but first of all, the related config setup :

character_set_server=utf8mb4
collation_server=utf8mb4_0900_ai_ci
skip-character-set-client-handshake
sort_buffer_size=512K

NOTE: mind to use a bigger sort buffer for UTF8

The results are obtained with on the same 2S Skylake as in the previously published RO tests with latin1 and with the same test workloads (just that for latin1 you need to change character_set_server= latin1 and collation_server= latin1_swedish_ci)

So far, here we are :

MySQL Performance : over 1.8M QPS with 8.0 GA on 2S Skylake !

Dimitri — Tue, 24 Apr 2018 19:14:00 GMT

Last year we already published our over 2.1M QPS record with MySQL 8.0 -- it was not yet GA on that moment and the result was obtained on the server with 4CPU Sockets (4S) Intel Broadwell v4. We did not plan any improvement in 8.0 for RO related workloads, and the main target of this test was to ensure there is NO regressions in the results (yet) comparing to MySQL 5.7 (where the main RO improvements were delivered). While for MySQL 8.0 we mostly focused our efforts on lagging WRITE performance in MySQL/InnoDB, and our "target HW" was 2CPU Sockets servers (2S) -- which is probably the most widely used HW configuration for todays MySQL Server deployments..

However, not only SW, but also HW is progressing quickly these days ! -- and one of my biggest surprises last time was about Intel Skylake CPU ;-)) -- the following graph is reflecting the difference between similar 2S servers, where one is having the "old" 44cores-HT Broadwell v4, and another the "new" 48cores-HT Skylake CPUs :

the difference is really impressive, specially when you see that just on 32 users load (when CPU is not at all saturated not on 44cores nor 48cores) there is already 50% gain with Skylake ! (and this is about a pure "response time"), and on peak QPS level it's over 1.8M QPS (not far from 80% gain over Brodawell)..

And this results is marking our next milestone in MySQL RO performance on 2S HW ! ;-))

Read more... (3 min remaining to read)

MySQL Performance : Testing 8.0 with less blood..

Dimitri — Tue, 24 Apr 2018 06:19:00 GMT

This is just a short reminder about what to keep in mind when you're preparing some MySQL 8.0 performance testing (or any other 8.0 evaluation) and want to do it "with less blood" ;-))

So far, here is the list :

8.0 is using UTF8 by default, so if you're expecting to compare apples-to-apples, configure it with "latin1" as it was before to compare to 5.7/5.6/etc. (or configure them all to UTF8 if your target is to compare UTF8)..
binlog is enabled by default, so mind to switch it OFF if it's not in your target..
SSL is ON by default (switch it OFF if not your target)
auto UNDO truncate is ON by default (if you prefer to avoid any periodic spikes in background of flushing activity due UNDO auto truncate, just switch this features OFF (while you'll still be able to involve the same truncate manually whenever you need it))
there is a new default authentication plugin (and if you want to see your old apps still working with 8.0, just initialize your MySQL instance + use in your config file the old plugin instead (NOTE: you may still switch plugins via ALTER))
InnoDB doublewrite fix is still NOT part of the published code, so unless your target is to really show how this missed fix is impacting your workload, switch it OFF (but still mind to bombard Sunny with complaining messages about how this fix is important for you ;-))

And now all these points in action :

MySQL Performance : my slides from MySQL Day & FOSDEM Feb.2018

Dimitri — Sun, 11 Feb 2018 20:04:00 GMT

As promised, the following are links to slides from my talks during MySQL Day and FOSDEM @Brussels in Feb.2018 :

MySQL Day : 8.0-dev Scalability & Benchmarks
FOSDEM #18 : 8.0 InnoDB Re-Design

NOTE : for those who did not follow, CATS is not the only change in InnoDB ;-))

Rgds,
-Dimitri

dim_STAT v.9.0 CoreUpdate-17-12 is here !

Dimitri — Fri, 29 Dec 2017 22:29:00 GMT

Year is finishing, and as a kind of "end-of-year" gift, I'm happy to announce that a freshy new CoreUpdate-17-12 is available from now ! ;-))

IMPORTANT : this is a very "sensible" update, and you cannot just to apply it on the top of already deployed dim_STAT instance as before.. -- there was a need to fix several issues within "under hood" binaries to make the new stuff working properly, so the new code has simply no chances to work with old binaries.. So far, I decided to make it all as a "single shot move" -- align a new update shipment with a moving to "64bit" versions support only :

e.g. this is not a new version of dim_STAT
this is just a "remastered" 64bit release + several visible and internal fixes
any 32bit releases are remained "as it", and it makes no more sense to continue to support them..
64bit versions of dim_STAT v.9.0 are available for Linux and MacOSX (macOS)
any further CoreUpdate will work only with 64bit version having the latest supported binaries..

So, what about this "new stuff" requiring such deep changes to go till the binaries remastering ?.. -- a very long story short, this is all about support of SVG images ! ;-)) -- I've started to develop dim_STAT exactly 20 years (!!) ago.. (hard to believe time is flying so fast..) -- initially dim_STAT used Java Applets (it was very lightweight (yes! it was so 20 years ago ;-)) and it was really cool, "live", etc.) -- but then Java support in a browser became only heavier and heavier, so I've added PNG images support (which could bring back the initial lightweight to dim_STAT and make it "usable" again ;-)) -- and today, things are changing again ;-)) -- "retina" and other "high resolution" screens become more an more popular, and on these screens my previously "good enough" PNG graphs are looking just "ugly" (to be polite ;-)). After testing and analyzing tons of various JS-based (or other) live graphing tools/libs, I've finally stopped my choice on SVG ! -- it's already supported by most of web browsers, still lightweight, and has a huge advantage -- it's extremely well "readable" !! and you can scale (!!) it very easily as much as you want ;-)) (so, no more color confusions and simply ugly graphics ;-))

An SVG graph is looking like this :

well, this is just a snapshot, but hope you already can get an idea about how much "more clean" your graphs could be ;-))

MySQL Performance: 8.0 re-designed REDO log & ReadWrite Workloads Scalability

Dimitri — Mon, 23 Oct 2017 14:19:00 GMT

This post is following the story of MySQL 8.0 Performance & Scalability started with article about 2.1M QPS obtained on Read-Only workloads. The current story will cover now our progress in Read-Write workloads..

Historically our Read-Only scalability was a big pain, as Read-Only (RO) workloads were often slower than Read-Write (sounds very odd: "add Writes to your Reads to go faster", but this was our reality ;-)) -- and things were largely improved here since MySQL 5.7 where we broke 1M QPS barrier and reached 1.6M QPS for the first time. However, improving Writes or mixed Read+Writes (RW) workloads is a much more complex story..

What are the main scalability show-stoppers in MySQL 5.7 and current 8.0 release candidate for RW and IO-bound workloads? - the most "killing" are the following ones :

REDO log contentions are blocking your whole transaction processing from going faster..
Transaction (TRX) management becomes very quickly a huge bottleneck as soon as your transactions are fast and/or short..
internal locking and row locking (LOCK) will quickly kill your performance as soon as your data access pattern is not uniform, etc..
and yet more, historically as soon as you're involving any IO operations, they all will go via one single and global locking path (fil_system mutex) which will make a use of faster storage solutions (flash) simply useless..

so, it was definitively a time to take our hands on this ;-))

The whole story about is pretty amazing, but I have to be short, so :

in short : we know exactly what we have to do to get a rid of this all
we have a working prototype code allowing us to expect pretty important potential gains in RW and pure Write workloads
the only real problem here is that a road from "prototype" to "production quality" code is much longer than anyone could expect (even me ;-))
so within MySQL 8.0 GA timeframe we could not deliver all the fixes we have, and we have to go by priority here..
and the priority #1 from the list of issues mentioned above is for sure going to REDO and IO problems, as the most common show-stoppers for most of RW workloads today
the 8.0 planned changes are not yet final, but you may already get a first idea about by trying our "preview" MySQL 8.0-labs release

MySQL Performance : 2.1M QPS on 8.0-rc

Dimitri — Wed, 04 Oct 2017 01:33:00 GMT

The first release candidate of MySQL 8.0 is here, and I'm happy to share few performance stories about. This article will be about the "most simple" one -- our in-memory Read-Only performance ;-))

However, the used test workload was here for double reasons :

1) validate MySQL 8.0 performance
2) fully evaluate the "New" Sysbench developed by Alex (https://github.com/akopytov/sysbench)

Going ahead to the second point, the main worry about New Sysbench was about its LUA overhead (the previous version 0.5 was running slower than the old one 0.4 due LUA) -- a long story short, I can confirm now that the New Sysbench is running as fast as the oldest "most lightweight" Sysbench binary I have in use ! so, KUDOS Alex !!! ;-))

While regarding the improvements coming with MySQL 8.0 on Read-Only workloads I'd mention :

several "overheads" were fixed
the most notable one is related to UTF8, of course
however, even latin1 related functions were improved little bit
but this was only about "overheads", and nothing about "scalability"
because the main "scalability" gap was already made with MySQL 5.7 two years ago ;-))
so, our main merit with MySQL 8.0 here will be rather NOT TO LOOSE the already obtained gain !
(agree, sounds very odd, but if you'll just look on the list of the all new features coming with 8.0 you can imagine our code path is not going to be shorter, right ? ;-))
so the fair test here will be to compare 8.0 vs 5.7 and 5.6 with latin1 encoding
(for UTF8 the winner is 8.0 and from very far, which you already know)

The most "sensible" RO workload in Sysbench is Point-Selects, so here is my test scenario:

workload : New Sysbench RO point-selects
data volume : 8 tables of 10M rows each
encoding : latin1
user load levels : 1, 2, 4, .. 1024
engines : MySQL 8.0, MySQL 5.7, MySQL 5.6
server : 96cores-HT 4CPU sockets 2.2Ghz (Broadwell), OL7.3

and here is the result :

Observations :

2.1M SQL Query/sec for MySQL 8.0 -- our current new Max QPS record obtained in MySQL history !
which is great, no doubt !
however, there is a clearly seen small, but visible QPS regression on lower load levels..
which is not really cool (even if could be easily explained by increased code path + new DD + etc.. etc..)
well, adding it to my list of "low load" performance issues and will investigate later..

MySQL Performance : 8.0-dev Progress Details

Dimitri — Tue, 09 May 2017 19:05:00 GMT

As promised, here is the follow up of the MySQL 8.0-dev Progress teaser ;-)

so, yes, we expect to see the most hot contentions gone, as it's seen from the following graph :

Observations :

the graph is representing the spin waits / spin rounds events happening during the same Sysbench Update-NoKEY workload
the load is progressing from 8 concurrent users to 16, 32, .. 512
3 engines are tested one after one : MySQL 5.7, MySQL 8.0 (current DMR), MySQL 8.0-dev (current prototype)
first all 3 engines are running on 22cores-HT only (1 CPU socket)
then, the second time : on 44cores-HT (2 CPU sockets)
and the most important thing to retain about this graph is that on 8.0-dev we see all main hot contentions gone ;-)

Read more... (4 min remaining to read)

All Older Posts Are Here..

Dimitri — Mon, 01 May 2017 13:00:00 GMT

All the older posts from this blog you may still find here :

have fun ! ;-))

Rgds,
-Dimitri

DimitriK's (dim) Weblog

My Slides from FOSDEM25 and Pre-FOSDEM Belgian Days 2025

MySQL Performance : Switching InnoDB REDO Threads=OFF/ON

MySQL Performance : Benchmark kit (BMK-kit)

dim_STAT : v.9.0 CoreUpdate-20-12

MySQL Performance : Understanding InnoDB IO Internals & "Checkpointing"

MySQL Performance : TPCC "Mystery" [SOLVED]

MySQL Performance : XFS -vs- EXT4 Story

MySQL Performance : The New InnoDB Double Write Buffer in Action

My Slides about MySQL 8.0 Scalability & Benchmarks from #PerconaLIVE 2019 Austin, TX

dim_STAT : Collect MySQL & System stats @Linux locally

My Slides about MySQL 8.0 Performance from #OOW18 and #PerconaLIVE 2018

MySQL Performance : 8.0 on IO-bound OLTP_RW vs Percona Server 5.7

128GB BUFFER POOL

MySQL Performance : 8.0 GA on IO-bound TPCC

MySQL Performance : 8.0 GA on IO-bound Sysbench OLTP with Optane -vs- SSD

MySQL Performance : IP port -vs- UNIX socket impact in 8.0 GA

MySQL Performance : more in depth with latin1 and utf8mb4 in 8.0 GA

MySQL Performance : 8.0 GA and TPCC Workloads

MySQL Performance : 1M *IO-bound* QPS with 8.0 GA on Intel Optane SSD !

MySQL Performance : 8.0 RW & Binlog impact

MySQL Performance : 8.0 and Sysbench OLTP_RW / Update-NoKEY

MySQL Performance : 8.0 and UTF8 impact

MySQL Performance : over 1.8M QPS with 8.0 GA on 2S Skylake !

MySQL Performance : Testing 8.0 with less blood..

MySQL Performance : my slides from MySQL Day & FOSDEM Feb.2018

dim_STAT v.9.0 CoreUpdate-17-12 is here !

MySQL Performance: 8.0 re-designed REDO log & ReadWrite Workloads Scalability

MySQL Performance : 2.1M QPS on 8.0-rc

MySQL Performance : 8.0-dev Progress Details

All Older Posts Are Here..

MySQL Performance : 1M IO-bound QPS with 8.0 GA on Intel Optane SSD !