<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="performance-schema-statement-digests">
      </a>
      29.10 Performance Schema Statement Digests and Sampling
     </h2>
    </div>
   </div>
  </div>
  <p>
   The MySQL server is capable of maintaining statement digest
      information. The digesting process converts each SQL statement to
      normalized form (the statement digest) and computes a SHA-256 hash
      value (the digest hash value) from the normalized result.
      Normalization permits statements that are similar to be grouped
      and summarized to expose information about the types of statements
      the server is executing and how often they occur. For each digest,
      a representative statement that produces the digest is stored as a
      sample. This section describes how statement digesting and
      sampling occur and how they can be useful.
  </p>
  <p>
   Digesting occurs in the parser regardless of whether the
      Performance Schema is available, so that other features such as
      MySQL Enterprise Firewall and query rewrite plugins have access to statement digests.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="performance-schema-statement-digests.html#statement-digests-general" title="Statement Digest General Concepts">
       Statement Digest General Concepts
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="performance-schema-statement-digests.html#statement-digests-performance-schema" title="Statement Digests in the Performance Schema">
       Statement Digests in the Performance Schema
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="performance-schema-statement-digests.html#statement-digests-memory-use" title="Statement Digest Memory Use">
       Statement Digest Memory Use
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="performance-schema-statement-digests.html#statement-digests-sampling" title="Statement Sampling">
       Statement Sampling
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="statement-digests-general">
       </a>
       Statement Digest General Concepts
      </h3>
     </div>
    </div>
   </div>
   <p>
    When the parser receives an SQL statement, it computes a
        statement digest if that digest is needed, which is true if any
        of the following conditions are true:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Performance Schema digest instrumentation is enabled
      </p>
     </li>
     <li class="listitem">
      <p>
       MySQL Enterprise Firewall is enabled
      </p>
     </li>
     <li class="listitem">
      <p>
       A query rewrite plugin is enabled
      </p>
     </li>
    </ul>
   </div>
   <p>
    The parser is also used by the
    <a class="link" href="encryption-functions.html#function_statement-digest-text">
     <code class="literal">
      STATEMENT_DIGEST_TEXT()
     </code>
    </a>
    and
    <a class="link" href="encryption-functions.html#function_statement-digest">
     <code class="literal">
      STATEMENT_DIGEST()
     </code>
    </a>
    functions,
        which applications can call to compute a normalized statement
        digest and a digest hash value, respectively, from an SQL
        statement.
   </p>
   <p>
    The
    <a class="link" href="server-system-variables.html#sysvar_max_digest_length">
     <code class="literal">
      max_digest_length
     </code>
    </a>
    system
        variable value determines the maximum number of bytes available
        per session for computation of normalized statement digests.
        Once that amount of space is used during digest computation,
        truncation occurs: no further tokens from a parsed statement are
        collected or figure into its digest value. Statements that
        differ only after that many bytes of parsed tokens produce the
        same normalized statement digest and are considered identical if
        compared or if aggregated for digest statistics.
   </p>
   <div class="warning" style="margin-left: 0.5in; margin-right: 0.5in;">
    <div class="admon-title">
     Warning
    </div>
    <p>
     Setting the
     <a class="link" href="server-system-variables.html#sysvar_max_digest_length">
      <code class="literal">
       max_digest_length
      </code>
     </a>
     system variable to zero disables digest production, which also
          disables server functionality that requires digests.
    </p>
   </div>
   <p>
    After the normalized statement has been computed, a SHA-256 hash
        value is computed from it. In addition:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       If MySQL Enterprise Firewall is enabled, it is called and the digest as
            computed is available to it.
      </p>
     </li>
     <li class="listitem">
      <p>
       If any query rewrite plugin is enabled, it is called and the
            statement digest and digest value are available to it.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the Performance Schema has digest instrumentation
            enabled, it makes a copy of the normalized statement digest,
            allocating a maximum of
       <a class="link" href="performance-schema-system-variables.html#sysvar_performance_schema_max_digest_length">
        <code class="literal">
         performance_schema_max_digest_length
        </code>
       </a>
       bytes for it. Consequently, if
       <a class="link" href="performance-schema-system-variables.html#sysvar_performance_schema_max_digest_length">
        <code class="literal">
         performance_schema_max_digest_length
        </code>
       </a>
       is less than
       <a class="link" href="server-system-variables.html#sysvar_max_digest_length">
        <code class="literal">
         max_digest_length
        </code>
       </a>
       , the copy
            is truncated relative to the original. The copy of the
            normalized statement digest is stored in the appropriate
            Performance Schema tables, along with the SHA-256 hash value
            computed from the original normalized statement. (If the
            Performance Schema truncates its copy of the normalized
            statement digest relative to the original, it does not
            recompute the SHA-256 hash value.)
      </p>
     </li>
    </ul>
   </div>
   <p>
    Statement normalization transforms the statement text to a more
        standardized digest string representation that preserves the
        general statement structure while removing information not
        essential to the structure:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Object identifiers such as database and table names are
            preserved.
      </p>
     </li>
     <li class="listitem">
      <p>
       Literal values are converted to parameter markers. A
            normalized statement does not retain information such as
            names, passwords, dates, and so forth.
      </p>
     </li>
     <li class="listitem">
      <p>
       Comments are removed and whitespace is adjusted.
      </p>
     </li>
    </ul>
   </div>
   <p>
    Consider these statements:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa34891305"><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">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> orders <span class="token keyword">WHERE</span> customer_id<span class="token operator">=</span><span class="token number">10</span> <span class="token operator">AND</span> quantity<span class="token operator">&gt;</span><span class="token number">20</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> orders <span class="token keyword">WHERE</span> customer_id <span class="token operator">=</span> <span class="token number">20</span> <span class="token operator">AND</span> quantity <span class="token operator">&gt;</span> <span class="token number">100</span></code></pre>
   </div>
   <p>
    To normalize these statements, the parser replaces data values
        by
    <code class="literal">
     ?
    </code>
    and adjusts whitespace. Both statements
        yield the same normalized form and thus are considered
    <span class="quote">
     “
     <span class="quote">
      the same
     </span>
     ”
    </span>
    :
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa79125345"><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">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> orders <span class="token keyword">WHERE</span> customer_id <span class="token operator">=</span> ? <span class="token operator">AND</span> quantity <span class="token operator">&gt;</span> ?</code></pre>
   </div>
   <p>
    The normalized statement contains less information but is still
        representative of the original statement. Other similar
        statements that have different data values have the same
        normalized form.
   </p>
   <p>
    Now consider these statements:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa79193203"><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">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> customers <span class="token keyword">WHERE</span> customer_id <span class="token operator">=</span> <span class="token number">1000</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> orders <span class="token keyword">WHERE</span> customer_id <span class="token operator">=</span> <span class="token number">1000</span></code></pre>
   </div>
   <p>
    In this case, the normalized statements differ because the
        object identifiers differ:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa43694561"><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">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> customers <span class="token keyword">WHERE</span> customer_id <span class="token operator">=</span> ?
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> orders <span class="token keyword">WHERE</span> customer_id <span class="token operator">=</span> ?</code></pre>
   </div>
   <p>
    If normalization produces a statement that exceeds the space
        available in the digest buffer (as determined by
    <a class="link" href="server-system-variables.html#sysvar_max_digest_length">
     <code class="literal">
      max_digest_length
     </code>
    </a>
    ), truncation
        occurs and the text ends with
    <span class="quote">
     “
     <span class="quote">
      ...
     </span>
     ”
    </span>
    . Long
        normalized statements that differ only in the part that occurs
        following the
    <span class="quote">
     “
     <span class="quote">
      ...
     </span>
     ”
    </span>
    are considered the same.
        Consider these statements:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa88018111"><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">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> mytable <span class="token keyword">WHERE</span> cola <span class="token operator">=</span> <span class="token number">10</span> <span class="token operator">AND</span> colb <span class="token operator">=</span> <span class="token number">20</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> mytable <span class="token keyword">WHERE</span> cola <span class="token operator">=</span> <span class="token number">10</span> <span class="token operator">AND</span> colc <span class="token operator">=</span> <span class="token number">20</span></code></pre>
   </div>
   <p>
    If the cutoff happens to be right after the
    <code class="literal">
     AND
    </code>
    , both statements have this normalized
        form:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa36378966"><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">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> mytable <span class="token keyword">WHERE</span> cola <span class="token operator">=</span> ? <span class="token operator">AND</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
   </div>
   <p>
    In this case, the difference in the second column name is lost
        and both statements are considered the same.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="statement-digests-performance-schema">
       </a>
       Statement Digests in the Performance Schema
      </h3>
     </div>
    </div>
   </div>
   <p>
    In the Performance Schema, statement digesting involves these
        elements:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       A
       <code class="literal">
        statements_digest
       </code>
       consumer in the
       <a class="link" href="performance-schema-setup-consumers-table.html" title="29.12.2.2 The setup_consumers Table">
        <code class="literal">
         setup_consumers
        </code>
       </a>
       table controls
            whether the Performance Schema maintains digest information.
            See
       <a class="xref" href="performance-schema-consumer-filtering.html#performance-schema-consumer-filtering-statement-digest" title="Statement Digest Consumer">
        Statement Digest Consumer
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       The statement event tables
            (
       <a class="link" href="performance-schema-events-statements-current-table.html" title="29.12.6.1 The events_statements_current Table">
        <code class="literal">
         events_statements_current
        </code>
       </a>
       ,
       <a class="link" href="performance-schema-events-statements-history-table.html" title="29.12.6.2 The events_statements_history Table">
        <code class="literal">
         events_statements_history
        </code>
       </a>
       , and
       <a class="link" href="performance-schema-events-statements-history-long-table.html" title="29.12.6.3 The events_statements_history_long Table">
        <code class="literal">
         events_statements_history_long
        </code>
       </a>
       )
            have columns for storing normalized statement digests and
            the corresponding digest SHA-256 hash values:
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          <code class="literal">
           DIGEST_TEXT
          </code>
          is the text of the
                normalized statement digest. This is a copy of the
                original normalized statement that was computed to a
                maximum of
          <a class="link" href="server-system-variables.html#sysvar_max_digest_length">
           <code class="literal">
            max_digest_length
           </code>
          </a>
          bytes, further truncated as necessary to
          <a class="link" href="performance-schema-system-variables.html#sysvar_performance_schema_max_digest_length">
           <code class="literal">
            performance_schema_max_digest_length
           </code>
          </a>
          bytes.
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           DIGEST
          </code>
          is the digest SHA-256 hash
                value computed from the original normalized statement.
         </p>
        </li>
       </ul>
      </div>
      <p>
       See
       <a class="xref" href="performance-schema-statement-tables.html" title="29.12.6 Performance Schema Statement Event Tables">
        Section 29.12.6, “Performance Schema Statement Event Tables”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       The
       <a class="link" href="performance-schema-statement-summary-tables.html" title="29.12.20.3 Statement Summary Tables">
        <code class="literal">
         events_statements_summary_by_digest
        </code>
       </a>
       summary table provides aggregated statement digest
            information. This table aggregates information for
            statements per
       <code class="literal">
        SCHEMA_NAME
       </code>
       and
       <code class="literal">
        DIGEST
       </code>
       combination. The Performance
            Schema uses SHA-256 hash values for aggregation because they
            are fast to compute and have a favorable statistical
            distribution that minimizes collisions. See
       <a class="xref" href="performance-schema-statement-summary-tables.html" title="29.12.20.3 Statement Summary Tables">
        Section 29.12.20.3, “Statement Summary Tables”
       </a>
       .
      </p>
     </li>
    </ul>
   </div>
   <p>
    Some Performance Tables have a column that stores original SQL
        statements from which digests are computed:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       The
       <code class="literal">
        SQL_TEXT
       </code>
       column of the
       <a class="link" href="performance-schema-events-statements-current-table.html" title="29.12.6.1 The events_statements_current Table">
        <code class="literal">
         events_statements_current
        </code>
       </a>
       ,
       <a class="link" href="performance-schema-events-statements-history-table.html" title="29.12.6.2 The events_statements_history Table">
        <code class="literal">
         events_statements_history
        </code>
       </a>
       , and
       <a class="link" href="performance-schema-events-statements-history-long-table.html" title="29.12.6.3 The events_statements_history_long Table">
        <code class="literal">
         events_statements_history_long
        </code>
       </a>
       statement event tables.
      </p>
     </li>
     <li class="listitem">
      <p>
       The
       <code class="literal">
        QUERY_SAMPLE_TEXT
       </code>
       column of the
       <a class="link" href="performance-schema-statement-summary-tables.html" title="29.12.20.3 Statement Summary Tables">
        <code class="literal">
         events_statements_summary_by_digest
        </code>
       </a>
       summary table.
      </p>
     </li>
    </ul>
   </div>
   <p>
    The maximum space available for statement display is 1024 bytes
        by default. To change this value, set the
    <a class="link" href="performance-schema-system-variables.html#sysvar_performance_schema_max_sql_text_length">
     <code class="literal">
      performance_schema_max_sql_text_length
     </code>
    </a>
    system variable at server startup. Changes affect the storage
        required for all the columns just named.
   </p>
   <p>
    The
    <a class="link" href="performance-schema-system-variables.html#sysvar_performance_schema_max_digest_length">
     <code class="literal">
      performance_schema_max_digest_length
     </code>
    </a>
    system variable determines the maximum number of bytes available
        per statement for digest value storage in the Performance
        Schema. However, the display length of statement digests may be
        longer than the available buffer size due to internal encoding
        of statement elements such as keywords and literal values.
        Consequently, values selected from the
    <code class="literal">
     DIGEST_TEXT
    </code>
    column of statement event tables
        may appear to exceed the
    <a class="link" href="performance-schema-system-variables.html#sysvar_performance_schema_max_digest_length">
     <code class="literal">
      performance_schema_max_digest_length
     </code>
    </a>
    value.
   </p>
   <p>
    The
    <a class="link" href="performance-schema-statement-summary-tables.html" title="29.12.20.3 Statement Summary Tables">
     <code class="literal">
      events_statements_summary_by_digest
     </code>
    </a>
    summary table provides a profile of the statements executed by
        the server. It shows what kinds of statements an application is
        executing and how often. An application developer can use this
        information together with other information in the table to
        assess the application's performance characteristics. For
        example, table columns that show wait times, lock times, or
        index use may highlight types of queries that are inefficient.
        This gives the developer insight into which parts of the
        application need attention.
   </p>
   <p>
    The
    <a class="link" href="performance-schema-statement-summary-tables.html" title="29.12.20.3 Statement Summary Tables">
     <code class="literal">
      events_statements_summary_by_digest
     </code>
    </a>
    summary table has a fixed size. By default the Performance
        Schema estimates the size to use at startup. To specify the
        table size explicitly, set the
    <a class="link" href="performance-schema-system-variables.html#sysvar_performance_schema_digests_size">
     <code class="literal">
      performance_schema_digests_size
     </code>
    </a>
    system variable at server startup. If the table becomes full,
        the Performance Schema groups statements that have
    <code class="literal">
     SCHEMA_NAME
    </code>
    and
    <code class="literal">
     DIGEST
    </code>
    values not matching existing values in the table in a special
        row with
    <code class="literal">
     SCHEMA_NAME
    </code>
    and
    <code class="literal">
     DIGEST
    </code>
    set to
    <code class="literal">
     NULL
    </code>
    . This
        permits all statements to be counted. However, if the special
        row accounts for a significant percentage of the statements
        executed, it might be desirable to increase the summary table
        size by increasing
    <a class="link" href="performance-schema-system-variables.html#sysvar_performance_schema_digests_size">
     <code class="literal">
      performance_schema_digests_size
     </code>
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="statement-digests-memory-use">
       </a>
       Statement Digest Memory Use
      </h3>
     </div>
    </div>
   </div>
   <p>
    For applications that generate very long statements that differ
        only at the end, increasing
    <a class="link" href="server-system-variables.html#sysvar_max_digest_length">
     <code class="literal">
      max_digest_length
     </code>
    </a>
    enables
        computation of digests that distinguish statements that would
        otherwise aggregate to the same digest. Conversely, decreasing
    <a class="link" href="server-system-variables.html#sysvar_max_digest_length">
     <code class="literal">
      max_digest_length
     </code>
    </a>
    causes the
        server to devote less memory to digest storage but increases the
        likelihood of longer statements aggregating to the same digest.
        Administrators should keep in mind that larger values result in
        correspondingly increased memory requirements, particularly for
        workloads that involve large numbers of simultaneous sessions
        (the server allocates
    <a class="link" href="server-system-variables.html#sysvar_max_digest_length">
     <code class="literal">
      max_digest_length
     </code>
    </a>
    bytes per
        session).
   </p>
   <p>
    As described previously, normalized statement digests as
        computed by the parser are constrained to a maximum of
    <a class="link" href="server-system-variables.html#sysvar_max_digest_length">
     <code class="literal">
      max_digest_length
     </code>
    </a>
    bytes,
        whereas normalized statement digests stored in the Performance
        Schema use
    <a class="link" href="performance-schema-system-variables.html#sysvar_performance_schema_max_digest_length">
     <code class="literal">
      performance_schema_max_digest_length
     </code>
    </a>
    bytes. The following memory-use considerations apply regarding
        the relative values of
    <a class="link" href="server-system-variables.html#sysvar_max_digest_length">
     <code class="literal">
      max_digest_length
     </code>
    </a>
    and
    <a class="link" href="performance-schema-system-variables.html#sysvar_performance_schema_max_digest_length">
     <code class="literal">
      performance_schema_max_digest_length
     </code>
    </a>
    :
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       If
       <a class="link" href="server-system-variables.html#sysvar_max_digest_length">
        <code class="literal">
         max_digest_length
        </code>
       </a>
       is
            less than
       <a class="link" href="performance-schema-system-variables.html#sysvar_performance_schema_max_digest_length">
        <code class="literal">
         performance_schema_max_digest_length
        </code>
       </a>
       :
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          Server features other than the Performance Schema use
                normalized statement digests that take up to
          <a class="link" href="server-system-variables.html#sysvar_max_digest_length">
           <code class="literal">
            max_digest_length
           </code>
          </a>
          bytes.
         </p>
        </li>
        <li class="listitem">
         <p>
          The Performance Schema does not further truncate
                normalized statement digests that it stores, but
                allocates more memory than
          <a class="link" href="server-system-variables.html#sysvar_max_digest_length">
           <code class="literal">
            max_digest_length
           </code>
          </a>
          bytes
                per digest, which is unnecessary.
         </p>
        </li>
       </ul>
      </div>
     </li>
     <li class="listitem">
      <p>
       If
       <a class="link" href="server-system-variables.html#sysvar_max_digest_length">
        <code class="literal">
         max_digest_length
        </code>
       </a>
       equals
       <a class="link" href="performance-schema-system-variables.html#sysvar_performance_schema_max_digest_length">
        <code class="literal">
         performance_schema_max_digest_length
        </code>
       </a>
       :
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          Server features other than the Performance Schema use
                normalized statement digests that take up to
          <a class="link" href="server-system-variables.html#sysvar_max_digest_length">
           <code class="literal">
            max_digest_length
           </code>
          </a>
          bytes.
         </p>
        </li>
        <li class="listitem">
         <p>
          The Performance Schema does not further truncate
                normalized statement digests that it stores, and
                allocates the same amount of memory as
          <a class="link" href="server-system-variables.html#sysvar_max_digest_length">
           <code class="literal">
            max_digest_length
           </code>
          </a>
          bytes
                per digest.
         </p>
        </li>
       </ul>
      </div>
     </li>
     <li class="listitem">
      <p>
       If
       <a class="link" href="server-system-variables.html#sysvar_max_digest_length">
        <code class="literal">
         max_digest_length
        </code>
       </a>
       is
            greater than
       <a class="link" href="performance-schema-system-variables.html#sysvar_performance_schema_max_digest_length">
        <code class="literal">
         performance_schema_max_digest_length
        </code>
       </a>
       :
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          Server features other than the Performance Schema use
                normalized statement digests that take up to
          <a class="link" href="server-system-variables.html#sysvar_max_digest_length">
           <code class="literal">
            max_digest_length
           </code>
          </a>
          bytes.
         </p>
        </li>
        <li class="listitem">
         <p>
          The Performance Schema further truncates normalized
                statement digests that it stores, and allocates less
                memory than
          <a class="link" href="server-system-variables.html#sysvar_max_digest_length">
           <code class="literal">
            max_digest_length
           </code>
          </a>
          bytes
                per digest.
         </p>
        </li>
       </ul>
      </div>
     </li>
    </ul>
   </div>
   <p>
    Because the Performance Schema statement event tables might
        store many digests, setting
    <a class="link" href="performance-schema-system-variables.html#sysvar_performance_schema_max_digest_length">
     <code class="literal">
      performance_schema_max_digest_length
     </code>
    </a>
    smaller than
    <a class="link" href="server-system-variables.html#sysvar_max_digest_length">
     <code class="literal">
      max_digest_length
     </code>
    </a>
    enables administrators to balance these factors:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       The need to have long normalized statement digests available
            to server features outside the Performance Schema
      </p>
     </li>
     <li class="listitem">
      <p>
       Many concurrent sessions, each of which allocates
            digest-computation memory
      </p>
     </li>
     <li class="listitem">
      <p>
       The need to limit memory consumption by the Performance
            Schema statement event tables when storing many statement
            digests
      </p>
     </li>
    </ul>
   </div>
   <p>
    The
    <a class="link" href="performance-schema-system-variables.html#sysvar_performance_schema_max_digest_length">
     <code class="literal">
      performance_schema_max_digest_length
     </code>
    </a>
    setting is not per session, it is per statement, and a session
        can store multiple statements in the
    <a class="link" href="performance-schema-events-statements-history-table.html" title="29.12.6.2 The events_statements_history Table">
     <code class="literal">
      events_statements_history
     </code>
    </a>
    table. A
        typical number of statements in this table is 10 per session, so
        each session consumes 10 times the memory indicated by the
    <a class="link" href="performance-schema-system-variables.html#sysvar_performance_schema_max_digest_length">
     <code class="literal">
      performance_schema_max_digest_length
     </code>
    </a>
    value, for this table alone.
   </p>
   <p>
    Also, there are many statements (and digests) collected
        globally, most notably in the
    <a class="link" href="performance-schema-events-statements-history-long-table.html" title="29.12.6.3 The events_statements_history_long Table">
     <code class="literal">
      events_statements_history_long
     </code>
    </a>
    table. Here, too,
    <em class="replaceable">
     <code>
      N
     </code>
    </em>
    statements stored
        consumes
    <em class="replaceable">
     <code>
      N
     </code>
    </em>
    times the memory indicated
        by the
    <a class="link" href="performance-schema-system-variables.html#sysvar_performance_schema_max_digest_length">
     <code class="literal">
      performance_schema_max_digest_length
     </code>
    </a>
    value.
   </p>
   <p>
    To assess the amount of memory used for SQL statement storage
        and digest computation, use the
    <a class="link" href="show-engine.html" title="15.7.7.16 SHOW ENGINE Statement">
     <code class="literal">
      SHOW ENGINE
        PERFORMANCE_SCHEMA STATUS
     </code>
    </a>
    statement, or monitor these
        instruments:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa37515854"><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">SELECT</span> <span class="token keyword">NAME</span>
       <span class="token keyword">FROM</span> performance_schema<span class="token punctuation">.</span>setup_instruments
       <span class="token keyword">WHERE</span> <span class="token keyword">NAME</span> <span class="token operator">LIKE</span> <span class="token string">'%.sqltext'</span><span class="token punctuation">;</span>
