The Problems of Managing MySQL’s Configuration

I want to keep a record of the configuration of the MySQL servers I manage. The configuration of some servers differs from others and over time the configuration may vary, partly as a result of upgrades in the mysql version or the use of the particular mysql instance, so tracking this is important.

Configuration items in MySQL can be thought of in 2 separate parts: the static configuration files which determine the behaviour of the server when it starts up (my.cnf) and the running configuration of the server in question. The latter information is usually obtained by running SHOW GLOBAL VARIABLES and SHOW SLAVE STATUS if the server is a slave.

I’d also like to compare the 2 sets of configuration so I can see if a local change has been made to the running server which is not reflected in its configuration file. I might want to correct this, or at least be aware of it.

However, collecting and comparing this configuration information is not as easy to do completely as one would really like. There are various inconsistencies which make this task troublesome.

  • Getting the default configuration out of the system is hard: mysqld --help --verbose sort of shows you this information but you have to filter out the junk.
  • Use of dashes ('-') in variable names in my.cnf or command line parameter names vs '_' in SHOW GLOBAL VARIABLES.
  • 5.5.16 seems to try to ‘partially’ startup when it shouldn’t.  See bug#63187.
  • You can not query the default configuration information from the running server.
  • SHOW SLAVE STATUS uses mixed-case variable names vs SHOW GLOBAL VARIABLES which uses lower-case variable names.
  • SHOW GLOBAL VARIABLES uses lower-case variable names vs SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES which shows them in upper-case.
  • Some variables get their multiple values sorted (others don’t)
  • Conversion of sql_mode special values is not consistent or clear.
  • my.cnf may use letter multipliers to indicate size (K, M, G), show global variables uses actual numbers.
  • my.cnf uses 0/1 vs SHOW GLOBAL VARIABLES using OFF/ON for certain switches. In some cases just having the variable defined implies ‘= 1′.
  • Some directory paths provided without a trailing ‘/’ in my.cnf have the trailing ‘/’ added in SHOW GLOBAL VARIABLES.
  • Some variables can take relative paths (relative to datadir) whereas SHOW GLOBAL VARIABLES shows the full path.
  • Some variable names differ in my.cnf compared to the equivalent setting in SHOW GLOBAL VARIABLES.
  • Some variable names have changed with newer versions of MySQL yet in many cases no warning is given that the new (correct?) variable name should be used and the old name is deprecated.
  • Not all my.cnf settings, DEFAULT SETTINGS (234 values) are shown in SHOW GLOBAL VARIABLES (312 values) in 5.5.16, (update 2013-04-08: 294 values vs 420 values in 5.6.10).
  • Some settings are incomplete (log_bin does not provide the full name of the binlog files set in my.cnf).
  • etc…

So all of these issues means that if I want to look at the /etc/my.cnf files and compare that to the running configuration it’s pretty hard to do well.  That may not seem like such a big deal until you realise that many mysql servers may have an uptime over a year. In that time the configuration may have evolved and this may mean you need to bring down mysql to adjust its configuration or alternatively see the difference and accept it’s not such a big issue and can be corrected the next time the server is restarted.

I’ve brought this up with Oracle support but guess that many in the community are so used to this they think it’s normal. If you manage several servers then it really is helpful to be able to collect this information and also see if servers run with the expected settings. Not doing so may mean that performance is affected and no-one notices.

So I’d like to ask that Oracle consider addressing this issue and remove some of the aforementioned inconsistencies, and perhaps also providing something like a SHOW DEFAULT SETTINGS or INFORMATION_SCHEMA.DEFAULT_SETTINGS output.  If you are in a company which goes through a lot of change and that includes the servers that are being managed, it will be much easier to ensure the servers are configured properly and if they were to address that.

Note software from other places makes this really easy and that can be very handy. Postfix‘s postconf shows the current configuration, but when called with -n will only show the non-default options and postconf -d shows all default settings.  So this certainly can be done.

Update: 2012-02-21

It seemed useful to actually add a list of bug reports and related posts.

Bug reports:

  • Bug#19612: InnoDB files per table my.cnf option is read inconsistently
  • Bug#55288: Use of the underscore or hyphen for variables and options is confusing
  • Bug#55705: configuration tool for MySQL server options
  • Bug#63187: mysqld –help –verbose should not try to startup but only provide help
  • Bug#64403: MySQL does not warn of deprecated variables on startup
  • Bug#64474: No clean way to get compiled-in settings from mysqld for a storage engine

Related posts:

Please advise me of other missing references.

Tags: , , , , , , , , , , , ,

5 Responses to “The Problems of Managing MySQL’s Configuration”

  1. The issues you mentioned are pretty familiar to me too. We set out to write a tool to compare a running server’s configuration to its my.cnf and found that it’s hard, as you said. The result is mk-config-diff (now pt-config-diff). But it’s imperfect and incomplete.

  2. Simon J Mudd says:

    Indeed. I have been working on my own script (which is still unfinished) but perhaps it’s better for me to report differences I see and get pt-config-diff improved. That avoids forking the problem.
    What would be nice is an option in pt-config-diff to allow for the configuration options to be changed: that is adjust the running configuration setting based on those in “/etc/my.cnf”, when possible and warn when the change is not dynamic and would require a restart of mysqld.

    https://bugs.launchpad.net/percona-toolkit/+bug/889739 is one issue I’ve found so far.
    Another issue is: https://blueprints.launchpad.net/percona-toolkit/+spec/pt-config-diff-replication-checks

  3. I believe the reverse is usually needed: adjust my.cnf to match the current settings. I’ve seen many cases of someone fixing a problem with SET, but never updating my.cnf, and if we just revert that, we’d risk reintroducing the problem.

  4. Simon J Mudd says:

    Well yes, and of course it depends. I can see cases where what you say is valid.

    I use puppet quite a bit and have recently moved from how the /etc/my.cnf is setup on the servers I manage and have moved over to a templating system. That’s great but has the inconvenience that it needs to be supplimented by something which adjusts the running config to match the adjusted template (if needed and possible). That’s something I need to work on now, as otherwise the running config won’t match what is in /etc/my.cnf, and it may not be obvious. Shutting down a running mysqld if it’s not needed is not really the way to pick up a config change, and if changes are made in the template to options such as expire_logs_days you really do want these changes to be applied immediately.

    I’m curious how others manage this.

  5. I suspect nobody does it “elegantly” :(

Leave a Reply