High Performance MySQL, 4th Edition

Chapter 3. Performance Schema

Contributed by Sveta Smirnova

Tuning the performance of databases under high load is an iterative cycle. Every time you make a change to tune the performance of the database, you need to understand if the change had any effect. Are your queries running faster than before? Are locks slowing down the application, or are they entirely gone? Did memory usage change? Did the time spent waiting on disk change? Once you understand how to answer these questions, you’ll be able to evaluate and respond to day-to-day situations faster and with more confidence.

Performance Schema is a database that stores the data required to answer these questions. This chapter will help you understand how Performance Schema works, what its limitations are, and how to best go about using it—along with its companion sys schema—to uncover common information about what is going on inside MySQL.

Introduction to Performance Schema

Performance Schema provides low-level metrics on operations running inside MySQL server. To explain how Performance Schema works, there are two concepts I need to introduce early.

The first is an instrument. An instrument refers to any portion of the MySQL code that we want to capture information about. For example, if we want to collect information about metadata locks, we would need to enable the wait/lock/meta​data/sql/mdl instrument.

The second concept is a consumer, which is simply a table that stores the information about what code was instrumented. ...