Improving MySQL/MariaDB query performance through optimizer tuning

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.

  • We give an overview of what data access methods are there in MySQL,
  • how these access methods are composed into a query execution plan (QEP), and
  • how all this is shown in EXPLAIN, and what are the first signs of inefficient plans.

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:

  • what tools and techniques can be used to analyze individual query performance,
  • how to find where the optimizer made the wrong choice,
  • how to invent a better query plan,
  • and how to force the optimizer use your plan.

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.

Track: 
Tutorials
Experience level: 
Intermediate

Schedule info

Time slot: 
10 April 09:30 - 12:30
Room: 
Ballroom D

Schedule Info

10 April 09:30 - 12:30 @
Ballroom D

Speakers

Timour Katchaounov's picture
Query Optimizer Developer, Monty Program AB

Timour has worked on the MySQL/MariaDB optimizer since 2004. Some
of the projects he implemented are a greedy query optimizer and
subquery materialization. He also worked on re-engineering
and other optimizer projects. Before joining MySQL, he received a
Ph.D. in database technology from Uppsala University,
Sweden. During his studies he contributed code to the federated
query optimizer of IBM's DB2.

Sergei Petrunia's picture
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

Slides




Sponsored By