Query Optimization with MySQL 5.6: Old and New Tricks

11 November 09:00am - 12:00pm @ Orchard 2
Experience level: 
3 hours tutorial
MATERIAL FOR THE TUTORIAL Slides: http://www.slideshare.net/jynus/query-optimization56 VirtualBox 4.3.0 64bit Machine Image -1.7GB- (OVA file, File > Import Appliance): md5sum: 9fdaff735f17bd6ffe42cde5bdcce204 percona_live.ova * Google Drive Mirror: http://tinyurl.com/query56google * Dropbox Mirror: http://tinyurl.com/query56dropbox Separate downloads: * CentOS 6.4: http://mirror.bytemark.co.uk/centos/6.4/isos/x86_64/ * Percona Server 5.6: http://www.percona.com/downloads/ * My Movies Application: https://launchpad.net/my-movies * IMDB database: http://imdbpy.sourceforge.net/ TUTORIAL DESCRIPTION It doesn't matter what new SSD technologies appear, or what are the latest breakthroughs in flushing algorithms: the number one cause for MySQL applications being slow is poor execution plan of SQL queries. While the latest GA version provided a huge amount of transparent optimizations -specially for JOINS and subqueries- it is still the developer's responsibility to take advantage of all new MySQL 5.6 features. In this tutorial we will propose the attendants a sample PHP application with bad response time. Through practical examples, we will suggest step-by-step strategies to improve its performance, including: * Checking MySQL & InnoDB configuration * Internal (performance_schema) and external tools for profiling (pt-query-digest) * New EXPLAIN tools * Simple and multiple column indexing * Covering index technique * Index condition pushdown * Batch key access * Subquery optimization The tutorial is aimed for MySQL application developers and DBAs with basic knowledge of Linux and PHP (but no previous knowledge of 5.6 is assumed). A virtual machine will be provided to the attendants with the preconfigured environment: a LAMP system with the custom-developed application and the preloaded schema. Agenda: -30 min: Introduction and environment setup: Who am I? What are we doing to do? Example application and VM setup. -30 min: Tools for query profiling: New EXPLAIN features, pt-query-digest, performance_schema -10 min: Server configuration: MyISAM vs. InnoDB. InnoDB configuration. -50min: Indexing: Which queries can and cannot benefit from indexes. When and where to index. Column order. ICP. -30 min: Other new query planner features in 5.6: MRR & BKA, lazy subquery execution. -15 min: Results and wrap-up: Final benchmark and performance comparison.


MySQL DBA, Wikimedia Foundation
Jaime works as a database administrator for the non-profit organization Wikimedia Foundation, host of Wikipedia and other similar open knowledge projects. His main topics of interest are MySQL query optimization and architectures for high availability. He is a former MySQL A.B./Sun Microsystems/Oracle/Percona instructor and consultant, and has also worked as an independent MySQL consultant at DBAhire.com