<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="performance-schema-examples">
      </a>
      29.19 Using the Performance Schema to Diagnose Problems
     </h2>
    </div>
   </div>
  </div>
  <div class="toc">
   <dl class="toc">
    <dt>
     <span class="section">
      <a href="performance-schema-query-profiling.html">
       29.19.1 Query Profiling Using Performance Schema
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="performance-schema-obtaining-parent-events.html">
       29.19.2 Obtaining Parent Event Information
      </a>
     </span>
    </dt>
   </dl>
  </div>
  <a class="indexterm" name="idm46045064282992">
  </a>
  <a class="indexterm" name="idm46045064281536">
  </a>
  <p>
   The Performance Schema is a tool to help a DBA do performance
      tuning by taking real measurements instead of
   <span class="quote">
    “
    <span class="quote">
     wild
      guesses.
    </span>
    ”
   </span>
   This section demonstrates some ways to use the
      Performance Schema for this purpose. The discussion here relies on
      the use of event filtering, which is described in
   <a class="xref" href="performance-schema-filtering.html" title="29.4.2 Performance Schema Event Filtering">
    Section 29.4.2, “Performance Schema Event Filtering”
   </a>
   .
  </p>
  <p>
   The following example provides one methodology that you can use to
      analyze a repeatable problem, such as investigating a performance
      bottleneck. To begin, you should have a repeatable use case where
      performance is deemed
   <span class="quote">
    “
    <span class="quote">
     too slow
    </span>
    ”
   </span>
   and needs
      optimization, and you should enable all instrumentation (no
      pre-filtering at all).
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      Run the use case.
     </p>
    </li>
    <li class="listitem">
     <p>
      Using the Performance Schema tables, analyze the root cause of
          the performance problem. This analysis relies heavily on
          post-filtering.
     </p>
    </li>
    <li class="listitem">
     <p>
      For problem areas that are ruled out, disable the
          corresponding instruments. For example, if analysis shows that
          the issue is not related to file I/O in a particular storage
          engine, disable the file I/O instruments for that engine. Then
          truncate the history and summary tables to remove previously
          collected events.
     </p>
    </li>
    <li class="listitem">
     <p>
      Repeat the process at step 1.
     </p>
     <p>
      With each iteration, the Performance Schema output,
          particularly the
      <a class="link" href="performance-schema-events-waits-history-long-table.html" title="29.12.4.3 The events_waits_history_long Table">
       <code class="literal">
        events_waits_history_long
       </code>
      </a>
      table,
          contains less and less
      <span class="quote">
       “
       <span class="quote">
        noise
       </span>
       ”
      </span>
      caused by
          nonsignificant instruments, and given that this table has a
          fixed size, contains more and more data relevant to the
          analysis of the problem at hand.
     </p>
     <p>
      With each iteration, investigation should lead closer and
          closer to the root cause of the problem, as the
      <span class="quote">
       “
       <span class="quote">
        signal/noise
       </span>
       ”
      </span>
      ratio improves, making analysis
          easier.
     </p>
    </li>
    <li class="listitem">
     <p>
      Once a root cause of performance bottleneck is identified,
          take the appropriate corrective action, such as:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: disc; ">
       <li class="listitem">
        <p>
         Tune the server parameters (cache sizes, memory, and so
              forth).
        </p>
       </li>
       <li class="listitem">
        <p>
         Tune a query by writing it differently,
        </p>
       </li>
       <li class="listitem">
        <p>
         Tune the database schema (tables, indexes, and so forth).
        </p>
       </li>
       <li class="listitem">
        <p>
         Tune the code (this applies to storage engine or server
              developers only).
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      Start again at step 1, to see the effects of the changes on
          performance.
     </p>
    </li>
   </ol>
  </div>
  <p>
   The
   <code class="literal">
    mutex_instances.LOCKED_BY_THREAD_ID
   </code>
   and
   <code class="literal">
    rwlock_instances.WRITE_LOCKED_BY_THREAD_ID
   </code>
   columns are extremely important for investigating performance
      bottlenecks or deadlocks. This is made possible by Performance
      Schema instrumentation as follows:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      Suppose that thread 1 is stuck waiting for a mutex.
     </p>
    </li>
    <li class="listitem">
     <p>
      You can determine what the thread is waiting for:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa32038304"><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> performance_schema<span class="token punctuation">.</span>events_waits_current
<span class="token keyword">WHERE</span> THREAD_ID <span class="token operator">=</span> <em class="replaceable">thread_1</em><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      Say the query result identifies that the thread is waiting for
          mutex A, found in
      <code class="literal">
       events_waits_current.OBJECT_INSTANCE_BEGIN
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      You can determine which thread is holding mutex A:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa92843656"><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> performance_schema<span class="token punctuation">.</span>mutex_instances
<span class="token keyword">WHERE</span> OBJECT_INSTANCE_BEGIN <span class="token operator">=</span> <em class="replaceable">mutex_A</em><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      Say the query result identifies that it is thread 2 holding
          mutex A, as found in
      <code class="literal">
       mutex_instances.LOCKED_BY_THREAD_ID
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      You can see what thread 2 is doing:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa89411573"><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> performance_schema<span class="token punctuation">.</span>events_waits_current
<span class="token keyword">WHERE</span> THREAD_ID <span class="token operator">=</span> <em class="replaceable">thread_2</em><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
   </ol>
  </div>
 </div>
 <br/>
</div>
