What are MySQL Server Status Variables

Server System Variables

The MySQL server maintains many server system variables that indicate how it is configured. Each system variable has a default value. You can set variables at server startup by doing either of the following:

• Use options on the command line.
• Use an option file.

You can refer to system variable values in expressions. You can view the values that a server uses.

- Use this command (at a shell prompt) to see the values that a server uses based on its compiled-in defaults and any option files that it reads:

$ mysqld --verbose --help

- Use this command (at a shell prompt) to see the values that a server uses based on its compiled-in defaults, ignoring the settings in any option files:

$ mysqld --no-defaults --verbose --help

- Within the mysql client, use this command to see only the variable values, without the additional startup options:

SHOW GLOBAL VARIABLES;

For more information about server system variables, see the MySQL Reference Manual: http://dev.mysql.com/doc/mysql/en/server-system-variables.html.

Dynamic System Variables

You can change most variables dynamically while the server is running. In this way, you can modify the operation of the server without having to stop or restart it.

MySQL maintains two scopes that contain system variables. GLOBAL variables affect the overall operation of the server. SESSION variables affect its operation for individual client connections. Variables exist in one or the other scope, or in both.

Examples of variables and their scope include:

• Global only: key_buffer_size, query_cache_size
• Bothglobal and session:sort_buffer_size, max_join_size
• Session only: timestamp, error_count

When you change variable values, the following points apply:

• Setting a session variable requires no special privilege, but a client can change only its own session variables, not those of any other client.
• LOCALand @@localare synonyms for SESSION and @@session.
• If you do not specify GLOBAL or SESSION, SET changes the session variable if it exists and produces an error if it does not.

Displaying Dynamic System Variables

The specific SHOW VARIABLE session example in the slide uses ‘bulk%’ to find the variable for setting the insert buffer size, but any variable can be entered by using a whole variable name, or by entering a partial variable name with the percent sign (%) wildcard.

Specific variable types must be set as follows:

• Use a string value for variables that have a string type such as CHAR or VARCHAR.
• Use a numeric value for variables that have a numeric type such as INT or DECIMAL.
• Set variables that have a Boolean (BOOLor BOOLEAN) type to 0, 1, ON, or OFF. (If they are set on the command line or in an option file, use the numeric values.)
• Set variables of an enumerated type to one of the available values for the variable, but you can also set them to the number that corresponds to the desired enumeration value. For enumerated server variables, the first enumeration value corresponds to 0. This differs from ENUM columns, for which the first enumeration value corresponds to 1.

List all available variables and their values:

SHOW [GLOBAL|SESSION] VARIABLES;

List specific variable values:

mysql> SHOW VARIABLES LIKE 'bulk%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| bulk_insert_buffer_size | 8388608 |
+-------------------------+---------+

Set a new value and then list:

mysql> SET bulk_insert_buffer_size=4000000;
mysql> SHOW VARIABLES LIKE 'bulk%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| bulk_insert_buffer_size | 4000000 |
+-------------------------+---------+

For more information about dynamic server variables, see the MySQL Reference Manual: http://dev.mysql.com/doc/mysql/en/dynamic-system-variables.html.

Structured System Variables

A structured variable differs from a regular system variable in two ways:

• It is a structure with components that specify closely related server parameters.
• There can be several instances of a given type of structured variable. Each one has a different name and refers to a different resource maintained by the server.

MySQL supports a structured variable to govern the operation of key caches. MySQL supports one structured variable type, which specifies parameters governing the operation of key caches. A key cache structured variable has these components:

key_buffer_size
key_cache_block_size
key_cache_division_limit
key_cache_age_threshold

To refer to a component of a structured variable instance, you can use a compound name:

instance_name.component_name format

Examples:

hot_cache.key_buffer_size
hot_cache.key_cache_block_size
cold_cache.key_cache_block_size

For more information about structured system variables, see the MySQL Reference Manual: http://dev.mysql.com/doc/mysql/en/structured-system-variables.html.

Server Status Variables

Use the SHOW STATUS statement to assess the health of a system. Choose from two categories (similar to dynamic variables):

LOCALis a synonym for SESSION. If no modifier is present, the default is SESSION.

SHOW STATUS Example:

mysql> SHOW GLOBAL STATUS;
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Aborted_clients       |   0   |
| Aborted_connects      |   0   |
| Binlog_cache_disk_use |   0   |
| Bytes_received        |  169  |
| Bytes_sent            |  331  |
| Com_admin_commands    |   0   |
...

Some status variables have only a global value. For these, you get the same value for both GLOBAL and SESSION. For more information about server status variables, see the MySQL Reference Manual: http://dev.mysql.com/doc/mysql/en/server-status-variables.html.