Databases Reference
In-Depth Information
Table Locking
The
Table_locks_immediate
and
Table_locks_waited
variables tell you how many locks
were granted immediately and how many had to be waited for. Be aware, however, that
they show only server-level locking statistics, not storage engine locking statistics.
InnoDB-Specific
The
Innodb_*
variables show some of the data included in
SHOW ENGINE INNODB STA
TUS
, discussed later in this appendix. The variables can be grouped together by name:
Innodb_buffer_pool_*
,
Innodb_log_*
, and so on. We discuss InnoDB's internals more
in a moment, when we examine
SHOW ENGINE INNODB STATUS
.
These variables are available in MySQL 5.0 and newer, and they have an important side
effect: they create a global lock and traverse the entire InnoDB buffer pool before re-
leasing the lock. In the meantime, other threads will run into the lock and block until
it is released. This skews some status values, such as
Threads_running
, so they will
appear higher than normal (possibly much higher, depending on how busy your server
is). The same effect happens when you run
SHOW ENGINE INNODB STATUS
or access these
statistics via the
INFORMATION_SCHEMA
tables (in MySQL 5.0 and newer,
SHOW STATUS
and
SHOW VARIABLES
are mapped to queries against the
INFORMATION_SCHEMA
tables behind
the scenes).
These operations can, therefore, be expensive in these versions of MySQL—checking
the server status too frequently (e.g., once a second) can cause significant overhead.
Using
SHOW STATUS LIKE
doesn't help, because it retrieves the full status and then post-
filters it.
There are many more variables in MySQL 5.5 than in 5.1, and even more in Percona
Server.
Plugin-Specific
MySQL 5.1 and newer support pluggable storage engines and provide a mechanism
for storage engines to register their own status and configuration variables with the
MySQL server. You might see some plugin-specific variables if you're using a pluggable
storage engine. Such variables always begin with the name of the plugin.
SHOW ENGINE INNODB STATUS
The InnoDB storage engine exposes a lot of information about its internals in the output
of
SHOW ENGINE INNODB STATUS
, or its older synonym,
SHOW INNODB STATUS
.
Unlike most of the
SHOW
commands, its output consists of a single string, not rows and
columns. It is divided into sections, each of which shows information about a different
part of the InnoDB storage engine. Some of the output is most useful for InnoDB
