Databases Reference
In-Depth Information
SHOW STATUS
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 com-
mand, 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.
In MySQL 5.1 and newer, you can select values directly from the
INFORMATION_
SCHEMA.GLOBAL_STATUS
and
INFORMATION_SCHEMA.SESSION_STATUS
tables.
We use the term “status variable” to refer to a value from
SHOW STATUS
and the term “system variable” to refer to a server configuration variable.
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 vari-
ables, MySQL now maintains some variables globally and some on a per-connection
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.
1
There are hundreds of status variables. 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 wrap-
ped around to zero. (This is very rarely a problem on 64-bit builds.)
1. There's a gotcha waiting here: if you use an old version of
mysqladmin
on a new server, it won't use
SHOW
GLOBAL STATUS
, so it won't display the “right” information.
