MySQL 8.0 Performance: Scalability & Benchmarks

对于每个寻找有关MySQL8.0性能最新消息的人来说,本课程都很有趣:
-MySQL8.0现在已经上市了!
-但MySQL8.0的性能呢?;-)
-使用mysql 8.0获得的最新基准测试结果将成为讨论的中心。
-因为MySQL的每个基准工作负载都是“要解决的问题”
-每一个解决的问题都是你的产品的潜在收益!
-MySQL8.0带来了许多重要的内部设计更改
-如何使他们最有效地行动起来?
-期待什么样的权衡,什么已经是好的,什么是“还没有”?
-MySQL8.0如何使用最新的硬件?
-您真的可以通过在最新的闪存上部署数据来加速IO吗?
-在本次谈话中,这些问题和许多其他问题都得到了解答,并通过基准测试结果加以证明。
注意:由于MySQL8.0采用了“持续发布”模型,所以每次更新都会有一些新的改进,包括性能;-)

展开查看详情

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