Channels ▼
RSS

Database

Detailed Profiling of SQL Activity in MySQL 5.6


The new MySQL 5.6 release adds instrumentation that enables developers and DBAs to better understand what is happening at all layers of the MySQL server. One of the major improvements is the addition of statement statistics, in either a raw (per-statement) or aggregate form, with the ability to drill down into the stages of execution that each statement goes through. It also enables further drill down into the low-level wait events that occur during each stage of execution.

Performance Schema Introduction

Introduced in MySQL 5.5, the Performance Schema is a new default database schema (called performance_schema), focused primarily on low-level performance metrics of the MySQL server runtime. This feature is designed to complement the INFORMATION_SCHEMA, which is focused on database metadata.

The tables in the performance_schema database use a new storage engine (called PERFORMANCE_SCHEMA), which is specifically designed for tracking the low-level information required to profile the server. All tracked performance metrics are stored in fixed-size ring buffers, which are allocated when the MySQL database is started. (It's designed this way for performance, so no costly memory allocation is needed while recording performance statistics).

Like INFORMATION_SCHEMA, all data within performance_schema is non-persistent — performance metrics within it are reset when the MySQL Database is restarted. The core job of performance_schema is to track events. With MySQL 5.6, there are now three classes of events:

  • Statement Events: Per-statement statistics, such as runtime, lock time, etc.
  • Stage Events: These record the stages of statement execution, such as optimizing, sending data, etc.
  • Wait Events: These track low-level wait periods, such as file I/O, network I/O, mutex contention points, etc.

Each of these events is classified by Instruments. Each defined instrument records runtime statistics on a specific aspect of the MySQL database, with the naming convention for each wait event instrument following a hierarchical taxonomy: Class/Order/Family/Genus/Species, while stages and statements only go as far as the Family level. For instance, file I/O against an InnoDB data file is called wait/io/file/innodb/innodb_data_file, while a wait on an InnoDB Buffer Pool mutex is called wait/synch/mutex/innodb/buf_pool_mutex.

Within the current version of MySQL 5.6, there are 541 individual instrumentation points that Performance Schema can track, across 11 major types of instrumentation:

  • statement/sql/: Normal SQL statement executions (DML or DDL, etc.)
  • statement/com/: Protocol level commands
  • stage/sql/: The stages within each statement execution
  • wait/io/file/: File I/O to data/index files or logs
  • wait/io/socket/: Network I/O between the clients and server
  • wait/io/table/: I/O between the storage engines and the SQL layer of the server
  • wait/lock/table/: Table locks (at the SQL layer, not including InnoDB row-level locks)
  • wait/synch/mutex/: Attempts to grab a mutex lock internally within the server
  • wait/synch/rwlock/: Attempts to grab a read/write lock internally within the server
  • wait/synch/cond/: Waiting on a condition to be raised within the server
  • idle: Records the time that a connection sits idle

For each event, there are two core performance metrics tracked: the number of times that the event happened, and the latency of each event down to picosecond (thousandth of a nanosecond) precision. On top of this, there may be various other statistics recorded, which differ based upon the type of instrumentation point. For instance, a file I/O event will record the file that was being accessed, the type of file access (open, read, write, sync, close, and so on), and how many bytes were read or written.

When enabled, each instrument is tracked with Consumers, which are individual tables within Performance Schema that track the events in various ways. The major consumers are:

mysql> SHOW TABLES FROM performance_schema 
-> WHERE Tables_in_performance_schema LIKE '%current' 
-> OR Tables_in_performance_schema LIKE '%history%'; 

+--------------------------------+ 
| Tables_in_performance_schema   | 
+--------------------------------+ 
| events_stages_current          | 
| events_stages_history          | 
| events_stages_history_long     | 
| events_statements_current      | 
| events_statements_history      | 
| events_statements_history_long | 
| events_waits_current 		 | 
| events_waits_history 		 | 
| events_waits_history_long  	 | 
+--------------------------------+ 

Each type of table has these characteristics:

  • *_current tracks the currently executing or last executed event per thread
  • *_history tracks the last 10 events executed per thread
  • *_history_long tracks the last 10,000 events executed globally

The data from these tables is then further aggregated over several different dimensions. More information is available in the Performance Schema documentation.

Configuring Performance Schema for Profiling in Development

As of MySQL 5.6, Performance Schema is enabled by default. However, not all instrumentation has been enabled by default, as this can impact overall performance of the MySQL database when used in production. With production deployments in mind, only the higher latency types of events are enabled automatically: statements, file I/O, table I/O, table locks, and idle events.           

When profiling your application activity in development, it's beneficial to have much more information available to you at the cost of a little runtime overhead to get at those performance metrics. Instruments can be enabled in two ways: either at server start, using the performance_schema_instrument system variable; or via the performance_schema.setup_instruments table at runtime, which has this structure:

mysql> SELECT * FROM performance_schema.setup_instruments; 
+-----------------------------------------------+---------+-------+ 
| NAME                                          | ENABLED | TIMED | 
+-----------------------------------------------+---------+-------+ 
| wait/synch/mutex/sql/PAGE::lock               | NO      | NO    | 
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_sync   | NO      | NO    | 
...
| wait/io/file/innodb/innodb_data_file          | YES     | YES   | 
| wait/io/file/innodb/innodb_log_file           | YES     | YES   | 
...
+-----------------------------------------------+---------+-------+ 

Any of the tables within the Performance Schema whose names are prefixed with setup_ can be used to update the configuration dynamically using a regular UPDATE statement. For example, use the following statement to enable all instruments dynamically:

UPDATE performance_schema.setup_instruments 
   SET enabled = 'YES', timed = 'YES';

Once this is set, you can use the following statement to disable all mutex instruments:

UPDATE performance_schema.setup_instruments 
   SET enabled = 'NO', timed = 'NO'
 WHERE name LIKE 'wait/synch/mutex/%';

The performance_schema.setup_consumers table can be modified in the same way for consumers. A good base configuration to use in your options file during development is to enable everything on server start:

[mysqld]

performance_schema
performance_schema_instrument = '%=on'
performance_schema_consumer_events_stages_current = ON
performance_schema_consumer_events_stages_history = ON
performance_schema_consumer_events_stages_history_long = ON
performance_schema_consumer_events_statements_history = ON
performance_schema_consumer_events_statements_history_long = ON
performance_schema_consumer_events_waits_current = ON
performance_schema_consumer_events_waits_history = ON
performance_schema_consumer_events_waits_history_long = ON


Related Reading


More Insights






Currently we allow the following HTML tags in comments:

Single tags

These tags can be used alone and don't need an ending tag.

<br> Defines a single line break

<hr> Defines a horizontal line

Matching tags

These require an ending tag - e.g. <i>italic text</i>

<a> Defines an anchor

<b> Defines bold text

<big> Defines big text

<blockquote> Defines a long quotation

<caption> Defines a table caption

<cite> Defines a citation

<code> Defines computer code text

<em> Defines emphasized text

<fieldset> Defines a border around elements in a form

<h1> This is heading 1

<h2> This is heading 2

<h3> This is heading 3

<h4> This is heading 4

<h5> This is heading 5

<h6> This is heading 6

<i> Defines italic text

<p> Defines a paragraph

<pre> Defines preformatted text

<q> Defines a short quotation

<samp> Defines sample computer code text

<small> Defines small text

<span> Defines a section in a document

<s> Defines strikethrough text

<strike> Defines strikethrough text

<strong> Defines strong text

<sub> Defines subscripted text

<sup> Defines superscripted text

<u> Defines underlined text

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.

 
Disqus Tips To upload an avatar photo, first complete your Disqus profile. | View the list of supported HTML tags you can use to style comments. | Please read our commenting policy.
 

Video