Documentation Home
MySQL Performance Schema
Related Documentation Download this Excerpt
PDF (US Ltr) - 0.6Mb
PDF (A4) - 0.6Mb

5.6 Pre-Filtering by Thread

The threads table contains a row for each server thread. Each row contains information about a thread and indicates whether monitoring is enabled for it. For the Performance Schema to monitor a thread, these things must be true:

  • The thread_instrumentation consumer in the setup_consumers table must be YES.

  • The threads.INSTRUMENTED column must be YES.

  • Monitoring occurs only for those thread events produced from instruments that are enabled in the setup_instruments table.

The INSTRUMENTED column in the threads table indicates the monitoring state for each thread. For foreground threads (resulting from client connections), the initial INSTRUMENTED value is determined by whether the user account associated with the thread matches any row in the setup_actors table.

For background threads, there is no associated user. INSTRUMENTED is YES by default and setup_actors is not consulted.

The initial setup_actors contents look like this:

mysql> SELECT * FROM performance_schema.setup_actors;
| %    | %    | %    |

The HOST and USER columns should contain a literal host or user name, or '%' to match any name.

The Performance Schema uses the HOST and USER columns to match each new foreground thread. (ROLE is unused.) The INSTRUMENTED value for the thread becomes YES if any row matches, NO otherwise. This enables instrumenting to be applied selectively per host, user, or combination of host and user.

By default, monitoring is enabled for all new foreground threads because the setup_actors table initially contains a row with '%' for both HOST and USER. To perform more limited matching such as to enable monitoring only for some foreground threads, you must delete this row because it matches any connection.

Suppose that you modify setup_actors as follows:

TRUNCATE TABLE performance_schema.setup_actors;

Now setup_actors is empty and there are no rows that could match incoming connections. Consequently, the Performance Schema sets the INSTRUMENTED column to NO for all new foreground threads.

Suppose that you further modify setup_actors:

INSERT INTO performance_schema.setup_actors
(HOST,USER,ROLE) VALUES('localhost','joe','%');
INSERT INTO performance_schema.setup_actors
(HOST,USER,ROLE) VALUES('%','sam','%');

Now the Performance Schema determines how to set the INSTRUMENTED value for new connection threads as follows:

  • If joe connects from the local host, the connection matches the first inserted row.

  • If joe connects from any other host, there is no match.

  • If sam connects from any host, the connection matches the second inserted row.

  • For any other connection, there is no match.

Modifications to the setup_actors table affect only foreground threads created subsequent to the modification, not existing threads. To affect existing threads, modify the INSTRUMENTED column of threads table rows.