<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="innodb-standard-monitor">
      </a>
      17.17.3 InnoDB Standard Monitor and Lock Monitor Output
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045149335376">
  </a>
  <a class="indexterm" name="idm46045149333920">
  </a>
  <a class="indexterm" name="idm46045149332432">
  </a>
  <a class="indexterm" name="idm46045149331360">
  </a>
  <p>
   The Lock Monitor is the same as the Standard Monitor except that
      it includes additional lock information. Enabling either monitor
      for periodic output turns on the same output stream, but the
      stream includes extra information if the Lock Monitor is enabled.
      For example, if you enable the Standard Monitor and Lock Monitor,
      that turns on a single output stream. The stream includes extra
      lock information until you disable the Lock Monitor.
  </p>
  <p>
   Standard Monitor output is limited to 1MB when produced using the
   <a class="link" href="show-engine.html" title="15.7.7.16 SHOW ENGINE Statement">
    <code class="literal">
     SHOW ENGINE INNODB
      STATUS
    </code>
   </a>
   statement. This limit does not apply to output
      written to server standard error output
      (
   <code class="literal">
    stderr
   </code>
   ).
  </p>
  <p>
   Example Standard Monitor output:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa13547549"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SHOW</span> <span class="token keyword">ENGINE</span> INNODB <span class="token keyword">STATUS</span>\G
<span class="token output"><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
  Type<span class="token punctuation">:</span> InnoDB
  Name<span class="token punctuation">:</span>
Status<span class="token punctuation">:</span>
=====================================
2018-04-12 15<span class="token punctuation">:</span>14<span class="token punctuation">:</span>08 0x7f971c063700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 4 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops<span class="token punctuation">:</span> 15 srv_active, 0 srv_shutdown, 1122 srv_idle
srv_master_thread log flush and writes<span class="token punctuation">:</span> 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO<span class="token punctuation">:</span> reservation count 24
OS WAIT ARRAY INFO<span class="token punctuation">:</span> signal count 24
RW-shared spins 4, rounds 8, OS waits 4
RW-excl spins 2, rounds 60, OS waits 2
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait<span class="token punctuation">:</span> 2.00 RW-shared, 30.00 RW-excl, 0.00 RW-sx
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2018-04-12 14<span class="token punctuation">:</span>57<span class="token punctuation">:</span>24 0x7f97a9c91700 Transaction<span class="token punctuation">:</span>
TRANSACTION 7717, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 3
MySQL thread id 8, OS thread handle 140289365317376, query id 14 localhost root update
INSERT INTO child VALUES (NULL, 1), (NULL, 2), (NULL, 3), (NULL, 4), (NULL, 5), (NULL, 6)
Foreign key constraint fails for table `test`.`child`<span class="token punctuation">:</span>
,
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE
  CASCADE ON UPDATE CASCADE
Trying to add in child table, in index par_ind tuple<span class="token punctuation">:</span>
DATA TUPLE<span class="token punctuation">:</span> 2 fields;
 0<span class="token punctuation">:</span> len 4; hex 80000003; asc     ;;
 1<span class="token punctuation">:</span> len 4; hex 80000003; asc     ;;

But in parent table `test`.`parent`, in index PRIMARY,
the closest match we can find is record<span class="token punctuation">:</span>
PHYSICAL RECORD<span class="token punctuation">:</span> n_fields 3; compact format; info bits 0
 0<span class="token punctuation">:</span> len 4; hex 80000004; asc     ;;
 1<span class="token punctuation">:</span> len 6; hex 000000001e19; asc       ;;
 2<span class="token punctuation">:</span> len 7; hex 81000001110137; asc       7;;

