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.
MySQL users often wonder how to find out what their server is actually doing at any point in time—usually when things start to slow down or behave strangely. You can look at operating system statistics to figure out how busy the server is, but that really doesn’t reveal much. Knowing that the CPU is at 100% utilization or that there’s a lot of disk I/O occurring provides a high-level picture of what is going on, but MySQL can tell far more.
Several SHOW commands provide a window into what’s going on inside MySQL. They provide access to MySQL’s configuration variables, ongoing statistics, and counters, as well as a description of what each client is doing.
Show Variables
The easiest way to verify that configuration changes have taken effect is to ask MySQL for its current variable settings. The SHOW VARIABLES command does just that. Executing it produces quite a bit of output, which looks something like this:
mysql> SHOW VARIABLES;
Variable_name
Value
back_log
basedir
binlog_cache_size
character_set
concurrent_insert
connect_timeout
datadir
20
mysql
32768
latin1
ON
5
/home/mysql/data/
The output continues from there, covering over 120 variables in total. The variables are listed in alphabetical order, which is convenient for reading, but sometimes related variables aren’t anywhere near each other in the output. The reason for this is because as MySQL evolves, new variables are added with more descriptive names, but the older variable names aren’t changed; it would break compatibility for any program that expects them. * (* In the rare event they do change, MySQL retains the old names as aliases for the new ones.)
Many of the variables in the list may be adjusted by a set-variable entry in any of MySQL’s configuration files. Some of them are compiled-in values that can not be changed. They’re really constants (not variables), but they still show up in the out put of SHOW VARIABLES . Still others are boolean flags.
Notice that the output of SHOW VARIABLES (and all of the SHOW commands, for that matter) looks just like the output of any SQL query. It’s tabular data. MySQL returns the output in a structured format, making it easy to write tools that can summarize and act on the output of these commands. We’ll put that to good use in later chapters.
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!
