SHOW ENGINE Syntax

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 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 , "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 MariaDB compilation time (for example, in include/univ.i in the InnoDB part of the MariaDB 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.row_size Status: 76
*************************** 4. row ***************************
 Type: performance_schema
 Name: events_waits_history.row_count Status: 10000
*************************** 5. row ***************************
 Type: performance_schema
 Name: events_waits_history.memory Status: 760000
...
*************************** 57. row ***************************
 Type: performance_schema
 Name: performance_schema.memory Status: 26459600
...

The intent of this statement is to help the DBA to understand the effects that different options have on memory requirements.

Name values consist of two parts, which name an internal buffer and an attribute of the buffer, respectively:

• Internal buffers that are exposed as a table in the performance_schema database are named after the table. Examples: events_waits_history.row_size, mutex_instances.row_count.
• Internal buffers that are not exposed as a table are named within parentheses. Examples: (pfs_cond_class).row_size, (pfs_mutex_class).memory.
• Values that apply to the Performance Schema as a whole begin with performance_schema. Example: performance_schema.memory.

Attributes have these meanings:

• row_size cannot be changed. It is the size of the internal record used by the implementation.
• row_count can be changed depending on the configuration options.
• For a table, tbl_name.memory is the product of row_size multiplied by row_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).

In some cases, there is a direct relationship between a configuration parameter and a SHOW ENGINE value. For example, events_waits_history_long.row_count corresponds to performance_schema_events_waits_history_long_size. In other cases, the relationship is more complex. For example, events_waits_history.row_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).

Retornar