1

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?

Improve this question

1 Answer 1

Reset to default
2

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.

Improve this answer
2
  • My understanding is that the dynamic variables are those that you can change at runtime. In my specific case, we're using 5.5, so audit_log_file is listed as not dynamic. However, I know that you can just edit the my.cnf and restart the server to change the value of audit_log_file. I didn't think to narrow down my search by limiting to values that aren't dynamic, but you can still change some non-dynamic values with a simple restart of the server. Having that final list of variables, or a way in the documentation to identify those variables, would still be helpful to me.
    – Thomas Owens
    Commented Nov 5, 2016 at 10:21
  • 1
    Links line the ones I gave you are the best that I know of.
    – Rick James
    Commented Nov 5, 2016 at 15:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.