Explaining performance schema tables ... with pictures

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.