Marc Alff's blog

Spreading the word about the Performance Schema

noreply@blogger.com (Marc Alff) — Thu, 16 May 2013 10:02:00 +0000

In case you missed it, more and more people are now spreading the word about the Performance Schema, which is a very good thing.

#DBHangOps 4/10/13

Mark Leith presents the Performance Schema and ps_helpers.

Random quotes from the recording:
"I am already seeing so many benefits out of this, ..."
"This looks fantastic"
"Very cool"

OurSQL Episode 139: Starting to Perform

Sheeri and Gerry present the Performance Schema.

Ramdom quote from the recording:
"I am looking at this feature [digests], and I think it's amazing"

Webinar: MySQL 5.6 Performance Schema

PeterZ presents the Performance Schema, and ps_helpers, followed by a Q & A session with myself as a special quest.

Watch for updates with the recording, I don't want to add quotes just from memory (there are good ones too).

Marc Alff,
Performance Schema Architect,
Oracle.

On configuring the Performance Schema

noreply@blogger.com (Marc Alff) — Tue, 16 Apr 2013 09:29:00 +0000

On configuring the Performance Schema

This article is a user guide about MySQL 5.6 Performance Schema configuration. As with many things, the way to approach problems may vary a lot based on systems, user experiences, or just plain opinions, so the "Your Mileage May Vary" caution applies here.

It is easy to get lost in details, and yet starting with the big picture in mind helps to understand not only how, but also more importantly why, to do things ...

The magic recipe is as follows

Define your goals
Define what to instrument
Define how much detail to collect
Provide sizing data
Monitor sizing problems

Define your goals

Performance instrumentation in general can be used for many different things, ranging from casual monitoring in production to debugging in development, with every flavor in between.

Deciding what to instrument is a balancing act. Too little instrumented means too little functionality provided. Too much instrumented means too many details, making the result itself hard to interpret. Depending on the workload, it may be just fine or it could be too much overhead.

In general, instrumentation should typically be on items you have direct control on, and not on just everything available just because it is there.

Some examples to illustrate:

A developer maintaining a SQL application will want to know about SQL statements, because these are the items that can be changed, improved, optimized, etc.

Likewise, someone nurturing the database schema will want to know about load by table, to understand which part of the schema might need improvement, where to change indexes, or which tables to change, normalize, denormalize, etc.

A developer writing code in the server itself, or in a storage engine, will want to know about internal server bottlenecks, and will need the mutex and related instrumentation.

While every goal is valid by itself, chances are that every task is not performed in the same MySQL instance (you do not debug your custom C plugin code directly in the business critical database used in production, do you ?).

Performance instrumentation can be, and most likely is, different per server deployment.

Instrumentation goals should reflect that.

Note that extreme clarity in goal definition here helps a lot too.

Monitoring how much file io is performed globally (say, for capacity planning) is one goal.

Profiling the server to find which application is causing file io (say, to know which team to send a complaint to) is another goal.

Profiling every statement of an application to find which ones are causing file io (say, to resolve the previous complaint) is yet another goal.

These examples illustrates that even when the general area is the same (file io), the level of instrumentation needed for each task may not be the same.

Define what to instrument

The performance schema is in fact a collection of different instrumentations added to the server code.

Broad statements like "With the Performance Schema" are meaningless, since this does not accurately describe what is or is not used, which is ultimately configuration dependent.

Even more confusing, given that new features are available as new releases are published, the definition of "With the Performance Schema" changes with time.

The proper way to reason is to look at each kind of instrumentation individually:

"wait/io/file/%", for file io
"wait/io/socket/%", for network io
"wait/io/table/%", for table io
"wait/lock/table/%", for table locks
"wait/synch/cond/%", for conditions
"wait/synch/mutex/%", for mutexes
"wait/synch/rwlock/%", for read write locks
"stage/%", for stages
"idle", for clients idle time.
"statement/com/%", for raw commands in the client/server protocol
"statement/sql/%", for SQL queries.

And then, for each instrumentation, decide if the instrumentation is either needed as a whole or not, to support your goals.

By default, the server ships with the following enabled only:

File io,
Table io,
Table lock,
Statements,
Idle instrumentation.

These defaults are meant to fit what we expect most deployments will need, so it should be a good starting point.

If you have specific requirements, reviewing that list to:

add the instrumentation you need
remove the instrumentation you do not need

will be a huge step towards making the Performance Schema:

actually give results where you expect them,
reduce overhead by not computing statistics which are not even looked at.

In configuration terms, "what to instrument" is controlled by the content of table setup_instruments.

If any changes beside out of the box values are needed, the my.cnf option to use is performance-schema-instrument.

Define how much detail to collect

One aspect which seems less known is that, when talking about a given instrumentation, the Performance Schema can give more or less details about events recorded.

More details means a better functionality, but also needs more computation and can imply a greater overhead.

In configuration terms, "how much detail to collect" is controlled by the content of table setup_consumers.

The pattern is the same for all instrumentation.

For statements for example, all the following consumers:

global_instrumentation
thread_instrumentation
statements_digest
events_statements_current
events_statements_history
events_statements_history_long

affect one way or another how the statement instrumentation collects data.

The "global_instrumentation" is the master runtime switch which controls whether the Performance Schema is enabled or not, so obviously this consumer should be enabled.

The "thread_instrumentation" controls whether statistics are collected by thread, or just globally. If you need statistics aggregated by account or user or host, these rely on the per thread instrumentation.

The "statement_digests" consumer controls whether query digests are computed and aggregated to produce statistics grouped by queries signatures in table performance_schema.events_statements_summary_by_digest.

The consumer "events_statement_current" controls if the table performance_schema.events_statements_current is populated or not. This table contains much more details about each statements beside global or per thread metrics, with many attributes not found elsewhere.

Consumers "events_statements_history" and "events_statements_history_long" further control if historical data is preserved or not, with two different retention policies.

In terms of performance impact, instrumenting statements can mean anything
from:

do nothing (global_instrumentation is disabled)
collect a few columns in 1 table (global_instrumentation alone is enabled)

to collecting data for all columns in all 9 events_statements_* tables, possibly with fancy features like query digests.

Picking the right level of details needed to support the instrumentation goals is critical.

By default, the following consumers are enabled:

global_instrumentation
thread_instrumentation
statements_digest
events_statements_current

This list is again meant to be a reasonable starting point when not knowing where to start, but it by no means is what you have to use.

Some points to consider when deciding which consumer to add or remove from this list.

For digests, the computation will take some CPU, so if nobody (be a script, a human, a monitoring application) is even looking at table events_statements_summary_by_digest, there is no point in keeping the consumer "statements_digest" enabled.

For current statements, if there is a need to monitor progress of a live query (which table events_statements_current allows), or to see individual queries with greater details, the consumer "events_statement_current" should be enabled. Otherwise, this is a good candidate to disable.

A special note about the thread instrumentation: even if there is no functional reason to enable it (i.e., there is no need to get per user statistics), there could be a technical reason to use it. The technical background is complex, but this is worth mentioning. By forcing the code to keep per thread statistics buffer, locality of memory access is actually improved, and concurrent access to the same memory locations is reduced, which seem to benefit a lot machines with many cores.

Long story short, do not disable the thread instrumentation and automatically assumes it must cause overhead, benchmark first.

Provide sizing data

Defining the content of tables setup_instruments and setup_consumers is one major part that tells the performance schema what to instrument.

Now, the second major part is to find room to store the collected data somewhere ... this is where sizing comes into play.

The Performance Schema sizing parameters are the following (do not look at the values, this is just a test instance).

mysql> show global variables like "performance_schema%";
+--------------------------------------------------------+-------+
| Variable_name                                          | Value |
+--------------------------------------------------------+-------+
| performance_schema                                     | ON    |
| performance_schema_accounts_size                       | 100   |
| performance_schema_digests_size                        | 200   |
| performance_schema_events_stages_history_long_size     | 1000  |
| performance_schema_events_stages_history_size          | 10    |
| performance_schema_events_statements_history_long_size | 1000  |
| performance_schema_events_statements_history_size      | 10    |
| performance_schema_events_waits_history_long_size      | 10000 |
| performance_schema_events_waits_history_size           | 10    |
| performance_schema_hosts_size                          | 100   |
| performance_schema_max_cond_classes                    | 80    |
| performance_schema_max_cond_instances                  | 1000  |
| performance_schema_max_file_classes                    | 50    |
| performance_schema_max_file_handles                    | 32768 |
| performance_schema_max_file_instances                  | 10000 |
| performance_schema_max_mutex_classes                   | 200   |
| performance_schema_max_mutex_instances                 | 5000  |
| performance_schema_max_rwlock_classes                  | 30    |
| performance_schema_max_rwlock_instances                | 5000  |
| performance_schema_max_socket_classes                  | 10    |
| performance_schema_max_socket_instances                | 1000  |
| performance_schema_max_stage_classes                   | 150   |
| performance_schema_max_statement_classes               | 168   |
| performance_schema_max_table_handles                   | 1000  |
| performance_schema_max_table_instances                 | 500   |
| performance_schema_max_thread_classes                  | 50    |
| performance_schema_max_thread_instances                | 200   |
| performance_schema_session_connect_attrs_size          | 2048  |
| performance_schema_setup_actors_size                   | 100   |
| performance_schema_setup_objects_size                  | 100   |
| performance_schema_users_size                          | 100   |
+--------------------------------------------------------+-------+
31 rows in set (0.00 sec)

The variable named "performance_schema" is just a ON/OFF flag and not an integer, so there are really 30 parameters in total.

While it may seem overwhelming, keep reading.

The following 8 parameters:

performance_schema_max_cond_classes
performance_schema_max_file_classes
performance_schema_max_mutex_classes
performance_schema_max_rwlock_classes
performance_schema_max_socket_classes
performance_schema_max_stage_classes
performance_schema_max_statement_classes
performance_schema_max_thread_classes

are guaranteed to be correct just out of the box.

So, why exposing a configuration parameter then ? Because there are remote use cases, for example when a third party storage engine is instrumented with the Performance Schema (and yes, there are some), these parameters needs to be adjusted to account for the instrumentation contributed by the storage engine. Even if these parameters are expected to change very rarely in practice, hard coding is still something to avoid.

The following 6 parameters:

performance_schema_events_stages_history_long_size
performance_schema_events_stages_history_size
performance_schema_events_statements_history_long_size
performance_schema_events_statements_history_size
performance_schema_events_waits_history_long_size
performance_schema_events_waits_history_size

affect directly how much memory is used to keep historical data, but have no impact on CPU. Keeping the default value is perfectly valid and will not have negative effects. Setting a size to 0 to not spend the memory is also perfectly fine, at the cost of some flexibility (no history without a server restart then).

14 down, 16 to go ...

The following 3 parameters:

performance_schema_accounts_size
performance_schema_hosts_size
performance_schema_users_size

indicates how many different users, hosts, or accounts (combinations of 'user_name'@'host_name') are expected to connect to the server.

For one thing, defining these values should be fairly easy (you do define different security users for different applications instead of running everything as root with SUPER privileges, right ? Count them, you have performance_schema_users_size).

For another, the Performance Schema is actually very forgiving if any of these sizing is too low. Statistics may not be collected for each user/host/account there is, but performance will not degrade either.

The parameter performance_schema_session_connect_attrs_size is important to adjust only if there are applications that actually provide extra attributes per connection. This is a brand new feature, and to take advantage of it, the application needs to change the connect code (or at the very minimum the connect string), and use a connector that supports this new feature. In all likelihood, this parameter needs no change for now.

The parameter performance_schema_digests_size indicates how many different query digests can be tracked simultaneously. If statements digests are not used, this parameter can be ignored. When used, how many different digests are needed is very workload dependent, and can be hard to estimate. A value too high will mean unused memory, while a value too low will cause too many digests statistics to be collapsed into the NULL sinkhole, making the digest summary less meaningful, but not slower.

The parameters performance_schema_setup_actors_size and performance_schema_setup_objects_size do need to change only if a lot of singularities (turn instrumentation on for this user, turn it off for this table) are defined with explicit INSERT in tables setup_actors or setup_objects. Unless you use these tables, in which case you know how many rows are inserted there, these parameters need no change.

7 more down, only 9 to go ...

The remaining 9 configuration parameters are:

performance_schema_max_cond_instances
performance_schema_max_file_handles
performance_schema_max_file_instances
performance_schema_max_mutex_instances
performance_schema_max_rwlock_instances
performance_schema_max_socket_instances
performance_schema_max_table_handles
performance_schema_max_table_instances
performance_schema_max_thread_instances

The bad news is that tuning these 9 parameters is critical, as this directly impacts how the Performance Schema functions internally. Parameters that are severely undersized will cause internal contentions in the Performance Schema implementation, which in turns is a known cause of overhead.

The good news is that the server can also "auto tune" itself, by computing a value for each internal instrumentation buffer (this is what these parameters are for), based on yet other configuration items.

Namely, the Performance Schema code looks at these "fundamental four":

max_connections
table_definition_cache
table_open_cache
open_files_limit

from the server my.cnf file, and use an heuristic to estimate how many instruments of each kind are needed.

Some estimates are trivial: to simplify, 1 connection means 1 thread and 1 socket, so knowing max_connections really helps to size performance_schema_max_thread_instances and performance_schema_max_socket_instances ...

The key takeaway here is that the better a server is tuned for the workload expected, the better the Performance Schema will perform, with a sizing aligned on the expected workload.

Monitor sizing problems

With proper sizing, the Performance Schema will use the internal buffers to keep track of instrumented objects in general, and produce statistics as expected.

What happens with "improper" sizing then ?

A sizing parameter set to a high value means a lot of memory used. The memory consumed is fixed and never changes, so nothing will degrade other time.

To help understanding which sizing causes which memory consumption, the Performance Schema expose details with SHOW ENGINE PERFORMANCE_SCHEMA STATUS.

A sizing parameter set to a low value can cause two major effects.

First, this will cause at some points the code to drop statistics / events / instrumented objects on the floor, purely and simply. This is a functional problem, since now statistics collected are only partial, and therefore not trustworthy.

Second, if the workload forces the code to allocate very often instrumentation for new objects (when opening a table, a file, a new session), and if the code spends a lot of time to try (and fail) to find memory for the object to instrument, this is also a technical problem, with code that suddenly causes a bottleneck, degrading performances.

When this happens, a status counter for "lost" data is incremented.

Under normal operations, the Performance Schema should always report zero in all the following indicators.

mysql> show global status like "performance_schema%";
+-----------------------------------------------+-------+
| Variable_name                                 | Value |
+-----------------------------------------------+-------+
| Performance_schema_accounts_lost              | 0     |
| Performance_schema_cond_classes_lost          | 0     |
| Performance_schema_cond_instances_lost        | 0     |
| Performance_schema_digest_lost                | 0     |
| Performance_schema_file_classes_lost          | 0     |
| Performance_schema_file_handles_lost          | 0     |
| Performance_schema_file_instances_lost        | 0     |
| Performance_schema_hosts_lost                 | 0     |
| Performance_schema_locker_lost                | 0     |
| Performance_schema_mutex_classes_lost         | 0     |
| Performance_schema_mutex_instances_lost       | 0     |
| Performance_schema_rwlock_classes_lost        | 0     |
| Performance_schema_rwlock_instances_lost      | 0     |
| Performance_schema_session_connect_attrs_lost | 0     |
| Performance_schema_socket_classes_lost        | 0     |
| Performance_schema_socket_instances_lost      | 0     |
| Performance_schema_stage_classes_lost         | 0     |
| Performance_schema_statement_classes_lost     | 0     |
| Performance_schema_table_handles_lost         | 0     |
| Performance_schema_table_instances_lost       | 0     |
| Performance_schema_thread_classes_lost        | 0     |
| Performance_schema_thread_instances_lost      | 0     |
| Performance_schema_users_lost                 | 0     |
+-----------------------------------------------+-------+
23 rows in set (0.00 sec)

Any non zero value is a sign that the corresponding sizing parameter is too low and needs to be increased.

Defining the proper sizing might take a few attempts and is a necessary limitation of the Performance Schema "static buffer allocation" design.

This design is a deliberate choice, which avoids completely a different instrumentation pitfall. Assume a different implementation that allocates and frees memory as needed to keep statistics, instead of using a fixed amount of memory. This instrumentation can be very user friendly, for a while. Then the day comes when the workload measured climbs into uncharted territory, with unanticipated spikes (if you can anticipate, you can provide the sizing data, right ?).

When that day comes, which one is better:

taking the server down, due to out of memory conditions or swapping, caused by an uncontrollable memory consumption ?
or cutting the instrumentation off ("lost" counters), and let the server proceed with the workload spike ?

Ease of use and robustness can sometime be conflicting requirements, the Performance Schema design strongly favors robustness, while providing tuning heuristics to alleviate ease of use annoyances.

Marc Alff,
Oracle.

MySQL 5.6 Performance Schema is GA

noreply@blogger.com (Marc Alff) — Tue, 05 Feb 2013 13:17:00 +0000

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.

Fiddle with the Performance Schema

noreply@blogger.com (Marc Alff) — Wed, 03 Oct 2012 19:33:00 +0000

I recently found an interesting tool to play with databases, SQLFiddle.

The tool is used to share some fragments of code, running on a live database, to allow users to not only see (read) the code, but also actually execute it, to play with the code.

See this link for all the details.

The nice part is that MySQL 5.5 is part of the supported databases, try it here.

It gets better: MySQL 5.6 is also there, so you get to try new 5.6 features, or your favorite query, in a blink, without having to do an installation.

