<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="innodb-enabling-monitors">
      </a>
      17.17.2 Enabling InnoDB Monitors
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045149420336">
  </a>
  <a class="indexterm" name="idm46045149418848">
  </a>
  <p>
   When
   <code class="literal">
    InnoDB
   </code>
   monitors are enabled for periodic
      output,
   <code class="literal">
    InnoDB
   </code>
   writes the output to
   <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
    <span class="command">
     <strong>
      mysqld
     </strong>
    </span>
   </a>
   server standard error output
      (
   <code class="literal">
    stderr
   </code>
   ) every 15 seconds, approximately.
  </p>
  <p>
   <code class="literal">
    InnoDB
   </code>
   sends the monitor output to
   <code class="literal">
    stderr
   </code>
   rather than to
   <code class="literal">
    stdout
   </code>
   or fixed-size memory buffers to avoid potential buffer overflows.
  </p>
  <p>
   On Windows,
   <code class="literal">
    stderr
   </code>
   is directed to the default
      log file unless configured otherwise. If you want to direct the
      output to the console window rather than to the error log, start
      the server from a command prompt in a console window with the
   <a class="link" href="server-options.html#option_mysqld_console">
    <code class="option">
     --console
    </code>
   </a>
   option. For more
      information, see
   <a class="xref" href="error-log-destination-configuration.html#error-log-destination-configuration-windows" title="Default Error Log Destination on Windows">
    Default Error Log Destination on Windows
   </a>
   .
  </p>
  <p>
   On Unix and Unix-like systems,
   <code class="literal">
    stderr
   </code>
   is
      typically directed to the terminal unless configured otherwise.
      For more information, see
   <a class="xref" href="error-log-destination-configuration.html#error-log-destination-configuration-unix" title="Default Error Log Destination on Unix and Unix-Like Systems">
    Default Error Log Destination on Unix and Unix-Like Systems
   </a>
   .
  </p>
  <p>
   <code class="literal">
    InnoDB
   </code>
   monitors should only be enabled when you
      actually want to see monitor information because output generation
      causes some performance decrement. Also, if monitor output is
      directed to the error log, the log may become quite large if you
      forget to disable the monitor later.
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    To assist with troubleshooting,
    <code class="literal">
     InnoDB
    </code>
    temporarily enables standard
    <code class="literal">
     InnoDB
    </code>
    Monitor
        output under certain conditions. For more information, see
    <a class="xref" href="innodb-troubleshooting.html" title="17.20 InnoDB Troubleshooting">
     Section 17.20, “InnoDB Troubleshooting”
    </a>
    .
   </p>
  </div>
  <p>
   <code class="literal">
    InnoDB
   </code>
   monitor output begins with a header
      containing a timestamp and the monitor name. For example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa86452364"><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 operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span>
