MySQL Settings (also known as Server Variables) have interesting property. When you set variable in running server this change is not persisted in any way and server will be back to old value upon restart. MySQL also does not have option to re-read config file without restarting as some other software so approach to change config file and when instruct server to re-read it also does not work. This leads to runtime settings being different from settings set in config file, and unexpected change on restart a frequent problem.
pt-config-diff is the tool which can help with this problem a lot, being able to compare settings in my.cnf to those server is currently running with. The problem however this only works well if settings are set in my.cnf as if default option was used and we change it in run time we can’t detect such change easily because MySQL Server does not seems to have an easy way to check what was the default value for given Server Variable.
The only way I’m aware about is running the server from command line with –no-defaults –verbose –help options:
1 2 3 4 5 6 7 8 9 10 11 12 | pz@ubuntu:~$ /usr/sbin/mysqld --no-defaults --verbose --help ... timed-mutexes FALSE tmp-table-size 16777216 tmpdir /tmp transaction-alloc-block-size 8192 transaction-isolation REPEATABLE-READ transaction-prealloc-size 4096 updatable-views-with-limit YES userstat FALSE verbose TRUE wait-timeout 28800 |
Which is however rather ugly and only works with shell access to the server which is not always the case.
Interesting enough MySQL Allows you to SET variable to default value (compile time default, not the one server was started with) yet there seems not to be a way to read it:
1 2 3 4 5 6 7 8 9 10 | mysql> set global sort_buffer_size=DEFAULT; Query OK, 0 rows affected (0.00 sec) mysql> select @@global.sort_buffer_size; +---------------------------+ | @@global.sort_buffer_size | +---------------------------+ | 2097152 | +---------------------------+ 1 row in set (0.00 sec) |
This could be used as technique to detect the value for DEFAULT variables for SESSION variables, yet for some GLOBAL variables setting them back and forth would not be safe.
The simple change which would make dealing with MySQL variables in automated way a lot more convenient would be extending INFORMATION_SCHEMA.GLOBAL_VARIABLES Currently as of MySQL 5.5 it contains only variable name and value. Yet I would suggest adding few more columns such as DEFAULT – to hold compile time default value for variable and STARTUP to hold the value the server was started with.
It also might be good idea to extend SELECT syntax to ease querying of variable global value Right now I can select:
1 2 3 4 5 6 7 | mysql> select @@global.sort_buffer_size; +---------------------------+ | @@global.sort_buffer_size | +---------------------------+ | 2097152 | +---------------------------+ 1 row in set (0.00 sec) |
If I could only refer to “default” or “startup” in addition to “global” and “session” prefixes which are available now it would be quite nice.
Shameless plug: the mycheckpoint monitoring tool records not only the server’s dynamic data (status variables, slave, os status), but also the server variables. So you get a periodic snapshot of all server variables.
It provides with a VIEW which crunches monitored data to tell you which server variables have been changed, from what value to what value, and when.
I’ve already found this to help out in the initial problem you describe: what happens when someone changes a parameter dynamically and then restarts the server?
Well, you would get the information about the variable being changed in the first place, but you would also notice a pile of changes right next to server startup (another status being monitored). The two are easy to lay side by side.
And the best is, you get to know the value of the dynamically set variable prior to server restart — something that otherwise gets completely lost.
+1 to everything said in the post, and I would add a parameter which tells me exactly which my.cnf files are being used by the server (I hate hunting them down). Is it /etc/my.cnf? /etc/mysql/my.cnf? /usr/local/mysql/data/my.cnf? /var/lib/mysql/my.cnf?
Shlomi,
Yes… I think one of the problems here is MySQL concept of treating all options as command line parameters which may not come from config file at all. I think software which has main config file which is being read (which may have includes etc) is much better in a way being able to see it.
And yes I agree tracking history of variables is a good thing to do.. you might be able to learn a lot about performance impact from long change correlation.
Oracle solved this problem many years ago with the introduction of the “spfile”, a binary file that holds all non-default parameters and supports dynamic updates. They also introduced ways to convert from a pfile (a text config file like my.cnf) and the spfile via commands like “create spfile from pfile;”, and the ability to startup the database with whatever pfile or spfile you want via commands like “startup pfile=/tmp/inittest.ora” or “startup spfile=/tmp/sptest.ora”.The most obvious solution would be for mysql to do something similar.
It would be nice to get real support for running multiple instances of MySQL on the same server via named instances like Oracle also– I’m sure mysql will get there eventually; it’s been slowly but surely catching up to oracle 8i for the past decade.
The mysql-test-run.pl script does something similar, which is to dump the contents of GLOBAL_VARIABLES before and after a test run. In this case, this dump could be done in init_file, so it would work (to some extent) to identify any variable that was changed at runtime if we compare with a dump taken right before shutdown.
I’ve also blogged about MySQL’s server configuration management problems (http://blog.wl0.org/2011/11/managing-mysqls-configuration/) and agree that having a way to see default, and startup values would be good. If that were solved together with normalising the variables names between the /etc/my.cnf and SHOW GLOBAL VARIABLES output then we would be in a much better state than we are now.
So +1 for this post.
Davi,
Yes… it is possible (and good idea) to have external monitoring which tracks not only status counters but also variables, when one would be able to track history of changes. As consultant though I would like to be able to get it all from running MySQL without anything setup in advance.
Simon,
There is a lot of “mess” in this space. The fact for example not all variables are shown in “show variables” difference in namings and bunch of aliases accumulated over time make it pretty complicated. I constantly see people for example setting table_cache and table_open_cache at the same time to different values while it is same in new MySQL versions 🙂
SHOW VARIABLES is very frustrating. What I have always wanted is more columns in the output: one for the options the server started with, one for the global defaults, one for the compiled-in defaults, one for the current session’s variables. With NULLs where there is no value, e.g. the session-level column is NULL for a global-only variable.
SHOW STATUS would be better like this, too.
Peter, make a bug report (feature request) for this and post the URL. I’m also adjusting some of my posts where I complain about current functionality, as it’s fair to say that if there’s no bug. report it’s possible that the comments just may not get seen. Having the bug report ensures that Oracle see the wanted “feature request”, even if that may take a long time to appear. Then Oracle customers can reference that feature request and say “I want that too”.