<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><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><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>
<span class="token output"><span class="token punctuation">|</span> NAME                                                             <span class="token punctuation">|</span></span>
<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><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><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>
<span class="token output"><span class="token punctuation">|</span> memory/performance_schema/events_statements_history.sqltext      <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> memory/performance_schema/events_statements_current.sqltext      <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> memory/performance_schema/events_statements_history_long.sqltext <span class="token punctuation">|</span></span>
<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><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><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>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token keyword">NAME</span>
       <span class="token keyword">FROM</span> performance_schema<span class="token punctuation">.</span>setup_instruments
       <span class="token keyword">WHERE</span> <span class="token keyword">NAME</span> <span class="token operator">LIKE</span> <span class="token string">'memory/performance_schema/%.tokens'</span><span class="token punctuation">;</span>
<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><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><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>
<span class="token output"><span class="token punctuation">|</span> NAME                                                                 <span class="token punctuation">|</span></span>
<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><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><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>
<span class="token output"><span class="token punctuation">|</span> memory/performance_schema/events_statements_history.tokens           <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> memory/performance_schema/events_statements_current.tokens           <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> memory/performance_schema/events_statements_summary_by_digest.tokens <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> memory/performance_schema/events_statements_history_long.tokens      <span class="token punctuation">|</span></span>
<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><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><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></code></pre>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="statement-digests-sampling">
       </a>
       Statement Sampling
      </h3>
     </div>
    </div>
   </div>
   <p>
    The Performance Schema uses statement sampling to collect
        representative statements that produce each digest value in the
    <a class="link" href="performance-schema-statement-summary-tables.html" title="29.12.20.3 Statement Summary Tables">
     <code class="literal">
      events_statements_summary_by_digest
     </code>
    </a>
    table. These columns store sample statement information:
    <code class="literal">
     QUERY_SAMPLE_TEXT
    </code>
    (the text of the
        statement),
    <code class="literal">
     QUERY_SAMPLE_SEEN
    </code>
    (when the
        statement was seen), and
    <code class="literal">
     QUERY_SAMPLE_TIMER_WAIT
    </code>
    (the statement wait
        or execution time). The Performance Schema updates all three
        columns each time it chooses a sample statement.
   </p>
   <p>
    When a new table row is inserted, the statement that produced
        the row digest value is stored as the current sample statement
        associated with the digest. Thereafter, when the server sees
        other statements with the same digest value, it determines
        whether to use the new statement to replace the current sample
        statement (that is, whether to resample). Resampling policy is
        based on the comparative wait times of the current sample
        statement and new statement and, optionally, the age of the
        current sample statement:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Resampling based on wait times: If the new statement wait
            time has a wait time greater than that of the current sample
            statement, it becomes the current sample statement.
      </p>
     </li>
     <li class="listitem">
      <p>
       Resampling based on age: If the
       <a class="link" href="performance-schema-system-variables.html#sysvar_performance_schema_max_digest_sample_age">
        <code class="literal">
         performance_schema_max_digest_sample_age
        </code>
       </a>
       system variable has a value greater than zero and the
            current sample statement is more than that many seconds old,
            the current statement is considered
       <span class="quote">
        “
        <span class="quote">
         too old
        </span>
        ”
       </span>
       and the new statement replaces it. This occurs even if the
            new statement wait time is less than that of the current
            sample statement.
      </p>
     </li>
    </ul>
   </div>
   <p>
    By default,
    <a class="link" href="performance-schema-system-variables.html#sysvar_performance_schema_max_digest_sample_age">
     <code class="literal">
      performance_schema_max_digest_sample_age
     </code>
    </a>
    is 60 seconds (1 minute). To change how quickly sample
        statements
    <span class="quote">
     “
     <span class="quote">
      expire
     </span>
     ”
    </span>
    due to age, increase or
        decrease the value. To disable the age-based part of the
        resampling policy, set
    <a class="link" href="performance-schema-system-variables.html#sysvar_performance_schema_max_digest_sample_age">
     <code class="literal">
      performance_schema_max_digest_sample_age
     </code>
    </a>
    to 0.
   </p>
  </div>
 </div>
 <br/>
</div>
