Home arrow MySQL arrow Page 8 - Back to Basics

The SHOW Commands - MySQL

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.

TABLE OF CONTENTS:
  1. Back to Basics
  2. Binary Versus Compiled From Source Installations
  3. MySQL.com Binary Versus Distribution Binary
  4. Configuration Files
  5. File Format
  6. Sample Files
  7. Reconfiguration
  8. The SHOW Commands
  9. SHOW PROCESSLIST
  10. SHOW STATUS
By: O'Reilly Media
Rating: starstarstarstarstar / 19
June 08, 2004

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement
 

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.

Buy the book!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!

Visit the O'Reilly Network http://www.oreillynet.com for more online content.



 
 
>>> More MySQL Articles          >>> More By O'Reilly Media
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: