16.1 Interpreting mysqld Server Information
The main purpose of the 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 time, and many of them can be changed while the server is running. Also, the built-in value 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.
16.1.1 Accessing Server Configuration Information
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 |
| 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 can be displayed in other ways as well. mysqladmin variables provides command-line access to the complete list of system variables. The MySQLCC graphical client provides access to them in its server administration window. 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 time 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 buffer size of 16MB:
[mysqld]
datadir = /var/mysql/data
key_buffer_size = 16M
Numeric option values can have a suffix letter of K, M, or G to indicate units of kilobytes, megabytes, or gigabytes, respectively.
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's running to use a different data directory. Other variables are dynamic and can be changed while the server is running. For example, either of the following statements tells the server to change the size of the key buffer to 24MB:
mysql> SET GLOBAL key_buffer_size = 24*1024*1024;
mysql> SET @@global.key_buffer_size = 24*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 a global variable, 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 table_type, which controls the default table type used for CREATE TABLE statements that do not specify a table type explicitly. The global table_type value is used to set the session table_type variable for each client when the client connects, but the client may change its session variable value to use a different default table type.
Session variables are set using syntax similar to that for setting global variables. For example, the default table type may be set either globally or only for the current connection using the following statements:
mysql> SET GLOBAL table_type = MyISAM;
mysql> SET @@global.table_type = MyISAM;
mysql> SET SESSION table_type = InnoDB;
mysql> SET @@session.table_type = 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 table_type variable:
mysql> SET LOCAL table_type = InnoDB;
mysql> SET @@local.table_type = InnoDB;
mysql> SET table_type = InnoDB;
mysql> SET @@table_type = 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.table_type, @@session.table_type;
+---------------------+----------------------+
| @@global.table_type | @@session.table_type |
+---------------------+----------------------+
| 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 @@table_type;
+--------------+
| @@table_type |
+--------------+
| INNODB |
+--------------+
The MySQL Reference Manual indicates which variables are dynamic and whether they have global or session forms.
16.1.2 Accessing Server Status Information
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 | 160 |
| Aborted_connects | 6 |
| Bytes_received | 34971464 |
| Bytes_sent | 43375040 |
| Com_admin_commands | 15004 |
...
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_queries_in_cache | 360 |
| Qcache_inserts | 12823 |
| Qcache_hits | 21145 |
| Qcache_lowmem_prunes | 584 |
| Qcache_not_cached | 10899 |
| Qcache_free_memory | 231008 |
| Qcache_free_blocks | 98 |
| 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. The MySQLCC graphical client provides access to status information in its server administration window.
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 queries 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_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 | 10691 |
+---------------+-------+
mysql> SHOW STATUS LIKE 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select | 23727 |
+---------------+-------+
Com_select does not include the number of queries 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 16.3, "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 16.2, "Tuning Memory Parameters." Bytes_received and Bytes_sent show the amount of traffic sent over the network.
Status information can help you determine how smoothly the server is running or how well it's performing. Section 16.1.3, "Measuring Server Load," discusses some ways to use status variables to assess server load.
16.1.3 Measuring Server Load
Status information that the server provides can be used to assess how hard it's working:
Several status variables displayed by SHOW STATUS provide load information. For example, Questions indicates the number of queries the server has processed and Uptime indicates the number of seconds the server has been running. Combining these, the ratio Questions/Uptime tells you how many queries per second the server has processed. Slow_queries indicates the number of queries that take a long time to process. Ideally, its value should increase slowly or not at all. If it increases quickly, you might have a problem with certain queries. The slow query log shows the text of slow queries and provides information about how long they took. Restart the server with the slow query log enabled, let it run for a while, and then take a look at the log to see which queries turn up there. You can use this log to identify queries that might need optimizing, as discussed in section 13.2.1, "Identifying Candidates for Query Analysis." SHOW PROCESSLIST displays information about the activity of each currently connected client. For example, the presence of a large number of blocked queries might indicate that another connection is running a query that's inefficient and should be examined to see whether it can be optimized. The SHOW PROCESSLIST statement always shows your own queries. If you have the PROCESS privilege, it also shows queries being run by other accounts. To get a concise report of the server's load status, invoke the mysql client program and use its STATUS (or \s) command to display a general snapshot of the current connection state. The last part of the output provides some information about the server load:
mysql> STATUS;
--------------
mysql Ver 12.22 Distrib 4.0.16, for intel-linux (i686)
Connection id: 34816
Current database: world
Current user: myname@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Server version: 4.0.16-log
Protocol version: 10
Connection: Localhost via UNIX socket
Client characterset: latin1
Server characterset: latin1
UNIX socket: /tmp/mysql.sock
Uptime: 51 days 3 hours 40 min 37 sec
Threads: 4 Questions: 216232 Slow queries: 5 Opens: 652
Flush tables: 6 Open tables: 64 Queries per second avg: 0.050
--------------
For a discussion of ways to reduce server load by helping it work more effectively, see sections 16.2, "Tuning Memory Parameters," and 16.3, "Using the Query Cache."
16.1.4 Accessing Server Error Messages
The preceding sections describe how to obtain information that the server provides during the course of its normal operation. The server also provides diagnostic information about exceptional conditions in the form of error messages:
On Windows, the server opens an error log file, which by default is named host_name.err in the data directory. (Older versions use the name mysql.err.) If you start the server from the command line with the --console option, it writes error information to the console window rather than to the error log. On Unix, if you invoke mysqld directly, it sends diagnostics to its standard error output, which normally is your terminal. If you invoke the server using mysqld_safe (or mysql.server, which in turn invokes mysqld_safe), diagnostic output is redirected to an error log. By default, the error log name is host_name.err in the server's data directory.
Diagnostic output produced by the server includes information about normal startup and shutdown. It also includes messages about abnormal execution conditions, such as the following:
Unrecognized startup options. If the server attempts to start up but quits almost immediately, you might have a bad option listed in an option file. The error log can tell you this. Failure of the server to open its network interfaces: the TCP/IP port, the Windows named pipe, or the Unix socket file. The server cannot use an interface that is already in use by another server. Storage engine initialization failure. This might occur due to incorrect configuration of the storage engine (for example, if a file specified as part of the InnoDB tablespace cannot be opened), or detection of conditions that make it impossible to continue (for example, if a storage engine detects table corruption but cannot correct it automatically). Failure to find SSL certificate or key files that are named by startup options. Inability of the server to change its user ID on Unix. This can happen if you specify a --user option but do not start the server as root so that it can relinquish root privileges and change to a different user. Problems related to replication.
On Unix, if the server was started by the mysqld_safe script, mysqld_safe itself may write information to the error log. For example, if mysqld_safe detects that the server has died, it automatically restarts the server after writing mysqld restarted to the log.
| 