Skip navigation links
MySQL 5.4 Feature Summary :: 6 Diagnostic and Monitoring Capabilities
7 Configuration Differences in MySQL 5.4 from MySQL 5.1 »
Section Navigation      [Toggle]

6. Diagnostic and Monitoring Capabilities

MySQL 5.4 provides improved access to execution and performance information. Diagnostic improvements include Dtrace probes, expanded SHOW ENGINE INNODB STATUS output, and new status variables.

DTrace Support

MySQL 5.4 includes support for DTrace. The DTrace probes work on Solaris, Mac OS X, and FreeBSD. For information on using DTrace in MySQL, see Tracing mysqld Using DTrace.

Enhanced SHOW ENGINE INNODB STATUS Output

The output from SHOW ENGINE INNODB STATUS includes more information. These changes are based on patches developed by Google, about which more information can be found here: 

http://code.google.com/p/google-mysql-tools/wiki/InnodbStatus
http://code.google.com/p/google-mysql-tools/wiki/NewShowInnodbStatus

A description of revisions to statement output follows.

Output includes a BACKGROUND THREAD section: 

----------
BACKGROUND THREAD
----------
srv_master_thread loops: 12 1_second, 12 sleeps, 0 10_second, 2 background,
  2 flush
srv_master_thread log flush: 12 sync, 15 async
srv_wait_thread_mics 0 microseconds, 0.0 seconds
spinlock delay for 6 delay 20 rounds is 15 mics

The srv_master_thread lines shows work done by the main background thread.

The srv_wait_thread_mics line indicates how many microseconds threads wait to use InnoDB due to innodb_thread_concurrency limits.

The spinlock line shows the number of microseconds the spinloop spins before sleeping (the time spent busy-waiting).

The SEMAPHORES section includes a line to show the number of spinlock rounds per OS wait for a mutex: 

----------
SEMAPHORES
----------
...
Spin rounds per wait: 0.00 mutex, 20.00 RW-shared, 0.00 RW-excl

Lines in the FILE I/O section that show information for individual I/O threads display additional information to indicate the number of pages read and written, the number of system calls for those operations, and the time in milliseconds to complete them (total and average per request). 

--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
  reads 0 writes 0 requests 0 io secs 0.000000 io msecs/request 0.000000
  max_io_wait 0.000000
I/O thread 1 state: waiting for i/o request (log thread)
  reads 0 writes 3 requests 3 io secs 0.006906 io msecs/request 2.302000
  max_io_wait 6.861000
I/O thread 2 state: waiting for i/o request (read thread)
  reads 33 writes 0 requests 2 io secs 0.005925 io msecs/request 2.962500
  max_io_wait 5.895000
...
Summary of background IO slot status: 0 issued, 0 done, 0 claimed, sleep set 0

The Summary of background IO slot status line describes the state of pending IO requests. InnoDB provides real or simulated asynchronous I/O to perform disk I/O for many uses. Requests are put into request arrays with a fixed number of slots per array. Other threads read the requests, remove them from the array and perform the I/O. The counts indicate the number of requests in the following states:

  • issued: The request is in the array and the I/O operation is in progress.

  • done: The I/O operation has completed, but the thread that requested the operation has not been notified.

  • claimed: The I/O operation has yet to be started.

New Status Variables

The Innodb_have_sync_atomic and Innodb_heap_enabled status variables provide information about availability of MySQL 5.4 SMP features; see Section 3, “Scalability Improvements”.

Innodb_wake_ups indicates the number of wakeups that should not have occurred. These are wakeups in a condition on a mutex when the condition is not yet met so the thread must go back to sleep.