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.