<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="sys-diagnostics">
      </a>
      30.4.4.2 The diagnostics() Procedure
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045061655312">
  </a>
  <a class="indexterm" name="idm46045061653856">
  </a>
  <p>
   Creates a report of the current server status for diagnostic
          purposes.
  </p>
  <p>
   This procedure disables binary logging during its execution by
          manipulating the session value of the
   <a class="link" href="replication-options-binary-log.html#sysvar_sql_log_bin">
    <code class="literal">
     sql_log_bin
    </code>
   </a>
   system variable.
          That is a restricted operation, so the procedure requires
          privileges sufficient to set restricted session variables. See
   <a class="xref" href="system-variable-privileges.html" title="7.1.9.1 System Variable Privileges">
    Section 7.1.9.1, “System Variable Privileges”
   </a>
   .
  </p>
  <p>
   Data collected for
   <a class="link" href="sys-diagnostics.html" title="30.4.4.2 The diagnostics() Procedure">
    <code class="literal">
     diagnostics()
    </code>
   </a>
   includes this information:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Information from the
      <a class="link" href="sys-metrics.html" title="30.4.3.21 The metrics View">
       <code class="literal">
        metrics
       </code>
      </a>
      view (see
      <a class="xref" href="sys-metrics.html" title="30.4.3.21 The metrics View">
       Section 30.4.3.21, “The metrics View”
      </a>
      )
     </p>
    </li>
    <li class="listitem">
     <p>
      Information from other relevant
      <code class="literal">
       sys
      </code>
      schema views, such as the one that determines queries in
              the 95th percentile
     </p>
    </li>
    <li class="listitem">
     <p>
      Information from the
      <a class="link" href="mysql-cluster-ndbinfo.html" title="25.6.17 ndbinfo: The NDB Cluster Information Database">
       <code class="literal">
        ndbinfo
       </code>
      </a>
      schema, if the
              MySQL server is part of NDB Cluster
     </p>
    </li>
    <li class="listitem">
     <p>
      Replication status (both source and replica)
     </p>
    </li>
   </ul>
  </div>
  <p>
   Some of the sys schema views are calculated as initial
          (optional), overall, and delta values:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The initial view is the content of the view at the start
              of the
      <a class="link" href="sys-diagnostics.html" title="30.4.4.2 The diagnostics() Procedure">
       <code class="literal">
        diagnostics()
       </code>
      </a>
      procedure. This output is the same as the start values
              used for the delta view. The initial view is included if
              the
      <code class="literal">
       diagnostics.include_raw
      </code>
      configuration option is
      <code class="literal">
       ON
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      The overall view is the content of the view at the end of
              the
      <a class="link" href="sys-diagnostics.html" title="30.4.4.2 The diagnostics() Procedure">
       <code class="literal">
        diagnostics()
       </code>
      </a>
      procedure.
              This output is the same as the end values used for the
              delta view. The overall view is always included.
     </p>
    </li>
    <li class="listitem">
     <p>
      The delta view is the difference from the beginning to the
              end of procedure execution. The minimum and maximum values
              are the minimum and maximum values from the end view,
              respectively. They do not necessarily reflect the minimum
              and maximum values in the monitored period. Except for the
      <a class="link" href="sys-metrics.html" title="30.4.3.21 The metrics View">
       <code class="literal">
        metrics
       </code>
      </a>
      view, the delta is
              calculated only between the first and last outputs.
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="sys-diagnostics-parameters">
       </a>
       Parameters
      </h5>
     </div>
    </div>
   </div>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <code class="literal">
        in_max_runtime INT UNSIGNED
       </code>
       : The
                maximum data collection time in seconds. Use
       <code class="literal">
        NULL
       </code>
       to collect data for the default
                of 60 seconds. Otherwise, use a value greater than 0.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        in_interval INT UNSIGNED
       </code>
       : The sleep
                time between data collections in seconds. Use
       <code class="literal">
        NULL
       </code>
       to sleep for the default of 30
                seconds. Otherwise, use a value greater than 0.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        in_auto_config ENUM('current', 'medium',
                'full')
       </code>
       : The Performance Schema configuration
                to use. Permitted values are:
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          <code class="literal">
           current
          </code>
          : Use the current
                    instrument and consumer settings.
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           medium
          </code>
          : Enable some instruments
                    and consumers.
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           full
          </code>
          : Enable all instruments and
                    consumers.
         </p>
        </li>
       </ul>
      </div>
      <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
       <div class="admon-title">
        Note
       </div>
       <p>
        The more instruments and consumers enabled, the more
                  impact on MySQL server performance. Be careful with
                  the
        <code class="literal">
         medium
        </code>
        setting and especially
                  the
        <code class="literal">
         full
        </code>
        setting, which has a large
                  performance impact.
       </p>
      </div>
      <p>
       Use of the
       <code class="literal">
        medium
       </code>
       or
       <code class="literal">
        full
       </code>
       setting requires the
       <a class="link" href="privileges-provided.html#priv_super">
        <code class="literal">
         SUPER
        </code>
       </a>
       privilege.
      </p>
      <p>
       If a setting other than
       <code class="literal">
        current
       </code>
       is
                chosen, the current settings are restored at the end of
                the procedure.
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="sys-diagnostics-configuration-options">
       </a>
       Configuration Options
      </h5>
     </div>
    </div>
   </div>
   <p>
    <a class="link" href="sys-diagnostics.html" title="30.4.4.2 The diagnostics() Procedure">
     <code class="literal">
      diagnostics()
     </code>
    </a>
    operation can be
            modified using the following configuration options or their
            corresponding user-defined variables (see
    <a class="xref" href="sys-sys-config.html" title="30.4.2.1 The sys_config Table">
     Section 30.4.2.1, “The sys_config Table”
    </a>
    ):
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <code class="literal">
        debug
       </code>
       ,
       <code class="literal">
        @sys.debug
       </code>
      </p>
      <p>
       If this option is
       <code class="literal">
        ON
       </code>
       , produce
                debugging output. The default is
       <code class="literal">
        OFF
       </code>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        diagnostics.allow_i_s_tables
       </code>
       ,
       <code class="literal">
        @sys.diagnostics.allow_i_s_tables
       </code>
      </p>
      <p>
       If this option is
       <code class="literal">
        ON
       </code>
       , the
       <a class="link" href="sys-diagnostics.html" title="30.4.4.2 The diagnostics() Procedure">
        <code class="literal">
         diagnostics()
        </code>
       </a>
       procedure is
                permitted to perform table scans on the Information
                Schema
       <a class="link" href="information-schema-tables-table.html" title="28.3.38 The INFORMATION_SCHEMA TABLES Table">
        <code class="literal">
         TABLES
        </code>
       </a>
       table. This
                can be expensive if there are many tables. The default
                is
       <code class="literal">
        OFF
       </code>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        diagnostics.include_raw
       </code>
       ,
       <code class="literal">
        @sys.diagnostics.include_raw
       </code>
      </p>
      <p>
       If this option is
       <code class="literal">
        ON
       </code>
       , the
       <a class="link" href="sys-diagnostics.html" title="30.4.4.2 The diagnostics() Procedure">
        <code class="literal">
         diagnostics()
        </code>
       </a>
       procedure
                output includes the raw output from querying the
       <a class="link" href="sys-metrics.html" title="30.4.3.21 The metrics View">
        <code class="literal">
         metrics
        </code>
       </a>
       view. The default
                is
       <code class="literal">
        OFF
       </code>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        statement_truncate_len
       </code>
       ,
       <code class="literal">
        @sys.statement_truncate_len
       </code>
      </p>
      <p>
       The maximum length of statements returned by the
       <a class="link" href="sys-format-statement.html" title="30.4.5.5 The format_statement() Function">
        <code class="literal">
         format_statement()
        </code>
       </a>
       function. Longer statements are truncated to this
                length. The default is 64.
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="sys-diagnostics-example">
       </a>
       Example
      </h5>
     </div>
    </div>
   </div>
   <p>
    Create a diagnostics report that starts an iteration every
            30 seconds and runs for at most 120 seconds using the
            current Performance Schema settings:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa41640703"><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">CALL</span> sys<span class="token punctuation">.</span><span class="token keyword">diagnostics</span><span class="token punctuation">(</span><span class="token number">120</span><span class="token punctuation">,</span> <span class="token number">30</span><span class="token punctuation">,</span> <span class="token string">'current'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    To capture the output from the
    <code class="literal">
     diagnostics()
    </code>
    procedure in a file as it
            runs, use 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>
    client
    <code class="literal">
     tee
     <code class="filename">
      filename
     </code>
    </code>
    and
    <code class="literal">
     notee
    </code>
    commands (see
    <a class="xref" href="mysql-commands.html" title="6.5.1.2 mysql Client Commands">
     Section 6.5.1.2, “mysql Client Commands”
    </a>
    ):
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa20667383"><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> tee diag<span class="token punctuation">.</span><span class="token keyword">out</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">CALL</span> sys<span class="token punctuation">.</span><span class="token keyword">diagnostics</span><span class="token punctuation">(</span><span class="token number">120</span><span class="token punctuation">,</span> <span class="token number">30</span><span class="token punctuation">,</span> <span class="token string">'current'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> notee<span class="token punctuation">;</span></code></pre>
   </div>
  </div>
 </div>
 <br/>
</div>
