39.1. Interpreting mysqld Server Information

39.1. Interpreting mysqld Server Information

The main purpose of MySQL Server is to perform queries on behalf of clients that need access to databases. However, the server also keeps track of information that is useful to administrators, and you can ask the server to report this information by using various forms of the SHOW statement:

• SHOW VARIABLES displays server system variables. These indicate such things as directory locations, server capabilities, and sizes of caches and buffers. You can set system variables to control how the server operates. They can be set at server startup, and many of them can be changed while the server is running. Also, the built-in values for many system variables can be specified at compile time if you build MySQL from source.

• SHOW STATUS displays server status variables that indicate the extent and types of activities the server is performing. These variables provide information such as how long the server has been running, number of queries processed, amount of network traffic, and statistics about the query cache. You can use status information to assess how much of a load your server is processing and how well it is handling the load. This information provides useful feedback for assessing whether system variables should be changed to improve server performance.

This chapter discusses several representative system and status variables, but many more exist. The MySQL Reference Manual provides a full list of variable names and meanings.

39.1.1. Accessing Server System Variables

Many aspects of server operation are controlled by means of a set of system variables that reflect server configuration. To display these variables, use the SHOW VARIABLES statement:

 mysql> SHOW VARIABLES; +-------------------------+-------------------+ | Variable_name           | Value             | +-------------------------+-------------------+ | back_log                | 50                | | basedir                 | /usr/local/mysql/ | | binlog_cache_size       | 32768             | | bulk_insert_buffer_size | 8388608           | | character_set           | latin1            | ... 

To display only those variables with names that match a given pattern, add a LIKE pattern-matching clause. The pattern is not case sensitive and may contain the '%' and '_' wildcard pattern metacharacters. For example, the sizes for many of the server's buffers can be displayed as follows:

 mysql> SHOW VARIABLES LIKE '%buffer_size'; +-------------------------+---------+ | Variable_name           | Value   | +-------------------------+---------+ | bulk_insert_buffer_size | 8388608 | | innodb_log_buffer_size  | 1048576 | | join_buffer_size        | 131072  | | key_buffer_size         | 8388600 | | myisam_sort_buffer_size | 8388608 | | preload_buffer_size     | 32768   | | read_buffer_size        | 131072  | | read_rnd_buffer_size    | 262144  | | sort_buffer_size        | 2097144 | +-------------------------+---------+ 

If the pattern contains no metacharacters, the statement displays only the named variable:

 mysql> SHOW VARIABLES LIKE 'datadir'; +---------------+------------------------+ | Variable_name | Value                  | +---------------+------------------------+ | datadir       | /usr/local/mysql/data/ | +---------------+------------------------+ 

System variables may be displayed in other ways as well. mysqladmin variables provides command-line access to the complete list of system variables. MySQL Administrator has a Health section with a Server Variables tab that displays system variables. Both clients implement this capability by sending a SHOW VARIABLES statement to the server and displaying the results.

System variables can be set at server startup using options on the command line or in option files. For example, on a Unix machine, you can put the following lines in the /etc/my.cnf option file to specify a data directory of /var/mysql/data and a key cache size of 64MB:

 [mysqld] datadir = /var/mysql/data key_buffer_size = 64M 

Numeric option values can have a suffix letter of K, M, or G to indicate units of kilobytes, megabytes, or gigabytes.

Some server system variables are static and can only be set at startup time. (You need not know which for the exam.) For example, you can specify the data directory by means of a datadir startup option, but you cannot tell a server that is running to use a different data directory. Other variables are dynamic and can be changed while the server is running. For example, both of the following statements tell the server to change the size of the key cache to 128MB:

 mysql> SET GLOBAL key_buffer_size = 128*1024*1024; mysql> SET @@global.key_buffer_size = 128*1024*1024; 

With a SET statement, you cannot use a suffix of K, M, or G to indicate units for the value, but you can use an expression.

The key_buffer_size variable is (as the preceding statements indicate) a global server variable. Some variables exist in both global and session forms:

• The global form applies server-wide and is used to initialize the corresponding session variable for new client connections. Each client may subsequently change its own session variable value.

• The session form is session-specific and applies only to a particular client connection.

To set global variables, you must have the SUPER privilege. Any client may set its own session variables.

An example of the type of variable that has both forms is storage_engine, which controls the default storage engine used for CREATE TABLE statements that do not specify a storage engine explicitly. The global storage_engine value is used to set the session storage_engine variable for each client when the client connects, but the client may change its session variable value to use a different default storage engine.

Session variables are set using syntax similar to that for setting global variables. For example, the default storage engine may be set either globally or only for the current connection using the following statements:

 mysql> SET GLOBAL storage_engine = MyISAM; mysql> SET @@global.storage_engine = MyISAM; mysql> SET SESSION storage_engine = InnoDB; mysql> SET @@session.storage_engine = InnoDB; 

LOCAL is a synonym for SESSION. Also, if you do not indicate explicitly whether to set the global or session version of a variable, MySQL sets the session variable. Each of these statements sets the session storage_engine variable:

 mysql> SET LOCAL storage_engine = InnoDB; mysql> SET @@local.storage_engine = InnoDB; mysql> SET storage_engine = InnoDB; mysql> SET @@storage_engine = InnoDB; 