<span class="token number">2014</span><span class="token operator">-</span><span class="token number">10</span><span class="token operator">-</span><span class="token number">16</span> <span class="token number">18</span>:<span class="token number">37</span>:<span class="token number">29</span> <span class="token number">0x7fc2a95c1700</span> INNODB MONITOR OUTPUT
<span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span><span class="token operator">=</span></code></pre>
  </div>
  <p>
   The header for the standard
   <code class="literal">
    InnoDB
   </code>
   Monitor
      (
   <code class="literal">
    INNODB MONITOR OUTPUT
   </code>
   ) is also used for the
      Lock Monitor because the latter produces the same output with the
      addition of extra lock information.
  </p>
  <p>
   The
   <a class="link" href="innodb-parameters.html#sysvar_innodb_status_output">
    <code class="literal">
     innodb_status_output
    </code>
   </a>
   and
   <a class="link" href="innodb-parameters.html#sysvar_innodb_status_output_locks">
    <code class="literal">
     innodb_status_output_locks
    </code>
   </a>
   system
      variables are used to enable the standard
   <code class="literal">
    InnoDB
   </code>
   Monitor and
   <code class="literal">
    InnoDB
   </code>
   Lock Monitor.
  </p>
  <p>
   The
   <a class="link" href="privileges-provided.html#priv_process">
    <code class="literal">
     PROCESS
    </code>
   </a>
   privilege is required
      to enable or disable
   <code class="literal">
    InnoDB
   </code>
   Monitors.
  </p>
  <h4>
   <a name="idm46045149390080">
   </a>
   Enabling the Standard InnoDB Monitor
  </h4>
  <p>
   Enable the standard
   <code class="literal">
    InnoDB
   </code>
   Monitor by setting
      the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_status_output">
    <code class="literal">
     innodb_status_output
    </code>
   </a>
   system
      variable to
   <code class="literal">
    ON
   </code>
   .
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa49827581"><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 keyword">SET</span> <span class="token keyword">GLOBAL</span> innodb_status_output<span class="token operator">=</span><span class="token keyword">ON</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   To disable the standard
   <code class="literal">
    InnoDB
   </code>
   Monitor, set
   <a class="link" href="innodb-parameters.html#sysvar_innodb_status_output">
    <code class="literal">
     innodb_status_output
    </code>
   </a>
   to
   <code class="literal">
    OFF
   </code>
   .
  </p>
  <p>
   When you shut down the server, the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_status_output">
    <code class="literal">
     innodb_status_output
    </code>
   </a>
   variable is
      set to the default
   <code class="literal">
    OFF
   </code>
   value.
  </p>
  <h4>
   <a name="idm46045149379760">
   </a>
   Enabling the InnoDB Lock Monitor
  </h4>
  <p>
   <code class="literal">
    InnoDB
   </code>
   Lock Monitor data is printed with the
   <code class="literal">
    InnoDB
   </code>
   Standard Monitor output. Both the
   <code class="literal">
    InnoDB
   </code>
   Standard Monitor and
   <code class="literal">
    InnoDB
   </code>
   Lock Monitor must be enabled to have
   <code class="literal">
    InnoDB
   </code>
   Lock Monitor data printed periodically.
  </p>
  <p>
   To enable the
   <code class="literal">
    InnoDB
   </code>
   Lock Monitor, set the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_status_output_locks">
    <code class="literal">
     innodb_status_output_locks
    </code>
   </a>
   system
      variable to
   <code class="literal">
    ON
   </code>
   . Both the
   <code class="literal">
    InnoDB
   </code>
   standard Monitor and
   <code class="literal">
    InnoDB
   </code>
   Lock Monitor must be enabled to have
   <code class="literal">
    InnoDB
   </code>
   Lock Monitor data printed periodically:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa4432794"><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 keyword">SET</span> <span class="token keyword">GLOBAL</span> innodb_status_output<span class="token operator">=</span><span class="token keyword">ON</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token keyword">GLOBAL</span> innodb_status_output_locks<span class="token operator">=</span><span class="token keyword">ON</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   To disable the
   <code class="literal">
    InnoDB
   </code>
   Lock Monitor, set
   <a class="link" href="innodb-parameters.html#sysvar_innodb_status_output_locks">
    <code class="literal">
     innodb_status_output_locks
    </code>
   </a>
   to
   <code class="literal">
    OFF
   </code>
   . Set
   <a class="link" href="innodb-parameters.html#sysvar_innodb_status_output">
    <code class="literal">
     innodb_status_output
    </code>
   </a>
   to
   <code class="literal">
    OFF
   </code>
   to also disable the
   <code class="literal">
    InnoDB
   </code>
   Standard Monitor.
  </p>
  <p>
   When you shut down the server, the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_status_output">
    <code class="literal">
     innodb_status_output
    </code>
   </a>
   and
   <a class="link" href="innodb-parameters.html#sysvar_innodb_status_output_locks">
    <code class="literal">
     innodb_status_output_locks
    </code>
   </a>
   variables are set to the default
   <code class="literal">
    OFF
   </code>
   value.
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    To enable the
    <code class="literal">
     InnoDB
    </code>
    Lock Monitor for
    <a class="link" href="show-engine.html" title="15.7.7.16 SHOW ENGINE Statement">
     <code class="literal">
      SHOW ENGINE INNODB
        STATUS
     </code>
    </a>
    output, you are only required to enable
    <a class="link" href="innodb-parameters.html#sysvar_innodb_status_output_locks">
     <code class="literal">
      innodb_status_output_locks
     </code>
    </a>
    .
   </p>
  </div>
  <h4>
   <a name="idm46045149354928">
   </a>
   Obtaining Standard InnoDB Monitor Output On Demand
  </h4>
  <p>
   As an alternative to enabling the standard
   <code class="literal">
    InnoDB
   </code>
   Monitor for periodic output, you can
      obtain standard
   <code class="literal">
    InnoDB
   </code>
   Monitor output on demand
      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>
   SQL statement, which fetches the output to
      your client program. If you are using the
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   interactive client, the output is more readable if you replace the
      usual semicolon statement terminator with
   <code class="literal">
    \G
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa11188596"><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</code></pre>
  </div>
  <p>
   <a class="link" href="show-engine.html" title="15.7.7.16 SHOW ENGINE Statement">
    <code class="literal">
     SHOW ENGINE INNODB
      STATUS
    </code>
   </a>
   output also includes
   <code class="literal">
    InnoDB
   </code>
   Lock Monitor data if the
   <code class="literal">
    InnoDB
   </code>
   Lock Monitor is
      enabled.
  </p>
  <h4>
   <a name="idm46045149344096">
   </a>
   Directing Standard InnoDB Monitor Output to a Status File
  </h4>
  <p>
   Standard
   <code class="literal">
    InnoDB
   </code>
   Monitor output can be enabled
      and directed to a status file by specifying the
   <code class="option">
    --innodb-status-file
   </code>
   option at startup. When this
      option is used,
   <code class="literal">
    InnoDB
   </code>
   creates a file named
   <code class="filename">
    innodb_status.
    <em class="replaceable">
     <code>
      pid
     </code>
    </em>
   </code>
   in the data directory and writes output to it every 15 seconds,
      approximately.
  </p>
  <p>
   <code class="literal">
    InnoDB
   </code>
   removes the status file when the server
      is shut down normally. If an abnormal shutdown occurs, the status
      file may have to be removed manually.
  </p>
  <p>
   The
   <code class="option">
    --innodb-status-file
   </code>
   option is intended for
      temporary use, as output generation can affect performance, and
      the
   <code class="filename">
    innodb_status.
    <em class="replaceable">
     <code>
      pid
     </code>
    </em>
   </code>
   file can become quite large over time.
  </p>
 </div>
 <br/>
</div>