And icing on the 5.6 cake, the PERFORMANCE_SCHEMA is also enabled, with grants given to the user to query performance schema tables.

For example, this is a small "sqlfiddle" that shows how this works.

As you can see looking at the results after executing this code fragment, the performance schema is there, ready for you to play with it.

Marc Alff,
Oracle

P.S.

Views expressed here are my own.
Not a product or service endorsement.

Performance Schema, nailing the host cache coffin down

noreply@blogger.com (Marc Alff) — Wed, 11 Apr 2012 18:31:00 +0000

Every old mansion has some old history, and the older it is, the scarier it gets ...

MySQL is no exception: there are some very old ghosts still lurking inside the server.

One of these ghosts is the 'host cache', which haunts the server main entrance halls, just right pass the TCP/IP front door.

How long has this ghost been there ? From the very beginning it seems, as is was first spotted on April 26, 2006 ... Yes, that was 6 years ago, back in the 4.1 old times.

This ghost has been haunting DBA's worst nightmares since then.

Public sightings include:
- Bug#22821 Adding "SHOW HOST_CACHE" patch from Jeremy Cole
- Bug#24906 No command to monitor blocked hosts
- Bug#45817 Please add SHOW command for inc_host_errors(max_connect_errors)
- Bug#50963 add information_schema.error_count
- Bug#59404 More info from host cache
- http://www.facebook.com/notes/mysqlfacebook/monitoring-for-errors/293204560932

Why is the host cache a ghost ?

Because it can not be seen. But make no mistake, it is there, waiting to scare database connections away.

Why is it causing nightmares ?

Because it is speechless (no trace in the log), very stealthy (some people do not know it is there), and yet deadly effective at killing visitors, without telling anyone.
And definitely without telling any living soul.

If an application ever sees something like this, then you know this application really did upset the ghost too much, which is now seeing red and killing at first sight.

That last part, the fact that the ghost actually has a spirit of it's own, and will remember an annoying visitor, is what makes it the worst: troubleshooting a system that show some hysteresis, while staying completely in the dark, is no fun at all.

The only thing the host cache ghost would tell anyone, just as a final reminder that is is still lurking, is how many visitors got killed.
No when, no why, no who ... just a casualty report.

While keeping unwanted visitors out might be a good thing after all, also preventing wanted but poorly configured visitors to connect to the database is really a bad thing.

This is why this old ghost from the past has now finally been put to rest, thanks to the performance schema in the MySQL 5.6.5-m8 release: see the new table performance_schema.host_cache.

With the new performance_schema.host_cache table, not only a DBA can see the host cache content, but the content has been expanded to also see a count for every possible error that can prevent a visitor to get in.

Finally some light in that entrance hall.

Now, we are still in April 2012, and this feature is 6 years overdue, but don't get fooled ... this is not a faery tale.
The fact that such things can happen might be a reason to believe in Santa Claus again.

Marc Alff, Oracle.

Performance schema or COM_ status counters ?

noreply@blogger.com (Marc Alff) — Fri, 02 Dec 2011 10:16:00 +0000

In MySQL 5.6.3, that is, a while ago already, the performance schema added instrumentation for statements.

This major enhancement seem to have gone unnoticed, so a quick review of how it works, especially compared to the existing COM_ status counters that everyone is used to, can perhaps put some light here.

The MySQL server maintains 'COM_%' counters for statements executed.

These counters are available in both the session and global status.

Let's look at a few:

mysql> select variable_name from information_schema.global_status
where variable_name like "com\_%" order by variable_name limit 20;
+------------------------+
| variable_name          |
+------------------------+
| COM_ADMIN_COMMANDS     |
| COM_ALTER_DB           |
| COM_ALTER_DB_UPGRADE   |
| COM_ALTER_EVENT        |
| COM_ALTER_FUNCTION     |
| COM_ALTER_PROCEDURE    |
| COM_ALTER_SERVER       |
| COM_ALTER_TABLE        |
| COM_ALTER_TABLESPACE   |
| COM_ANALYZE            |
| COM_ASSIGN_TO_KEYCACHE |
| COM_BEGIN              |
| COM_BINLOG             |
| COM_CALL_PROCEDURE     |
| COM_CHANGE_DB          |
| COM_CHANGE_MASTER      |
| COM_CHECK              |
| COM_CHECKSUM           |
| COM_COMMIT             |
| COM_CREATE_DB          |
+------------------------+
20 rows in set (0.00 sec)

Now, let's take a look at what is instrumented in the performance schema, for statements:

mysql> select name from setup_instruments

where name like "statement/sql/%" order by name limit 20;
+----------------------------------+
| name                             |
+----------------------------------+
| statement/sql/                   |
| statement/sql/alter_db           |
| statement/sql/alter_db_upgrade   |
| statement/sql/alter_event        |
| statement/sql/alter_function     |
| statement/sql/alter_procedure    |
| statement/sql/alter_server       |
| statement/sql/alter_table        |
| statement/sql/alter_tablespace   |
| statement/sql/analyze            |
| statement/sql/assign_to_keycache |
| statement/sql/begin              |
| statement/sql/binlog             |
| statement/sql/call_procedure     |
| statement/sql/change_db          |
| statement/sql/change_master      |
| statement/sql/check              |
| statement/sql/checksum           |
| statement/sql/commit             |
| statement/sql/create_db          |
+----------------------------------+
20 rows in set (0.00 sec)

The resemblance is striking. In fact, the names present in performance_schema.setup_instruments are derived from the COM_ names, with a few tweaks.

For real SQL statements, such as ALTER TABLE, 'COM_ALTER_TABLE' corresponds to 'statement/sql/alter_table'.

For what show status advertizes as 'admin commands', which is just a common bucket for different things, the performance schema instrumentation actually is more detailed, and gives each command a real name, so that commands from the client/server protocol are visible too:

mysql> select name from setup_instruments

where name like "statement/com/%" order by name;
+------------------------------+
| name                         |
+------------------------------+
| statement/com/Binlog Dump    |
| statement/com/Change user    |
| statement/com/Close stmt     |
| statement/com/Connect        |
| statement/com/Connect Out    |
| statement/com/Create DB      |
| statement/com/Daemon         |
| statement/com/Debug          |
| statement/com/Delayed insert |
| statement/com/Drop DB        |
| statement/com/Error          |
| statement/com/Execute        |
| statement/com/Fetch          |
| statement/com/Field List     |
| statement/com/Init DB        |
| statement/com/Kill           |
| statement/com/Long Data      |
| statement/com/Ping           |
| statement/com/Prepare        |
| statement/com/Processlist    |
| statement/com/Query          |
| statement/com/Quit           |
| statement/com/Refresh        |
| statement/com/Register Slave |
| statement/com/Reset stmt     |
| statement/com/Set option     |
| statement/com/Shutdown       |
| statement/com/Sleep          |
| statement/com/Statistics     |
| statement/com/Table Dump     |
| statement/com/Time           |
+------------------------------+
31 rows in set (0.00 sec)

Overall, there are 140 'COM_%' entries in show status, for 165 'statement/%' entries in the performance schema. The performance schema also count errors with special entries, which is not available in show status. All the details are explained in the documentation.

Now, what about the statistics collected by the statement instrumentation then ?

In SHOW STATUS, each 'COM_%' entry is only a counter. There are counters for the current session (aka your own connection), and global counters.

In the performance schema, there are more detailed statistics and not just a counter. These statistics are for every session (table statements_summary_by_thread_by_event_name) or global (table statements_summary_global_by_event_name).

In fact, there are even more levels of aggregation as intermediate steps between SESSION and GLOBAL, with statistics by account, user or host, which the SHOW STATUS command do not provide.

The difference between 'current session' and 'every session' is fundamental ... a monitoring tool needs to see what a monitored application is doing, not itself.

As for details about the data provided, let's take a quick look:

For SHOW STATUS:

mysql> select * from information_schema.global_status

where variable_name like "com_select" \G
*************************** 1. row ***************************
 VARIABLE_NAME: COM_SELECT
VARIABLE_VALUE: 34
1 row in set (0.00 sec)

For the performance_schema:

mysql> select * from performance_schema.events_statements_summary_global_by_event_name

where event_name like "statement/sql/select" \G
*************************** 1. row ***************************
                 EVENT_NAME: statement/sql/select
                 COUNT_STAR: 34
             SUM_TIMER_WAIT: 103780188000
             MIN_TIMER_WAIT: 76388000
             AVG_TIMER_WAIT: 3052358000
             MAX_TIMER_WAIT: 39010345000
              SUM_LOCK_TIME: 3818000000
                 SUM_ERRORS: 1
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 1441
          SUM_ROWS_EXAMINED: 10574
SUM_CREATED_TMP_DISK_TABLES: 2
     SUM_CREATED_TMP_TABLES: 14
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 31
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 1071
              SUM_SORT_SCAN: 24
          SUM_NO_INDEX_USED: 31
     SUM_NO_GOOD_INDEX_USED: 0
1 row in set (0.00 sec)

There are even more differences.

Having statistics is great, but people need to reset them once in a while, still when doing monitoring.

For SHOW STATUS, the command FLUSH STATUS has shortcomings. See feature requests like bug#22875 about it.

The performance schema actually provides the functionality to reset statistics.

The syntax is a bit different: the missing 'FLUSH SESSION STATUS' corresponds to 'TRUNCATE TABLE performance_schema.events_statements_summary_by_thread_by_event_name'.

It is all there in 5.6.3

-- Marc Alff, Oracle.

MySQL 5.6.4 PERFORMANCE SCHEMA

noreply@blogger.com (Marc Alff) — Thu, 01 Dec 2011 21:29:00 +0000

New performance schema features in 5.6.4

As development of the 5.6 serie continues, a new milestone (5.6.4) is now available.

The source code for mysql-trunk is on launchpad, and the MySQL 5.6 documentation is also public.

In this milestone, a few new performance schema features have been implemented, as can be seen in the changelog.

MY.CNF, or easier configuration

Until now, the performance schema used a very simple, or shall I say, crude, way to decide what needs to be instrumented or not: flip the master switch on (performance_schema), and you get absolutely everything available, with as much details as possible, by default. And then, to change the defaults, the only way was to use DML statements to tell the performance schema to do less.

With the number of instrumented artifacts growing (table setup_instruments went from 212 entries in 5.5 to 503 entries in 5.6.4), and with the number of tables also growing (the performance_schema database went from 17 tables in 5.5 to 48 tables in 5.6.4), there is now the need for a better, easier way to control the instrumented content.

The first major change implemented is that instead of getting all the available instrumentation enabled by default, only a selected few instruments are enabled. For example, not every DBA is interested in knowing the low level implementation details of the server code itself, so things that are only fascinating to server developers like mutexes are disabled. Conversely, DBA are likely to be looking often at statements that applications are executing against the server, so all the statement instrumentation is enabled by default. Nothing is hard coded and everything can be changed. By providing a configuration "out of the box" that is much closer to real production scenarios, the extra work to configure the performance schema exactly how you want it is reduced, a lot.

Similar to limiting which instrument is enabled in the table setup_instruments, the list of consumers enabled by default, which basically controls how much details the instrumentation provides, has been revised. Consumers such as event histories, which contributed a lot to the performance schema overhead only to maintain tables that are after all rarely used, have been turned off by default.

The second major change is that the performance schema does not need to be told again, with DML, what to do after each server startup. The specification about settings in tables setup_instruments and setup_consumers, which again define how many different events are collected and with which level of details, can be written in the server my.cnf file. The syntax in the my.cnf file is simple and powerful enough so that one can tell in one line "disable all the mutexes" or "enable all the file io in this storage engine", without having to even spell out each and every instruments (remember that there are 500+ by now) known to the performance schema.

In short, this new feature should be good news to many people, especially early adopters.

END_EVENT_ID, or easier ways to access hierarchical data

The performance schema provides data. A lot. With many details.

When looking in particular at individual events, instead of just statistics with aggregations, these events (statements, stages, waits of many different type) all relate to each others and form collectively a big hierarchical data tree.

For a good description of hierarchical data and methods to manipulate it, see this excellent article.

The performance schema tables were already supporting the 'Adjacency List Model', with columns such as NESTING_EVENT_ID in an event pointing to EVENT_ID in a parent event.

With 5.6.4 and the introduction of columns like END_EVENT_ID, the performance schema tables are now also supporting the 'Nested Set Model'.

That's right, we now support both models, so an application can use what makes the most sense given the use case.

Why is that important ? Well, people like Mark Leith convinced me that it is, in particular because it helps a lot to navigate the data efficiently, for example to produce big fancy graphs.

Extended file io statistics

The file io instrumentation was added in the performance schema in 5.5 already. Unfortunately, the statistics collected by file in 5.5 are not very informative, as only a few columns are provided.

With 5.6.4, the summaries for file io have been revised and improved, to provide more content.

To see the difference, compare side by side the 5.5 documentation and the 5.6 documentation, and see the new columns.

Reminder

MySQL 5.6.4 is not a GA release, and should not be used in production.

-- Marc Alff, Oracle.

MYSQL GET DIAGNOSTICS

noreply@blogger.com (Marc Alff) — Tue, 18 Oct 2011 08:07:00 +0000

A new feature just got merged into mysql-trunk, the GET DIAGNOSTICS statement.

Many people have been asking for this for a very long time, so it is worth mentioning it.

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.6.4-m6-log |
+--------------+
1 row in set (0.00 sec)

mysql> drop table test.no_such_table;
ERROR 1051 (42S02): Unknown table 'test.no_such_table'

Why is it important ? In short, it allows to programmatically (i.e., in SQL) inspect what happened in a SQL exception.

mysql> get diagnostics condition 1
  @p1 = MESSAGE_TEXT,
  @p2 = RETURNED_SQLSTATE,
  @p3 = MYSQL_ERRNO,
  @p4 = SCHEMA_NAME,
  @p5 = TABLE_NAME;

Query OK, 0 rows affected (0.00 sec)

Exception handling code can then inspect attributes of the exception raised, to find out which error in particular occurred. Most critical items are fully implemented.

mysql> select @p1, @p2, @p3;
+------------------------------------+-------+------+
| @p1                                | @p2   | @p3  |
+------------------------------------+-------+------+
| Unknown table 'test.no_such_table' | 42S02 | 1051 |
+------------------------------------+-------+------+
1 row in set (0.00 sec)

Unfortunately, others are not fully supported yet.

mysql> select @p4, @p5;
+------+------+
| @p4  | @p5  |
+------+------+
|      |      |
+------+------+
1 row in set (0.00 sec)

The SCHEMA_NAME and TABLE_NAME condition items should be 'test' and 'no_such_table' respectively, indicating which table caused the DROP TABLE statement to fail.

The GET DIAGNOSTICS statement is implemented, and returns the values found in the server diagnostics area, but what is missing now is to revise the implementation of every statement (such as DROP TABLE) to populate every attribute of the diagnostics area when raising an error.

While it may not look so impressive at first glance, implementing the GET DIAGNOSTICS statement is actually a huge step forward: now with SIGNAL and RESIGNAL, GET DIAGNOSTICS allows an application to implement proper SQL exception handling.

-- Marc Alff

Explaining performance schema tables ... with pictures

noreply@blogger.com (Marc Alff) — Mon, 29 Aug 2011 19:10:00 +0000

The problem

For illustration, let's assume a MySQL server, with a couple of users (A, B, C, ...) who perform queries against some tables (T1, T2, etc) in the database.
Sooner of later, someone will want to know which user is performing which amount of table io against which table ... How can instrumentation help with that ?

	T1	T2	T3	T4	T5
User A	???	???	???	???	???
User B	???	???	???	???	???
User C	???	???	???	???	???

Performance schema aggregates

The performance schema provides different "summaries" tables, which aggregate statistics collected using different criteria.

TABLE performance_schema.table_io_waits_summary_by_table

	T1	T2	T3	T4	T5
User A	???	???	???	???	???
User B	???	???	???	???	???
User C	???	???	???	???	???
TOTAL	ALL-T1	ALL-T2	ALL-T3	ALL-T4	ALL-T5

This table shows directly the table io statistics, grouped by table.
In our picture, this is the TOTAL row.

TABLE performance_schema.events_waits_summary_by_user_by_event_name

	T1	T2	T3	T4	T5	TOTAL
User A	???	???	???	???	???	ALL-A
User B	???	???	???	???	???	ALL-B
User C	???	???	???	???	???	ALL-C

This table shows statistics, but grouped by user instead of objects.
In our picture, this is the TOTAL column.
Many different statistics are collected (this is a _BY_EVENT_NAME table), so to get the table io statistics only, use a where clause such as "WHERE EVENT_NAME like 'wait/io/table/%' "
Oh, and by the way: this table is part of a new feature merged recently in mysql-trunk.
The complete list of new tables delivered is:

accounts
users
hosts
events_waits_summary_by_account_by_event_name
events_waits_summary_by_user_by_event_name
events_waits_summary_by_host_by_event_name
events_stages_summary_by_account_by_event_name
events_stages_summary_by_user_by_event_name
events_stages_summary_by_host_by_event_name
events_statements_summary_by_account_by_event_name
events_statements_summary_by_user_by_event_name
events_statements_summary_by_host_by_event_name

Performance schema filters

The performance schema also provides tables used to limit the scope of events collected by the instrumentation. These filters can operate on different criteria as well.

TABLE performance_schema.setup_actors

	T1	T2	T3	T4	T5
User A	???	???	???	???	???
User B	???	???	???	???	???
User C	???	???	???	???	???

Using table setup_actors, one can selectively enable or disable some users (rows in the array used in example, which are displayed in gray).

