Why I am So Excited About the MySQL Performance Schema

The improved Performance Schema in MySQL 5.6 provides a new way of investigating issues in the database. Many issues that previously required tools such as strace, dtrace, etc. can now be investigated directly from inside MySQL in a platform independent way using standard SQL statements.

The Performance Schema is enabled by default starting from the latest milestone release, 5.6.6. You have instruments which are the things you can measure, and consumers which are those that use the measurements. Not all instruments and consumers are enabled out of the box, however once the plugin is enabled, individual instruments and consumers can be switched on and off dynamically.

As an example take the case mentioned in What's the innodb main thread really doing? where the main InnoDB thread appears to be stuck in “doing background drop tables” even though no tables are being dropped. Now the underlying issue has been resolved in 5.6, but other issues could show up. So how would the Performance Schema help in cases like that?

First you need to ensure that the necessary instrumentations and consumers are enabled:

mysql> use performance_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> UPDATE setup_consumers SET ENABLED = 'Yes' WHERE Name = 'events_waits_current';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> UPDATE setup_instruments SET ENABLED = 'Yes', TIMED = 'Yes' WHERE Name LIKE '%mutex%';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 145  Changed: 0  Warnings: 0


The above settings are just the specifics to the investigation below. You will also need to ensure all the involved threads are instrumented, and most likely you also want to enable other instruments and/or consumers to be able to get more details, for example if an SQL query is involved, you can get more information about what the query is doing.

The mutex contention can now easily be investigated using the two tables events_waits_current and mutex_instances. The events_waits_current table will have a row for each combination of thread and event. Threads includes both background threads and foreground threads (those clients are using), so for example an interaction between the purge thread(s) in InnoDB and a query submitted by the application can be checked.

A very simple query to get the investigation started can look like:

SELECT w1.THREAD_ID AS Victim_Thread_ID, w1.EVENT_ID AS Victim_Event_ID,
       w1.EVENT_NAME AS Victim_Event_Name, w1.SOURCE AS Victim_Source,
       t1.NAME AS Victim_Thread_Name, t1.PROCESSLIST_ID AS Victim_Process_ID,
       w2.THREAD_ID AS Culprit_Thread_ID, w2.EVENT_ID AS Culprit_Event_ID,
       w2.EVENT_NAME AS Culprit_Event_Name, w2.SOURCE AS Culprit_Source,
       t2.NAME AS Culprit_Thread_Name, t2.PROCESSLIST_ID AS Culprit_Process_ID,
  FROM performance_schema.events_waits_current w1
       INNER JOIN performance_schema.mutex_instances mi ON mi.OBJECT_INSTANCE_BEGIN = w1.OBJECT_INSTANCE_BEGIN
       INNER JOIN performance_schema.events_waits_current w2 ON w2.THREAD_ID = mi.LOCKED_BY_THREAD_ID
       INNER JOIN performance_schema.threads t1 ON t1.THREAD_ID = w1.THREAD_ID
       INNER JOIN performance_schema.threads t2 ON t2.THREAD_ID = w2.THREAD_ID;

Once you have the basic information about the threads involved, you can look for more information. One example is to use the events_statements_current, events_statements_history, and events_statements_history_long tables to get more knowledge of any foreground threads involved.

If you attend MySQL Connect 2012, you will be able to learn more about the MySQL Performance Schema for example by attending the hands-on lab “Improving Performance with the MySQL Performance Schema (HOL10472)” where you will be able to try out the Performance Schema yourself.

If you cannot make it to MySQL Connect or cannot wait to try the Performance Schema yourself, you can download MySQL Server 5.6 from https://dev.mysql.com/downloads/mysql/5.6.html. I can also recommend downloading Mark Leith's ps_helper views and stored procedures.

I have worked with MySQL databases since 2006 both as an SQL developer, a database administrator, and for more than eight years as part of the Oracle MySQL Support team. I have spoken at MySQL Connect and Oracle OpenWorld on several occasions. I have contributed to the sys schema and four Oracle Certified Professional (OCP) exams for MySQL 5.6 to 8.0. I have written four books, all published at Apress.

2 Comments on “Why I am So Excited About the MySQL Performance Schema

  1. I am thrilled that MySQL will soon have much more instrumentation. A lot of people at Oracle/Sun/MySQL worked hard on this. But, that query above isn’t “very simple”, nor is it even “simple’. I wouldn’t want to memorize it nor would I want to type it.

    So please tell us how we can avoid memorizing or typing that query.

    • Hi Mark,

      I absolutely agree that in an emergency you do not want to type a query like that by hand. That’s why I also point to something like ps_helper (doesn’t currently have something like the above query).

      My hands-on lab at MySQL Connect will go into more details that will help understand the relationship between the P_S tables, so take this as teaser.

Leave a Reply

Your email address will not be published.


This site uses Akismet to reduce spam. Learn how your comment data is processed.