mysql 5.4 - theory and practice
DESCRIPTION
An introduction to MySQL 5.4, with some theoretical stuff (from Allan Packer and Mikael Ronström) and some practical demosTRANSCRIPT
![Page 1: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/1.jpg)
MySQL 5.4Theory and practice
Giuseppe Maxia
MySQL Community Team Lead
![Page 2: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/2.jpg)
This release is very special for
two reasons
![Page 3: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/3.jpg)
Tight team work
1MySQL architects, top MySQL coders, Sun
performance engineers, all worked together to
create this release
![Page 4: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/4.jpg)
How to improve performancemethod efficacy difficulty
Schema optimization ***** #####Server tuning ** #### Query tuning ** ####Hardware upgrade *** # Replication ** #####Partitioning ***** #####Server Upgrade (5.4) ***** #
2
![Page 5: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/5.jpg)
5.4 = 5.1 +
performance patches
![Page 6: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/6.jpg)
WHAT THE MAKERS SAY
![Page 7: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/7.jpg)
How Sun & MySQL are Driving MySQL Performance and ScalabilityAllan PackerPrincipal EngineerPerformance TechnologiesSun Microsystems
Mikael RonströmPrincipal EngineerDatabase EngineeringSun Microsystems
All the slides in this sectionare part of the presentation given by Allan and Mikael at the MySQL Conference 2009
![Page 8: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/8.jpg)
Agenda Why did we Need a Performance &
Scalability Project? What's in MySQL 5.4 How Well Does 5.4 Perform? Where to Next?
![Page 9: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/9.jpg)
Why a Perf & Scalability Project? MySQL grew up with single-core, 1- to 2-chip CPUs The world has moved to multi-core, multi-chip CPUs
A single Sun Niagara2 chip has 64 “CPUs”! Where we started from in Feb 08:
Houston, we have a problem!
![Page 10: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/10.jpg)
What Could Be Done? Sun has a performance group with many
years of performance experience with proprietary databases Including Oracle, Sybase, IBM DB2,
Informix Sun closely and actively involved in
helping them achieve achieve their current performance & scalability
All of them started with single CPU systems
The process is iterative and takes time Find a bottleneck and fix it Exposes the next bottleneck
![Page 11: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/11.jpg)
MySQL Perf & Scalability Project A virtual team with Sun and MySQL
engineers Goal is to improve scalability for CMT and
multi-core systems Fully utilize CPU on current and future
two-chip systems Scale out when it makes sense
Key target platforms Linux and Solaris But most improvements should benefit all
platforms
[...]
![Page 12: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/12.jpg)
MySQL Perf & Scalability Project
Key target environments Web 2.0, transaction-based
Key storage engines InnoDB and Falcon
Deliver product improvements & best practice guides
![Page 13: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/13.jpg)
Introducing MySQL 5.4 The first MySQL release that focuses
primarily on performance and scalability Features significant community
contributions Performance & scalability enhancements
from 5.4.0 will be brought back into MySQL 5.1 Further enhancements are already in
process Available now for download Support available for MySQL Enterprise
customers
![Page 14: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/14.jpg)
What's in MySQL 5.4? A major focus on InnoDB performance
enhancements Google SMP and IO patches Out-of-the-box performance improvements
Variable defaults have been increased to suit current hardware
New InnoDB Thread concurrency algorithm Improved spinloop in InnoDB Performance fixes backported from 6.0 Various OS-specific optimizations and fixes Better Observability via Dtrace probes for
MySQL Server Build script to build on Linux, Solaris, MacOS
X
![Page 15: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/15.jpg)
Acknowledgements for MySQL 5.4? Kudos to Heikki and the team at Oracle/
InnoDB For delivering a robust and feature-rich
storage engine Kudos to Mark, Ben and the team at Google
For the significant community contribution of the Google patches
Not a single bug found in the Google patches (which hadn't already been found by Google team) after long and serious test efforts
![Page 16: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/16.jpg)
Google SMP Patch Improved RW-locks in InnoDB plus disabling InnoDB
internal memory manager Using tcmalloc on Linux and mtmalloc on Solaris
Improves Sysbench results by almost 10% Google SMP patch performance improvements
Benefit is greatest with more CPUs/concurrency Some query types see substantial improvements
whereas for others the impact is smaller 5-10% for Sysbench read-only benchmark
Google SMP patch in MySQL 5.4 Works with GCC Works on Solaris (x86 and SPARC) A patch available for it to work with Intel's compiler
(not yet in 5.4.0) Work ongoing to get it to work on Windows (patch
exists but not yet fully tested)
![Page 17: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/17.jpg)
Google IO patches Improve InnoDB IO handling Improved DBT2 results for IO bound loads
By up to 20% Makes it easier to use more disk drives with
InnoDB Still a lot of work to do for InnoDB IO
handling
![Page 18: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/18.jpg)
Updated Default Values Out-of-the-box performance improvements
Default values are now adapted to a basic modern server
Number of tables in table open cache increased from 64 to 400 This update removes LOCK_open
contention for many workloads Other InnoDB parameters
Increased default and minimum sizes of a number of InnoDB parameters, including
Buffer pool size (was 8MB(!), now 1GB) Log sizes Autoincrement size of files
![Page 19: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/19.jpg)
InnoDB Thread Concurrency New InnoDB thread concurrency algorithm
The old algorithm used a mutex which was a scalability bottleneck in itself
All attempts failed to improve the algorithm while still using mutexes and atomic variables
New strategy: Use OS scheduler for queueing, together with atomic variable Pros: Very efficient, no scalability
problems Cons: Fairness of queueing cannot be
maintained Implementation: If too many threads need
access, yield, next attempt sleep 10ms if too many, next attempt sleep 10ms if too many and next time always enter
![Page 20: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/20.jpg)
InnoDB Thread Concurrency More on the new algorithm
Overhead at lower thread counts: about 1% Gain at higher thread counts: 5-15% Some benchmark showed as much as 200%
gain When going to thousands of threads the
algorithm starts to degrade compared to original implementation
The new behavior is the default To go back to old algorithm, set
--innodb-thread-concurrency-timer-based=0
An even better thread concurrency algorithm requires tracking of threads being blocked on IO or locks
![Page 21: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/21.jpg)
Improved spinloop in InnoDB Added PAUSE instruction inside spinloop
On x86 implemented as PAUSE instruction
On SPARC implemented as Compare-and-swap operation (based on research paper on CMT boxes)
Throughput gain of 3-4% on single thread per core machines, expect more on multiple threads per core machines
![Page 22: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/22.jpg)
Better Observability Introducing Dtrace probes in MySQL Server
Available on supported platforms OpenSolaris, MacOS X, FreeBSD
(experimental) Zero cost if probes are not enabled Negligible overhead when probes are
enabled Can monitor MySQL, the OS, applications
Without changing the source Includes a scripting language and
automated aggregation
![Page 23: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/23.jpg)
Better Observability Introducing Dtrace probes in MySQL Server
Backported from 6.0 development tree Many build problems have been
overcome in this solution Possible to add engine-specific probe
files No InnoDB probes at this point in time
(however pid probe and syscall probes will get a lot of insight into InnoDB internals)
For use of probes refer to Vince Carbone's presentation on Dtrace probes for MySQL Server
![Page 24: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/24.jpg)
Dtrace probes in MySQL Server Now available at the start and end of
operations like: Query execution Command execution Query start/stop Query Parser execution SELECT command
execution DELETE command
execution UPDATE command
execution MULTI UPDATE command
execution MULTI DELETE command
execution INSERT .. SELECT
command execution update_row in handler
interface
delete_row in handler interface
write_row in handler interface lock/unlock external locks in
handler interface read row in handler interface
(rnd_next + rnd_pos) index read row in handler
interface (many variants) network read/write Connection start/stop Query cache hit/miss Filesort start/stop MyISAM keycache read start /
read block / hit / miss / read done / write start / write block / write done
![Page 25: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/25.jpg)
Build Script To simplify building the MySQL Server from
source we have added a heavily documented script BUILD/build_mccge.sh
Originally developed to build MySQL Cluster Carrier Grade Edition
Works for Linux, Solaris and Mac OS X, can also be used on FreeBSD although not tested there
![Page 26: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/26.jpg)
How Well Does 5.4 Perform? MySQL 5.4 compared with MySQL 5.1
MySQL 5.4 throughput increases 71% from 32 to 64 vCPUs Based on an
OLTP workload derived from an industry-std benchmark
Scalability 30x from 1 to 64 vCPUs Based on only 8
cores (not 64)
![Page 27: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/27.jpg)
MySQL 5.4 on 64-way CMT Sysbench Throughput
Increases 63% for Read only Tail off beyond 64 threads needs some
work Increases 67% for Read write
![Page 28: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/28.jpg)
MySQL 5.4 on Nehalem on Linux Sysbench Read-only
MySQL 5.4 outperforms MySQL 5.1 except at higher concurrencies Needs to be root caused and resolved
(not observed on Solaris)
![Page 29: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/29.jpg)
MySQL 5.4 on Nehalem on Linux Sysbench Read-write
MySQL 5.4 clearly outperforms and outscales MySQL 5.1, across the board
![Page 30: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/30.jpg)
MySQL 5.4 on Nehalem on Solaris On this test Solaris scales and performs well
![Page 31: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/31.jpg)
MySQL 5.4 versus 5.1 Performance Eastress2004 is a subset of
SPECjAppServer2004 MySQL 5.4 is 59%
faster than MySQL 5.1 for web application with significant database activity
MySQL 5.4 scales better on 16 way x86 server improving server utilization, handling more concurrent users and providing 60% better price/performance
Like SPECjAppServer2004, EAStress2004 models a typical web application and has a significant number of read/update/insert and delete on the database.EAStress2004 results are not reviewed by SPEC.
![Page 32: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/32.jpg)
What's Cooking? Replication Scalability Google v3 patch Performance Schema Improvements of Partitioning scalability LOCK_open/LOCK_mdl fixes Fixing the kernel_mutex and Rollback segment
mutexes for improved scalability of write workloads kernel_mutex covers 30k lines of code in
InnoDB Use of ISM on Solaris for large pages
Locks buffer pool in memory and thus avoids swapping problems
Thread Pool Management
![Page 33: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/33.jpg)
Replication Scalability Improved use of prepare_commit_mutex Decreased impact of LOCK_log Goal is to get server with binlogging
activated to scale on par with server without binlogging
Multithreaded slave Patches already exist
But still in development or test phases Some are prototypes at this point in time
![Page 34: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/34.jpg)
Google v3 Patch Decreased impact of buffer pool mutex
By splitting buffer pool page hash into separate set of mutexes
Improved concurrency of Dirty page writing By splitting buffer pool flush list into
separate mutex Decreased impact of log write mutex in
InnoDB Improved IO handling
Through manipulation of IO write algorithms
![Page 35: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/35.jpg)
Performance Schema Better observability Platform independent Low overhead
![Page 36: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/36.jpg)
Partitioning Scalability Key Cache per partition
Improve scalability of MyISAM partitioned tables Key cache is scalability bottleneck in
MyISAM TRUNCATE partition support Improve handling of open files with
partitioned tables with very many partitions Particularly relevant to MyISAM
![Page 37: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/37.jpg)
LOCK_open / LOCK_mdl LOCK_open and LOCK_mdl are intricate
components of the MySQL Server and its Meta data handling
Very intertwined code Will investigate
How to handle them better How to optimize mutex code itself in the
MySQL Server
![Page 38: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/38.jpg)
Kernel Mutex kernel_mutex protects
Transaction list, Transaction Id, and Transaction view
Locks per transaction Locks on InnoDB pages A few server variables Thread queueing after finding a lock that
needs to be waited on Parts of the transaction state Rollback Segment history length
In short, a lot of fairly unrelated things Need to split in a few areas
Possibly use RW-locks
![Page 39: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/39.jpg)
Thread Pool Management Need an efficient implementation of Thread
Pools that scales in itself Improve current 6.0 implementation of
Thread Pool Bring in ideas from new InnoDB Thread
Concurrency algorithm into server Ensure that important blocking points in the
Server and storage engines are communicated to the Thread Pool Management subsystem
Goal is to make Thread Pools the default in the MySQL Server
![Page 40: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/40.jpg)
In Conclusion MySQL 5.4 boosts performance and
scalability MySQL 5.4.0 is available now for download There's more to come
Watch this space! Thanks for joining us Q&A
![Page 41: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/41.jpg)
Practical experience
![Page 42: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/42.jpg)
sysbench preparetime sysbench \ --test=oltp \ --oltp-table-size=1000000 \ --mysql-db=test \ --mysql-user=msandbox \ --mysql-password=msandbox \ --mysql-host=127.0.0.1 \ --mysql-port=$PORT \ --num-threads=8 prepare
![Page 43: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/43.jpg)
sysbench r/osysbench \ --test=oltp \ --oltp-table-size=1000000 \ --mysql-db=test \ --mysql-user=msandbox \ --mysql-password=msandbox \ --mysql-host=127.0.0.1 \ --mysql-port=$PORT \ --max-time=60 \ --oltp-read-only=on \ --max-requests=0 \ --num-threads=8 run
![Page 44: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/44.jpg)
sysbench r/wsysbench \ --test=oltp \ --oltp-table-size=1000000 \ --mysql-db=test \ --mysql-user=msandbox \ --mysql-password=msandbox \ --mysql-host=127.0.0.1 \ --mysql-port=$PORT \ --max-time=60 \ --oltp-read-only=off \ --max-requests=0 \ --num-threads=8 run
![Page 45: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/45.jpg)
sysbench resultsMySQL 5.0 read-only
![Page 46: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/46.jpg)
sysbench resultsMySQL 5.1 read-only
![Page 47: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/47.jpg)
sysbench resultsMySQL 5.0 R/W
![Page 48: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/48.jpg)
sysbench resultsMySQL 5.1 R/W
![Page 49: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/49.jpg)
sysbench resultsMySQL 5.4 R/O
![Page 50: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/50.jpg)
sysbench resultsMySQL 5.4 R/W
![Page 51: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/51.jpg)
sysbench results graphMySQL 5.0 read-only
![Page 52: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/52.jpg)
sysbench results graphMySQL 5.0 R/W
![Page 53: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/53.jpg)
sysbench results graphMySQL 5.1 R/O
![Page 54: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/54.jpg)
sysbench results graphMySQL 5.1 R/W
![Page 55: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/55.jpg)
sysbench results graphMySQL 5.4 R/O
![Page 56: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/56.jpg)
sysbench results graphMySQL 5.4 R/W
![Page 57: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/57.jpg)
Live example
DEMO
![Page 58: MySQL 5.4 - Theory and Practice](https://reader034.vdocuments.us/reader034/viewer/2022042623/5492439dac7959482e8b458b/html5/thumbnails/58.jpg)
QUESTIONS?
Slides in my bloghttp://datacharmer.blogspot.com
THANKS