The performance of a DBMS system depends on its two main components - optimizer and storage engine (typically InnoDB). The primary focus of MySQL community has been the storage engine, however, recent versions of both MySQL and MariaDB have (partially overlapping) improved optimizer features, better optimizer control and diagnostics. The goal of this tutorial is to empower the MariaDB/MySQL user with the knowledge how to utilize and control both new and old optimizer features to achieve performance improvements from several times up to several orders of magnitude. A. We first explain how to tell if a performance problem is caused by the query optimizer. Every SQL query optimizer can produce a poor query plan, and the MariaDB/MySQL optimizers are no exception. We'll demonstrate what tools and techniques can be used to tell if an application runs inefficient query plans, as well as discuss some common false alarms. B. In order to analyze query plans, one needs to understand how queries are executed.
C. Once you've found a query with a slow query plan, what to do about it? Some queries are just impossible to improve. Some may be using SQL constructs which the optimizer cannot handle efficiently. Finally, some queries can be run faster if the optimizer gets some help from the user. You'll be equipped with knowledge to make the distinction between these cases. In case the optimizer needs help, we'll show how to provide it:
D. Finally, we discuss global tuning. What you should know about global optimizer settings such as optimizer_switch, and various memory buffers such as sort_buffer_size and join_buffer_size. We will show what can be achieved by tuning these variables and how to do it.
10 April 09:30 - 12:30 @
Query Optimizer Developer, Monty Program AB
Timour has worked on the MySQL/MariaDB optimizer since 2004. Some
Optimizer Developer, Monty Program Ab
Sergei Petrunia is a query optimizer developer at Monty Program Ab. He implemented MariaDB's optimizer features like Table Elimination (MariaDB 5.1), improvements to semi-join subquery optimizations (MariaDB 5.3/5.5) and SHOW EXPLAIN (MariaDB 10.0).
Prior to Monty Program, he has worked for MySQL Ab/Sun Microsystems, where he was the author of query optimizer features like Partition Pruning, Index Merge (MySQL 5.1), and numerous smaller improvements in query optimizer and other parts of the server.
He maintains a highly technical blog at http://s.petrunia.net/blog