MySQL 8.0 Performance: Scalability & Benchmarks

1 .MySQL 8.0 Performance: 
 Insert Picture Here Scalability & Benchmarks Dimitri KRAVTCHUK MySQL Performance Architect @Oracle Copyright © 2012, Oracle and/or its affiliates. All rights reserved. Insert Information Protection Policy Classification from Slide 12 

2 .The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle. 

3 .Are you Dimitri?.. ;-) • Yes, it's me :-) • Hello from Paris! ;-) • Passionated by Systems and Databases Performance • Previous 15 years @Sun Benchmark Center • Started working on MySQL Performance since v3.23 • But during all that time just for “fun” only ;-) • Since 2011 “officially” @MySQL Performance full time now • http://dimitrik.free.fr/blog / @dimitrik_fr 

4 .Agenda • Overview of MySQL Performance • Where we’re with MySQL 8.0 & Benchmark results.. • RO / Skylake / UTF8.. • RW / new REDO / Resource Groups • IO-bound / Optane • Sysbench / TPCC-like / dbSTRESS • Pending issues.. • Q&A 

5 .Why MySQL Performance ?... 

6 .Why MySQL Performance ?.. • Any solution may look “good enough”... 

7 .Why MySQL Performance ?.. • Until it did not reach its limit.. 

8 .Why MySQL Performance ?.. • And even improved solution may not resist to increasing load.. 

9 .Why MySQL Performance ?.. • And reach a similar limit.. 

10 .Why MySQL Performance ?.. • Analyzing your workload performance and testing your limits may help you to understand ahead the resistance of your solution to incoming potential problems ;-) 

11 .Why MySQL Performance ?.. • However : • Even a very powerful solution but 
 leaved in wrong hands may still be 
 easily broken!... :-) 

12 .Why MySQL Performance ? Is Performance your priority #1 ? Dimitri DB Guru 

13 .Why MySQL Performance ? Is Performance your priority #1 ? NO ! — the priority #1 is deployment flexibility ! Dimitri DB Guru 

14 .Why MySQL Performance ? Is Performance your priority #1 ? NO ! — the priority #1 is deployment flexibility ! Even with bad response time ?.. Dimitri DB Guru 

15 .Why MySQL Performance ? Is Performance your priority #1 ? NO ! — the priority #1 is deployment flexibility ! Even with bad response time ?.. Bad response time ? — no one will ever want to deploy such a shit.. Dimitri DB Guru 

16 .Why MySQL Performance ? Is Performance your priority #1 ? NO ! — the priority #1 is deployment flexibility ! Even with bad response time ?.. Bad response time ? — no one will ever want to deploy such a shit.. So ?… 🤔🤔🤔 Dimitri DB Guru 

17 .The MySQL Performance Best Practice #1 is... ???.. 

18 .The MySQL Performance Best Practice #1 is... ???.. USE YOUR BRAIN !!! ;-) 

19 .The MySQL Performance Best Practice #1 is... ???.. USE YOUR BRAIN !!! ;-) THE MAIN 
 SLIDE! ;-)) 

20 .Common Sources of MySQL Performance Problems.. • “Fixable” ones ;-) • DB Schema/ Indexes/ SQL query/ Optimizer plan/ Apps code/ etc. etc.. • odd tuning/ wrong config setup/ • e.g. generally can be fixed by => RTFM ! ;-)
 My main topic ;-) • “By design” ones.. • known ?.. • workaround ?.. • can be ever fixed ?.. • heh… • work in progress.. <= and here is where we come ;-)) 

21 .Why Benchmarks ? • Common perception of benchmarks is often odd.. • “not matching real world”… • “pure Marketing”.. • “pure BenchMarketing”.. • etc. etc. etc.. • well.. • “it depends..” © • get your own opinion by understanding
 of the tested workloads ! • e.g. remind Best Practice #1 ;-)) 

22 .Benchmarks & MySQL • Every test workload is pointing to a problem to resolve ! • evaluate & understand the problem(s) • then try to fix it • or propose a workaround • evaluate & confirm the fix / workaround • keep running in QA to discover any 
 potential regression ON TIME !.. • As well : • kind of “reference” of what to expect • evaluate any new HW, systems, etc.. 

23 .Example: iiBench (INSERT Benchmark) • Main claim : • InnoDB is xN times slower vs Write-oriented Engine XXX • so, use XXX, as it’s better
 • Test Scenario : • x16 parallel iiBench processes running together during 1H • each process is using its own table • one test with SELECTs, another without..
 • Key point : • during INSERT activity, B-Tree index in InnoDB growing quickly • as soon as index pages have no more place in BP and re-read from storage, performance is going down.. • e.g. “by design” problem ;-)) 

24 .iiBench 100M x16 : BP= 128G/ 64G/ 32G/ 16G • Observations : • until B-Tree remains in BP => 300K INSERT/sec.. (and if not, QPS drop) BP=128G | BP=64G | BP=32G | BP=16G | free buffers !!! 

25 .iiBench 100M x16 & 1M-parts : BP= 128G/ 64G/ 32G/ 16G • Observations : • workaround : keep index cached in BP (via table partitions or other) BP=128G | BP=64G | BP=32G | BP=16G | 

26 .Test Workload • Before to jump into something complex... • Be sure first you're comfortable with 
 “basic” operations! • Single table? Many tables? • Short queries? Long queries? • Remember: any complex load in fact is just 
 a mix of simple operations.. • So, try to split problems.. • Start from as simple as possible.. • And then increase complexity progressively.. • NB : any test case is important !!! • Consider the case rather reject it with “I’m sure you’re doing something wrong..” ;-)) • And even if you were doing something wrong, try to understand its impact.. • (Best Practice #1 once again ;-)) 

27 .The Best Test Workload • For You : • workload simulating your Production ! • JMetter (free) • LoadRunner ($$) • etc. • otherwise : • use “generic” test workloads • change / adapt / extend.. • share with us ! ;-)) 

28 .“Generic” Test Workloads @MySQL • Sysbench - #1 • The “Entry Ticket” Workloads - looks simple, but still the most complete test kit ! • OLTP, RO/RW, points on various RO and RW issues • DBT2 / TPCC-like • OLTP, RW, pretty complex, growing db, no options, deadlocks • DBT3 • DWH, RO, complex heavy queries, loved by Optimizer Team ;-) • dbSTRESS • OLTP, RO/RW, several tables, points on RW and Sec.IDX issues • iiBench • pure INSERT bombarding + optionally SELECTs, points on B-Tree issues • LinkBench (Facebook) • OLTP, RW, looks intensive and IO-hungry 

29 .Why Sysbench is #1 ?.. • Historically : • the most simple to install, to use, most lightweight • why entry ticket : covers most important “key workload cases” in MySQL performance
 • New Sysbench : • https://github.com/akopytov/sysbench • have fixed all past issues • high flexibility for any test scenario with LUA scripts • integrated LUA JIT => high execution speed + lightweight ! • more various test scenarios are expected to come • excellent opportunity to write your own test cases ! • move and use it now ! ;-)