Sveta Smirnova

MySQL performance can be improved by tuning queries, server options, and hardware. Traditionally it was an area of responsibility of three different roles: Development, DBA, and System Administrators. Now DevOps handle these all. But there is a gap. Knowledge, gained by MySQL DBAs after years or focus on a single product is hard to gain when you focus on more than one. This is why Sveta Smirnova (Percona, Support Team) is doing this session. She will show minimal, but the most effective, set of options that will improve your MySQL performance. For illustrations, she will use real user stories from her Support experience.

It is common knowledge that built-in asynchronous master-master (active-active) replication is not safe. I remember times when the official MySQL User Reference Manual stated that such an installation is not recommended for production use. Some experts repeat this claim even now.

While this statement is generally true, I worked with thousands of shops that successfully avoided asynchronous replication limitations in active-active setups.

In this talk, I will show how they did it, demonstrate situations when asynchronous master-master replication is the best possible high availability option and beats such solutions as Galera or InnoDB Clusters. I will also cover common mistakes, leading to disasters.

Born in 2010 in MySQL 5.5.3 as "a feature for monitoring server execution at a low level," grown in 5.6 times with performance fixes and DBA-faced features, in MySQL 5.7 Performance Schema is a mature tool, used by humans and more and more monitoring products. It becomes more popular over the years. In this talk I will give an overview of Performance Schema, focusing on its tuning, performance, and usability.

Performance Schema helps to troubleshoot query performance, complicated locking issues, memory leaks, resource usage, problematic behavior, caused by inappropriate settings and much more. It comes with hundreds of options which allow precisely tune what to instrument. More than 100 consumers store collected data.

Performance Schema is a potent tool. And very complicated at the same time. It does not affect performance in most cases and can slow down server dramatically if configured without care. It collects a lot of data, and sometimes this data is hard to read.

This talk will start from the introduction of how Performance Schema designed, and you will understand why it slowdowns server in some cases and does not affect your queries in others. Then we will discuss which information you can retrieve from Performance Schema and how to do it effectively.

I will cover its companion sys schema and graphical monitoring tools.

MySQL Test Framework (MTR) provides unit test suite for MySQL. Tests in the framework are written by MySQL Server developers and contributors and run to ensure build is working correctly.

I found this is not the only thing which can be done with MTR. I regularly use it in my Support job to help customers and verify bug reports.

With MySQL Test Framework I can:

• Create complicated environment in single step and re-use it later
• Test same scenario on dozens of MySQL/Percona/MariaDB server versions with single command
• Test concurrent scenarios
• Test errors and return codes
• Work with results, external commands, stored routines

Everything with single script which can be reused on any machine any time with any MySQL/Percona/MariaDB Server version.

In this session I will show my way of working with MySQL Test Framework and I hope you will love it as I do!

Apache Spark is a cluster computing framework, similar to Apache Hadoop. There are a number of tasks where MySQL does not show great performance: for example MySQL is not massively parallel system and a single query will only utilize 1 CPU core . Spark, on the the other hand is designed to be massively parallel; in addition Spark is a clustering framework, so you can easily add more compute nodes so that Spark can utilize more resources and scale.

Apache Drill is similar project aimed to make data discovery easier. For example it allow you to join data sources in MySQL, MongoDB, flat files, other RDBMS, etc.

In this talk I will demonstrate how to use Apache Spark together with MySQL for data analysis. I will sho how Apache Spark aggregates data (wikipedia pageview statistics) and stores the resultset in MySQL. I will also show how to use Apache Spark with multiple sources and join virtual tables from MySQL, flat files and even MongoDB.

Do you miss SQL commands which MySQL does not support? Do you want it to have missed SQL standard features or intruduce new fancy command? Maybe contribute fix to https://bugs.mysql.com/bug.php?id=2 "Does not make Toast"? Or just replace badly written query with more efficient? With pre-parse Query Rewrite Plugings you can do most of these.

In this session I will demonstrate how to create, install and use Query Rewrite plugins. We will introduce completely new SQL command and let MySQL execute it.

At the end of September 2014, just before Oracle Open World conference, one of MySQL Labs releases presented a new killer feature for MySQL 5.7: Query Rewrite Plugins for optimizer! This is a long waited way for MySQL to both preprocess/modify query on the fly without any middle-tire software like MySQL Proxy (that can easily become a bottleneck or single point of failure) and force proper execution plan, without any change in application code. We can get functionality similar to stored outlines of Oracle RDBMS and more, as MySQL provides APIs for both literal query rewrite before parsing and for accessing/modifying query tree after parsing. During this session I will identify most important real life use cases for query rewrites, explore the features of Rewriter plugin originally created by Oracle engineers for their Lab release and provide some details about APIs and writing your own custom plugins.

Now Query Rewrite Plugins are part of current MySQL GA version 5.7. This means this is the time to actively explore them and use.

Performance Schema in version 5.6, released in February, 2013, is really powerful tool which can help DBA to find why every trickiest issue started to occur. Version 5.7 introduces even more instruments and tables. And while all these give you great power, you can stuck choosing which instrument to use. In this session I will start from typical problems description, then guide you how to use Performance Schema to find out what causes the issue, what is the reason for wrong behavior and how information received can help you to solve particular problem.

With 552 instruments in version 5.6 and 797 in version 5.7 as well as with 52 vs 75 tables Performance Schema is really powerful tool which can help to find source of almost every problem your MySQL Server can run into. But at the same time it becomes not easy to find out which instrumentation and which table can help in each and every case. Traditionally Performance Schema sessions teach about what is in the tables. I will, in contrast, start from the issue, then show which instruments and tables can help to solve it.

Data is everywhere. User comments, preferences, online games features, all that small chunks of information which clients need to communicate with its servers use JSON format now. This format can be read into an object using parser built-in in all modern clients. But what about server side? What about information which should be stored for future use? Do you still need to manually parse JSON data to be able to effectively search through? Do you still need to spend time retrieving documents, load parse library and process? Not anymore! In this session I will present set of functions which manipulate with data in JSON format inside MySQL server. These functions search, modify and validate JSON data for you.

JSON UDFs is a set of UDF functions which perform queries using JSON language. This MySQL Labs project available at http://labs.mysql.com/ Further information about JSON UDF functions exists on author's blog at https://blogs.oracle.com/svetasmirnova/tags/json

I presented first public version of functions last year. Since than 3 minor and 1 major releases of the functions were provided. I hardly worked on feature requests, sent by the users, implemented 4 new functions, accepted 1 contribution. In this session I will tell about the functions: what do they do, how to install them, will say few words about future plans.

Query runs slow, all threads are stacking for ages during peak time, application suddenly started run slower than before, ... This is long and not complete list of performance issues you can experiment. Are you curious why your MySQL installation does not work so fast as you wish to? I will discuss methods which help to find why this happens and give you direction how to solve one or another problem. I will prefer applications which come together with MySQL distribution as higher available, but don't stack with them and also discuss those great third-party tools which have not analogues. You will learn how to choose a method best suited for particular problem and effectively use it.