MySQL Bugs: #64941: performance_schema setup tables do not hold their settings permanently

Bug #64941	performance_schema setup tables do not hold their settings permanently
Submitted:	11 Apr 2012 9:38	Modified:	10 Jan 2013 12:25
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Performance Schema	Severity:	S4 (Feature request)
Version:	5.5.22	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	windmill

Description:
This doesn't seem important until you need to change it but that issue has happened to me.

The following Bug "13898343 : THREAD LOOPS ENDLESSLY IN LF_PINBOX_PUT_PINS WHILE HOLDING LOCK_THREAD_COUNT" causes an issue on 5.5.22. This will be fixed in 5.5.23 but requires a workaround of disabling some of the performance_schema instrumentation.

To do so required modifying in my case: setup_instruments and setup_consumers, disabling some of the settings. However if the server is restarted the changed settings are not retained and as such the server becomes again subject to the previous issue.

In my opinion configuration settings of performance_schema should be permanent over a restart of mysqld, so that the behaviour of the server does not change significantly or in a way which is not intended.

5.6 includes a lot more instrumentation of things so this will become even more important.

How to repeat:
1. start mysqld
2. run something like the following.

UPDATE	setup_instruments SET	ENABLED = 'NO' WHERE	NAME LIKE '%/io/%' AND	ENABLED = 'YES';
UPDATE	setup_consumers SET	ENABLED = 'NO' WHERE	NAME LIKE 'file%' AND	ENABLED = 'YES';

3. restart mysql 
4. see that the settings have not been carried over to the restarted server.

Suggested fix:
Ensure that the setup tables' contents are maintained over a server restart.

This is a valid feature request.

However, it is already implemented in 5.6.

http://dev.mysql.com/doc/refman/5.6/en/performance-schema options.html#option_mysqld_performance_schema_instrument

So this FR is about the backport to 5.5.

see also : http://dev.mysql.com/doc/refman/5.6/en/performance-schema-startup-configuration.html

and 

http://marcalff.blogspot.co.uk/2011/12/mysql-564-performance-schema.html

Analysis from Marc Alff:

For 5.5, the work around is to write a .sql file that contains all the configuration desired, using UPDATE statements on tables performance_schema.setup_*, and to use the following server option to execute this .sql file at startup:

  --init-file=name    Read SQL commands from this file at startup

When using the same my.cnf file for multiple servers in a big deployment,
this implies to deploy a copy of the .sql file used as well.

The feature is implemented already in 5.6, and there is a work around for 5.5

Backporting this 5.6 feature to 5.5 is not needed (and not desirable, to avoid changing production releases).

Marking as "won't fix" for the backport.