<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="performance-schema-startup-configuration">
      </a>
      29.3 Performance Schema Startup Configuration
     </h2>
    </div>
   </div>
  </div>
  <p>
   To use the MySQL Performance Schema, it must be enabled at server
      startup to enable event collection to occur.
  </p>
  <p>
   The Performance Schema is enabled by default. To enable or disable
      it explicitly, start the server with the
   <a class="link" href="performance-schema-system-variables.html#sysvar_performance_schema">
    <code class="literal">
     performance_schema
    </code>
   </a>
   variable set
      to an appropriate value. For example, use these lines in the
      server
   <code class="filename">
    my.cnf
   </code>
   file:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-ini"><div class="docs-select-all right" id="sa3353001"><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-ini"><span class="token selector">[mysqld]</span>
<span class="token constant">performance_schema</span><span class="token attr-value"><span class="token punctuation">=</span>ON</span></code></pre>
  </div>
  <p>
   If the server is unable to allocate any internal buffer during
      Performance Schema initialization, the Performance Schema disables
      itself and sets
   <a class="link" href="performance-schema-system-variables.html#sysvar_performance_schema">
    <code class="literal">
     performance_schema
    </code>
   </a>
   to
   <code class="literal">
    OFF
   </code>
   , and the server runs without
      instrumentation.
  </p>
  <p>
   The Performance Schema also permits instrument and consumer
      configuration at server startup.
  </p>
  <p>
   To control an instrument at server startup, use an option of this
      form:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa33585571"><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-terminal"><span class="token constant">--performance-schema-instrument</span>=<span class="token atrule">'<em class="replaceable">instrument_name</em>=<em class="replaceable">value</em>'</span></code></pre>
  </div>
  <p>
   Here,
   <em class="replaceable">
    <code>
     instrument_name
    </code>
   </em>
   is an instrument
      name such as
   <code class="literal">
    wait/synch/mutex/sql/LOCK_open
   </code>
   ,
      and
   <em class="replaceable">
    <code>
     value
    </code>
   </em>
   is one of these values:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       OFF
      </code>
      ,
      <code class="literal">
       FALSE
      </code>
      , or
      <code class="literal">
       0
      </code>
      : Disable the instrument
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       ON
      </code>
      ,
      <code class="literal">
       TRUE
      </code>
      , or
      <code class="literal">
       1
      </code>
      : Enable and time the instrument
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       COUNTED
      </code>
      : Enable and count (rather than
          time) the instrument
     </p>
    </li>
   </ul>
  </div>
  <p>
   Each
   <a class="link" href="performance-schema-options.html#option_mysqld_performance-schema-instrument">
    <code class="option">
     --performance-schema-instrument
    </code>
   </a>
   option can specify only one instrument name, but multiple
      instances of the option can be given to configure multiple
      instruments. In addition, patterns are permitted in instrument
      names to configure instruments that match the pattern. To
      configure all condition synchronization instruments as enabled and
      counted, use this option:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa32280601"><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-terminal"><span class="token constant">--performance-schema-instrument</span>=<span class="token atrule">'wait/synch/cond/%=COUNTED'</span></code></pre>
  </div>
  <p>
   To disable all instruments, use this option:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa89629005"><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-terminal"><span class="token constant">--performance-schema-instrument</span>=<span class="token atrule">'%=OFF'</span></code></pre>
  </div>
  <p>
   Exception: The
   <code class="literal">
    memory/performance_schema/%
   </code>
   instruments are built in and cannot be disabled at startup.
  </p>
  <p>
   Longer instrument name strings take precedence over shorter
      pattern names, regardless of order. For information about
      specifying patterns to select instruments, see
   <a class="xref" href="performance-schema-filtering-names.html" title="29.4.9 Naming Instruments or Consumers for Filtering Operations">
    Section 29.4.9, “Naming Instruments or Consumers for Filtering Operations”
   </a>
   .
  </p>
  <p>
   An unrecognized instrument name is ignored. It is possible that a
      plugin installed later may create the instrument, at which time
      the name is recognized and configured.
  </p>
  <p>
   To control a consumer at server startup, use an option of this
      form:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa70276416"><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-terminal"><span class="token constant">--performance-schema-consumer-<em class="replaceable">consumer_name</em></span><span class="token attr-value"><span class="token punctuation">=</span><em class="replaceable">value</em></span></code></pre>
  </div>
  <p>
   Here,
   <em class="replaceable">
    <code>
     consumer_name
    </code>
   </em>
   is a consumer name
      such as
   <code class="literal">
    events_waits_history
   </code>
   , and
   <em class="replaceable">
    <code>
     value
    </code>
   </em>
   is one of these values:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       OFF
      </code>
      ,
      <code class="literal">
       FALSE
      </code>
      , or
      <code class="literal">
       0
      </code>
      : Do not collect events for the consumer
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       ON
      </code>
      ,
      <code class="literal">
       TRUE
      </code>
      , or
      <code class="literal">
       1
      </code>
      : Collect events for the consumer
     </p>
    </li>
   </ul>
  </div>
  <p>
   For example, to enable the
   <code class="literal">
    events_waits_history
   </code>
   consumer, use this option:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa85533066"><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-terminal"><span class="token constant">--performance-schema-consumer-events-waits-history</span><span class="token attr-value"><span class="token punctuation">=</span>ON</span></code></pre>
  </div>
  <p>
   The permitted consumer names can be found by examining 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. Patterns are
      not permitted. Consumer names 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 use
      underscores, but for consumers set at startup, dashes and
      underscores within the name are equivalent.
  </p>
  <p>
   The Performance Schema includes several system variables that
      provide configuration information:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa48648073"><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">VARIABLES</span> <span class="token operator">LIKE</span> <span class="token string">'perf%'</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> Variable_name                                          <span class="token punctuation">|</span> Value   <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> performance_schema                                     <span class="token punctuation">|</span> ON      <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> performance_schema_accounts_size                       <span class="token punctuation">|</span> 100     <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> performance_schema_digests_size                        <span class="token punctuation">|</span> 200     <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> performance_schema_events_stages_history_long_size     <span class="token punctuation">|</span> 10000   <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> performance_schema_events_stages_history_size          <span class="token punctuation">|</span> 10      <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> performance_schema_events_statements_history_long_size <span class="token punctuation">|</span> 10000   <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> performance_schema_events_statements_history_size      <span class="token punctuation">|</span> 10      <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> performance_schema_events_waits_history_long_size      <span class="token punctuation">|</span> 10000   <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> performance_schema_events_waits_history_size           <span class="token punctuation">|</span> 10      <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> performance_schema_hosts_size                          <span class="token punctuation">|</span> 100     <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> performance_schema_max_cond_classes                    <span class="token punctuation">|</span> 80      <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> performance_schema_max_cond_instances                  <span class="token punctuation">|</span> 1000    <span class="token punctuation">|</span></span>
