Slides and Other Files From My Hands-On Labs at MySQL Connect 2012

First of all a big thank you to all of you who attended my two Hands-On Labs (HOL) session at this year’s MySQL Connect. I ended up doing two sessions as there was a last minute cancellation, so in addition to the previously announced session about the Performance Schema, I also did an introduction to MySQL.

The slides and the workbook for the Performance Schema session will become available from the official Oracle OpenWorld/MySQL Connect catalog, but you can also get the files from my blog which for the Performance Schema session will also include the helper functions and procedures used and some sample queries used to create load on the server.

HOL10471 – Getting Started with MySQL

This session only had slides, however the slides includes the commands and queries executed during the session. Note that the part on using MySQL Workbench (by Alfredo Kojimais) is not included.

The slides are available at: HOL 10471 – Getting Started with MySQL

HOL10472 – Improving Performance with the MySQL Performance Schema

The Performance Schema lab used several files. The main one to use is the workbook which includes details on the queries and commands to run. The hol10472.tgz also includes some support files (for example stored routines) used.

The following files can be downloaded:

Have fun playing with MySQL!

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

Note

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,
       t1.PROCESSLIST_INFO AS Victim_SQL,
       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,
       t2.PROCESSLIST_INFO AS Culprit_SQL
  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.

What is the MySQL Performance Schema and Why is It Needed?

When you have a non-trivial database installation, you will inevitably sooner or later encounter performance related issues ranging from a query not executing as fast as desirable to complete meltdowns where the database does not respond at all.

Until MySQL 5.5 the tools available to investigate what is going on inside MySQL were somewhat limited. Some of the tools were:

  • The slow and general query logs
  • The status counters available through SHOW [SESSION|GLOBAL] STATUS
  • Storage engine status, e.g. SHOW ENGINE INNODB STATUS
  • The EXPLAIN command to investigate the query plan of a SELECT statement
  • SHOW PROFILE to profile one or more queries
  • The MySQL error log

All of these tools are very useful, but also have their limitations, for example the SHOW STATUS mainly consists of counters that does not provide much insight into what is happening specifically.

In MySQL 5.5 a new tool was introduced, the Performance Schema (often abbreviated P_S). The Performance Schema consist of instrumentation points directly in the source code which allow inspection of the internals of the server at runtime. Some of the advantages for the Performance Schema implementation are:

  • The Performance Schema data is available through the PERFORMANCE SCHEMA storage engine in the performance_schema database, so it is possible to query the data using standard SQL statements.
  • The Performance Schema is available irrespective of the platform, so while the exact data collected will differ between platforms, the way it works from a DBAs perspective it is the same. This for example means it is possible to create tools that can work across all the MySQL instances. A great example of this is the ps_helper collection of views and stored procedures written by Mark Leith.
  • It is possible to configure the Performance Schema dynamically as long as the plugin has been enabled (this is the defaults as of MySQL 5.6.6).
  • It is easy to add new instrumentation points including adding instrumentation to third party plugins.
  • Enabling the Performance Schema is transparent to normal operations (although obviously there will be a small performance impact – MySQL 5.6 is much better in this respect than MySQL 5.5 though).

For the full list of implementation details, see the documentation of the Performance Schema in the MySQL Reference Manual.

In MySQL 5.5 the amount of information in the Performance Schema is relatively limited. If you take a look into the performance_schema database, it becomes immediately obvious that much has happened between MySQL 5.5 and 5.6.6: the former has 17 views, the latter 52 views. The information available in MySQL 5.5 is primarily I/O oriented whereas MySQL 5.6 also – among other – includes:

  • Statement information (similar to the slow query log)
  • Information about index usage, e.g. to identify unused indexes
  • The possibility to create a stack trace for a given thread
  • etc.

The Performance Schema is the subject of both a talk and a hands-on lab at the upcoming MySQL Connect 2012 which takes place on September 29th and 30th in San Francisco. These two sessions will go into greater depth about the above features. You can also read more about the sessions at:

Attending MySQL Connect 2012

In the last weekend of September, MySQL Connect 2012 will take place in San Francisco as part of Oracle OpenWorld. MySQL Connect is a two day event that allows the attendees to focus on MySQL with the possibility to add on to Oracle OpenWorld or JavaOne.

I will be running the Hands-On Lab session Improving Performance with the MySQL Performance Schema (HOL10472) with the following abstract:

The Performance Schema is a tool first introduced in MySQL 5.5. It gives access to information about server events, from function calls to a group of statements. MySQL 5.6 greatly enhances the Performance Schema with new instrumentation points as well as more flexibility for configuring which connections (such as users and hosts) and parts (from databases to instrumentation points) of MySQL should be instrumented. In this hands-on lab, the attendees will configure the Performance Schema to get the best benefit in their environment; walk through practical examples, using high-level summaries; drill down to detailed wait events; and investigate common problems such as slow queries.

The session is scheduled for Saturday 29 September from 5:30 PM to 6:30 PM.

There will also be a talk on the subject of the Performance Schema by Mark Leith. If you attend both of these sessions, you should be well prepared to take advantage of the new Performance Schema features included in the upcoming MySQL 5.6 release.

There is a total of 78 sessions at MySQL Connect 2012, so if you have not already registered, be sure to do it before 24 August when the Early Bird rate ($500 discount compared to an onsite registration) ends.