MySQL 5.6 Performance Schema is GA

The PERFORMANCE SCHEMA was first introduced in MySQL 5.5, and provided some instrumentation.

With MySQL 5.6, the existing instrumentation has been improved a lot, and a lot of new instrumentation was added also.

Now is a good time to review the overall picture ...

The performance schema tables
In 5.5, the tables available are:

mysql> show tables;
+----------------------------------------------+
| Tables_in_performance_schema                 |
+----------------------------------------------+
| cond_instances                               |
| events_waits_current                         |
| events_waits_history                         |
| events_waits_history_long                    |
| events_waits_summary_by_instance             |
| events_waits_summary_by_thread_by_event_name |
| events_waits_summary_global_by_event_name    |
| file_instances                               |
| file_summary_by_event_name                   |
| file_summary_by_instance                     |
| mutex_instances                              |
| performance_timers                           |
| rwlock_instances                             |
| setup_consumers                              |
| setup_instruments                            |
| setup_timers                                 |
| threads                                      |
+----------------------------------------------+
17 rows in set (0.00 sec)


In 5.6, we have:

mysql> show tables;
+----------------------------------------------------+
| Tables_in_performance_schema                       |
+----------------------------------------------------+
| accounts                                           |
| cond_instances                                     |
| events_stages_current                              |
| events_stages_history                              |
| events_stages_history_long                         |
| events_stages_summary_by_account_by_event_name     |
| events_stages_summary_by_host_by_event_name        |
| events_stages_summary_by_thread_by_event_name      |
| events_stages_summary_by_user_by_event_name        |
| events_stages_summary_global_by_event_name         |
| events_statements_current                          |
| events_statements_history                          |
| events_statements_history_long                     |
| events_statements_summary_by_account_by_event_name |
| events_statements_summary_by_digest                |
| events_statements_summary_by_host_by_event_name    |
| events_statements_summary_by_thread_by_event_name  |
| events_statements_summary_by_user_by_event_name    |
| events_statements_summary_global_by_event_name     |
| events_waits_current                               |
| events_waits_history                               |
| events_waits_history_long                          |
| events_waits_summary_by_account_by_event_name      |
| events_waits_summary_by_host_by_event_name         |
| events_waits_summary_by_instance                   |
| events_waits_summary_by_thread_by_event_name       |
| events_waits_summary_by_user_by_event_name         |
| events_waits_summary_global_by_event_name          |
| file_instances                                     |
| file_summary_by_event_name                         |
| file_summary_by_instance                           |
| host_cache                                         |
| hosts                                              |
| mutex_instances                                    |
| objects_summary_global_by_type                     |
| performance_timers                                 |
| rwlock_instances                                   |
| session_account_connect_attrs                      |
| session_connect_attrs                              |
| setup_actors                                       |
| setup_consumers                                    |
| setup_instruments                                  |
| setup_objects                                      |
| setup_timers                                       |
| socket_instances                                   |
| socket_summary_by_event_name                       |
| socket_summary_by_instance                         |
| table_io_waits_summary_by_index_usage              |
| table_io_waits_summary_by_table                    |
| table_lock_waits_summary_by_table                  |
| threads                                            |
| users                                              |
+----------------------------------------------------+
52 rows in set (0.00 sec)


The difference itself is, to say the least, significant.

A total of 35 tables have been added. That's right, the number of tables tripled, going from 17 to 52.

Also, some existing tables have been improved, to provide more functionality.

Focus of 5.5
The instrumentation provided in 5.5 focuses exclusively on low level waits closely related to the server code itself. Namely, we have pthread mutexes, read write locks, and conditions, all together abbreviated as "mutexes and friends", plus file I/O.

This instrumentation covers (almost, see 5.6) all possible causes for a thread execution to stall and "wait" for something. Having this instrumentation in place is the foundation needed to perform any latency analysis.

Focus of 5.6
More waits
With 5.6, remaining reasons for a thread to stall have been instrumented as well, with the addition of the network I/O instrumentation, and the "idle" instrumentation. Now that every possible root cause that can cause latency is covered, the "wait" area is functionally complete.

Going SQL level
With this major part done, 5.6 also instrument things at a higher level, closer to end users. For example, when a database user performs an INSERT into a TABLE, sure, the server and the storage engine at the very end of the code stack might be waiting on a mutex ... but a mutex is just how an operation is implemented, which is a different point of view from what operation is executed.

With 5.6, the performance schema expands instrumentation from C level to SQL level, which significantly expands the target audience from server developers or power users to include general database users as well.

