Bug #69098 Please make innodb_purge_threads a dynamic setting
Submitted: 30 Apr 2013 6:37 Modified: 28 Mar 2017 8:46
Reporter: Simon Mudd (OCA) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:5.6.11, 5.7, 8.0.1 OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: dynamic, innodb_purge_threads

[30 Apr 2013 6:37] Simon Mudd
This value is normally set to 1 but can be set to a higher value.

To aid in seeing if the setting helps performance it is most useful to make the setting dynamic as that means the server does not have to be shutdown and there's only a tiny "blip" while any purge threads are stopping before a new number is restarted.

Better documentation at http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_purge_threads on seeing how the change may be working and if the number of threads is too low or too high would also be helpful.

How to repeat:
Try to change the setting and you see:

mysql> set global innodb_purge_threads = 2;
ERROR 1238 (HY000): Variable 'innodb_purge_threads' is a read only variable

Suggested fix:
Make the setting dynamic. The temporary interruption to purge I/O to enable the change is certainly better than having to shut down the server and restart it. Thats very disruptive on busy systems.

Also please add more information to the documentation on how we can analyse if the current settings are suitable or not.
[28 Mar 2017 8:46] Simon Mudd
Still not dynamic in 8.0.1