TABLE performance_schema.setup_objects

	T1	T2	T3	T4	T5
User A	???	???	???	???	???
User B	???	???	???	???	???
User C	???	???	???	???	???

Using table setup_objects, one can selectively enable or disable some SQL tables (columns in the array used in example, which are displayed in gray).
The table setup_objects now has a new column named "ENABLED", which was added recently in the trunk (another new feature). This allows to easily enable "all tables except ...".

On combining filters

	T1	T2	T3	T4	T5
User A	???	???	???	???	???
User B	???	???	???	???	???
User C	???	???	???	???	???

Using multiple filters at once is a great way to limit the scope of the queries instrumented. Assuming an homogeneous repartition of queries for example, collecting statistics for 2/3 of the users, for 3/5 of the tables, allows to reduce the amount of events collected by a factor 6/15.
The overhead of the table io instrumentation is directly affected here, with only 40% of the events processed.

On using orthogonality

Using a filter in one dimension, and an aggregate in another dimension, allows to dive deeper and get a picture of only a "slice" of the server activity. This particular property of orthogonality is a key to the performance schema design. Examples of usage below.

What is this user doing ?

	T1	T2	T3	T4	T5
User A	???	???	???	???	???
User B	B1	B2	B3	B4	B4
User C	???	???	???	???	???
TOTAL	B1	B2	B3	B4	B5

Table setup_actors can isolate activity of a given user (or users). Aggregates by object will then show the detailed activity of the users(s) of interest on the database schema.

Who is touching this table ?

	T1	T2	T3	T4	T5	TOTAL
User A	???	???	A3	???	???	A3
User B	???	???	B3	???	???	B3
User C	???	???	C3	???	???	C3

Likewise, table setup_object can isolate activity on a given table (or set of tables). Aggregates by user will then show the detailed activity of different users on the table(s) of interest.

Last words

Please refer to the MySQL 5.6 performance schema documentation for more details.
MySQL 5.6.3 is not a GA release, do not use in production. Subject to change without notice.
-- Marc Alff, Oracle.

Performance schema, overhead tuning

noreply@blogger.com (Marc Alff) — Fri, 24 Jun 2011 16:25:00 +0000

Performance schema performance tuning

One of the most important question users ask before deciding to use the performance schema is: what is the overhead ? The underlying concern of course is to make sure deploying the performance schema does not negatively impact production ("First, do no harm").
The question is simple, and yet the answer is not so simple, as it depends on so many things.
As seen in some benchmarks done already, the overhead when using the performance schema can vary a lot, and in some cases really causes degradations.
A lot of time has been spent investigating the root causes (note the plural form) for the performance schema overhead in general, so I think it is a good time to share current findings.

Server workload

Analysing performances, and testing the effects of different code or configuration changes, can hardly be done in a vacuum. That's where benchmarks come in.
The problem with benchmarks in general is that different benchmarks will produce different loads, and that a given code may perform very well under some conditions but poorly under others ...
One particular property of the performance schema is that, since it instruments the server itself, the load and behavior of the server:

is there capacity left or is it saturated ?
is it CPU bound or disk io bound ?,
paging ?
swaping ?
contended in a specific place (query cache, table cache, innodb buffer pool, [insert your favorite here]) ?

directly influences the load put on the performance schema code:

how many mutexes / rwlocks / cond events are detected ?
how many file io events are detected ?
how many table io / table lock events are detected ?
how many stages / statements are detected ?

Given this load as input, the user configuration of the performance schema:

the sizing of internal performance schema buffers,
which instrument is enabled ?
which instrument is timed ?
which timer is used ?
which consumer is enabled ?
is there some filtering per user ? per host ? (table setup_actors)
is there some filtering per object? (table setup_objects)

will decide what to do with the events seen:

discard them,
count them,
time them,
aggregate them, possibly with fancy aggregations,

all of which affects overhead.
To summarize, the overall system can be modeled as giant pipe, which consists of, in order:

the client application and the generated application workload,
the mysql server and the generated instrumentation workload,
the performance schema and the generated statistics / overhead.

It is critical to understand how that pipe works to interpret results.
For example, if a client application is sending very few queries to the server, generating a very "small" load, a benchmark with or without the performance schema is not going to show any differences. To be meaningful, a benchmark (of the database) has to put a lot of pressure (on the database). This is hardly a surprise, but here comes the interesting part.
Next, if a server is poorly configured for the application workload received, and if the server itself does not perform well for that load, the instrumentation workload on the performance schema will be "small". A properly configured server will execute more queries, transactions, etc, which will generate more events to instrument. To be meaningful, a benchmark (of the performance schema) has to put a lot of pressure (on the performance schema).
An immediate consequence is that benchmarks of the performance schema should disable the query cache: there are currently 474 instrumentation points in the server. If most executions paths bounce off of the query cache, stress will be on "wait/synch/mutex/sql/Query_cache::structure_guard_mutex" only, cutting off the load generated from the lower layers on the server code, such as the table io instrumentation, or the storage engines instrumentation.
A more subtle consequence is that every time a performance bottleneck in the server code is removed, either by a bug fix or by a better server tuning, server efficiency goes up, which increases the number of statements / transactions / whatever the benchmark metric is counting, that are executed. While this is good news for the server, it actually increases the instrumentation workload for the performance schema.
In conclusion, and in my opinion, to interpret a performance schema benchmark, results should describe not only the overhead observed, but also the load seen that caused the overhead. By load seen, I mean here the instrumentation load seen at the server / performance schema interface, since this is the real stress put on the performance schema code. The more details are available for each stage of the pipe, the better.
Time to dive into numbers then ...

The trunk benchmark

The load used for this analysis is sysbench, and more specifically the sysbench oltp read-only benchmark.
The reason this particular case was picked is not because I like the results on this one better.
The reasons are:

sysbench oltp was the test used in previous benchmarks, and to reproduce the results (and analyse the reasons for overhead), it helps to be in similar conditions,
a read only, in memory only load eliminates a lot of noise from measurements, making analysis easier: the goal here is not to get a number (by "just" running a benchmark), it is to get an explanation for the number, so that the code can be improved,
the performance schema code itself is "CPU only, user land only" code, as it does not make any io or any system call. A benchmark that shares the same characteristics seems more likely to provide interesting insight.

Time permitting, I hope to get to do the same analysis for other cases (sysbench read-write, dbt2, etc).
The test machine used is a 8 CPU / 32 GB Linux box, so the test configuration is sized accordingly:

for sysbench: --oltp-table-size=1000000
for mysql my.cnf: innodb_buffer_pool_size = 12G

Historical note: testing done for this post started on a 8 CPU 16 GB machine, then moved to another lab. The original configuration was not changed, hence the 12G buffer pool only.

The trunk results

All results are for mysql-trunk (revno 3195, 2011-06-13), compiled in Release configuration. This corresponds to the current code for mysql 5.6, which is still in development.

Tests without the performance schema

With this build, the performance instrumentation is not compiled. All the computation of overhead expressed as percentages are relative to this reference, which is used as the ultimate baseline.

Connections	TPS	CPU User (%)	CPU System (%)	CPU Idle (%)
1	807	10	2	88
2	1439	20	5	75
4	2610	40	10	50
8	5086	84	14	2
12	5037	84	15	1
16	5038	85	15	0
32	4854	85	15	0
48	4762	85	15	0
64	4118 (*)	60	32	8
128	3538 (*)	57	35	8
256	1746 (*)	57	35	8

The overall CPU statistics are collected by vmstat, and helps to qualify in which mode the server is operating.
Looking at the overall shape of the performance curve, three different operating modes can be observed:

Ramping up (1 to 8 connections)
Peek efficiency (8 to 48 connections)
Server progressive degradation (48 to infinity connections)

For a 8 core machine, this looks reasonable.
(*) A special note about results for 64 / 128 / 256 connections: the performance measured in this cases is not stable and can vary a lot (50 percent) between runs. This is due to the machine saturation: at 256 connections, 256 threads are running in sysbench, plus 256+16 in mysqld, which makes 512+ active threads on a 8 core. Numbers are posted for this case as well, to include this mode of operation in the analysis.

Tests with the performance schema

Now that we have established what the baseline looks like, executing the same workload on the same hardware, under the same conditions gives the following results. The CPU user / system / idle statistics from vmstat are not repeated (the overall profile is the same) for brevity.

With performance schema	disabled		off		global (timed)		thread (timed)
Connections	TPS	Overhead (%)	TPS	Overhead (%)	TPS	Overhead (%)	TPS	Overhead (%)
1	796	1.36	775	3.97	684	15.24	681	15.61
2	1409	2.08	1349	6.25	1198	16.75	1191	17.23
4	2544	2.53	4634	5.86	2115	18.97	2114	19.00
8	4844	4.76	4634	8.89	3974	21.86	3913	23.06
12	4850	3.71	4662	7.44	4020	20.19	3979	21.00
16	4847	3.79	4676	7.19	4051	19.59	4020	20.21
32	4676	3.61	4485	7.60	3851	20.66	3789	21.94
48	4582	3.78	4384	7.94	3759	21.06	3692	22.47
64 (*)	2548	38.13	3048	25.98	3392	17.63	3286	20.20
128 (*)	2035	42.48	2007	43.27	1938	45.22	1917	45.82
256 (*)	1745	0.06	1697	2.81	1662	4.81	1624	6.99

(*) The same comment about results stability applies here. Because the results are unstable with great variations, the overhead computed after normalization on a unstable baseline gives meaningless results.

Analysis

Overall, the overhead in the most basic configuration, with all instruments enabled and only the global instrumentation consumer enabled, is at 20 percent already.
This is a lot. Looking in more details at these results, it appears that:

Adding the thread instrumentation consumer (to get statistics per thread) does only marginally increase overhead: at 8 connections, the overhead goes from 21.86% to 23.06%.
The overhead depends on the number of connections. It is progressive in the 1-8 connections range, peaks at 8 connections, then is both stable and significant at 8-48 connections.
Peak TPS without the performance schema was at 8 connections. With the performance schema, it seems to move to 12 or even 16 connections.

To understand the root cause for this 20% toll, the following measurements are also instructive:

With the performance schema enabled but all instruments disabled (configuration "off"), the overhead is at ~8%. For code doing nothing, this is actually quite high.
When the performance schema is disabled at startup time (configuration "disabled"), the overhead is at ~4%. This configuration is not very useful from a functional point of view, but from a technical point of view it executes a code path which is a subset of the configuration "off". That makes the numbers collected useful.

Based on this, overhead seems to originate from, roughly:

compiling with the performance schema (configuration "disabled"): ~4% cost,
starting with the performance schema (configuration "off"): additional ~4% cost,
enabling the global consumer only (configuration "global (timed)"): additional ~12% cost,
adding the thread consumer only (configuration "global (timed)"): additional ~1% cost

To understand better which code is executed, and how often, let's use the performance schema itself to get some insight on the server execution. After all, that is precisely what the performance schema is for.
Using the "thread (timed)" configuration, with the assumption that the instrumentation does not distort the workload, and looking at details for 8 connections, which is chosen because it corresponds to the peak performance without the performance schema, statistics collected are as follows below.

Total number of wait events.

mysql> select sum(count_star), sum(sum_timer_wait)
    ->   from performance_schema.events_waits_summary_global_by_event_name;
+-----------------+---------------------+
| sum(count_star) | sum(sum_timer_wait) |
+-----------------+---------------------+
|      1098658477 |     797822128662705 |
+-----------------+---------------------+
1 row in set (1.79 sec)

Top waits, by count

mysql> select * from performance_schema.events_waits_summary_global_by_event_name
    ->   where count_star>0 order by count_star desc limit 25;
+-----------------------------------------------+------------+-----------------+----------------+----------------+----------------+
| EVENT_NAME                                    | COUNT_STAR | SUM_TIMER_WAIT  | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT |
+-----------------------------------------------+------------+-----------------+----------------+----------------+----------------+
| wait/io/table/sql/handler                     |  482492695 | 583215424951365 |          42210 |        1208680 |     7913678535 |
| wait/synch/mutex/innodb/srv_conc_mutex        |  265080324 | 142377797253015 |          30150 |         537005 |     1862766495 |
| wait/synch/mutex/sql/THD::LOCK_thd_data       |  122077261 |   6519294560295 |          30150 |          53265 |     1072076715 |
| wait/synch/rwlock/innodb/hash table locks     |   38409624 |   5420427797475 |          27135 |         141035 |      360901530 |
| wait/synch/mutex/innodb/read_view_mutex       |   32554195 |   6146001298890 |          24120 |         188605 |     1011083265 |
| wait/synch/mutex/sql/LOCK_open                |   32553870 |   9359193544965 |          30150 |         287430 |     1080446355 |
| wait/lock/table/sql/handler                   |   32553724 |  20576707870950 |          99495 |         631810 |     1083789990 |
| wait/synch/mutex/mysys/THR_LOCK::mutex        |   32553724 |   6964485402105 |          30150 |         213730 |     3022857090 |
| wait/synch/rwlock/innodb/trx_sys_lock         |   18592602 |   5270410352025 |          42210 |         283410 |     1622682045 |
| wait/synch/rwlock/sql/LOCK_grant              |   16276408 |   3502306526580 |          51255 |         215070 |      892225935 |
| wait/synch/rwlock/innodb/btr_search_latch     |   16198467 |   4144473779535 |          27135 |         255605 |     1048297410 |
| wait/synch/rwlock/sql/MDL_lock::rwlock        |    2325412 |    524010714480 |          54270 |         225120 |       36433260 |
| wait/synch/mutex/mysys/BITMAP::mutex          |    2325268 |    409667867595 |          42210 |         175875 |      908214480 |
| wait/synch/mutex/innodb/trx_mutex             |    1163261 |     32488536510 |          24120 |          27805 |        7419915 |
| wait/synch/mutex/sql/MDL_map::mutex           |    1162780 |    274705866855 |          36180 |         236175 |      133772535 |
| wait/synch/mutex/sql/LOCK_plugin              |    1162673 |    212769433395 |          33165 |         182910 |      791328960 |
| wait/synch/mutex/innodb/lock_mutex            |    1162633 |    145945057815 |          39195 |         125290 |       14173515 |
| wait/synch/mutex/innodb/log_sys_mutex         |       7757 |      2441752020 |          27135 |         314565 |     1239502680 |
| wait/synch/mutex/innodb/os_mutex              |       2490 |       271684665 |          30150 |         108875 |       17423685 |
| wait/synch/mutex/innodb/fil_system_mutex      |       1402 |       146559150 |          24120 |         104520 |        5538555 |
| wait/synch/mutex/innodb/flush_list_mutex      |        607 |        96362415 |          33165 |         158455 |         346725 |
| wait/io/file/innodb/innodb_log_file           |        600 |   2693980326195 |       23094900 |     4489967145 |   819050374485 |
| wait/synch/mutex/innodb/buf_pool_mutex        |        516 |        40862295 |          27135 |          79060 |         521595 |
| wait/synch/mutex/innodb/log_flush_order_mutex |        473 |        49503285 |          33165 |         104520 |         397980 |
| wait/synch/mutex/innodb/lock_wait_mutex       |        428 |        71949960 |          63315 |         167835 |        1706490 |
+-----------------------------------------------+------------+-----------------+----------------+----------------+----------------+
25 rows in set (1.79 sec)

Top waits, by time

mysql> select * from performance_schema.events_waits_summary_global_by_event_name
    ->   where count_star>0 order by sum_timer_wait desc limit 25;
+-------------------------------------------+------------+-----------------+----------------+----------------+----------------+
| EVENT_NAME                                | COUNT_STAR | SUM_TIMER_WAIT  | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT |
+-------------------------------------------+------------+-----------------+----------------+----------------+----------------+
| wait/io/table/sql/handler                 |  482492695 | 583215424951365 |          42210 |        1208680 |     7913678535 |
| wait/synch/mutex/innodb/srv_conc_mutex    |  265080324 | 142377797253015 |          30150 |         537005 |     1862766495 |
| wait/lock/table/sql/handler               |   32553724 |  20576707870950 |          99495 |         631810 |     1083789990 |
| wait/synch/mutex/sql/LOCK_open            |   32553870 |   9359193544965 |          30150 |         287430 |     1080446355 |
| wait/synch/mutex/mysys/THR_LOCK::mutex    |   32553724 |   6964485402105 |          30150 |         213730 |     3022857090 |
| wait/synch/mutex/sql/THD::LOCK_thd_data   |  122077261 |   6519294560295 |          30150 |          53265 |     1072076715 |
| wait/synch/mutex/innodb/read_view_mutex   |   32554342 |   6146010024300 |          24120 |         188605 |     1011083265 |
| wait/synch/rwlock/innodb/hash table locks |   38409624 |   5420427797475 |          27135 |         141035 |      360901530 |
| wait/synch/rwlock/innodb/trx_sys_lock     |   18592651 |   5270414135850 |          42210 |         283410 |     1622682045 |
| wait/synch/rwlock/innodb/btr_search_latch |   16198467 |   4144473779535 |          27135 |         255605 |     1048297410 |
| wait/synch/rwlock/sql/LOCK_grant          |   16276408 |   3502306526580 |          51255 |         215070 |      892225935 |
| wait/io/file/innodb/innodb_log_file       |        600 |   2693980326195 |       23094900 |     4489967145 |   819050374485 |
| wait/synch/rwlock/sql/MDL_lock::rwlock    |    2325412 |    524010714480 |          54270 |         225120 |       36433260 |
| wait/synch/mutex/mysys/BITMAP::mutex      |    2325268 |    409667867595 |          42210 |         175875 |      908214480 |
| wait/synch/mutex/sql/MDL_map::mutex       |    1162780 |    274705866855 |          36180 |         236175 |      133772535 |
| wait/synch/mutex/sql/LOCK_plugin          |    1162673 |    212769433395 |          33165 |         182910 |      791328960 |
| wait/synch/mutex/innodb/lock_mutex        |    1162633 |    145945057815 |          39195 |         125290 |       14173515 |
| wait/synch/mutex/innodb/trx_mutex         |    1163457 |     32497638795 |          24120 |          27805 |        7419915 |
| wait/io/file/innodb/innodb_data_file      |          8 |     23031533745 |       54936315 |     2878941425 |     7430889600 |
| wait/synch/rwlock/innodb/checkpoint_lock  |          4 |      5048894880 |         250245 |     1262223720 |     2745088155 |
| wait/synch/mutex/innodb/log_sys_mutex     |       8095 |      2471021640 |          27135 |         305185 |     1239502680 |
| wait/synch/mutex/sql/LOCK_thread_count    |         31 |       830150100 |          30150 |       26778895 |      235748880 |
| wait/synch/mutex/innodb/os_mutex          |       2539 |       276339825 |          30150 |         108540 |       17423685 |
| wait/synch/mutex/innodb/fil_system_mutex  |       1451 |       149064615 |          24120 |         102510 |        5538555 |
| wait/synch/mutex/innodb/flush_list_mutex  |        704 |       111835395 |          33165 |         158790 |         346725 |
+-------------------------------------------+------------+-----------------+----------------+----------------+----------------+
25 rows in set (1.79 sec)