The most important SQL object manipulated by SQL applications being a TABLE (doh), 5.6 adds instrumentation for tables, covering table I/O, index I/O and table locks.

What a SQL application uses to manipulate these objects is a query (or statement), and therefore, 5.6 has instrumentation for every SQL statement.

Statement digests are computed on the fly, and aggregated on the fly, in memory.

Stages
An intermediate level between statements and waits is also covered by the stages instrumentation. It captures the very same data that the SHOW PROFILE feature collects, but presents results in a different way. Note that the performance schema stage instrumentation provides functionality that SHOW PROFILE does not support, such as monitoring stages from another session, and a lot of aggregations with summaries tables.

More flexibility
The performance schema in 5.5 already introduced the concept on turning on or off each instrument independently, implemented by the table performance_schema.setup_instruments, with the enabled column.

Filtering by "instrument", or probe in the server code, is one dimension to the problem, but there are other dimensions as well.

With 5.6, the performance schema can also filter the traffic recorded by users. Assuming different applications are deployed using different user accounts, which happen to be a best practice for security reasons anyway, this means that the performance schema can filter the workload by applications, with the new table performance_schema.setup_actors. This is a second dimension.

With 5.6 still, the performance schema can also filter the application workload by SQL object, with table performance_schema.setup_objects. This is the third dimension.

For each new dimension, summary tables are provided as well.

Analysing a workload is a complex problem. This problem can now be "seen in 3D", with filters:

  • - enable / disable by instrument with setup_instruments (same as 5.5)
  • - enable / disable by user/host/account with setup_actors (new in 5.6)
  • - enable / disable by object with setup_objects (new in 5.6)

or with aggregated summaries:

  • - summaries by event_name (same as 5.5)
  • - summaries by user/host/account (new in 5.6)
  • - summaries by objects (new in 5.6)

or any combinations, really.

This fundamentally "orthogonal" property of the performance schema instrumentation makes it very powerful tool.

State information
While 5.5 only reports "wait" data in the performance schema, 5.6 also contains "state" data.

The performance_schema.host_cache table fits in this category, as it exposes the internal server host_cache, which for the circumstance has been also greatly enhanced. Using the performance_schema.host_cache, a DBA can see at once not only if a connection from a client is rejected, but also why, and for which client.

While this just sounds obvious functionality that should be there, it has not always been such a given in MySQL history. DBA who administer very large deployments should appreciate this new feature.

Another area where the performance schema exposes state is connection metadata.

The client server protocol in 5.6 was enhanced to allow a connecting application to provide connection attributes. These attributes are exposed in the performance schema in table performance_schema.session_connect_attrs.

The server implementation itself does nothing with it, and yet this data is priceless: this allows a DBA to correlate a session in the database server with a specific component or object in the client application.

For example, a connection running queries to render a page in HTML for a web server could be marked with the originating URL. Tracing where exactly queries do come from (say, in case they need to be improved) can be much easier now.

Improved easy of use
Configuring the performance schema has never been easier.

For once, it is enabled by default out of the box.

The default configuration has been chosen to provide the functionality we expect most DBA will need the most. Of course, nothing is hard coded, and defaults can be changed.

Secondly, the configuration to use can be saved permanently in the server my.cnf file. This is a major improvement over 5.5, which forces the use of DML statements.

A previously big task was to provide a lot of sizing parameters in 5.5. Third improvement with 5.6, the sizing still exists, but there is also a new helper: if the DBA really does not know how to size a parameter, in most cases it can be automatically sized by the server, with embedded heuristics. With the magic "-1" value for a performance schema sizing parameter, the code will do the math based on existing key server parameters. And when auto sizing is available, it is ... the default.

Other ease of use improvements relates to the structure of the performance schema table themselves. As events of different kind are related or nested within each others, detailed individual events recorded in different history tables form together a complex hierarchy.

To help any application to navigate this hierarchy the way it makes the most sense for the application, the performance schema implements, at the same time, both the "nested set" and the "adjacency list" data model.

Reduced runtime overhead
Last but not least, during the overall development cycle of 5.6, the performance schema implementation has been benchmarked, tuned, optimized, sometime re designed, sometime re factored, and all the above happened several times during several iterations.

The overall result of this work is now available in 5.6, which implements numerous performance improvements.

Get ready, MySQL 5.6.10 is now GA
Adding the PERFORMANCE SCHEMA in MySQL 5.5 was a major leap forward compared to 5.1.

The PERFORMANCE SCHEMA in MySQL 5.6 also is a leap forward compared to 5.5 ...

A quantum leap.

Marc Alff,
MySQL Performance Schema Architect,
Oracle.