Spec-Zone .ru
спецификации, руководства, описания, API
|
|
Spec-Zone .ru
спецификации, руководства, описания, API
|
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW
ENGINE displays operational information about a storage engine. The following statements currently
are supported:
SHOW ENGINE INNODB STATUSSHOW ENGINE INNODB MUTEXSHOW ENGINE PERFORMANCE_SCHEMA STATUS
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
14.2.4.4, "SHOW ENGINE INNODB STATUS and the InnoDB Monitors".
SHOW
ENGINE INNODB MUTEX displays InnoDB mutex statistics. Statement output
has the following columns:
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.i 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 permit 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 permitting 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.
Use SHOW ENGINE PERFORMANCE_SCHEMA STATUS to inspect the internal operation of the
Performance Schema code:
mysql> SHOW ENGINE PERFORMANCE_SCHEMA STATUS\G...*************************** 3. row *************************** Type: performance_schema Name: events_waits_history.sizeStatus: 76*************************** 4. row *************************** Type: performance_schema Name: events_waits_history.countStatus: 10000*************************** 5. row *************************** Type: performance_schema Name: events_waits_history.memoryStatus: 760000...*************************** 57. row *************************** Type: performance_schema Name: performance_schema.memoryStatus: 26459600...
This statement is intended to help the DBA understand the effects that different Performance Schema options have on memory requirements.
Name values consist of two parts, which name an internal buffer and a buffer
attribute, respectively. Interpret buffer names as follows:
An internal buffer that is not exposed as a table is named within parentheses.
Examples: (pfs_cond_class).size, (pfs_mutex_class).memory.
An internal buffer that is exposed as a table in the performance_schema
database is named after the table, without parentheses. Examples: events_waits_history.size,
mutex_instances.count.
A value that applies to the Performance Schema as a whole begins with performance_schema. Example: performance_schema.memory.
Buffer attributes have these meanings:
size is the size of the internal record used by the
implementation, such as the size of a row in a table. size values cannot be
changed.
count is the number of internal records, such as the
number of rows in a table. count values can be changed using Performance
Schema configuration options.
For a table, tbl_name.memorysize and count. For the Performance Schema as a
whole, performance_schema.memory is the sum of all the memory used (the sum
of all other memory values).
Some size and count attributes were named row_size and row_count before MySQL 5.7.1.
In some cases, there is a direct relationship between a Performance Schema configuration parameter and a SHOW ENGINE value. For example, events_waits_history_long.count corresponds to performance_schema_events_waits_history_long_size. In other cases, the
relationship is more complex. For example, events_waits_history.count corresponds
to performance_schema_events_waits_history_size (the number of rows per thread)
multiplied by performance_schema_max_thread_instances ( the number of threads).