SHOW MUTEX STATUS displays InnoDB mutex statistics. From MySQL 5.0.3 to 5.0.32, the statement displays the following output fields:

  • Mutex

    The mutex name. The name indicates the mutex purpose. For example, the log_sys mutex is used by the InnoDB logging subsystem and indicates how intensive logging activity is. The buf_pool mutex protects the InnoDB buffer pool.

  • Module

    The source file where the mutex is implemented.

  • 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 that a 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_waits_time 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_waits_time is 0. timed_mutexes is off by default.

From MySQL 5.0.33 on, the statement uses the same output format as that just described, but only 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 the following fields. In the latter case (without UNIV_DEBUG), the information on which the statement 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.

  • File

    The source file where the mutex is implemented.

  • Line

    The line number in the source file where the mutex is created. This may change depending on your version of MySQL.

  • OS_waits

    Same as OS_waits_time.

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.

SHOW MUTEX STATUS was added in MySQL 5.0.3. In MySQL 5.1, SHOW MUTEX STATUS is deprecated and SHOW ENGINE INNODB MUTEX should be used instead. The latter statement displays similar information but in a somewhat different output format. SHOW MUTEX STATUS is removed in MySQL 5.5.

User Comments
Sign Up Login You must be logged in to post a comment.