Mysql – What MySQL configuration options require more than editing the the.cnf file and restarting the server

configurationMySQLperformanceperformance-tuning

One example of this is the innodb_log_file_size configuration option. As discussed here, there are a series of steps that starts an hour before shutdown and goes to after shutting down. However, this doesn't appear to be documented in the MySQL documentation for this variable and I don't see a list of variables that you may not be able to adjust by simply editing the configuration file and restarting the server.

Does such a list of variables exist? What variables do I need to potentially watch when tuning MySQL to ensure compatibility with existing binary backups?

Best Answer

It depends on which version you are using. By the time you get to 8.0, virtually everything can be dynamically changed. Here are 2 types of pages, each with a list. Note the version number in the URL; change as needed.

http://dev.mysql.com/doc/refman/5.7/en/dynamic-system-variables.html

https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html -- Note the "Dynamic" column.

innodb_log_file_size used to be one of the worst settings to change. You could not simply change the config and restart -- the server would complain that the size does not match. Recently (sorry, don't know what version), the 'right thing' would be done with the files. But it still required a restart.

In my experience, 90% of users can get away with setting innodb_buffer_pool_size based on RAM size, then ignore the rest of the settings.

(If you wanted to talk specifically about innodb parameters, say so.)

If you want a critique of your settings, provide RAM size, SHOW VARIABLES; and SHOW GLOBAL STATUS; after it has been up at least a day. I have about 150 formulas to test; they typically lead to 5-10 recommended changes of the VARIABLES.

Related Question