To explicitly display global or session variable values, use SHOW GLOBAL VARIABLES or SHOW SESSION VARIABLES. Without GLOBAL or SESSION, the SHOW VARIABLES statement displays session values.

It's also possible to use SELECT to display the values of individual global or session values:

 mysql> SELECT @@global.storage_engine, @@session.storage_engine; +-------------------------+--------------------------+ | @@global.storage_engine | @@session.storage_engine | +-------------------------+--------------------------+ | MyISAM                  | InnoDB                   | +-------------------------+--------------------------+ 

If @@ is not followed by a global or session scope specifier, the server returns the session variable if it exists, and the global variable otherwise:

 mysql> SELECT @@storage_engine; +------------------+ | @@storage_engine | +------------------+ | InnoDB           | +------------------+ 

The MySQL Reference Manual indicates which variables are dynamic and whether they have global or session forms.

39.1.2. Accessing Server Status Variables

The server tracks many aspects of its own operation using a set of status variables. It makes the current values of these variables available through the SHOW STATUS statement, which you use much like SHOW VARIABLES:

 mysql> SHOW STATUS; +--------------------------------+-----------+ | Variable_name                  | Value     | +--------------------------------+-----------+ | Aborted_clients                | 244       | | Aborted_connects               | 1         | | Binlog_cache_disk_use          | 0         | | Binlog_cache_use               | 1         | | Bytes_received                 | 319102331 | | Bytes_sent                     | 178928432 | | Com_admin_commands             | 0         | ... 

To display only those variables with names that match a given pattern, add a LIKE pattern-matching clause. The pattern is not case sensitive and may contain the '%' and '_' wildcard pattern metacharacters. For example, all query cache status variable names begin with Qcache and may be displayed as follows:

 mysql> SHOW STATUS LIKE 'qcache%'; +-------------------------+--------+ | Variable_name           | Value  | +-------------------------+--------+ | Qcache_free_blocks      | 98     | | Qcache_free_memory      | 231008 | | Qcache_hits             | 21145  | | Qcache_inserts          | 12823  | | Qcache_lowmem_prunes    | 584    | | Qcache_not_cached       | 10899  | | Qcache_queries_in_cache | 360    | | Qcache_total_blocks     | 861    | +-------------------------+--------+ 

If the pattern contains no metacharacters, the statement displays only the named variable:

 mysql> SHOW STATUS LIKE 'Uptime'; +---------------+---------+ | Variable_name | Value   | +---------------+---------+ | Uptime        | 5084640 | +---------------+---------+ 

Status variables may be obtained in other ways as well. mysqladmin extended-status provides command-line access to the complete list of status variables, and mysqladmin status displays a brief summary. MySQL Administrator has a Health section with a Status Variables tab that displays status variables.

The following list indicates some of the ways you can use status information:

• Several status variables provide information about how many connections the server is handling, including the number of successful and unsuccessful connection attempts, and also whether successful connections terminate normally or abnormally. From these variables, you can determine the following information:

  • The total number of connection attempts (both successful and unsuccessful):

     Connections 

    
    

  • The number of unsuccessful connection attempts:

     Aborted_connects 

    
    

  • The number of successful connection attempts:

     Connections - Aborted_connects 

    
    

  • The number of successful connections that terminated abnormally (for example, if the client died or the network went down):

     Aborted_clients 

    
    

  • The number of successful connections that terminated normally:

     Connections - Aborted_connects - Aborted_clients 

    
    

  • The number of clients currently connected to the server:

     Threads_connected 

    
    

• The Com variables give you a breakdown of the number of statements that the server has executed by statement type. You can see all these variables with the following statement:

   mysql> SHOW STATUS LIKE 'Com%'; +------------------------+-------+ | Variable_name          | Value | +------------------------+-------+ | Com_admin_commands     | 0     | | Com_alter_db           | 0     | | Com_alter_table        | 2     | | Com_analyze            | 0     | | Com_backup_table       | 0     | | Com_begin              | 1     | | Com_change_db          | 629   | | Com_change_master      | 0     | ... 

  
  

  Or you can name specific variables:

   mysql> SHOW STATUS LIKE 'Com_delete'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Com_delete    | 315   | +---------------+-------+ mysql> SHOW STATUS LIKE 'Com_update'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Com_update    | 19447 | +---------------+-------+ mysql> SHOW STATUS LIKE 'Com_select'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Com_select    | 46073 | +---------------+-------+ 

  
  

  Com_select does not include the number of queries that are processed using the query cache because those queries are not executed in the usual sense. Their results are pulled directly from the query cache without consulting any tables. The number of such queries is given by the Qcache_hits status variable. See Section 39.4, "Using the Query Cache."

• The server caches open file descriptors when possible to avoid repeated file-opening operations, but a cache that's too small will not hold all the file descriptors you need. The Opened_tables variable indicates the number of times the server had to open files to access tables. It provides a measure of whether your table cache is large enough. See Section 39.3, "Tuning Memory Parameters."

• Bytes_received and Bytes_sent show the amount of traffic sent over the network between the server and its clients.

Status information can help you determine how smoothly the server is running or how well it's performing. Section 39.2, "Measuring Server Load," discusses some ways to use status variables to assess server load.