SHOW ENGINE engine_name
{STATUS | MUTEX}
SHOW ENGINE
displays operational
information about a storage engine. The following statements
currently are supported:
SHOW ENGINE INNODB STATUS SHOW ENGINE INNODB MUTEX
SHOW ENGINE INNODB
STATUS
displays extensive information from the
standard InnoDB
Monitor about the state of
the InnoDB
storage engine. For information
about the standard monitor and other InnoDB
Monitors that provide information about
InnoDB
processing, see
Section 13.6.13.2, “SHOW ENGINE INNODB
STATUS
and the InnoDB
Monitors”.
SHOW ENGINE INNODB
MUTEX
displays InnoDB
mutex
statistics. The statement displays the following fields:
Type
Always InnoDB
.
Name
The source file where the mutex is implemented, and the line number in the file where the mutex is created. The line number may change depending on your version of MySQL.
Status
The mutex status. This field displays several values if
UNIV_DEBUG
was defined at MySQL
compilation time (for example, in
include/univ.h
in the
InnoDB
part of the MySQL source tree). If
UNIV_DEBUG
was not defined, the statement
displays only the os_waits
value. In the
latter case (without UNIV_DEBUG), the information on which
the output is based is insufficient to distinguish regular
mutexes and mutexes that protect rw-locks (which allow
multiple readers or a single writer). Consequently, the
output may appear to contain multiple rows for the same
mutex.
count
indicates how many times the
mutex was requested.
spin_waits
indicates how many times
the spinlock had to run.
spin_rounds
indicates the number of
spinlock rounds. (spin_rounds
divided
by spin_waits
provides the average
round count.)
os_waits
indicates the number of
operating system waits. This occurs when the spinlock
did not work (the mutex was not locked during the
spinlock and it was necessary to yield to the operating
system and wait).
os_yields
indicates the number of
times a the thread trying to lock a mutex gave up its
timeslice and yielded to the operating system (on the
presumption that allowing other threads to run will free
the mutex so that it can be locked).
os_wait_times
indicates the amount of
time (in ms) spent in operating system waits, if the
timed_mutexes
system
variable is 1 (ON
). If
timed_mutexes
is 0
(OFF
), timing is disabled, so
os_wait_times
is 0.
timed_mutexes
is off by
default.
Information from this statement can be used to diagnose system
problems. For example, large values of
spin_waits
and spin_rounds
may indicate scalability problems.
MySQL Enterprise.
The SHOW ENGINE
statement provides valuable information about the state of
your server. For expert interpretation of this information,
subscribe to the MySQL Enterprise Monitor. For more
information, see http://www.mysql.com/products/enterprise/advisors.html.
engine_name
STATUS
User Comments
Add your own comment.