Total number of stage events

mysql> select sum(count_star), sum(sum_timer_wait)
    ->   from performance_schema.events_stages_summary_global_by_event_name;
+-----------------+---------------------+
| sum(count_star) | sum(sum_timer_wait) |
+-----------------+---------------------+
|       244153447 |    2263079088326000 |
+-----------------+---------------------+
1 row in set (0.00 sec)

Top stages, by count

mysql> select * from performance_schema.events_stages_summary_global_by_event_name
    ->   where count_star>0 order by count_star desc limit 25;
+--------------------------------+------------+-----------------+----------------+----------------+----------------+
| EVENT_NAME                     | COUNT_STAR | SUM_TIMER_WAIT  | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT |
+--------------------------------+------------+-----------------+----------------+----------------+----------------+
| stage/sql/init                 |   34879169 | 157347558824000 |        2374000 |        4511000 |     1685373000 |
| stage/sql/cleaning up          |   18602306 |  25263461317000 |         628000 |        1358000 |     2278265000 |
| stage/sql/closing tables       |   18602129 | 384870074281000 |         628000 |       20689000 |     4374172000 |
| stage/sql/query end            |   18602129 |  23815226118000 |         698000 |        1280000 |     1674268000 |
| stage/sql/checking permissions |   16276937 |  37278853417000 |        1327000 |        2290000 |     1734543000 |
| stage/sql/Opening tables       |   16276936 |  91344126412000 |        3352000 |        5611000 |     1641511000 |
| stage/sql/executing            |   16276863 |  15264844128000 |         628000 |         937000 |     1097577000 |
| stage/sql/statistics           |   16276863 | 325924010836000 |        6495000 |       20023000 |     3296152000 |
| stage/sql/Sending data         |   16276863 | 548279995436000 |        2794000 |       33684000 |     6805393000 |
| stage/sql/System lock          |   16276863 |  68298443425000 |        2444000 |        4196000 |     1102675000 |
| stage/sql/preparing            |   16276863 |  40997524468000 |        1676000 |        2518000 |     1090453000 |
| stage/sql/optimizing           |   16276863 |  31599629407000 |        1606000 |        1941000 |     1093176000 |
| stage/sql/end                  |   16276863 |  17257774496000 |         768000 |        1060000 |     1070408000 |
| stage/sql/Sorting result       |    2325266 | 270070871391000 |        7264000 |      116146000 |     3214855000 |
| stage/sql/removing tmp table   |    1162634 |  15392269553000 |        9359000 |       13239000 |      904741000 |
| stage/sql/freeing items        |    1162634 |  15041751826000 |        3143000 |       12937000 |     1106237000 |
| stage/sql/Creating tmp table   |    1162633 |  14681955665000 |        9568000 |       12628000 |     1092898000 |
| stage/sql/Copying to tmp table |    1162633 | 180350717326000 |      116008000 |      155122000 |     8074224000 |
+--------------------------------+------------+-----------------+----------------+----------------+----------------+
18 rows in set (0.00 sec)

Top stages, by time

mysql> select * from performance_schema.events_stages_summary_global_by_event_name
    ->   where count_star>0 order by sum_timer_wait desc limit 25;
+--------------------------------+------------+-----------------+----------------+----------------+----------------+
| EVENT_NAME                     | COUNT_STAR | SUM_TIMER_WAIT  | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT |
+--------------------------------+------------+-----------------+----------------+----------------+----------------+
| stage/sql/Sending data         |   16276863 | 548279995436000 |        2794000 |       33684000 |     6805393000 |
| stage/sql/closing tables       |   18602129 | 384870074281000 |         628000 |       20689000 |     4374172000 |
| stage/sql/statistics           |   16276863 | 325924010836000 |        6495000 |       20023000 |     3296152000 |
| stage/sql/Sorting result       |    2325266 | 270070871391000 |        7264000 |      116146000 |     3214855000 |
| stage/sql/Copying to tmp table |    1162633 | 180350717326000 |      116008000 |      155122000 |     8074224000 |
| stage/sql/init                 |   34879169 | 157347558824000 |        2374000 |        4511000 |     1685373000 |
| stage/sql/Opening tables       |   16276936 |  91344126412000 |        3352000 |        5611000 |     1641511000 |
| stage/sql/System lock          |   16276863 |  68298443425000 |        2444000 |        4196000 |     1102675000 |
| stage/sql/preparing            |   16276863 |  40997524468000 |        1676000 |        2518000 |     1090453000 |
| stage/sql/checking permissions |   16276937 |  37278853417000 |        1327000 |        2290000 |     1734543000 |
| stage/sql/optimizing           |   16276863 |  31599629407000 |        1606000 |        1941000 |     1093176000 |
| stage/sql/cleaning up          |   18602306 |  25263461317000 |         628000 |        1358000 |     2278265000 |
| stage/sql/query end            |   18602129 |  23815226118000 |         698000 |        1280000 |     1674268000 |
| stage/sql/end                  |   16276863 |  17257774496000 |         768000 |        1060000 |     1070408000 |
| stage/sql/removing tmp table   |    1162634 |  15392269553000 |        9359000 |       13239000 |      904741000 |
| stage/sql/executing            |   16276863 |  15264844128000 |         628000 |         937000 |     1097577000 |
| stage/sql/freeing items        |    1162634 |  15041751826000 |        3143000 |       12937000 |     1106237000 |
| stage/sql/Creating tmp table   |    1162633 |  14681955665000 |        9568000 |       12628000 |     1092898000 |
+--------------------------------+------------+-----------------+----------------+----------------+----------------+
18 rows in set (0.00 sec)

Total number of statement events

mysql> select sum(count_star), sum(sum_timer_wait)
    ->   from performance_schema.events_statements_summary_global_by_event_name;
+-----------------+---------------------+
| sum(count_star) | sum(sum_timer_wait) |
+-----------------+---------------------+
|        18602306 |    2293686457711000 |
+-----------------+---------------------+
1 row in set (0.00 sec)

Top statements, by count

mysql> select event_name, count_star, sum_timer_wait
         from performance_schema.events_statements_summary_global_by_event_name
         where count_star>0 order by count_star desc limit 25;
+---------------------------------+------------+------------------+
| event_name                      | count_star | sum_timer_wait   |
+---------------------------------+------------+------------------+
| statement/com/Execute           |   17439495 | 2267764442472000 |
| statement/sql/begin             |    1162633 |   25914627564000 |
| statement/com/Prepare           |         88 |       3041856000 |
| statement/com/Close stmt        |         80 |        729649000 |
| statement/com/Quit              |          9 |         47910000 |
| statement/sql/show_table_status |          1 |       3568260000 |
+---------------------------------+------------+------------------+
6 rows in set (0.00 sec)

Top statements, by time

mysql> select event_name, count_star, sum_timer_wait
         from performance_schema.events_statements_summary_global_by_event_name
         where count_star>0 order by sum_timer_wait desc limit 25;
+---------------------------------+------------+------------------+
| event_name                      | count_star | sum_timer_wait   |
+---------------------------------+------------+------------------+
| statement/com/Execute           |   17439495 | 2267764442472000 |
| statement/sql/begin             |    1162633 |   25914627564000 |
| statement/sql/show_table_status |          1 |       3568260000 |
| statement/com/Prepare           |         88 |       3041856000 |
| statement/com/Close stmt        |         80 |        729649000 |
| statement/com/Quit              |          9 |         47910000 |
+---------------------------------+------------+------------------+
6 rows in set (0.00 sec)

Timer statistics

mysql> select * from performance_schema.setup_timers;
+-----------+------------+
| NAME      | TIMER_NAME |
+-----------+------------+
| wait      | CYCLE      |
| stage     | NANOSECOND |
| statement | NANOSECOND |
+-----------+------------+
3 rows in set (0.00 sec)

mysql> select * from performance_schema.performance_timers;
+-------------+-----------------+------------------+----------------+
| TIMER_NAME  | TIMER_FREQUENCY | TIMER_RESOLUTION | TIMER_OVERHEAD |
+-------------+-----------------+------------------+----------------+
| CYCLE       |      2982179104 |                1 |             36 |
| NANOSECOND  |      1000000000 |                1 |           1404 |
| MICROSECOND |         1000000 |                1 |           1422 |
| MILLISECOND |            1034 |                1 |           1431 |
| TICK        |             104 |                1 |            774 |
+-------------+-----------------+------------------+----------------+
5 rows in set (0.00 sec)

Interpreting results

Let's do some basic math to understand what this means ...
The total number of wait events collected is "1098658477". That's a big number. In a more readable form, it reads "1,098,658,477". That still is a big number. In plain English, it is 1 Billion, with a B. This was for a 5 minutes (300 sec) run, and all the statistics were truncated just before the sysbench load, so only the load itself is counted here.
Events collection rates (in number or events per seconds) are then:

3662194 waits/sec, or 3.6 M waits/sec
813844 stages/sec, or 813 K stages/sec
62007 statements/sec, or 62 K statements/sec

These numbers are actually mind blowing. Keep in mind that there is no sampling involved. When each and every one of these events is collected, the performance schema time the event, and add statistics to the proper counter for the object and the thread involved, so that statistics per object, per table, per thread, etc, are available.
For what it is worth, we also have 13 stages per statements, and 59 waits per statements, on average.
Based on the top wait events, we can verify that this workload is indeed CPU bound and read only, since no file io are involved
An important point to note is that "wait/io/table" here represents table IO, as seen at the SQL layer / storage engine interface. A table IO event will time the code executed overall inside the storage engine, so it basically is the sum of the CPU time spent inside the engine and the waits blocking the storage engine. Having a big number reported in table io is expected.
Given the rate of collection for this load, 3.6 M waits/sec, the overhead in the performance schema is not caused by a big, rarely invoked, slow function, but rather by very frequent calls to very short functions.
With such a number of events, timing events cause a huge number of timer invocations.
For waits, the timer used is the cycle timer (see table performance_schema.setup_timers). For the CYCLE timer, the timer overhead alone, expressed in cycles, is 36 for this machine (see table performance_schema.performance_timers).
With 1098658477 events, 2 timer invocation per event, that makes 79103410344 cycles spent timing waits.
Given a CPU frequency of 2982179104 or 2.9 GHz, this represents 26.5224 seconds of CPU in a 300 seconds benchmark.
During this 300 sec benchmark, top reports that mysqld (the server process) consumes 723 percents of CPU (this is a 8 core, but sysbench alone eats 70 percent), so the overall CPU used by the server is 2169 sec. An overhead of 26 sec / 2169 sec represents 1.2 percent of CPU power, just timing waits.
Let's do the same math for stages and statements. They both use the same timer, nanoseconds.
With 244153447 stages, 2 timers per stage, 1404 cycles per timer, we have 685582879176 cycles or 229.8933 sec of CPU.
With 18602306 statements, 2 timers per stage, 1404 cycles per timer, we have 52235275248 cycles or 17.5158 sec of CPU.
Ouch ! That makes 229.8933+17.5158 or 247.4091 seconds of CPU calling the nanosecond timer. In terms of overhead, 247 seconds taken from 2169 represents 11.4 percent of mysqld spent for the nanosecond timer alone.
The bottom line is, this usage of the nanosecond timer is killing the performance. Now, why ? Intuitively, one would think that collecting and timing waits at a rate of 3.6 M / sec is more consuming than doing the same thing for stages and statements, at a rate of 813 K / sec and 62 K / sec respectively.
The root cause for this bad performances is hidden in plain sight, exposed by the performance_timers table.

mysql> select * from performance_schema.performance_timers;
+-------------+-----------------+------------------+----------------+
| TIMER_NAME  | TIMER_FREQUENCY | TIMER_RESOLUTION | TIMER_OVERHEAD |
+-------------+-----------------+------------------+----------------+
| CYCLE       |      2982179104 |                1 |             36 |
| NANOSECOND  |      1000000000 |                1 |           1404 | <-- Really ? This is HUGE !
| MICROSECOND |         1000000 |                1 |           1422 |
| MILLISECOND |            1034 |                1 |           1431 |
| TICK        |             104 |                1 |            774 |
+-------------+-----------------+------------------+----------------+
5 rows in set (0.00 sec)

This is actually the first time I find a machine where the overhead for the NANOSECOND, MICROSECOND and MILLISECOND timer is so high
Some details about the machine used ...

malff@tyr03:~>more /proc/cpuinfo
processor       : 0
vendor_id       : GenuineIntel
cpu family      : 6
model           : 23
model name      : Intel(R) Xeon(R) CPU           E5450  @ 3.00GHz
stepping        : 6
cpu MHz         : 2992.227
cache size      : 6144 KB
...
There are 8 cores total, all identical.

Timer details

malff@tyr03:withpfs>./unittest/mysys/my_rdtsc-t
1..11
# ----- Routine ---------------
# myt.cycles.routine          :             2 <-- RDTSC
# myt.nanoseconds.routine     :            11 <-- CLOCK_GETTIME
# myt.microseconds.routine    :            13
# myt.milliseconds.routine    :            18
# myt.ticks.routine           :            17
# ----- Frequency -------------
# myt.cycles.frequency        :    2986029850
# myt.nanoseconds.frequency   :    1000000000
# myt.microseconds.frequency  :       1000000
# myt.milliseconds.frequency  :          1035
# myt.ticks.frequency         :           104
# ----- Resolution ------------
# myt.cycles.resolution       :             1
# myt.nanoseconds.resolution  :             1
# myt.microseconds.resolution :             1
# myt.milliseconds.resolution :             1
# myt.ticks.resolution        :             1
# ----- Overhead --------------
# myt.cycles.overhead         :            36
# myt.nanoseconds.overhead    :          1404 <-- Ouch
# myt.microseconds.overhead   :          1422
# myt.milliseconds.overhead   :          1431
# myt.ticks.overhead          :           765
...

malff@tyr03:~>uname -a
Linux tyr03 2.6.32-100.28.17.el6.x86_64 #1 SMP Mon May 23 04:37:16 EDT 2011 x86_64 x86_64 x86_64 GNU/Linux

To have a point of comparison, the following are taken from a 4 core 8 Gb machine (my now several years old desktop)

mysql> select * from performance_timers;
+-------------+-----------------+------------------+----------------+
| TIMER_NAME  | TIMER_FREQUENCY | TIMER_RESOLUTION | TIMER_OVERHEAD |
+-------------+-----------------+------------------+----------------+
| CYCLE       |      2831810945 |                1 |             34 | <-- Marc desktop
| NANOSECOND  |      1000000000 |                1 |            102 | <-- Marc desktop
| MICROSECOND |         1000000 |                1 |            111 |
| MILLISECOND |            1037 |                1 |            127 |
| TICK        |             105 |                1 |            493 |
+-------------+-----------------+------------------+----------------+
5 rows in set (0.00 sec)

malff@linux-8edv:~> more /proc/cpuinfo
processor       : 0
vendor_id       : GenuineIntel
cpu family      : 6
model           : 23
model name      : Intel(R) Core(TM)2 Quad CPU    Q9550  @ 2.83GHz
stepping        : 10
cpu MHz         : 2833.000
cache size      : 6144 KB
...

malff@linux-8edv:~> uname -a
Linux linux-8edv 2.6.37.6-0.5-desktop #1 SMP PREEMPT 2011-04-25 21:48:33 +0200 x86_64 x86_64 x86_64 GNU/Linux

Both the lab machine (tyr03) and my desktop have an Intel processor, and they happen to even be of the same family (6) and model (23), only the model name differ (Xeon E5450 vs Core 2 Quad Q9550).
The CYCLE timer is implemented using RDTSC in both cases, and the overheads measured are very similar, with 36 and 34 cycles.
Implementation of the CYCLE timer is in my_timer_cycles() in mysys.
Details about the RDTSC instruction can be found in the processor spec, page C-32 (722). Family 6 model 23 is CPUID 06_17H.
For the nanoseconds timer, it is implemented in my_timer_nanoseconds() in both cases with a call to clock_gettime(CLOCK_REALTIME), in both cases on Linux 2.6.
The lab machine has a Linux 2.6.32 kernel, while the desktop has Linux 2.6.37.
The exact root cause of this huge difference in overhead (1404 vs 102) has not been explained yet.
In the mean time, given the cost of the nanosecond timer, maybe the performance schema overhead could be reduced by not timing events or timing them with the cycle timer.
This is worth a new set of measurements, still for mysql-trunk ...

