The SHOW STATUS command shows server status variables in a two-column name/value table. Unlike the server variables we mentioned in the previous section, these are read-only. You can view the variables by either executing SHOW STATUS as a SQL command or executing mysqladmin extended-status as a shell command. If you use the SQL command, you can use LIKE and WHERE to limit the results; the LIKE does a standard pattern match on the variable name. The commands return a table of results, but you can’t sort it, join it to other tables, or do other standard things you can do with MySQL tables.
The behavior of SHOW STATUS changed greatly in MySQL 5.0, but you might not notice unless you’re paying close attention. Instead of just maintaining one set of global variables, MySQL now maintains some variables globally and some on a perconnection basis. Thus, SHOW STATUS contains a mixture of global and session variables. Many of them have dual scope: there’s both a global and a session variable, and they have the same name. SHOW STATUS also now shows session variables by default, so if you were accustomed to running SHOW STATUS and seeing global variables, you won’t see them anymore; now you have to run SHOW GLOBAL STATUS instead.*
In MySQL 5.1 and newer, you can select values directly from the INFORMATION_
SCHEMA.GLOBAL_STATUS and INFORMATION_SCHEMA.SESSION_STATUS tables.
There are hundreds of status variables in a MySQL 5.0 server, and newer versions include even more. Most either are counters or contain the current value of some status
metric. Counters increment every time MySQL does something, such as initiating a full table scan (Select_scan). Metrics, such as the number of open connections to the server (Threads_connected), may increase and decrease. Sometimes there are several variables that seem to refer to the same thing, such as Connections (the number of connection attempts to the server) and Threads_connected; in this case, the variables are related, but similar names don’t always imply a relationship.
Counters are stored as unsigned integers. They use 4 bytes on 32-bit builds and 8 bytes on 64-bit builds, and they wrap back to 0 after reaching their maximum values. If you’re monitoring the variables incrementally, you might need to watch for and correct the wrap; you should also be aware that if your server has been up for a long time, you might see lower values than you expect simply because the variable’s values have wrapped around to zero. (This is very rarely a problem on 64-bit builds.) The best way to look at many of these variables is to see how much their values change over the course of a few minutes. You can use mysqladmin extended-status –r -i 5 or innotop.
The following is an overview—not an exhaustive list—of the different categories of variables you’ll see in SHOW STATUS. For full details on a given variable, you should consult the MySQL manual, which helpfully documents them at http://dev.mysql.com/doc/en/mysqld-option-tables.html.
Source of Information : OReIlly High Performance MySQL Second Edition
The behavior of SHOW STATUS changed greatly in MySQL 5.0, but you might not notice unless you’re paying close attention. Instead of just maintaining one set of global variables, MySQL now maintains some variables globally and some on a perconnection basis. Thus, SHOW STATUS contains a mixture of global and session variables. Many of them have dual scope: there’s both a global and a session variable, and they have the same name. SHOW STATUS also now shows session variables by default, so if you were accustomed to running SHOW STATUS and seeing global variables, you won’t see them anymore; now you have to run SHOW GLOBAL STATUS instead.*
In MySQL 5.1 and newer, you can select values directly from the INFORMATION_
SCHEMA.GLOBAL_STATUS and INFORMATION_SCHEMA.SESSION_STATUS tables.
There are hundreds of status variables in a MySQL 5.0 server, and newer versions include even more. Most either are counters or contain the current value of some status
metric. Counters increment every time MySQL does something, such as initiating a full table scan (Select_scan). Metrics, such as the number of open connections to the server (Threads_connected), may increase and decrease. Sometimes there are several variables that seem to refer to the same thing, such as Connections (the number of connection attempts to the server) and Threads_connected; in this case, the variables are related, but similar names don’t always imply a relationship.
Counters are stored as unsigned integers. They use 4 bytes on 32-bit builds and 8 bytes on 64-bit builds, and they wrap back to 0 after reaching their maximum values. If you’re monitoring the variables incrementally, you might need to watch for and correct the wrap; you should also be aware that if your server has been up for a long time, you might see lower values than you expect simply because the variable’s values have wrapped around to zero. (This is very rarely a problem on 64-bit builds.) The best way to look at many of these variables is to see how much their values change over the course of a few minutes. You can use mysqladmin extended-status –r -i 5 or innotop.
The following is an overview—not an exhaustive list—of the different categories of variables you’ll see in SHOW STATUS. For full details on a given variable, you should consult the MySQL manual, which helpfully documents them at http://dev.mysql.com/doc/en/mysqld-option-tables.html.
Source of Information : OReIlly High Performance MySQL Second Edition
|
|
0 comments
Post a Comment