Advanced MySQL Optimization Tutorial

Peter Zaitsev

Description

1M ySQ L Users Conference 2006 , April 24-27 | M ySQ L P erformance 4.1 vs 5.0 | © M ySQ L AB 2006 | w ww.mysql.comMySQL Performance 4.1 vs 5.0P eter Zaitsev, M ySQ L ABM ySQ L Users Conference 2006Santa Clara, CA April 24-27© MySQL AB 20062M ySQ L Users Conference 2006 , April 24-27 | M ySQ L P erformance 4.1 vs 5.0 | © M ySQ L AB 2006 | w ww.mysql.comIntroduction• Software life cycle performance evolution– What do you think ?• M ySQ L 5.0 vs 4.1 P erformance– Using Well optimized M ySQ L 4.1 features– Implicit usage of M ySQ L 5.0 new featrues– Explicit usage of M ySQ L 5.0 new features3M ySQ L Users Conference 2006 , April 24-27 | M ySQ L P erformance 4.1 vs 5.0 | © M ySQ L AB 2006 | w ww.mysql.comWhat is new version ?• Bugs are fixed– Often meaning more checks are being added, optimizations restricted• New features are added – Requiring more hooks – Increasing code base, data structure sizes, indirection– Algorithms changes required• Local code optimizations– Same algorithms implemented better• Global optimizations– New algorithms and data structures are implemented. M ore efficient4M ySQ L Users Conference 2006 , April 24-27 | M ySQ L P erformance 4.1 vs 5.0 | © M ySQ L AB 2006 | w ww.mysql.comWhat is about MySQL 5.0 ?• New Fe atures:– Stored P rocedures, Triggers, Views • Significantly increase parser complexity• M ore complex data structires, increased level of redirection• M any checks to handle these featues in other ...

Informations

• Publié par: Maphag
• Nombre de lectures: 19
• Langue: English

Extrait

MySQL Users Conference 2006 , April 24-27 | MySQL Performance 4.1 vs 5.0 |   © MySQL AB 2006| www.mysql.com

MySQL Performance 4.1 vs 5.0

Peter Zaitsev, MySQL AB

MySQL Users Conference 2006 Santa Clara,CA April 24-27

© MySQL AB 2006

1

• •

MySQL Users Conference 2006 , April 24-27 | MySQL Performance 4.1 vs 5.0 |   © MySQL AB 2006| www.mysql.com

Introduction

Software life cycle performance evolution – What do you think ? MySQL 5.0 vs 4.1 Performance – Using Well optimized MySQL 4.1 features – Implicit usage of MySQL 5.0 new featrues – Explicit usage of MySQL 5.0 new features

2

• •

• •

MySQL Users Conference 2006 , April 24-27 | MySQL Performance 4.1 vs 5.0 |   © MySQL AB 2006| www.mysql 3 .com

What is new version ?

Bugs are fixed – Often meaning more checks are being added, optimizations restricted New features are added – Requiring more hooks – Increasing code base, data structure sizes, indirection – Algorithms changes required Local code optimizations – Same algorithms implemented better Global optimizations – New algorithms and data structures are implemented. More efficient

•

• • •

MySQL Users Conference 2006 , April 24-27 | MySQL Performance 4.1 vs 5.0 |   © MySQL AB 2006| www.mysql.com 4

What is about MySQL 5.0 ?

New Features: – Stored Procedures, Triggers, Views •  parser complexitySignificantly increase • More complex data structires, increased level of redirection • Many checks to handle these featues in other code Local Code optimizations – Some. But it is hard to beat Monty Global optimizations – Greedy Join Optimizer, new execution method Performance bugs fixed – tiOpzemir – Mutexes

•

• • • •

MySQL Users Conference 2006 , April 24-27 | MySQL Performance 4.1 vs 5.0 |   © MySQL AB 2006| www.mysql.com 5

Beware of Optimizer

What we call Optimizer ? – The piece of code which decides how query should be executed • Creates query «plan» • In MySQL practically merged with «executioner» Optimizer is extremely algorithmically complex – Developed by elite crew of PhDs Have to often make decision having limited information Best plan selected by optimizer might not be the fastest Optimizer changes from version to version always break some queries – But many other queries are executed much faster

MySQL Users Conference 2006 , April 24-27 | MySQL Performance 4.1 vs 5.0 |   © MySQL AB 2006| www.mysql.com

Well Optimized MySQL 4.1

6

• •

MySQL Users Conference 2006 , April 24-27 | MySQL Performance 4.1 vs 5.0 |   © MySQL AB 2006| www.mysql.com 7

MySQL 5.0 with 4.1 application

If your application uses only features which werewell optimizedin MySQL 4.1 you might see performance degradation Does this mean I should stay with 4.1 ? – 5.0 has a lot of neat features for administration and management • Information schema, per thread status variables etc – You might have a chance to improve application by using 5.0 specific features – MySQL 4.1 will be getting only critical bug fixes • So you might not get fixes for bugs you find

• • •

MySQL Users Conference 2006 , April 24-27 | MySQL Performance 4.1 vs 5.0 |   © MySQL AB 2006| www.mysql.com 8

Benchmark Results: DBT2

24611

DBT2 – TPC-C Like benchmark developed by OSDL 4CPU Opteron, CPU Bound workload Results – Transactions per minute MySQL 5.0 vs 4.1 in DBT2 30000 28806 27500 26393 26412 25000 23905 22500 20000 17500 15000 12500 10000 7500 5000 2500 0

10872 9801

1

4

20

100

22051

4.1 5.0

• •

MySQL Users Conference 2006 , April 24-27 | MySQL Performance 4.1 vs 5.0 |   © MySQL AB 2006| www.mysql.com 9

Benchmarks Results SysBench

9324

Sysbench – simple benchmark for MySQL – Available sysbench.sourceforge.net 4 CPU Xeon 2.0Ghz, RH AS 3.0, Single Thread MySQL 5.0 vs 4.1 in SysBench 11000 10475 10000 9000 8000 7000 6000 5000 4000 3000 2000 1000 0

Simple

2485 2206

Range

3731 3361

Sum

1371 1338

Sort

4.1 5.0

• • • •

MySQL Users Conference 2006 , April 24-27 | MySQL Performance 4.1 vs 5.0 |   © MySQL AB 2006| www.mysql.com 10

Benchmark Results: TM1

TM1 – TeleCom One benchmark – Typical Home Location Register (HLR) scenario – Designed by Solid Database Developers Single CPU P4, Linux MySQL 5.0 vs 4.1 TM1 300 286 Database fits in memory 275 258 S M 0 2 5 2 2 MyI A 20 5 0 175 150 125 100 75 50 25 0

Result

4.1 5.0

• • • •

MySQL Users Conference 2006 , April 24-27 | MySQL Performance 4.1 vs 5.0 |   © MySQL AB 2006| www.mysql.com 11

Benchmark Results DBT-3

TPC-H Like benchmark developed by OSDL – Uses same schema and queries 0.1 scale size, sized to fit in memory Results are inseconds. Smaller better Difference is normally small MySQL 5.0 vs 4.1 TM1 900 857 – One query broken in 5.0 800 792 700 600 500 400 300 200 118 100 64 0 Power

throughtput

168 170

Load

4.1 5.0