<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
  </div>
  <p>
   The
   <a class="link" href="performance-schema-system-variables.html#sysvar_performance_schema">
    <code class="literal">
     performance_schema
    </code>
   </a>
   variable
      is
   <code class="literal">
    ON
   </code>
   or
   <code class="literal">
    OFF
   </code>
   to indicate
      whether the Performance Schema is enabled or disabled. The other
      variables indicate table sizes (number of rows) or memory
      allocation values.
  </p>
  <a class="indexterm" name="idm46045075647328">
  </a>
  <a class="indexterm" name="idm46045075645840">
  </a>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    With the Performance Schema enabled, the number of Performance
        Schema instances affects the server memory footprint, perhaps to
        a large extent. The Performance Schema autoscales many
        parameters to use memory only as required; see
    <a class="xref" href="performance-schema-memory-model.html" title="29.17 The Performance Schema Memory-Allocation Model">
     Section 29.17, “The Performance Schema Memory-Allocation Model”
    </a>
    .
   </p>
  </div>
  <p>
   To change the value of Performance Schema system variables, set
      them at server startup. For example, put the following lines in a
   <code class="filename">
    my.cnf
   </code>
   file to change the sizes of the
      history tables for wait events:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-ini"><div class="docs-select-all right" id="sa90036348"><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-ini"><span class="token selector">[mysqld]</span>
<span class="token constant">performance_schema</span>
<span class="token constant">performance_schema_events_waits_history_size</span><span class="token attr-value"><span class="token punctuation">=</span>20</span>
<span class="token constant">performance_schema_events_waits_history_long_size</span><span class="token attr-value"><span class="token punctuation">=</span>15000</span></code></pre>
  </div>
  <p>
   The Performance Schema automatically sizes the values of several
      of its parameters at server startup if they are not set
      explicitly. For example, it sizes the parameters that control the
      sizes of the events waits tables this way. The Performance Schema
      allocates memory incrementally, scaling its memory use to actual
      server load, instead of allocating all the memory it needs during
      server startup. Consequently, many sizing parameters need not be
      set at all. To see which parameters are autosized or autoscaled,
      use
   <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
    <span class="command">
     <strong>
      mysqld --verbose --help
     </strong>
    </span>
   </a>
   and examine the
      option descriptions, or see
   <a class="xref" href="performance-schema-system-variables.html" title="29.15 Performance Schema System Variables">
    Section 29.15, “Performance Schema System Variables”
   </a>
   .
  </p>
  <p>
   For each autosized parameter that is not set at server startup,
      the Performance Schema determines how to set its value based on
      the value of the following system values, which are considered as
   <span class="quote">
    “
    <span class="quote">
     hints
    </span>
    ”
   </span>
   about how you have configured your MySQL
      server:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-none"><div class="docs-select-all right" id="sa49958502"><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-none">max_connections
open_files_limit
table_definition_cache
table_open_cache</code></pre>
  </div>
  <p>
   To override autosizing or autoscaling for a given parameter, set
      it to a value other than −1 at startup. In this case, the
      Performance Schema assigns it the specified value.
  </p>
  <p>
   At runtime,
   <a class="link" href="show-variables.html" title="15.7.7.41 SHOW VARIABLES Statement">
    <code class="literal">
     SHOW VARIABLES
    </code>
   </a>
   displays
      the actual values that autosized parameters were set to.
      Autoscaled parameters display with a value of −1.
  </p>
  <p>
   If the Performance Schema is disabled, its autosized and
      autoscaled parameters remain set to −1 and
   <a class="link" href="show-variables.html" title="15.7.7.41 SHOW VARIABLES Statement">
    <code class="literal">
     SHOW VARIABLES
    </code>
   </a>
   displays −1.
  </p>
 </div>
 <br/>
</div>
