Tuesday, March 10, 2015

MySQL Performance Schema : setup_actors ENABLED column

performance_schema.setup_actors is a table in MySQL Performance schema which could be used to specify what users/hosts one wants to have instrumentation on for. By default connection from all users and hosts are enabled to be instrumented for. Here is the default configuration :

mysql> select * from performance_schema.setup_actors;
+------+------+------+
| HOST | USER | ROLE |

+------+------+------+
| %    | %    | %    |
+------+------+------+
1 row in set (0.00 sec)

It is easy to see that one can set for what user/host he/she wants to have instrumentation ON.

BUT, how about if one wants to say except. i.e. how about if one wants to say except 'mayank' turn ON instrumentation for every other user. Thats what is implemented in 5.7.6 DMR.

In latest MySQL-5.7.6 DMR, a new column added to performance_schema.setup_actors table. Its called 'ENABLED'. So the default setting for setup_actors table is now :

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


So, by default all users are enabled (legacy behavior). But now, one can easily say that except 'mayank' enabled instrumentation for all other users. Something like below:

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)


And same goes for host column i.e. one can say turn on 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)


So in the above configuration, user name 'mayank', be it from any host, is not instrumented and any user from host 'localhost' is also not instrumented. Instrumentation is ON for rest all users/hosts combinations.

more information can be found at mysql documentation  here :

http://dev.mysql.com/doc/refman/5.7/en/setup-actors-table.html