------------
TRANSACTIONS
------------
Trx id counter 7748
Purge done for trx's n<span class="token punctuation">:</span>o &lt; 7747 undo n<span class="token punctuation">:</span>o &lt; 0 state<span class="token punctuation">:</span> running but idle
History list length 19
LIST OF TRANSACTIONS FOR EACH SESSION<span class="token punctuation">:</span>
---TRANSACTION 421764459790000, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 7747, ACTIVE 23 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 9, OS thread handle 140286987249408, query id 51 localhost root updating
DELETE FROM t WHERE i = 1
------- TRX HAS BEEN WAITING 23 SEC FOR THIS LOCK TO BE GRANTED<span class="token punctuation">:</span>
RECORD LOCKS space id 4 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`t`
trx id 7747 lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD<span class="token punctuation">:</span> n_fields 4; compact format; info bits 0
 0<span class="token punctuation">:</span> len 6; hex 000000000202; asc       ;;
 1<span class="token punctuation">:</span> len 6; hex 000000001e41; asc      A;;
 2<span class="token punctuation">:</span> len 7; hex 820000008b0110; asc        ;;
 3<span class="token punctuation">:</span> len 4; hex 80000001; asc     ;;

------------------
TABLE LOCK table `test`.`t` trx id 7747 lock mode IX
RECORD LOCKS space id 4 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`t`
trx id 7747 lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD<span class="token punctuation">:</span> n_fields 4; compact format; info bits 0
 0<span class="token punctuation">:</span> len 6; hex 000000000202; asc       ;;
 1<span class="token punctuation">:</span> len 6; hex 000000001e41; asc      A;;
 2<span class="token punctuation">:</span> len 7; hex 820000008b0110; asc        ;;
 3<span class="token punctuation">:</span> len 4; hex 80000001; asc     ;;

--------
FILE I/O
--------
I/O thread 0 state<span class="token punctuation">:</span> waiting for i/o request (insert buffer thread)
I/O thread 1 state<span class="token punctuation">:</span> waiting for i/o request (log thread)
I/O thread 2 state<span class="token punctuation">:</span> waiting for i/o request (read thread)
I/O thread 3 state<span class="token punctuation">:</span> waiting for i/o request (read thread)
I/O thread 4 state<span class="token punctuation">:</span> waiting for i/o request (read thread)
I/O thread 5 state<span class="token punctuation">:</span> waiting for i/o request (read thread)
I/O thread 6 state<span class="token punctuation">:</span> waiting for i/o request (write thread)
I/O thread 7 state<span class="token punctuation">:</span> waiting for i/o request (write thread)
I/O thread 8 state<span class="token punctuation">:</span> waiting for i/o request (write thread)
I/O thread 9 state<span class="token punctuation">:</span> waiting for i/o request (write thread)
Pending normal aio reads<span class="token punctuation">:</span> [0, 0, 0, 0] , aio writes<span class="token punctuation">:</span> [0, 0, 0, 0] ,
 ibuf aio reads<span class="token punctuation">:</span>, log i/o's<span class="token punctuation">:</span>, sync i/o's<span class="token punctuation">:</span>
Pending flushes (fsync) log<span class="token punctuation">:</span> 0; buffer pool<span class="token punctuation">:</span> 0
833 OS file reads, 605 OS file writes, 208 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf<span class="token punctuation">:</span> size 1, free list len 0, seg size 2, 0 merges
merged operations<span class="token punctuation">:</span>
 insert 0, delete mark 0, delete 0
discarded operations<span class="token punctuation">:</span>
 insert 0, delete mark 0, delete 0