The trunk benchmark, revisited

With performance schema	global (not timed)		thread (not timed)		thread (all cycles)
Connections	TPS	Overhead (%)	TPS	Overhead (%)	TPS	Overhead (%)
1	757	6.20	752	6.82	739	8.43
2	1362	5.35	1327	7.78	1293	10.15
4	2441	6.48	2385	8.62	2328	10.80
8	4490	11.72	4384	13.80	4165	18.11
12	4489	10.88	4411	12.43	4220	16.22
16	4489	10.90	4436	11.95	4284	14.97
32	4336	10.67	4232	12.81	3992	17.76
48	4244	10.88	4111	13.67	3876	18.61
64 (*)	3977	3.42	3040	26.18	2527	38.64
128 (*)	2830	20.01	1972	44.26	1897	46.38
256 (*)	1683	3.61	1671	4.30	1629	6.70

(*) Noise at 64/128/256 still makes results unstable.
These new results, using a different configuration but still the same code in mysql-trunk, are interesting for two reasons.
First, it confirms the effect of timing alone. Timing a lot or events takes time, and timing a lot of events with a bad timer makes things really worse. Using the CYCLE timer for waits, stages and statements here, in the "thread (all cycle)" configuration, helped to regain 5 percent.
Second, it shows that once all timers are removed from the code path, the overhead is still in the ~11% range for the global only consumer. The code probably has room for optimizations, see the next section.

The tuning benchmark

The current code in mysql-trunk (at time of writing) can be improved and optimized, in an effort to reduce the overhead even more. A long list of different tweaks, changes, fixes ... has been the topic of a lot of research and tuning lately. Instead of going through each change, the cumulated changes are available in a different branch, in mysql-trunk-pfs-tuning.
This branch is available on launchpad for the technically curious. A bit of warning: it is used only for prototyping and benchmarking to see what code does (or does not) work better. It may not even build for your platform. Do not use this in production.

mysql-trunk-pfs-tuning	without pfs (baseline)		disabled		off		global (not timed)		global (timed)		thread (not timed)		thread (timed)		thread (all cycles)
Connections	TPS	Overhead (%)	TPS	Overhead (%)	TPS	Overhead (%)	TPS	Overhead (%)	TPS	Overhead (%)	TPS	Overhead (%)	TPS	Overhead (%)	TPS	Overhead (%)
1	819	0	788	3.79	778	5.01	771	5.86	684	16.48	755	7.81	678	17.22	744	9.16
2	1431	0	1405	1.82	1383	3.35	1354	5.38	1212	15.30	1329	7.13	1199	16.21	1307	8.67
4	2598	0	2567	1.19	2522	2.93	2452	5.62	2163	16.74	2394	7.85	2141	17.59	2371	8.74
8	4949	0	4980	-0.63	4799	3.03	4635	6.34	4105	17.05	4445	10.18	4061	17.94	4367	11.76
12	4948	0	4936	0.24	4759	3.82	4616	6.71	4112	16.90	4478	9.50	4076	17.62	4390	11.28
16	4944	0	4931	0.26	4754	3.84	4601	6.94	4131	16.44	4476	9.53	4077	17.54	4384	11.33
32	4764	0	4753	0.23	4596	3.53	4455	6.49	3984	16.37	4286	10.03	3945	17.19	4200	11.84
48	4682	0	4681	0.02	4519	3.48	4377	6.51	3908	16.53	4202	10.25	3860	17.56	4111	12.20
64 (*)	4148	0	3881	6.44	2583	37.73	3653	11.93	2916	29.70	3082	25.70	2863	30.98	3496	15.72
128 (*)	3659	0	2034	44.41	2024	44.68	1983	45.80	1924	47.42	2011	45.04	1886	48.46	1939	47.01
256 (*)	2514	0	1746	30.55	1732	31.11	1690	32.78	1632	35.08	1724	31.42	1640	34.77	1646	34.53

We can see some progress:

When all consumers are off, the overhead is in the 3% to 4% range, which is much better compared to ~8%.
The "global (timed)" and "thread (timed)" configurations still have an important overhead because they use the nanosecond timer, but this overhead decreased, which shows the benefits of the code optimizations.
The "thread (all cycles)" overhead is in the ~11% range.

Now that's better. With every instruments enabled and timed, for all waits stages and statements, and with statistics collected globally and per thread, 11% for this wealth of information is not so bad. Especially knowing that the overhead can be changed back to 3-4% at anytime, since all this configuration can be changed dynamically.

Take aways

The most important facts found here:

Timer performances do vary between platforms, and have a great impact. Check the timer performance itself with table performance_schema.performance_timers before deciding to time events.
The performance schema code itselt can be improved. Current optimizations are available in mysql-trunk-pfs-tuning, as a preview.
Overall, the performance schema captures instrumentation events at a very high rate. Limiting the choice of consumers and instruments to use is a good way to cut down the overhead.
There is no magic formula that says "overhead is X". Overhead depends so much on the work load, the hardware, the configuration, etc, that the best answer is "it depends".

The real question about overhead, given that it depends so much, really boils down to "What is the overhead for my application ?". The best way to find that is to try it ...
-- Marc Alff

Performance schema faq #2, what are all these server startup options ?

noreply@blogger.com (Marc Alff) — Wed, 20 Apr 2011 05:58:00 +0000

One of the first thing to do to use the performance schema is to add "performance_schema" in the my.cnf file.

This typically prompts a long list of question from users, as there is a lot to know here.

This article attempts to cover more specifically this area.

It is long, but (I hope) also worth it, so keep reading ;)

Also, since some new performance schema features have been advertised in the mysql labs, this article also covers some never seen before parts of the performance schema.

Really, keep reading ;)

Q: Why do I even have to do anything ? Can the performance schema not be enabled by default ?

No. It could, but this is not a good choice.

Let's face it, some users do not even read the documentation.

If someone is not even aware that the performance schema feature exists in 5.5 (or 5.6), turning it on by default for a new 5.5 installation, or even worse, turning it on by default after a 5.1 to 5.5 upgrade is not going to do anyone any good.

The performance schema will collect data, eat memory, spend time computing all kinds of aggregates that can be viewed from nice tables in the new performance_schema database ... that nobody will ever select from.

The performance schema is a new feature, and it is also an optional feature.

Forcing to use it by default would only irritate people doing a migration to a new release, which is never the best time to try new features, no matter how good they are. When doing a migration, the concern is typically to be back in production as soon as possible, and anything that can be sorted out later should be done ... later.

So, the long answer is that, by default, the performance schema is disabled. This is a conscious, conservative, choice.

Q: Ok, so I need to turn P_S on. Now, why are there so many other parameters ?

By the way, "P_S" is the acronym that is used internally for the performance schema, which mirrors "I_S" for the information_schema. This is not to be confused with "PS", which stands for prepared statements.

So yes, the performance schema has in fact many different startup parameters, which can also be seen as server variables.

Looking at mysql-5.6-labs-performance-schema for example:

mysql> show variables like "performance_schema%";
+--------------------------------------------------------+-------+
| Variable_name                                          | Value |
+--------------------------------------------------------+-------+
| performance_schema                                     | ON    |
| performance_schema_events_stages_history_long_size     | 1000  |
| performance_schema_events_stages_history_size          | 10    |
| performance_schema_events_statements_history_long_size | 1000  |
| performance_schema_events_statements_history_size      | 10    |
| performance_schema_events_waits_history_long_size      | 10000 |
| performance_schema_events_waits_history_size           | 10    |
| performance_schema_max_cond_classes                    | 80    |
| performance_schema_max_cond_instances                  | 1000  |
| performance_schema_max_file_classes                    | 50    |
| performance_schema_max_file_handles                    | 32768 |
| performance_schema_max_file_instances                  | 10000 |
| performance_schema_max_hosts                           | 100   |
| performance_schema_max_mutex_classes                   | 200   |
| performance_schema_max_mutex_instances                 | 5000  |
| performance_schema_max_rwlock_classes                  | 30    |
| performance_schema_max_rwlock_instances                | 5000  |
| performance_schema_max_stage_classes                   | 100   |
| performance_schema_max_statement_classes               | 165   |
| performance_schema_max_table_handles                   | 1000  |
| performance_schema_max_table_instances                 | 500   |
| performance_schema_max_thread_classes                  | 50    |
| performance_schema_max_thread_instances                | 200   |
| performance_schema_max_user_hosts                      | 100   |
| performance_schema_max_users                           | 100   |
| performance_schema_setup_actors_size                   | 100   |
| performance_schema_setup_objects_size                  | 100   |
+--------------------------------------------------------+-------+
27 rows in set (0.00 sec)

The reason why these parameters even exist is the result of significant architectural choices, which in turn are driven by very hard constraints on the system, so let's look at some details here ...

Problem: Monitoring a server should not freeze the server.

In other words, executing select * from performance_schema. should not cause other sessions to stop working.

That includes precisely the application connection that is currently executing instrumented code, which in turn feeds the very performance_schema table being read.

This has nothing to do with how the data is exposed, this would be the same for a performance_schema table, an information_schema table, a SHOW command.

This has to do with how the statistics are represented internally.

A way to implement instrumentation that is known to cause problems is for example the implementation of the SHOW PROCESSLIST statement.

A monitoring application that execute SHOW PROCESSLIST will partially freeze the server, preventing new sessions to connect, or old sessions to disconnect. This is because SHOW PROCESSLIST will lock a critical server mutex, LOCK_thread_count, for the time it takes to loop through all the threads.

See Bug#42930, for an example of similar problems.

Solution: the performance schema is lock-less

One of the goal of the performance schema implementation is to never be in that situation. To avoid that the monitoring application causes other threads to choke, the best way is to have *no* lock shared between the monitoring connection and the instrumented code.

No matter how it's done, the performance schema instrumentation should not use locks.

Problem: Monitoring a server should not crash the server

Ok, so let's pretend the SHOW PROCESSLIST implementation used as example here is not locking LOCK_thread_count at all. Developers to not add mutex locks in the code just for pleasure, they add locks to enforce data integrity and prevent bad things to happen.

The data protected by the lock is a linked list of all the connected sessions, where each session is represented by a THD object. The list is global to the server, and each THD is allocated and freed when users connect / disconnect from the server.

Without the lock, very soon, the execution (or this now hypothetical) SHOW PROCESSLIST statement would crash when using a bogus THD record, typically when looking at freed (and worse already reused) memory for a thread that just disconnected.

What this points to is that, when using code that is lock-free, using memory that is dynamically allocated and freed by other threads is going to be a real challenge, since there are no more protections in place.

Solution: the performance schema is not using dynamic memory

Since using malloc / free cause problem, then ... the performance schema code does not use malloc and free.

Instead, memory is allocated once and for all at server startup, and is freed when the server shuts down. During normal operations (everything in between), the performance schema memory is considered "static".

This has actually significant benefits, since a pointer to a region of this "performance schema static memory" is always guaranteed to point to valid memory.

Since using a linked list also causes problems, then ... the performance schema code prefers to use arrays.

The net result is that all the performance schema memory used to keep track of statistics in fact consist of arrays, allocated once.

Different arrays are used for different things. The size of these various arrays is given by the various server startup parameters.

Still looking at the SHOW PROCESSLIST statement, the performance schema instrumentation is in fact exposing a table named performance_schema.threads.

The data for the threads table is stored in an array, and the size of this array is given by the parameter performance_schema_max_thread_instances.

The benefit: select * from performance_schema.threads is lock-less. The inconvenience: performance_schema_max_thread_instances must come from somewhere.

Q: Ok, so there are sizing parameters. But so many of them ?

Every new instrumentation (mutex, file io, table lock, statements, ...) instruments different parts of the code, and needs different types of performance schema buffers. This is actually a good property, because this allows to control each type of instrumentation -- and how much memory to use for it -- independently.

So, each time a new buffer is used, how to size it is exposed as a parameter.

Q: How about default values that work out of the box ?

Given the wide range of deployments where the MySQL server can be installed, from a laptop that will ever see 2 clients and 10 tables for a demo, to a production scale server that can support a very large web farm and data model, whatever "default value" is written in the code will never satisfy everybody.

That is a given, there is no way around it.

If the default values are too big, starting a server on a small machine will consume too much memory, giving the impression that the performance schema is a "monster" and can not be used, say, in development when testing an application.

If the default values are too small, the performance schema will miss events at runtime, and will not be able to collect data for everything, giving the impression that it is broken and does not work.

The default values that are currently in the code should be reasonable enough so that running a server with the performance schema gives meaningful results, so that people can get a first impression of what the performance schema can do, out of the box, by changing just *one* parameter (to enable the performance schema).

Past that, when deploying the performance schema in a real production environment, or to do serious benchmarks, taking a closer look at the configuration itself will unfortunately be a necessary step at some point.

That being said, the picture is actually not so complex, since not so many parameters really need to be changed.

Q: What are the parameters I can just forget about ?

mysql> show variables like "performance_schema_%_classes";
+------------------------------------------+-------+
| Variable_name                            | Value |
+------------------------------------------+-------+
| performance_schema_max_cond_classes      | 80    |
| performance_schema_max_file_classes      | 50    |
| performance_schema_max_mutex_classes     | 200   |
| performance_schema_max_rwlock_classes    | 30    |
| performance_schema_max_stage_classes     | 100   |
| performance_schema_max_statement_classes | 165   |
| performance_schema_max_thread_classes    | 50    |
+------------------------------------------+-------+
7 rows in set (0.00 sec)

All these parameters follow the same principle, so let's take for example performance_schema_max_mutex_classes. What this value of 200 means, is that there is room by default for 200 different mutex instrumentation.

The number of mutex instrumentation varies as more and more code is instrumented, which varies by releases, and the way to find out what is currently instrumented is just to look at the setup_instruments table.

mysql> select count(*) from performance_schema.setup_instruments where name like "wait/synch/mutex/%";
+----------+
| count(*) |
+----------+
|      125 |
+----------+
1 row in set (0.00 sec)

125 of the 200 mutex classes slots are used, so there is still room left: everything is fine here.

There is a catch here, as both the 125 and the 200 comes from code shipped with the server ... so, if one value exceeds the other, it will be adjusted anyway.

The only reason this parameter is even exposed in the first place, is that, if a third party plugin also contains some performance schema instrumentation for mutex classes (and in this case, for a lot of them, since there are still 75 slots left), loading this third party plugin in the server will cause starvation, so the max_mutex_class setting will need to be adjusted.

And no, we can not predict how many mutexes a third party will use.

In practice, this settings can safely be forgotten. The day it will be needed, it will be there, and people might appreciate then the fact that not everything is hard coded.

Q: What are the parameters that are not really sensitive, where the value has little effect on performance and/or functionality ?

mysql> show variables like "performance_schema_%_history%_size";
+--------------------------------------------------------+-------+
| Variable_name                                          | Value |
+--------------------------------------------------------+-------+
| performance_schema_events_stages_history_long_size     | 1000  |
| performance_schema_events_stages_history_size          | 10    |
| performance_schema_events_statements_history_long_size | 1000  |
| performance_schema_events_statements_history_size      | 10    |
| performance_schema_events_waits_history_long_size      | 10000 |
| performance_schema_events_waits_history_size           | 10    |
+--------------------------------------------------------+-------+
6 rows in set (0.01 sec)

These settings affect how many records are kept in the various history buffers, so they have a direct impact on the amount of memory used. The time is takes to write a new record in history (and drop the oldest one) is fixed, and does not depends on the history size, to this sizing parameters do not impact runtime performances.

mysql> show variables like "performance_schema_setup_%_size";
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| performance_schema_setup_actors_size  | 100   |
| performance_schema_setup_objects_size | 100   |
+---------------------------------------+-------+
2 rows in set (0.00 sec)

This directly control the size of corresponding tables setup_actor and setup_object. How many entries are in these tables is directly up to the DBA. The performance schema uses a hash index internally, plus some level of cache on top, so that the number of records in these tables has no effect at all on performances.

mysql> show variables like "performance_schema_max_user%";
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| performance_schema_max_user_hosts | 100   |
| performance_schema_max_users      | 100   |
+-----------------------------------+-------+
2 rows in set (0.01 sec)

mysql> show variables like "performance_schema_max_host%";
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| performance_schema_max_hosts | 100   |
+------------------------------+-------+
1 row in set (0.00 sec)

For the performance_schema.users, performance_schema.hosts, and performance_schema.user_hosts tables, the performance schema also uses a hash index by user name and/or host name, so the total size of these tables is not going to significantly impact the time to look up a given record, which is done once when a session connects to the server.

Sizing these tables according to how many different users and or machines are connecting to the database server, however, will have a direct impact on whether or not the performance schema can keep statistics for everybody.

Q: What are the parameters I should really pay attention to then ?

mysql> show variables like "performance_schema_%_instances";
+-----------------------------------------+-------+
| Variable_name                           | Value |
+-----------------------------------------+-------+
| performance_schema_max_cond_instances   | 1000  |
| performance_schema_max_file_instances   | 10000 |
| performance_schema_max_mutex_instances  | 5000  |
| performance_schema_max_rwlock_instances | 5000  |
| performance_schema_max_table_instances  | 500   |
| performance_schema_max_thread_instances | 200   |
+-----------------------------------------+-------+
6 rows in set (0.01 sec)

