Database

Detailed Profiling of SQL Activity in MySQL 5.6

By Mark Leith, June 03, 2013

MySQL's latest update to the Performance Schema brings the ability to profile a statement's activity, low-level wait events, and I/O impact. It is the easiest and most detailed way to identify what statements to tune and how.

One of the easy ways to focus your profiling efforts is to turn off instrumentation for all threads apart from the one that you are examining. This will allow you to look at the history tables and see only data that is relevant to your tuning efforts, while also helping to ensure that no other activity on the instance ages out the data that you are trying to analyze.

This is where the performance_schema.threads table can help. This table exposes the same data as the output of SHOW PROCESSLIST, and also provides information on all background threads:

However, the table also has an INSTRUMENTED column that, like the setup_* tables, can be updated at runtime to turn on and off instrumentation by thread. To disable all threads before running your tests:

UPDATE performance_schema.threads SET instrumented = 'NO';

New connections will still be instrumented (unless you also update the setup_actorstable appropriately, by removing the default row). So, make sure you're not running any other application tests against your development instance, then make a new connection to run the specific statement(s) that you want to tune.

Stages do not have a DIGEST column from which to infer a statement, but they are linked to statements using event nesting. Each of the history tables has an EVENT_ID column for the current event, and a NESTING_EVENT_ID column, which is the event ID of its parent event.

Here, the longest period of waiting is during the "Creating sort index" stage, which is again related to creating a temporary table to satisfy a SELECT query. You can order this in any way you like (the example is chronologically), such as ORDER BY timer_wait DESC, for example, to make it more obvious which stages are the highest consumers of time.

If you have executed many queries, then you will want to look at these on a per-statement basis, finding each event_id from events_statements_history_long, and then selecting from the events_stages_history_long table using that id to filter on the NESTING_EVENT_ID column.

But it doesn't stop here; you can drill further into each of the wait events during each stage. This can be particularly telling if you have to do a lot of I/O, for example.

To look at the entire history of the thread, you can use a statement like the following example. It will dump the entire history available in order. This may start out with just statements, and no relating stage/wait events, depending on how many statements you have executed on the thread. It will give progressively more information as you track the thread history.

As you can imagine, there are many more options that can provide useful data for tuning the performance of MySQL. Many of those options are explained in greater detail in the links throughout this article. In all cases, if your work requires improving MySQL performance, you'll find the new Performance Schema to be a boon to your efforts.

Dr. Dobb's encourages readers to engage in spirited, healthy debate, including taking us to task.
However, Dr. Dobb's moderates all comments posted to our site, and reserves the right to modify or remove any content that it determines to be derogatory, offensive, inflammatory, vulgar, irrelevant/off-topic, racist or obvious marketing or spam. Dr. Dobb's further reserves the right to disable the profile of any commenter participating in said activities.

Video

This month's Dr. Dobb's Journal

This month,
Dr. Dobb's Journal is devoted to mobile programming. We introduce you to Apple's new Swift programming language, discuss the perils of being the third-most-popular mobile platform, revisit SQLite on Android
, and much more!