Hash table size 553253, node heap has 0 buffer(s)
Hash table size 553253, node heap has 1 buffer(s)
Hash table size 553253, node heap has 3 buffer(s)
Hash table size 553253, node heap has 0 buffer(s)
Hash table size 553253, node heap has 0 buffer(s)
Hash table size 553253, node heap has 0 buffer(s)
Hash table size 553253, node heap has 0 buffer(s)
Hash table size 553253, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number          19643450
Log buffer assigned up to    19643450
Log buffer completed up to   19643450
Log written up to            19643450
Log flushed up to            19643450
Added dirty pages up to      19643450
Pages flushed up to          19643450
Last checkpoint at           19643450
129 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 2198863872
Dictionary memory allocated 409606
Buffer pool size   131072
Free buffers       130095
Database pages     973
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes<span class="token punctuation">:</span> LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 810, created 163, written 404
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len<span class="token punctuation">:</span> 973, unzip_LRU len<span class="token punctuation">:</span> 0
I/O sum[0]<span class="token punctuation">:</span>cur[0], unzip sum[0]<span class="token punctuation">:</span>cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   65536
Free buffers       65043
Database pages     491
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes<span class="token punctuation">:</span> LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 411, created 80, written 210
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len<span class="token punctuation">:</span> 491, unzip_LRU len<span class="token punctuation">:</span> 0
I/O sum[0]<span class="token punctuation">:</span>cur[0], unzip sum[0]<span class="token punctuation">:</span>cur[0]
---BUFFER POOL 1
Buffer pool size   65536
Free buffers       65052
Database pages     482
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes<span class="token punctuation">:</span> LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 399, created 83, written 194
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len<span class="token punctuation">:</span> 482, unzip_LRU len<span class="token punctuation">:</span> 0
I/O sum[0]<span class="token punctuation">:</span>cur[0], unzip sum[0]<span class="token punctuation">:</span>cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=5772, Main thread ID=140286437054208 , state=sleeping
Number of rows inserted 57, updated 354, deleted 4, read 4421
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================</span></code></pre>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="innodb-standard-monitor-output-sections">
       </a>
       Standard Monitor Output Sections
      </h4>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045149310256">
   </a>
   <p>
    For a description of each metric reported by the Standard
        Monitor, refer to the
    <a class="ulink" href="/doc/mysql-em-plugin/en/myoem-metrics.html" target="_top">
     Metrics
    </a>
    chapter in the
    <a class="ulink" href="/doc/mysql-em-plugin/en/" target="_top">
     Oracle
        Enterprise Manager for MySQL Database User's Guide
    </a>
    .
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <code class="literal">
        Status
       </code>
      </p>
      <p>
       This section shows the timestamp, the monitor name, and the
            number of seconds that per-second averages are based on. The
            number of seconds is the elapsed time between the current
            time and the last time
       <code class="literal">
        InnoDB
       </code>
       Monitor
            output was printed.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        BACKGROUND THREAD
       </code>
      </p>
      <p>
       The
       <code class="literal">
        srv_master_thread
       </code>
       lines shows work
            done by the main background thread.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        SEMAPHORES
       </code>
      </p>
      <p>
       This section reports threads waiting for a semaphore and
            statistics on how many times threads have needed a spin or a
            wait on a mutex or a rw-lock semaphore. A large number of
            threads waiting for semaphores may be a result of disk I/O,
            or contention problems inside
       <code class="literal">
        InnoDB
       </code>
       .
            Contention can be due to heavy parallelism of queries or
            problems in operating system thread scheduling. Setting the
       <a class="link" href="innodb-parameters.html#sysvar_innodb_thread_concurrency">
        <code class="literal">
         innodb_thread_concurrency
        </code>
       </a>
       system variable smaller than the default value might help in
            such situations. The
       <code class="literal">
        Spin rounds per wait
       </code>
       line shows the number of spinlock rounds per OS wait for a
            mutex.
      </p>
      <p>
       Mutex metrics are reported by
       <a class="link" href="show-engine.html" title="15.7.7.16 SHOW ENGINE Statement">
        <code class="literal">
         SHOW ENGINE
            INNODB MUTEX
        </code>
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        LATEST FOREIGN KEY ERROR
       </code>
      </p>
      <p>
       This section provides information about the most recent
            foreign key constraint error. It is not present if no such
            error has occurred. The contents include the statement that
            failed as well as information about the constraint that
            failed and the referenced and referencing tables.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        LATEST DETECTED DEADLOCK
       </code>
      </p>
      <p>
       This section provides information about the most recent
            deadlock. It is not present if no deadlock has occurred. The
            contents show which transactions are involved, the statement
            each was attempting to execute, the locks they have and
            need, and which transaction
       <code class="literal">
        InnoDB
       </code>
       decided to roll back to break the deadlock. The lock modes
            reported in this section are explained in
       <a class="xref" href="innodb-locking.html" title="17.7.1 InnoDB Locking">
        Section 17.7.1, “InnoDB Locking”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        TRANSACTIONS
       </code>
      </p>
      <p>
       If this section reports lock waits, your applications might
            have lock contention. The output can also help to trace the
            reasons for transaction deadlocks.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        FILE I/O
       </code>
      </p>
      <p>
       This section provides information about threads that
       <code class="literal">
        InnoDB
       </code>
       uses to perform various types of
            I/O. The first few of these are dedicated to general
       <code class="literal">
        InnoDB
       </code>
       processing. The contents also
            display information for pending I/O operations and
            statistics for I/O performance.
      </p>
      <p>
       The number of these threads are controlled by the
       <a class="link" href="innodb-parameters.html#sysvar_innodb_read_io_threads">
        <code class="literal">
         innodb_read_io_threads
        </code>
       </a>
       and
       <a class="link" href="innodb-parameters.html#sysvar_innodb_write_io_threads">
        <code class="literal">
         innodb_write_io_threads
        </code>
       </a>
       parameters. See
       <a class="xref" href="innodb-parameters.html" title="17.14 InnoDB Startup Options and System Variables">
        Section 17.14, “InnoDB Startup Options and System Variables”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        INSERT BUFFER AND ADAPTIVE HASH INDEX
       </code>
      </p>
      <p>
       This section shows the status of the
       <code class="literal">
        InnoDB
       </code>
       insert buffer (also referred to as
            the
       <a class="link" href="glossary.html#glos_change_buffer" title="change buffer">
        change buffer
       </a>
       )
            and the adaptive hash index.
      </p>
      <p>
       For related information, see
       <a class="xref" href="innodb-change-buffer.html" title="17.5.2 Change Buffer">
        Section 17.5.2, “Change Buffer”
       </a>
       , and
       <a class="xref" href="innodb-adaptive-hash.html" title="17.5.3 Adaptive Hash Index">
        Section 17.5.3, “Adaptive Hash Index”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        LOG
       </code>
      </p>
      <p>
       This section displays information about the
       <code class="literal">
        InnoDB
       </code>
       log. The contents include the
            current log sequence number, how far the log has been
            flushed to disk, and the position at which
       <code class="literal">
        InnoDB
       </code>
       last took a checkpoint. (See
       <a class="xref" href="innodb-checkpoints.html" title="17.11.3 InnoDB Checkpoints">
        Section 17.11.3, “InnoDB Checkpoints”
       </a>
       .) The section also
            displays information about pending writes and write
            performance statistics.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        BUFFER POOL AND MEMORY
       </code>
      </p>
      <p>
       This section gives you statistics on pages read and written.
            You can calculate from these numbers how many data file I/O
            operations your queries currently are doing.
      </p>
      <p>
       For buffer pool statistics descriptions, see
       <a class="xref" href="innodb-buffer-pool.html#innodb-buffer-pool-monitoring" title="Monitoring the Buffer Pool Using the InnoDB Standard Monitor">
        Monitoring the Buffer Pool Using the InnoDB Standard Monitor
       </a>
       . For
            additional information about the operation of the buffer
            pool, see
       <a class="xref" href="innodb-buffer-pool.html" title="17.5.1 Buffer Pool">
        Section 17.5.1, “Buffer Pool”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        ROW OPERATIONS
       </code>
      </p>
      <p>
       This section shows what the main thread is doing, including
            the number and performance rate for each type of row
            operation.
      </p>
     </li>
    </ul>
   </div>
  </div>
 </div>
 <br/>
</div>