mysql> show variables like "performance_schema_%_handles";
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| performance_schema_max_file_handles  | 32768 |
| performance_schema_max_table_handles | 1000  |
+--------------------------------------+-------+
2 rows in set (0.00 sec)

The most critical parameters that affect how the performance schema is performing are the parameters that control the different buffers used to instrument what the code is doing, and the lower level it gets, the more sensitive it gets.

First, to make things clear, the values you see pasted in this blog are nowhere near realistic values. They are the values used by the mysql-test-run test scripts, which are chosen to be very low just to put more stress on the performance schema code itself. The goal of the test suite is to torture the performance schema, not to make it fly.

All these different parameters follow the same principle, so let's take an example again, with performance_schema_max_thread_instances.

A value of N means that the performance schema will ever be able to keep track of N threads at a time. In practice however, performance will degrade badly when reaching the very last -- and rare -- open slots in an array of size N, when trying to allocate a new entry for the very last thread that connects.

Finding an empty slot in an array 50 percent full is easier than finding an empty slot in an array 60, 70, 80 ... 95 ? 99 ? percent full.

The more memory is given to the performance schema for a given buffer, the lower the density of allocated records will be in that buffer, making performances (when allocating a new entry) better. Once an entry is allocated, all the performance schema code directly use the record with a direct pointer, so there is nothing else to tune.

Sizing this parameter to the "proper" value depends on two things a) the (estimated) maximum number of instrumented objects the server will ever see at a given time, and b) the load factor to apply to this buffer.

Estimating the former really depends on the nature of each buffer.

For threads for example, the total number of threads is going to be driven by the --max-connection parameter, plus a few extra threads used internally. There might be a few threads for replication, the event scheduler, storage engines, etc, but not that many, so for example "max-connection + 50" seems very generous already.

Estimating the later (the load factor) really depends on the volatility of each buffer.

For buffers where records are rarely added, like for example the performance_schema.host table (a record is inserted only when a connection from a never before seen machine is found), the record density of the table is not going to have a huge impact on performances.

For buffers where records are allocated and freed all the time, finding a free record the first time beats looping forever to find one, so a very low density is key.

An example of a high volatility buffer is performance_schema_max_table_handles, as table handles are allocated each time a table is opened, which, in a database server, happens very often.

Q: Humm. What happens when a buffer is too small ?

That part is actually very simple: the performance schema will tell you.

mysql> show status like "performance_schema%";
+-------------------------------------------+-------+
| Variable_name                             | Value |
+-------------------------------------------+-------+
| Performance_schema_cond_classes_lost      | 0     |
| Performance_schema_cond_instances_lost    | 0     |
| Performance_schema_file_classes_lost      | 0     |
| Performance_schema_file_handles_lost      | 0     |
| Performance_schema_file_instances_lost    | 0     |
| Performance_schema_hosts_lost             | 0     |
| Performance_schema_locker_lost            | 0     |
| Performance_schema_mutex_classes_lost     | 0     |
| Performance_schema_mutex_instances_lost   | 0     |
| Performance_schema_rwlock_classes_lost    | 0     |
| Performance_schema_rwlock_instances_lost  | 0     |
| Performance_schema_stage_classes_lost     | 0     |
| Performance_schema_statement_classes_lost | 0     |
| Performance_schema_table_handles_lost     | 0     |
| Performance_schema_table_instances_lost   | 0     |
| Performance_schema_thread_classes_lost    | 0     |
| Performance_schema_thread_instances_lost  | 0     |
| Performance_schema_user_hosts_lost        | 0     |
| Performance_schema_users_lost             | 0     |
+-------------------------------------------+-------+
19 rows in set (0.00 sec)

Q: Ok. And what happens when a buffer is too big ?

Nothing really.

The performance schema will use memory depending on the sizing used, so big sizing means a lot of memory, but that's all.

To find how much memory is used, and in particular how each setting affects each buffer size, there is SHOW ENGINE PERFORMANCE_SCHEMA STATUS.

mysql> SHOW ENGINE PERFORMANCE_SCHEMA STATUS;
+--------------------+----------------------------------------------------------------+----------+
| Type               | Name                                                           | Status   |
+--------------------+----------------------------------------------------------------+----------+
| performance_schema | events_waits_current.row_size                                  | 168      |
| performance_schema | events_waits_current.row_count                                 | 800      |
| performance_schema | events_waits_history.row_size                                  | 168      |
| performance_schema | events_waits_history.row_count                                 | 2000     |
| performance_schema | events_waits_history.memory                                    | 336000   |
...
| performance_schema | events_statements_current.row_size                             | 1976     |
| performance_schema | events_statements_current.row_count                            | 200      |
| performance_schema | events_statements_current.memory                               | 395200   |
| performance_schema | performance_schema.memory                                      | 58928908 |
+--------------------+----------------------------------------------------------------+----------+
129 rows in set (0.00 sec)

Q: Can the sizing part be automated somehow ?

It is not automated yet, but it sure can be, to some extent.

What is needed to get that done is first, to identify the magic formula that says, for each instrumentation type, something like:

total number of mutex = (number of singleton) + (number of tables * mutexes by table) + (number of threads * mutexes by thread) + ...

The challenge here is that this magic formula changes all the time, as implementation changes, but overall, there are not that many parameters in it. In any case, that part can be resolved with at least good enough heuristics.

Some parts of the magic formula also comes from the application that sends queries to the server. All things being equal, a statement that joins 20 tables is going to consume more table handles compared to a statement that just uses 1 or 2 tables, so the way the application is written also has an effect.

The second part is some feedback from users, and some benchmarking, to find reasonable values for load factors, based on each instrument type.

This is a long post already, thanks for reading it.

Regards,

-- Marc Alff, Oracle

Performance schema FAQ #1, enable without a server restart

noreply@blogger.com (Marc Alff) — Tue, 12 Apr 2011 18:03:00 +0000

Q: Is it possible to enable / disable the performance schema at runtime ?

A: Yes.

Configure, configure and configure

The performance schema is affected by three distinct set of configuration parameters, that take effect at compile time, server startup, or runtime.

Compile time options

When building from the source code, make sure the cmake flag 'WITH_PERFSCHEMA_STORAGE_ENGINE' is set to 'ON' (it is by default).

For packages built by Oracle, the performance schema is included by default.

Server startup options

Simply add the 'performance_schema' option to your my.cnf configuration file.

The effect of this option seems mis understood, leading people to think that the performance schema can only be enabled or disabled at server startup time, which is not the case.

The role of this startup option, with the related sizing parameters, is to initialize the performance schema internal memory buffers, and hook the performance schema instrumentation to the server and or plugins, so that everything is 'ready to go'. Note that 'ready to go' does not means 'enabled', it just means that things can be enabled or disabled later at runtime.

Runtime configuration

When the performance schema component is active in the server, it is listening to all events. Performance schema 'consumers' will decide, based on the content of the various setup tables in the performance_schema database, what to do with the event that just happened in the server code.

A table, performance_schema.setup_consumers, shows all the consumers available. This table has a ENABLED column that can be updated, giving the DBA full control on the internal performance schema execution flow.

There is a consumer which effectively controls globally all the data collection, so by turning this consumer ON or OFF, the entire performance schema instrumentation can be turned on or off, dynamically.

In 5.5, this consumer is named 'events_waits_current'. Note that this consumer in fact has two roles, as it controls both the overall data collection, and also the output table performance_schema.events_waits_current. This is a limitation, as there is no way to collect performance schema data independently of table events_waits_current.

In 5.6, this limitation has been lifted, so that the consumer which effectively control data collection is 'global_instrumentation', while the consumer named 'events_waits_current' controls only the table of the same name. The new consumer 'global_instrumentation' in 5.6 is the result of significant performance optimizations implemented in the code, made possible by decoupling the overall data collection from table events_waits_current.

Cheat sheet for mysql-5.5

To enable the performance schema at runtime (5.5), use the following query:

mysql> update performance_schema.setup_consumers set enabled='YES' where name='events_waits_current';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from performance_schema.setup_consumers;
+----------------------------------------------+---------+
| NAME                                         | ENABLED |
+----------------------------------------------+---------+
| events_waits_current                         | YES     |
| ...                                            ...     |
+----------------------------------------------+---------+
8 rows in set (0.01 sec)

To disable the performance schema at runtime (5.5), use the following query:

mysql> update performance_schema.setup_consumers set enabled='NO' where name='events_waits_current';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from performance_schema.setup_consumers;
+----------------------------------------------+---------+
| NAME                                         | ENABLED |
+----------------------------------------------+---------+
| events_waits_current                         | NO      |
| ...                                            ...     |
+----------------------------------------------+---------+
8 rows in set (0.01 sec)

Cheat sheet for mysql-5.6

To disable the performance schema at runtime (5.6), use the following query:

update performance_schema.setup_consumers set enabled='NO' where name='global_instrumentation';

To enable the performance schema at runtime (5.6), use the following query:

mysql> update performance_schema.setup_consumers set enabled='YES' where name='global_instrumentation';

Enjoy !

Marc Alff, Oracle.

The PERFORMANCE SCHEMA development continues

noreply@blogger.com (Marc Alff) — Thu, 24 Mar 2011 12:28:00 +0000

Now that mysql 5.5 is GA, development for the performance schema continues in the mysql-trunk code base. A lot of projects have been completed already, which are worth mentioning.

New table io instrumentation

Table io is now instrumented. As the performance instrumentation is located in the MySQL layer and not inside the storage engine, this instrumentation is de facto available to all storage engines, with zero code change.

New table lock instrumentation

Table locks are also instrumented. It is a nice complement to table io, and can be enabled or disabled independently from table io. Not all table waits are io problems, some are also contention caused by locks ... and there is now a way to observe that.

New stages instrumentation

Major execution phases of a statement, called stages, are now instrumented. This instrumentation is critical to identify which part of the execution path (parser, acl, locking, optimizer, runtime, etc) takes time.

New statements instrumentation

Statements themselves are now instrumented. As statements are what an application sends to the server, this is the instrumentation that makes the most sense to application developers. Now, once a statement is found to be "too slow", the lower level instrumentation like stage and waits can be used to see why.

New filtering options

A DBA may not want to monitor every single user session from every single application from every single host at once. Instrumentation for each session (or thread) can be turned on of off independently (TABLE performance_schema.threads, column INSTRUMENTED). Rules in the new table performance_schema.setup_actors can be used to instrument selectively traffic per user, per host, or per user+host.

Likewise, with table io and table lock instrumentation, a DBA may not want to instrument every single table in the database at once, but only instrument a subset of tables. Rules in the new tables performance_schema.setup_objects can be used for that.

Marc Alff, Oracle.

The PERFORMANCE SCHEMA is dead, long live the performance schema

noreply@blogger.com (Marc Alff) — Sat, 04 Dec 2010 15:35:00 +0000

The PERFORMANCE SCHEMA is dead

The initial implementation of the performance schema used to name tables in the performance schema database in UPPERCASE, as in 'EVENTS_WAITS_CURRENT'.

