La lecture en ligne est gratuite
Le téléchargement nécessite un accès à la bibliothèque YouScribe
Tout savoir sur nos offres
Télécharger Lire

Advanced MySQL Optimization Tutorial

De
29 pages
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 ...
Voir plus Voir moins

Vous aimerez aussi

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.mysql3 .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.com4
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.com5
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.com7
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.com8
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 3000028806 2750026393 26412 2500023905 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.com9
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 1100010475 10000 9000 8000 7000 6000 5000 4000 3000 2000 1000 0
Simple
24852206
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.com10
Benchmark Results: TM1
TM1 – TeleCom One benchmark Typical Home Location Register (HLR) scenario Designed by Solid Database Developers Single CPU P4, LinuxMySQL 5.0 vs 4.1 TM1 300286 Database fits in memory275 258 S M02522 MyI A2050 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.com11
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 smallMySQL 5.0 vs 4.1 TM1 900857 One query broken in 5.0 800792 700 600 500 400 300 200 118 10064 0 Power
throughtput
168 170
Load
4.1 5.0