MySQL and InnoDB Performance Baron Schwartz | OSDC 2010
MySQL Architecture MySQL Server Parser, Optimizer Executor, Caches Storage Engines
InnoDB Storage Engine • Transactional, ACID compliant • Reliable, Stable • Complex, Flexible • A History of Poor Scalability
Thinking About Performance
Response Time Is Everything
Response Time
Response Time
R=W+S
W S Utilization
Response Time
Response Time
R=W+S W
S Utilization
MySQL & InnoDB In Context
MySQL & InnoDB In Context Operating Environment
MySQL Server
InnoDB
Operating Environment RAM
I/O
CPU
Network
Operating Environment RAM • • • • •
RAID + BBU Fast SAS Disks SSD / FusionIO XFS Filesystem Deadline Scheduler
I/O
• Up to 100's of GB
CPU
Network
• Fast • Up to 24 Cores
• High Bandwidth • Low Latency
MySQL Performance
Nested-Loop Joins and Subqueries
MySQL Performance
Cache Scalability
InnoDB's Single “Main Thread”
• Too Much Work! Purging, Flushing • Main Thread Cannot Keep Up
Special-Purpose Threads
• Main Thread • Purge Thread • Multiple Read/Write IO Threads
InnoDB Mutex Contention
Buffer Pool
Splitting the Buffer Pool Mutex
Buffer Pool Flush List
LRU
Hash
Free
Current InnoDB Bottlenecks • Adaptive Hash Search Mutex • Index Lock Mutex • After These Are Solved, ???
How To Improve Performance
Measure Carefully
Proper diagnosis is 95% of the work
Use Good Tools • New Relic • MySQL Enterprise Monitor • Cacti – http://code.google.com/p/mysql-cacti-templates/
• Maatkit • GDB, strace, oprofile
Instrumentation is Key • The best performance data is INSIDE MySQL
Percona Server with XtraDB MySQL Server Parser, Optimizer
Tunability
Executor, Caches Storage Engines
Instrumentation
Diagnostics
Current Benchmarks
InnoDB -vs- XtraDB
XtraDB is much better than InnoDB in MySQL 5.1. http://tinyurl.com/xtradb-fusionio-jan-2010
MySQL 5.5 -vs- Percona Server
High performance is no use without stability. http://tinyurl.com/xtradb-mysql5-5-ap2010
Other Technologies • Cloud Computing – 2 of the 4 critical resources are uncontrollable
• “NoSQL” Databases – It's too soon to be sure
Summary • The operating environment is important • MySQL & InnoDB are getting much faster • The cloud is not a performance solution • You are the key to better performance
Where Do You Go Next? • Become a performance practitioner – Read High Performance MySQL 2nd Edition – Read Optimizing Oracle Performance – Read The Art Of Capacity Planning – Read http://www.percona.com/about-us/white-papers/
• Contribute! – Sponsor features you need or want – Help improve tools