MySQL :: MySQL Performance Schema: Instrumentation Exceptions

The setup_actors table in MySQL Performance Schema can be used to specify what users and hosts one wants to have instrumentation enabled for. By default, connections from all users and hosts are instrumented:

mysql> select * from performance_schema.setup_actors;
+------+------+------+
| HOST | USER | ROLE |
+------+------+------+
| %    | %    | %    |
+------+------+------+
1 row in set (0.00 sec)

mysql> select * from performance_schema.setup_actors;

+------+------+------+

| HOST | USER | ROLE |

+------+------+------+

| % | % | % |

+------+------+------+

1 row in set (0.00 sec)

You can then use standard SQL against this setup_actors table in order to specify what users and hosts you want to have instrumentation enabled for.

But what about the case where you want to enable instrumentation for everyone except the ‘mayank’ user?

In the MySQL 5.7.6 DMR, a new ENABLED column has been added to the setup_actors table. So now the default configuration looks like this:

mysql> select * from performance_schema.setup_actors;
+------+------+------+---------+
| HOST | USER | ROLE | ENABLED |
+------+------+------+---------+
| %    | %    | %    | YES     |
+------+------+------+---------+
1 row in set (0.00 sec)

mysql> select * from performance_schema.setup_actors;

+------+------+------+---------+

+------+------+------+---------+

| % | % | % | YES |

+------+------+------+---------+

1 row in set (0.00 sec)

This new column now allows us to easily specify that we want to enable instrumentation for all users except ‘mayank’:

mysql> insert into performance_schema.setup_actors values ('%','mayank','%','NO');
Query OK, 1 row affected (0.00 sec)

mysql> select * from performance_schema.setup_actors;
+------+--------+------+---------+
| HOST | USER   | ROLE | ENABLED |
+------+--------+------+---------+
| %    | %      | %    | YES     |
| %    | mayank | %    | NO      |
+------+--------+------+---------+
2 rows in set (0.00 sec)

mysql> insert into performance_schema.setup_actors values ('%','mayank','%','NO');

Query OK, 1 row affected (0.00 sec)

mysql> select * from performance_schema.setup_actors;

+------+--------+------+---------+

+------+--------+------+---------+

| % | % | % | YES |

| % | mayank | % | NO |

+------+--------+------+---------+

2 rows in set (0.00 sec)

The same goes for hosts as well. For example, you may want to enable instrumentation for all hosts except ‘localhost’:

mysql> insert into performance_schema.setup_actors values ('localhost','%','%','NO');
Query OK, 1 row affected (0.00 sec)

mysql> select * from performance_schema.setup_actors;
+-----------+--------+------+---------+
| HOST      | USER   | ROLE | ENABLED |
+-----------+--------+------+---------+
| %         | %      | %    | YES     |
| %         | mayank | %    | NO      |
| localhost | %      | %    | NO      |
+-----------+--------+------+---------+
3 rows in set (0.00 sec)

mysql> insert into performance_schema.setup_actors values ('localhost','%','%','NO');

Query OK, 1 row affected (0.00 sec)

mysql> select * from performance_schema.setup_actors;

+-----------+--------+------+---------+

+-----------+--------+------+---------+

| % | % | % | YES |

| % | mayank | % | NO |

| localhost | % | % | NO |

+-----------+--------+------+---------+

3 rows in set (0.00 sec)

So with the above configuration, instrumentation is enabled for all user/host combinations except for the ‘mayank’ user (from any host) and any user from ‘localhost’.

We hope that you find this new feature useful! If you have any questions please feel free to post them here on the blog post or in a support ticket. If you feel that you have encountered any related bugs, please let us know via a comment here, a bug report, or a support ticket.

As always, THANK YOU for using MySQL!