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).
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
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:
*_currenttracks the currently executing or last executed event per thread
*_historytracks the last 10 events executed per thread
*_history_longtracks 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/%';
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