Performance_schema considerations.

by Nov 23, 2011

I have for the first time been spending some time trying to understand the performance_schema. It is not easy to understand everything unless you are very well-versed in server internals (what I am not) and much information available here is probably more useful for server developers and testers than for ordinary users. But anyway some of the P_S tables are reasonable simple and useful. For instance the 'accounts' and 'hosts' tables (introduced in MySQL 5.6x) are immensely useful for everybody.

The reason why I spent time with this now was (I am totally egoistic, of course) that we just released the first beta in a new MONyog release cycle where you may expose P_S contents for monitoring. Please refer this Blog.

However I have two concerns with the P_S implementation. This derives from the fact that P_S is to some extent  updateable for users having sufficient privileges. You may for instance disable/enable 'consumers' and decide whether information should be recorded in counts of CPU clock-cycles or microseconds etc. Also some tables may be truncated in order to discard old information and free memory.

The interface for this is standard SQL statements (UPDATE, TRUNCATE). IMHO this leads to two problems/concerns:

1) Server restart. If the server restarts all P_S settings are reset to default. The UPDATEs from last running instance are lost. I am  simply missing a mechanism to load the UPDATEs again automatically at startup. I do not favour  introducing a lot of new server startup options and variables (that would be around 100 I think if all cases should be covered and there already are more than enough options IMHO). One option could be a P_S configuration file ('performance_schema.ini') or maybe an EVENT executing at startup ("at startup" could be a useful schedule setting for an EVENT in other contexts as well – but it is not in any server version currently). Also the problem cannot be worked around an automated way currently on Windows as far as I can see, as on this platform the server is not started by a 'script' but by an executable (the 'Windows Service Manager' reading details from a registry key). On Unix-type platforms you may (I think so!) modify the server startup script adding a call to Perl/Python/whatever script performing the updates on the P_S tables.

2) Multi-user/multi-admin environment. If for instance GRANTS for 'peter'@'thishost' are like "GRANT CREATE USER, SELECT,UPDATE ON *.* TO 'peter'@'thishost' WITH GRANT OPTION;" then peter may "GRANT SELECT,UPDATE ON performance_schema.* TO 'jimmy'@'thathost'LOCK TABLES performance_schema.setup_consumers READ;"). I don't find it fully satisfactory however, but I have no better solution with current implementation and also no usable idea for an improved implementation.  

Any thoughts?