Advanced MySQL Optimization Tutorial

Peter Zaitsev

Informations

• Publié par: Neveng
• Nombre de lectures: 39
• Langue: English

Extrait

1
MySQ L U sers C onference 2005 , A pril 18-21 | A dvanced MySQ L Optimization Tutorial | © MySQ L A B 2005 | w ww.mysql.com
Advanced MySQL
Performance Optimization
Peter Zaitsev, Tobias A splund
MySQ L A B
MySQ L U sers C onference 2005
Santa C lara, C A A pril 18-21
© MySQL AB 20052
MySQ L U sers C onference 2005 , A pril 18-21 | A dvanced MySQ L Optimization Tutorial | © MySQ L A B 2005 | w ww.mysql.com
Introductions
• Peter Zaitsev, MySQ L Inc
– Senior Performance E ngineer
– MySQ L Performance G roup Manager
– MySQ L Performance consulting and partner relationships
• To bias A splund
– MySQ L Training C lass Instructor
– MySQ L Performance Tuning C onsultant3
MySQ L U sers C onference 2005 , A pril 18-21 | A dvanced MySQ L Optimization Tutorial | © MySQ L A B 2005 | w ww.mysql.com
Table of Contents
• A bit of Performance/Benchmarking theory
• A pplication A rchitecture issues
• Schema design and query optimization
• Sever Settings O ptimizations
• Storage E ngine O ptimizations
• Replication
• C lustering
• Hardware and O S optimizations
• Real world application problems 4
MySQ L U sers C onference 2005 , A pril 18-21 | A dvanced MySQ L Optimization Tutorial | © MySQ L A B 2005 | w ww.mysql.com
Question Policy
• Interrupt us if something is unclear
• Keep long generic questions to the end
• A pproach us during the conference
• Write us: peter@ mysql.com, tobias@m ysql.com5
MySQ L U sers C onference 2005 , A pril 18-21 | A dvanced MySQ L Optimization Tutorial | © MySQ L A B 2005 | w ww.mysql.com
Audience Quick Poll
• Who are you ?
– D evelopers
– D BA s
– System A dministrators
– Managers
• How long have you been using MySQ L ?
• D id you ever have performance issues with MySQ L ?
• What is your previous database background ? 6
MySQ L U sers C onference 2005 , A pril 18-21 | A dvanced MySQ L Optimization Tutorial | © MySQ L A B 2005 | w ww.mysql.com
Defining Performance
• Simple world but many meanings
• Main objective:
– U sers (direct or indirect) should be satisfied
• Most typical performance metrics
– Throughput
– latency/ response time
– Scalability
– C ombined metrics 7
MySQ L U sers C onference 2005 , A pril 18-21 | A dvanced MySQ L Optimization Tutorial | © MySQ L A B 2005 | w ww.mysql.com
Throughput
• Metric: Transactions per time (second/min/hour)
– O nly some transactions from the mix can be counted
• E xample: TPC -C
• When to use
– Interactive multi user applications
• Problems:
– “starvation” - some users can be waiting too long
– single user may rather need his request served fast8
MySQ L U sers C onference 2005 , A pril 18-21 | A dvanced MySQ L Optimization Tutorial | © MySQ L A B 2005 | w ww.mysql.com
Response Time/Latency
• Metric: Time (milliseconds, seconds, minutes)
– derived: average/min/max response time
– derived 9 0 percentile response time
• E xample: sql-bench, SetQ uery
• When to use
– Batch jobs
– together with throughput in interactive applications
• Problems:
– C ounts wall clock time, does not take into account what else
is happening 9
MySQ L U sers C onference 2005 , A pril 18-21 | A dvanced MySQ L Optimization Tutorial | © MySQ L A B 2005 | w ww.mysql.com
Scalability
• Metric: A bility to maintain performance with changing
– load (incoming requests)
– database size
– concurrent connections
– hardware
• D ifferent performance metric
• “maintain performance” typically defined as response time
• When to use
– C apacity planning10
MySQ L U sers C onference 2005 , A pril 18-21 | A dvanced MySQ L Optimization Tutorial | © MySQ L A B 2005 | w ww.mysql.com
Queuing Theory
• Multi U ser applications
• Request waits in queue before being processed
• U ser response time = queueing delay + service time
– N on high tech example – support call center.
• “Hockey Stick” - queuing delay grows rapidly when system
getting close to saturation
• N eed to improve queueing delay or service time to
improve performance
• Improving service time reduces queuing delay