|
|
< Day Day Up > |
|
16.1 Interpreting mysqld Server InformationThe 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:
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 InformationMany 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:
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 InformationThe 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:
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 LoadStatus information that the server provides can be used to assess how hard it's working:
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 MessagesThe 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:
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:
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. |
|
|
< Day Day Up > |
|