This chapter from High Performance MySQL by Jeremy Zawodny and Derek J. Balling. (O'Reilly Media, ISBN: 0-596-00306-4, April 2004) talks about binary distributions, the sections in a configuration file, and some SHOW commands that provide a window into what’s going on inside MySQL. This book is for the MySQL administrator who has the basics down but realizes the need to go further.
When an administrator adjusts the server parameters, it’s common to go through an iterative process that involves making changes, restarting the server, performing some tests, and repeating the process. In fact, we’ll look at doing just that in Chapter 3. In the meantime, it’s worth mentioning that you should strongly con sider putting your MySQL configuration files into some sort of revision control sys tem (RCS, CVS, Subversion, etc.). Doing so gives you an easy way to track changes and back out of a bad configuration change.
As of MySQL 4.0, it’s possible to change server variables on the fly at runtime. For example, if you wanted to increase the size of the key buffer from what it was set to at startup, you might do the following:
mysql> SET GLOBAL key_buffer=50M;
This sets the global value for key_buffer to 50 MB.
Some variables, such as sort_buffer_size , can be set globally so that they affect all new threads on the server, or they can be defined so that they apply only to the cur rent MySQL client session. For example, if you wish to make a series of queries that might better use a large sort buffer, you can type:
mysql> SET SESSION sort_buffer_size=50M;
Variables set using the SESSION syntax are thread-specific and don’t alter the values other threads use.
It’s important to note that any change you make here, using either GLOBAL or SESSION syntax, will not survive a restart of the MySQL server; it’s completely transient in that regard. Runtime changes like this are excellent for testing scenarios such as, “If I increase my key_buffer value, will it improve my query performance?” Once you’ve found a value that works for you, though, remember to go back to your /etc/my.cnf file and put that value into your configuration file, or you may find yourself wonder ing weeks or months later why performance was so horrible after that reboot, com pletely forgetting the variable change you made on the fly months prior.
It’s also possible to use arguments on the mysqld_safe command line to override val ues defined in the configuration files. For example, you might do something like the following:
$ mysqld_safe -O key_buffer=50M
Like the earlier set-variable syntax, the -O syntax is deprecated as of Version 4.0. Here is a better way to issue that command:
$ mysqld_safe --key_buffer=50M
Command-line argument changes made in the mysql.server startup script will, obvi ously, survive from server restart to server restart, as long as that startup script is used to disable and reenable the server. It’s important to point out, though, that it’s usually better to have all your configuration declarations in a single place, so that maintenance doesn’t become a game of hide-and-seek with the configuration options, trying to remember where you set which values.
If you've enjoyed what you've seen here, or to get more information, click on the "Buy the book!" graphic. Pick up a copy today!