This naming scheme turned up to be difficult to work with (see Bug#57609), especially considering the established MySQL practice of converting table names to lowercase, using the lower_case_table_names server setting.

Long live the performance schema

Starting with MySQL 5.5.8, the performance schema tables have been renamed to lowercase.

This guarantees that a query such as 'select * from performance_schema.events_waits_current' will work consistently across any server deployment, regardless of the server platform or the server lower_case_table_names configuration parameter.

Life for people or automated tools using the performance schema just became much easier ...

Marc Alff, Oracle.

MySQL Performance Schema is in the trunk

noreply@blogger.com (Marc Alff) — Mon, 08 Mar 2010 15:15:00 +0000

It is in

As of 2010-03-06, the performance schema is merged into MySQL version 5.5.3-m3, in mysql-trunk.

The documentation for 5.5 has also been updated, and contains a new MySQL Performance Schema chapter.

Disclaimer

Please note that MySQL 5.5 is not a GA product. The performance schema feature may still change at any time, for any reason, and without notice. Customers should not make purchasing decisions based on the availability of the performance schema.

Marc Alff, Oracle.

Performance schema overview

noreply@blogger.com (Marc Alff) — Thu, 21 Jan 2010 07:25:00 +0000

Introduction

This paper is an introduction to the new 'performance schema' feature, which will be part of the upcoming MySQL 5.5 release. Covering in details every part of the performance schema would require much, much more than a simple article. The pace of this teaser is voluntarily fast, to have a quick overview of the new landscape, and help users already familiar with MySQL to understand by examples what the performance schema provides.

Please refer to the online documentation in the MySQL manual for more content.

Basic setup

Let's start a server, and a client connection for monitoring.

mysql> select version();
+---------------------+
| version()           |
+---------------------+
| 5.5.99-m3-debug-log |
+---------------------+
1 row in set (0.00 sec)

mysql> prompt Monitor> ;
PROMPT set to 'Monitor> '
Monitor>

Start two other clients, and set the prompt to identify them.

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               2 |
+-----------------+
1 row in set (0.00 sec)

mysql> prompt Client A (ID 2)> ;
PROMPT set to 'Client A (ID 2)> '
Client A (ID 2)>

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               3 |
+-----------------+
1 row in set (0.00 sec)

mysql> prompt Client B (ID 3)> ;
PROMPT set to 'Client B (ID 3)> '
Client B (ID 3)>

Performance schema visible artifacts

There is a new storage engine.

Monitor> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ndbcluster         | NO      | Clustered, fault-tolerant tables                               | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MyISAM             | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| InnoDB             | NO      | Supports transactions, row-level locking, and foreign keys     | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
10 rows in set (0.00 sec)

There is a new database.


Monitor> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mtr                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

Some new system variables are also available. Every system variable can be set on the server command line options, or set in the my.cnf configuration file.

Monitor> show variables like "performance_schema%";
+---------------------------------------------------+--------+
| Variable_name                                     | Value  |
+---------------------------------------------------+--------+
| performance_schema                                | ON     |
| performance_schema_events_waits_history_long_size | 10000  |
| performance_schema_events_waits_history_size      | 10     |
| performance_schema_max_cond_classes               | 80     |
| performance_schema_max_cond_instances             | 1000   |
| performance_schema_max_file_classes               | 50     |
| performance_schema_max_file_handles               | 32768  |
| performance_schema_max_file_instances             | 10000  |
| performance_schema_max_mutex_classes              | 200    |
| performance_schema_max_mutex_instances            | 1000   |
| performance_schema_max_rwlock_classes             | 20     |
| performance_schema_max_rwlock_instances           | 1000   |
| performance_schema_max_table_handles              | 100000 |
| performance_schema_max_table_instances            | 50000  |
| performance_schema_max_thread_classes             | 50     |
| performance_schema_max_thread_instances           | 1000   |
+---------------------------------------------------+--------+
16 rows in set (0.01 sec)

There are also status variables, to inspect the internal performance schema status.

Monitor> show status like "performance_schema%";
+------------------------------------------+-------+
| Variable_name                            | Value |
+------------------------------------------+-------+
| Performance_schema_cond_classes_lost     | 0     |
| Performance_schema_cond_instances_lost   | 0     |
| Performance_schema_file_classes_lost     | 0     |
| Performance_schema_file_handles_lost     | 0     |
| Performance_schema_file_instances_lost   | 0     |
| Performance_schema_locker_lost           | 0     |
| Performance_schema_mutex_classes_lost    | 0     |
| Performance_schema_mutex_instances_lost  | 0     |
| Performance_schema_rwlock_classes_lost   | 0     |
| Performance_schema_rwlock_instances_lost | 0     |
| Performance_schema_table_handles_lost    | 0     |
| Performance_schema_table_instances_lost  | 0     |
| Performance_schema_thread_classes_lost   | 0     |
| Performance_schema_thread_instances_lost | 0     |
+------------------------------------------+-------+
14 rows in set (0.00 sec)

SHOW ENGINE STATUS can also be used to inspect the performance schema internal state.

Monitor> show engine PERFORMANCE_SCHEMA status;
+--------------------+--------------------------------------------------------+----------+
| Type               | Name                                                   | Status   |
+--------------------+--------------------------------------------------------+----------+
| performance_schema | EVENTS_WAITS_CURRENT.ROW_SIZE                          | 136      |
| performance_schema | EVENTS_WAITS_CURRENT.ROW_COUNT                         | 3000     |
| performance_schema | EVENTS_WAITS_HISTORY.ROW_SIZE                          | 120      |
| performance_schema | EVENTS_WAITS_HISTORY.ROW_COUNT                         | 10000    |
| performance_schema | EVENTS_WAITS_HISTORY.MEMORY                            | 1200000  |
| performance_schema | EVENTS_WAITS_HISTORY_LONG.ROW_SIZE                     | 120      |
| performance_schema | EVENTS_WAITS_HISTORY_LONG.ROW_COUNT                    | 10000    |
| performance_schema | EVENTS_WAITS_HISTORY_LONG.MEMORY                       | 1200000  |
| performance_schema | (PFS_MUTEX_CLASS).ROW_SIZE                             | 248      |
| performance_schema | (PFS_MUTEX_CLASS).ROW_COUNT                            | 200      |
| performance_schema | (PFS_MUTEX_CLASS).MEMORY                               | 49600    |
...
| performance_schema | EVENTS_WAITS_SUMMARY_BY_THREAD_BY_EVENT_NAME.ROW_SIZE  | 48       |
| performance_schema | EVENTS_WAITS_SUMMARY_BY_THREAD_BY_EVENT_NAME.ROW_COUNT | 350000   |
| performance_schema | EVENTS_WAITS_SUMMARY_BY_THREAD_BY_EVENT_NAME.MEMORY    | 16800000 |
| performance_schema | PERFORMANCE_SCHEMA.MEMORY                              | 26459600 |
+--------------------+--------------------------------------------------------+----------+
45 rows in set (0.00 sec)

Let's see what is in the new performance_schema database ...


Monitor> 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
Monitor> show tables;
+----------------------------------------------+
| Tables_in_performance_schema                 |
+----------------------------------------------+
| COND_INSTANCES                               |
| EVENTS_WAITS_CURRENT                         |
| EVENTS_WAITS_HISTORY                         |
| EVENTS_WAITS_HISTORY_LONG                    |
| EVENTS_WAITS_SUMMARY_BY_EVENT_NAME           |
| EVENTS_WAITS_SUMMARY_BY_INSTANCE             |
| EVENTS_WAITS_SUMMARY_BY_THREAD_BY_EVENT_NAME |
| FILE_INSTANCES                               |
| FILE_SUMMARY_BY_EVENT_NAME                   |
| FILE_SUMMARY_BY_INSTANCE                     |
| MUTEX_INSTANCES                              |
| PERFORMANCE_TIMERS                           |
| PROCESSLIST                                  |
| RWLOCK_INSTANCES                             |
| SETUP_CONSUMERS                              |
| SETUP_INSTRUMENTS                            |
| SETUP_OBJECTS                                |
| SETUP_TIMERS                                 |
+----------------------------------------------+
18 rows in set (0.00 sec)

What is instrumented in the code ?

The available instrumentation is discovered dynamically.

Monitor> select * from SETUP_INSTRUMENTS;
+------------------------------------------------------------+---------+-------+
| NAME                                                       | ENABLED | TIMED |
+------------------------------------------------------------+---------+-------+
| wait/synch/mutex/sql/PAGE::lock                            | YES     | YES   |
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_sync                | YES     | YES   |
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_active              | YES     | YES   |
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_pool                | YES     | YES   |
| wait/synch/mutex/sql/LOCK_des_key_file                     | YES     | YES   |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_index             | YES     | YES   |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_prep_xids         | YES     | YES   |
| wait/synch/mutex/sql/Delayed_insert::mutex                 | YES     | YES   |
...
| wait/synch/rwlock/sql/LOCK_grant                           | YES     | YES   |
| wait/synch/rwlock/sql/LOGGER::LOCK_logger                  | YES     | YES   |
| wait/synch/rwlock/sql/LOCK_sys_init_connect                | YES     | YES   |
| wait/synch/rwlock/sql/LOCK_sys_init_slave                  | YES     | YES   |
| wait/synch/rwlock/sql/LOCK_system_variables_hash           | YES     | YES   |
| wait/synch/rwlock/sql/Query_cache_query::lock              | YES     | YES   |
| wait/synch/rwlock/sql/LOCK_dboptions                       | YES     | YES   |
| wait/synch/rwlock/myisam/MYISAM_SHARE::key_root_lock       | YES     | YES   |
| wait/synch/rwlock/myisam/MYISAM_SHARE::mmap_lock           | YES     | YES   |
...
| wait/synch/cond/sql/COND_flush_thread_cache                | YES     | YES   |
| wait/synch/cond/mysys/COND_alarm                           | YES     | YES   |
| wait/synch/cond/mysys/IO_CACHE_SHARE::cond                 | YES     | YES   |
| wait/synch/cond/mysys/IO_CACHE_SHARE::cond_writer          | YES     | YES   |
| wait/synch/cond/mysys/my_thread_var::suspend               | YES     | YES   |
| wait/synch/cond/mysys/THR_COND_threads                     | YES     | YES   |
...
| wait/io/file/csv/metadata                                  | YES     | YES   |
| wait/io/file/csv/data                                      | YES     | YES   |
| wait/io/file/csv/update                                    | YES     | YES   |
| wait/io/file/myisam/data_tmp                               | YES     | YES   |
| wait/io/file/myisam/dfile                                  | YES     | YES   |
| wait/io/file/myisam/kfile                                  | YES     | YES   |
| wait/io/file/myisam/log                                    | YES     | YES   |
| wait/io/file/myisammrg/MRG                                 | YES     | YES   |
+------------------------------------------------------------+---------+-------+
167 rows in set (0.00 sec)

So, for this platform, 167 artifacts in the server and storage engines are instrumented. Let's see the breakdown by type.

Monitor> select count(*) from SETUP_INSTRUMENTS where name like 'wait/synch/mutex/%';
+----------+
| count(*) |
+----------+
|       84 |
+----------+
1 row in set (0.00 sec)

Monitor> select count(*) from SETUP_INSTRUMENTS where name like 'wait/synch/cond/%';
+----------+
| count(*) |
+----------+
|       35 |
+----------+
1 row in set (0.00 sec)

Monitor> select count(*) from SETUP_INSTRUMENTS where name like 'wait/synch/rwlock/%';
+----------+
| count(*) |
+----------+
|       11 |
+----------+
1 row in set (0.00 sec)

Monitor> select count(*) from SETUP_INSTRUMENTS where name like 'wait/io/file/%';
+----------+
| count(*) |
+----------+
|       37 |
+----------+
1 row in set (0.00 sec)

What is running inside the server ?

The existing SHOW PROCESSLIST command can be used to see that ...


Monitor> show processlist;
+----+------+-----------+--------------------+---------+------+-------+------------------+
| Id | User | Host      | db                 | Command | Time | State | Info             |
+----+------+-----------+--------------------+---------+------+-------+------------------+
|  1 | root | localhost | performance_schema | Query   |    0 | NULL  | show processlist |
|  2 | root | localhost | NULL               | Sleep   | 3459 |       | NULL             |
|  3 | root | localhost | NULL               | Sleep   | 3386 |       | NULL             |
+----+------+-----------+--------------------+---------+------+-------+------------------+
3 rows in set (0.00 sec)

Ok, nice try. Now, what is really running in the server ?

Monitor> select * from PROCESSLIST;
+-----------+----+---------------------------+
| THREAD_ID | ID | NAME                      |
+-----------+----+---------------------------+
|         0 |  0 | thread/sql/main           |
|         3 |  2 | thread/sql/one_connection |
|         4 |  3 | thread/sql/one_connection |
|         2 |  1 | thread/sql/one_connection |
|         1 |  0 | thread/sql/signal_handler |
+-----------+----+---------------------------+
5 rows in set (0.00 sec)

Here we see two new threads that were previously hidden. Every thread instrumented in the code is displayed by the performance schema. This include for example, threads used internally by a storage engine.

We know Client A connection_id() is 2, let's find out what thread is executing Client A requests.

Monitor> select * from PROCESSLIST where ID=2;
+-----------+----+---------------------------+
| THREAD_ID | ID | NAME                      |
+-----------+----+---------------------------+
|         3 |  2 | thread/sql/one_connection |
+-----------+----+---------------------------+
1 row in set (0.00 sec)

What is this query waiting for, right now ?

First, we need a query that takes some time. For illustration, let's make client A sleep for 10 seconds.

Client A (ID 2)> select sleep(10);

While the query is still executing, see what event the code is waiting for.

Monitor> select * from EVENTS_WAITS_CURRENT where THREAD_ID=3\G
*************************** 1. row ***************************
            THREAD_ID: 3
             EVENT_ID: 46
           EVENT_NAME: wait/synch/cond/sql/Item_func_sleep::cond
               SOURCE: item_func.cc:3527
          TIMER_START: 5617352941894736
            TIMER_END: NULL
           TIMER_WAIT: NULL

                SPINS: NULL
        OBJECT_SCHEMA: NULL
          OBJECT_NAME: NULL
          OBJECT_TYPE: NULL
OBJECT_INSTANCE_BEGIN: 139803207157216
     NESTING_EVENT_ID: NULL
            OPERATION: timed_wait
      NUMBER_OF_BYTES: NULL
                FLAGS: 0
1 row in set (0.00 sec)

Looking at the results, we can tell that the query is still waiting (TIMER_WAIT is NULL) for an instrumented condition (EVENT_NAME starts with 'wait/synch/cond/'), which is located in the sql layer ('sql/'), and happen to be named 'Item_func_sleep::cond'. The instrumented call is located in file item_func.cc, line 3527 (SOURCE), and the call was a 'timed_wait' (OPERATION).

What are the recent wait events of this connection ?

After the sleep(10) is completed, let's look at the wait history for Client A connection.


Monitor> select THREAD_ID, EVENT_ID, EVENT_NAME, SOURCE, TIMER_WAIT, OBJECT_INSTANCE_BEGIN, OPERATION from EVENTS_WAITS_HISTORY where THREAD_ID=3 order by THREAD_ID, EVENT_ID;
+-----------+----------+---------------------------------------------+--------------------+---------------+-----------------------+------------+
| THREAD_ID | EVENT_ID | EVENT_NAME                                  | SOURCE             | TIMER_WAIT    | OBJECT_INSTANCE_BEGIN | OPERATION  |
+-----------+----------+---------------------------------------------+--------------------+---------------+-----------------------+------------+
|         3 |       43 | wait/synch/mutex/mysys/THR_LOCK_malloc      | safemalloc.c:181   |        399243 |              18775392 | lock       |
|         3 |       44 | wait/synch/mutex/mysys/THR_LOCK_malloc      | safemalloc.c:294   |        210035 |              18775392 | lock       |
|         3 |       45 | wait/synch/mutex/sql/LOCK_user_locks        | item_func.cc:3837  |        500907 |              18580768 | lock       |
|         3 |       46 | wait/synch/cond/sql/Item_func_sleep::cond   | item_func.cc:3527  | 5000241974790 |       140221896639968 | timed_wait |
|         3 |       47 | wait/synch/cond/sql/Item_func_sleep::cond   | item_func.cc:3527  | 5000237350843 |       140221896639968 | timed_wait |
|         3 |       48 | wait/synch/mutex/mysys/my_thread_var::mutex | item_func.cc:3853  |        612102 |              19805600 | lock       |
|         3 |       49 | wait/synch/mutex/sql/LOCK_plugin            | sql_plugin.cc:1013 |        356883 |              18701152 | lock       |
|         3 |       50 | wait/synch/rwlock/sql/LOGGER::LOCK_logger   | log.h:582          |        276046 |              18672512 | read_lock  |
|         3 |       51 | wait/synch/mutex/sql/LOG::LOCK_log          | log.cc:2515        |        372062 |              18989864 | lock       |
|         3 |       52 | wait/synch/mutex/sql/THD::LOCK_thd_data     | sql_class.cc:3267  |        276046 |              19798240 | lock       |
+-----------+----------+---------------------------------------------+--------------------+---------------+-----------------------+------------+
10 rows in set (0.00 sec)

It seems the wait for 10 seconds was interupted, so the code instead waited twice for 5 seconds, in the timed_wait.

What timer is used to measure all that ?

The performance schema provide different timers, with different characteristics.

Monitor> select * from PERFORMANCE_TIMERS;
+-------------+-----------------+------------------+----------------+
| TIMER_NAME  | TIMER_FREQUENCY | TIMER_RESOLUTION | TIMER_OVERHEAD |
+-------------+-----------------+------------------+----------------+
| CYCLE       |      2830706467 |                1 |             43 |
| NANOSECOND  |            NULL |             NULL |           NULL |
| MICROSECOND |         1000000 |                1 |            119 |
| MILLISECOND |            1037 |                1 |            128 |
| TICK        |             103 |                1 |            451 |
+-------------+-----------------+------------------+----------------+
5 rows in set (0.00 sec)

The CYCLE timer shows that this machine has a 2.83 GHz CPU. The NANOSECOND timer is not available. Which timer to pick is configurable.

Monitor> select * from SETUP_TIMERS;
+------+------------+
| NAME | TIMER_NAME |
+------+------------+
| wait | CYCLE      |
+------+------------+
1 row in set (0.00 sec)

Looking at history for a longer period

Let's use a sample scenario to illustrate how to use the history tables. First, we need some objects to use in queries.

Client A (ID 2)> create table test.t1(a int);
Query OK, 0 rows affected (0.07 sec)

Client A (ID 2)> lock table test.t1 read;
Query OK, 0 rows affected (0.00 sec)

Get a timestamp, we will analyse the history from now on.

Monitor> select max(TIMER_START) from EVENTS_WAITS_HISTORY into @marker_begin;
Query OK, 1 row affected (0.00 sec)

Monitor> select @marker_begin;
+------------------+
| @marker_begin    |
+------------------+
| 2461829402934558 |
+------------------+
1 row in set (0.00 sec)

Now the scenario itself: Client B will try to insert a row, and will be blocked. Client A will then unlock tables, unblocking B, which will finish.

Client B (ID 3)> insert into test.t1 values (1);

Client A (ID 2)> unlock tables;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (12.98 sec)

Get a timestamp again, and let's see what happened with clients A and B ...

Monitor> select max(TIMER_START) from EVENTS_WAITS_HISTORY into @marker_end;
Query OK, 1 row affected (0.00 sec)

Monitor> select @marker_end;
+------------------+
| @marker_end      |
+------------------+
| 2928891716761738 |
+------------------+
1 row in set (0.00 sec)

Monitor> select THREAD_ID, EVENT_ID, EVENT_NAME, SOURCE, TIMER_START, TIMER_WAIT, OBJECT_INSTANCE_BEGIN, OPERATION
    -> from EVENTS_WAITS_HISTORY_LONG where (THREAD_ID=3 or THREAD_ID=4) and
    -> (TIMER_START > @marker_begin) and (TIMER_START <= @marker_end)
    -> order by TIMER_START asc;
+-----------+----------+---------------------------------------------------------+--------------------+------------------+----------------+-----------------------+-----------+               
| THREAD_ID | EVENT_ID | EVENT_NAME                                              | SOURCE             | TIMER_START      | TIMER_WAIT     | OBJECT_INSTANCE_BEGIN | OPERATION |               
+-----------+----------+---------------------------------------------------------+--------------------+------------------+----------------+-----------------------+-----------+               
|         4 |       39 | wait/synch/mutex/sql/THD::LOCK_thd_data                 | sql_class.cc:3287  | 2612399119180647 |        1338223 |              19855856 | lock      |               
|         4 |       40 | wait/synch/mutex/sql/THD::LOCK_thd_data                 | sql_class.cc:3267  | 2612399126078620 |         216036 |              19855856 | lock      |               
|         4 |       41 | wait/synch/rwlock/sql/LOGGER::LOCK_logger               | log.h:582          | 2612399129553199 |         321230 |              18672512 | read_lock |               
|         4 |       42 | wait/synch/mutex/sql/LOG::LOCK_log                      | log.cc:2414        | 2612399134753242 |         458900 |              18988648 | lock      |               
|         4 |       43 | wait/synch/mutex/mysys/THR_LOCK_malloc                  | safemalloc.c:294   | 2612399222016607 |         390065 |              18775392 | lock      |               
|         4 |       44 | wait/synch/rwlock/sql/LOCK_grant                        | sql_acl.cc:3990    | 2612399231231319 |         144024 |              18595168 | read_lock |               
|         4 |       45 | wait/synch/mutex/sql/LOCK_global_read_lock              | lock.cc:1513       | 2612399233796570 |         369238 |              18593248 | lock      |               
|         4 |       46 | wait/synch/mutex/mysys/THR_LOCK_malloc                  | safemalloc.c:181   | 2612399239800747 |         224861 |              18775392 | lock      |
|         4 |       47 | wait/synch/mutex/sql/LOCK_open                          | sql_base.cc:2745   | 2612399244679560 |         318053 |              18592864 | lock      |
|         4 |       48 | wait/synch/mutex/mysys/THR_LOCK_malloc                  | safemalloc.c:181   | 2612399248817073 |         213212 |              18775392 | lock      |
|         4 |       49 | wait/synch/mutex/sql/TABLE_SHARE::mutex                 | sql_base.cc:404    | 2612399251601537 |         258043 |              19549224 | lock      |
|         4 |       50 | wait/synch/mutex/mysys/THR_LOCK_malloc                  | safemalloc.c:181   | 2612399255052112 |         210035 |              18775392 | lock      |
|         4 |       51 | wait/synch/mutex/mysys/THR_LOCK_malloc                  | safemalloc.c:181   | 2612399258115799 |         206858 |              18775392 | lock      |
|         4 |       52 | wait/synch/mutex/mysys/THR_LOCK_myisam                  | mi_open.c:106      | 2612399299375498 |         390065 |              18775520 | lock      |
|         4 |       53 | wait/io/file/myisam/dfile                               | mi_open.c:1271     | 2612399310495351 |        9763627 |       140221840474240 | open      |
|         4 |       54 | wait/synch/mutex/mysys/THR_LOCK_open                    | my_open.c:137      | 2612399317282482 |         429248 |              18775008 | lock      |
|         4 |       55 | wait/synch/mutex/mysys/THR_LOCK_malloc                  | safemalloc.c:181   | 2612399318713897 |         212859 |              18775392 | lock      |
|         4 |       56 | wait/synch/mutex/mysys/THR_LOCK_malloc                  | safemalloc.c:181   | 2612399322194477 |         206858 |              18775392 | lock      |
|         4 |       57 | wait/synch/mutex/myisam/MYISAM_SHARE::intern_lock       | mi_open.c:620      | 2612399323862755 |         320877 |              19920280 | lock      |
|         4 |       58 | wait/synch/mutex/mysys/THR_LOCK_malloc                  | safemalloc.c:181   | 2612399325593867 |         207211 |              18775392 | lock      |
|         4 |       59 | wait/synch/mutex/mysys/THR_LOCK_malloc                  | safemalloc.c:181   | 2612399328546359 |         204034 |              18775392 | lock      |
|         4 |       60 | wait/synch/mutex/sql/TABLE_SHARE::mutex                 | ha_myisam.cc:1712  | 2612399334115287 |         282047 |              19549224 | lock      |
|         4 |       61 | wait/synch/mutex/mysys/THR_LOCK_malloc                  | safemalloc.c:294   | 2612399342195810 |         210035 |              18775392 | lock      |
|         4 |       62 | wait/synch/mutex/mysys/THR_LOCK_malloc                  | safemalloc.c:294   | 2612399364672379 |         255219 |              18775392 | lock      |
|         4 |       63 | wait/synch/mutex/mysys/THR_LOCK_malloc                  | safemalloc.c:181   | 2612399369764404 |         252042 |              18775392 | lock      |
|         4 |       64 | wait/synch/mutex/myisam/MYISAM_SHARE::intern_lock       | mi_locking.c:53    | 2612399372932932 |         224861 |              19920280 | lock      |
|         4 |       65 | wait/synch/mutex/mysys/THR_LOCK::mutex                  | thr_lock.c:525     | 2612399376764394 |         237216 |              19920048 | lock      |
|         4 |       66 | wait/synch/cond/mysys/my_thread_var::suspend            | thr_lock.c:444     | 2612399380827071 | 12977803964893 |              19864808 | wait      |
|         3 |      199 | wait/synch/mutex/sql/THD::LOCK_thd_data                 | sql_class.cc:3287  | 2625377031586434 |        1311395 |              19798240 | lock      |
|         3 |      200 | wait/synch/mutex/sql/THD::LOCK_thd_data                 | sql_class.cc:3267  | 2625377038712798 |         212859 |              19798240 | lock      |
|         3 |      201 | wait/synch/rwlock/sql/LOGGER::LOCK_logger               | log.h:582          | 2625377043456412 |         345234 |              18672512 | read_lock |
|         3 |      202 | wait/synch/mutex/sql/LOG::LOCK_log                      | log.cc:2414        | 2625377048476425 |         486081 |              18988648 | lock      |
|         3 |      203 | wait/synch/mutex/mysys/THR_LOCK::mutex                  | thr_lock.c:814     | 2625377120023171 |         456076 |              19920048 | lock      |
|         3 |      204 | wait/synch/mutex/myisam/MYISAM_SHARE::intern_lock       | mi_locking.c:53    | 2625377132169195 |         231215 |              19920280 | lock      |
|         3 |      205 | wait/synch/mutex/mysys/THR_LOCK_malloc                  | safemalloc.c:294   | 2625377136297883 |         231215 |              18775392 | lock      |
|         3 |      206 | wait/synch/mutex/sql/LOCK_open                          | sql_base.cc:1201   | 2625377139598433 |         306051 |              18592864 | lock      |
|         3 |      207 | wait/synch/mutex/sql/LOCK_plugin                        | sql_plugin.cc:1013 | 2625377157094525 |         320877 |              18701152 | lock      |
|         3 |      208 | wait/synch/mutex/sql/THD::LOCK_thd_data                 | sql_class.cc:3267  | 2625377169918662 |         266868 |              19798240 | lock      |
|         4 |       67 | wait/synch/mutex/mysys/my_thread_var::mutex             | thr_lock.c:503     | 2625377197730120 |         648108 |              19864864 | lock      |
|         4 |       68 | wait/synch/rwlock/sql/LOCK_grant                        | sql_acl.cc:4266    | 2625377233568092 |         450075 |              18595168 | read_lock |
|         4 |       69 | wait/io/file/myisam/kfile                               | mi_locking.c:548   | 2625377256953989 |       23803143 |       140221840473616 | write     |
|         4 |       70 | wait/io/file/myisam/dfile                               | mi_dynrec.c:233    | 2625377283097522 |       32138179 |       140221840474240 | write     |
|         4 |       71 | wait/io/file/myisam/dfile                               | mi_dynrec.c:233    | 2625377316267873 |        3855819 |       140221840474240 | write     |
|         4 |       72 | wait/synch/mutex/sql/Query_cache::structure_guard_mutex | sql_cache.cc:523   | 2625377334778134 |         666111 |              18596216 | lock      |
|         4 |       73 | wait/synch/mutex/sql/Query_cache::structure_guard_mutex | sql_cache.cc:545   | 2625377336920491 |         206858 |              18596216 | lock      |
|         4 |       74 | wait/synch/mutex/sql/LOCK_global_read_lock              | lock.cc:1561       | 2625377342084175 |         342057 |              18593248 | lock      |
|         4 |       75 | wait/synch/mutex/sql/LOCK_plugin                        | sql_plugin.cc:1013 | 2625377346678117 |         416893 |              18701152 | lock      |
|         4 |       76 | wait/synch/mutex/mysys/THR_LOCK::mutex                  | thr_lock.c:814     | 2625377544669110 |         596923 |              19920048 | lock      |
|         4 |       77 | wait/synch/mutex/myisam/MYISAM_SHARE::intern_lock       | mi_locking.c:53    | 2625377550156848 |         429248 |              19920280 | lock      |
|         4 |       78 | wait/synch/mutex/mysys/KEY_CACHE::cache_lock            | mf_keycache.c:4115 | 2625377552830470 |         330055 |              18991320 | lock      |
|         4 |       79 | wait/io/file/myisam/kfile                               | mi_open.c:934      | 2625377557658098 |        4545934 |       140221840473616 | write     |
|         4 |       80 | wait/synch/mutex/mysys/THR_LOCK_malloc                  | safemalloc.c:294   | 2625377565252540 |         438073 |              18775392 | lock      |
|         4 |       81 | wait/synch/mutex/sql/LOCK_open                          | sql_base.cc:1201   | 2625377568201855 |         495259 |              18592864 | lock      |
|         4 |       82 | wait/synch/mutex/sql/THD::LOCK_thd_data                 | sql_class.cc:3267  | 2625377578982828 |         336056 |              19855856 | lock      |
+-----------+----------+---------------------------------------------------------+--------------------+------------------+----------------+-----------------------+-----------+
54 rows in set (0.02 sec)

Important things to notice:

client B (thread 4) executes events up to 66, and is blocked for 12 seconds in a wait.
client A (thread 3) executes events 199 to 208
client B (thread 4) then resumes execution.
Both threads are using a mutex named 'MYISAM_SHARE::intern_lock' in myisam.
Looking at (thread 4, event 64), and (thread 3, event 204), the mutex address is the same (OBJECT_INSTANCE_BEGIN).

There you have it: the two clients were competing on a table lock for a myisam table ... This illustrate that very detailed data can be observed in the performance schema history tables,

What's the big picture ?

The performance schema maintains aggregates from the low level wait events collected, here are sample queries showing aggregates based on different criteria.

Monitor> select * from EVENTS_WAITS_SUMMARY_BY_EVENT_NAME order by SUM_TIMER_WAIT desc limit 20;
+---------------------------------------------------+------------+----------------+----------------+----------------+----------------+
| EVENT_NAME                                        | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT |
+---------------------------------------------------+------------+----------------+----------------+----------------+----------------+
| wait/synch/cond/mysys/my_thread_var::suspend      |          1 | 12977803964893 | 12977803964893 | 12977803964893 | 12977803964893 |
| wait/io/file/sql/FRM                              |        605 |    61285161668 |         332879 |      101297787 |    44527633212 |
| wait/synch/mutex/mysys/THR_LOCK_malloc            |       2676 |      505940427 |         138023 |         189065 |        4560760 |
| wait/io/file/myisam/kfile                         |         82 |      406281114 |         459253 |        4954647 |      187585259 |
| wait/io/file/myisam/dfile                         |         50 |      149008360 |         621280 |        2980167 |       32138179 |
| wait/synch/cond/sql/COND_thread_count             |          1 |      140672618 |      140672618 |      140672618 |      140672618 |
| wait/synch/mutex/sql/THD::LOCK_thd_data           |        218 |      105269189 |         156026 |         482886 |        1569438 |
| wait/io/file/sql/casetest                         |         10 |       75699438 |        2265201 |        7569943 |       22200876 |
| wait/io/file/sql/pid                              |          3 |       65458555 |        4716786 |       21819518 |       35012658 |
| wait/io/file/sql/ERRMSG                           |          5 |       58504102 |        1422237 |       11700820 |       23653118 |
| wait/synch/mutex/mysys/THR_LOCK_open              |        249 |       58411263 |         147201 |         234583 |         636106 |
| wait/io/file/sql/dbopt                            |          9 |       58032847 |        2748458 |        6448094 |       11395899 |
| wait/synch/mutex/sql/LOCK_plugin                  |        271 |       54424481 |         144024 |         200828 |         543267 |
| wait/synch/mutex/sql/LOCK_open                    |        155 |       37872311 |         144024 |         244337 |         630105 |
| wait/synch/mutex/mysys/THR_LOCK::mutex            |        123 |       29474088 |         141200 |         239626 |         645284 |
| wait/io/file/sql/MYSQL_LOG                        |          4 |       29470558 |         732122 |        7367639 |       15266544 |
| wait/io/file/mysys/charset                        |          3 |       29210044 |        2685624 |        9736681 |       21207534 |
| wait/synch/mutex/sql/LOG::LOCK_log                |         72 |       23395428 |         234039 |         324936 |         486081 |
| wait/synch/rwlock/sql/LOGGER::LOCK_logger         |         73 |       18702646 |         117196 |         256200 |         945334 |
| wait/synch/mutex/sql/LOCK_global_system_variables |         54 |       13213849 |         152849 |         244700 |         483257 |
+---------------------------------------------------+------------+----------------+----------------+----------------+----------------+
20 rows in set (0.00 sec)

Monitor> select * from EVENTS_WAITS_SUMMARY_BY_INSTANCE order by SUM_TIMER_WAIT desc limit 20;
+----------------------------------------------+-----------------------+------------+----------------+----------------+----------------+----------------+
| EVENT_NAME                                   | OBJECT_INSTANCE_BEGIN | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT |
+----------------------------------------------+-----------------------+------------+----------------+----------------+----------------+----------------+
| wait/synch/cond/mysys/my_thread_var::suspend |              19864808 |          1 | 12977803964893 | 12977803964893 | 12977803964893 | 12977803964893 |
| wait/io/file/sql/FRM                         |       140221841262352 |         47 |    60087698477 |         332879 |     1278461669 |    44527633212 |
| wait/synch/mutex/mysys/THR_LOCK_malloc       |              18775392 |       2704 |      512769918 |         138023 |         189633 |        4560760 |
| wait/io/file/myisam/kfile                    |       140221840473616 |         15 |      267174404 |         705294 |       17811626 |      187585259 |
| wait/synch/cond/sql/COND_thread_count        |              18595424 |          1 |      140672618 |      140672618 |      140672618 |      140672618 |
| wait/synch/mutex/sql/THD::LOCK_thd_data      |              19232304 |        183 |       91473949 |         206858 |         499857 |        1626271 |
| wait/io/file/myisam/dfile                    |       140221840474240 |          6 |       68756281 |        3855819 |       11459380 |       32138179 |
| wait/io/file/sql/pid                         |       140221843119376 |          3 |       65458555 |        4716786 |       21819518 |       35012658 |
| wait/io/file/sql/FRM                         |       140221837128352 |         21 |       64639595 |         428895 |        3078075 |        8455409 |
| wait/io/file/sql/ERRMSG                      |       140221837099024 |          5 |       58504102 |        1422237 |       11700820 |       23653118 |
| wait/synch/mutex/mysys/THR_LOCK_open         |              18775008 |        249 |       58411263 |         147201 |         234583 |         636106 |
| wait/io/file/sql/FRM                         |       140221837118368 |         21 |       57031386 |         411245 |        2715780 |        6571095 |
| wait/synch/mutex/sql/LOCK_plugin             |              18701152 |        273 |       55084591 |         144024 |         201775 |         543267 |
| wait/io/file/sql/FRM                         |       140221837118992 |         21 |       54062656 |         404891 |        2574412 |        6213859 |
| wait/io/file/sql/FRM                         |       140221837123360 |         21 |       53900982 |         440897 |        2566713 |        5968171 |
| wait/io/file/sql/FRM                         |       140221837125232 |         21 |       52953177 |         399243 |        2521579 |        6217036 |
| wait/io/file/sql/FRM                         |       140221837123984 |         21 |       52554287 |         402067 |        2502585 |        6229038 |
| wait/io/file/sql/FRM                         |       140221837124608 |         21 |       52463213 |         426071 |        2498248 |        6262220 |
| wait/io/file/sql/FRM                         |       140221837119616 |         21 |       52320248 |         414069 |        2491440 |        5970995 |
| wait/io/file/sql/FRM                         |       140221837126480 |         21 |       52121509 |         399243 |        2481976 |        6055009 |
+----------------------------------------------+-----------------------+------------+----------------+----------------+----------------+----------------+
20 rows in set (0.00 sec)

Monitor> select * from EVENTS_WAITS_SUMMARY_BY_THREAD_BY_EVENT_NAME order by SUM_TIMER_WAIT desc limit 20;
+-----------+----------------------------------------------+------------+----------------+----------------+----------------+----------------+
| THREAD_ID | EVENT_NAME                                   | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT |
+-----------+----------------------------------------------+------------+----------------+----------------+----------------+----------------+
|         4 | wait/synch/cond/mysys/my_thread_var::suspend |          1 | 12977803964893 | 12977803964893 | 12977803964893 | 12977803964893 |
|         3 | wait/io/file/sql/FRM                         |         35 |    60054668620 |         332879 |     1715847674 |    44527633212 |
|         2 | wait/io/file/sql/FRM                         |        174 |      717013247 |         584921 |        4120765 |       13067354 |
|         0 | wait/io/file/sql/FRM                         |        396 |      513479801 |         384064 |        1296666 |        7093182 |
|         0 | wait/synch/mutex/mysys/THR_LOCK_malloc       |       1862 |      296733212 |         138023 |         159362 |        1797123 |
|         3 | wait/io/file/myisam/kfile                    |          9 |      223447588 |         999343 |       24827509 |      187585259 |
|         2 | wait/synch/mutex/mysys/THR_LOCK_malloc       |        763 |      193050758 |         198033 |         253015 |        4560760 |
|         0 | wait/synch/cond/sql/COND_thread_count        |          1 |      140672618 |      140672618 |      140672618 |      140672618 |
|         0 | wait/io/file/myisam/kfile                    |         67 |      139106710 |         459253 |        2076219 |        5604934 |
|         2 | wait/synch/mutex/sql/THD::LOCK_thd_data      |        191 |       96076716 |         206858 |         503019 |        1626271 |
|         0 | wait/io/file/myisam/dfile                    |         44 |       80252079 |         621280 |        1823910 |        3555416 |
|         0 | wait/io/file/sql/casetest                    |         10 |       75699438 |        2265201 |        7569943 |       22200876 |
|         1 | wait/io/file/sql/pid                         |          3 |       65458555 |        4716786 |       21819518 |       35012658 |
|         0 | wait/io/file/sql/ERRMSG                      |          5 |       58504102 |        1422237 |       11700820 |       23653118 |
|         2 | wait/io/file/sql/dbopt                       |          8 |       49073354 |        2748458 |        6134169 |       11395899 |
|         4 | wait/io/file/myisam/dfile                    |          3 |       45757625 |        3855819 |       15252541 |       32138179 |
|         2 | wait/synch/mutex/mysys/THR_LOCK_open         |        116 |       32695213 |         218860 |         281855 |         366061 |
|         0 | wait/synch/mutex/sql/LOCK_plugin             |        187 |       30413774 |         144024 |         162640 |         344881 |
|         0 | wait/io/file/sql/MYSQL_LOG                   |          4 |       29470558 |         732122 |        7367639 |       15266544 |
|         0 | wait/io/file/mysys/charset                   |          3 |       29210044 |        2685624 |        9736681 |       21207534 |
+-----------+----------------------------------------------+------------+----------------+----------------+----------------+----------------+
20 rows in set (0.01 sec)

Harvesting the data for more

The performance schema currently exposes only a few aggregates. Since the data is available as a SQL table (as opposed to a SHOW xxx result), regular SQL queries can be used, to build higher level score cards.

Monitor> select THREAD_ID, SUM(COUNT_STAR), SUM(SUM_TIMER_WAIT) from EVENTS_WAITS_SUMMARY_BY_THREAD_BY_EVENT_NAME group by THREAD_ID;
+-----------+-----------------+---------------------+
| THREAD_ID | SUM(COUNT_STAR) | SUM(SUM_TIMER_WAIT) |
+-----------+-----------------+---------------------+
|         0 |            3189 |          1501188274 |
|         1 |              12 |            69725619 |
|         2 |            2009 |          1539441825 |
|         3 |             206 |         60356070610 |
|         4 |              82 |      12977904034039 |
+-----------+-----------------+---------------------+
5 rows in set (0.01 sec)

Monitor> select SUM(COUNT_STAR), SUM(SUM_TIMER_WAIT) from EVENTS_WAITS_SUMMARY_BY_EVENT_NAME;
+-----------------+---------------------+
| SUM(COUNT_STAR) | SUM(SUM_TIMER_WAIT) |
+-----------------+---------------------+
|            5556 |      13041387778194 |
+-----------------+---------------------+
1 row in set (0.00 sec)

The end

Interested readers are encouraged to try and play with the server to discover more, and look for more postings related to the MySQL performance schema.

-- Marc Alff