<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="performance-schema-timing">
      </a>
      29.4.1 Performance Schema Event Timing
     </h3>
    </div>
   </div>
  </div>
  <p>
   Events are collected by means of instrumentation added to the
        server source code. Instruments time events, which is how the
        Performance Schema provides an idea of how long events take. It
        is also possible to configure instruments not to collect timing
        information. This section discusses the available timers and
        their characteristics, and how timing values are represented in
        events.
  </p>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="performance-schema-timers">
       </a>
       Performance Schema Timers
      </h4>
     </div>
    </div>
   </div>
   <p>
    Performance Schema timers vary in precision and amount of
          overhead. To see what timers are available and their
          characteristics, check the
    <a class="link" href="performance-schema-performance-timers-table.html" title="29.12.22.6 The performance_timers Table">
     <code class="literal">
      performance_timers
     </code>
    </a>
    table:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa99594257"><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 operator">*</span> <span class="token keyword">FROM</span> performance_schema<span class="token punctuation">.</span>performance_timers<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>
<span class="token output"><span class="token punctuation">|</span> TIMER_NAME  <span class="token punctuation">|</span> TIMER_FREQUENCY <span class="token punctuation">|</span> TIMER_RESOLUTION <span class="token punctuation">|</span> TIMER_OVERHEAD <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>
<span class="token output"><span class="token punctuation">|</span> CYCLE       <span class="token punctuation">|</span>      2389029850 <span class="token punctuation">|</span>                1 <span class="token punctuation">|</span>             72 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> NANOSECOND  <span class="token punctuation">|</span>      1000000000 <span class="token punctuation">|</span>                1 <span class="token punctuation">|</span>            112 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> MICROSECOND <span class="token punctuation">|</span>         1000000 <span class="token punctuation">|</span>                1 <span class="token punctuation">|</span>            136 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> MILLISECOND <span class="token punctuation">|</span>            1036 <span class="token punctuation">|</span>                1 <span class="token punctuation">|</span>            168 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> THREAD_CPU  <span class="token punctuation">|</span>       339101694 <span class="token punctuation">|</span>                1 <span class="token punctuation">|</span>            798 <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></code></pre>
   </div>
   <p>
    If the values associated with a given timer name are
    <code class="literal">
     NULL
    </code>
    , that timer is not supported on your
          platform.
   </p>
   <p>
    The columns have these meanings:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       The
       <code class="literal">
        TIMER_NAME
       </code>
       column shows the names
              of the available timers.
       <code class="literal">
        CYCLE
       </code>
       refers
              to the timer that is based on the CPU (processor) cycle
              counter.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        TIMER_FREQUENCY
       </code>
       indicates the number of
              timer units per second. For a cycle timer, the frequency
              is generally related to the CPU speed. The value shown was
              obtained on a system with a 2.4GHz processor. The other
              timers are based on fixed fractions of seconds.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        TIMER_RESOLUTION
       </code>
       indicates the number
              of timer units by which timer values increase at a time.
              If a timer has a resolution of 10, its value increases by
              10 each time.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        TIMER_OVERHEAD
       </code>
       is the minimal number of
              cycles of overhead to obtain one timing with the given
              timer. The overhead per event is twice the value displayed
              because the timer is invoked at the beginning and end of
              the event.
      </p>
     </li>
    </ul>
   </div>
   <p>
    The Performance Schema assigns timers as follows:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       The wait timer uses
       <code class="literal">
        CYCLE
       </code>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       The idle, stage, statement, and transaction timers use
       <code class="literal">
        NANOSECOND
       </code>
       on platforms where the
       <code class="literal">
        NANOSECOND
       </code>
       timer is available,
       <code class="literal">
        MICROSECOND
       </code>
       otherwise.
      </p>
     </li>
    </ul>
   </div>
   <p>
    At server startup, the Performance Schema verifies that
          assumptions made at build time about timer assignments are
          correct, and displays a warning if a timer is not available.
   </p>
   <p>
    To time wait events, the most important criterion is to reduce
          overhead, at the possible expense of the timer accuracy, so
          using the
    <code class="literal">
     CYCLE
    </code>
    timer is the best.
   </p>
   <p>
    The time a statement (or stage) takes to execute is in general
          orders of magnitude larger than the time it takes to execute a
          single wait. To time statements, the most important criterion
          is to have an accurate measure, which is not affected by
          changes in processor frequency, so using a timer which is not
          based on cycles is the best. The default timer for statements
          is
    <code class="literal">
     NANOSECOND
    </code>
    . The extra
    <span class="quote">
     “
     <span class="quote">
      overhead
     </span>
     ”
    </span>
    compared to the
    <code class="literal">
     CYCLE
    </code>
    timer is not significant, because the
          overhead caused by calling a timer twice (once when the
          statement starts, once when it ends) is orders of magnitude
          less compared to the CPU time used to execute the statement
          itself. Using the
    <code class="literal">
     CYCLE
    </code>
    timer has no
          benefit here, only drawbacks.
   </p>
   <p>
    The precision offered by the cycle counter depends on
          processor speed. If the processor runs at 1 GHz (one billion
          cycles/second) or higher, the cycle counter delivers
          sub-nanosecond precision. Using the cycle counter is much
          cheaper than getting the actual time of day. For example, the
          standard
    <code class="literal">
     gettimeofday()
    </code>
    function can take
          hundreds of cycles, which is an unacceptable overhead for data
          gathering that may occur thousands or millions of times per
          second.
   </p>
   <p>
    Cycle counters also have disadvantages:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       End users expect to see timings in wall-clock units, such
              as fractions of a second. Converting from cycles to
              fractions of seconds can be expensive. For this reason,
              the conversion is a quick and fairly rough multiplication
              operation.
      </p>
     </li>
     <li class="listitem">
      <p>
       Processor cycle rate might change, such as when a laptop
              goes into power-saving mode or when a CPU slows down to
              reduce heat generation. If a processor's cycle rate
              fluctuates, conversion from cycles to real-time units is
              subject to error.
      </p>
     </li>
     <li class="listitem">
      <p>
       Cycle counters might be unreliable or unavailable
              depending on the processor or the operating system. For
              example, on Pentiums, the instruction is
       <code class="literal">
        RDTSC
       </code>
       (an assembly-language rather than
              a C instruction) and it is theoretically possible for the
              operating system to prevent user-mode programs from using
              it.
      </p>
     </li>
     <li class="listitem">
      <p>
       Some processor details related to out-of-order execution
              or multiprocessor synchronization might cause the counter
              to seem fast or slow by up to 1000 cycles.
      </p>
     </li>
    </ul>
   </div>
   <p>
    MySQL works with cycle counters on x386 (Windows, macOS,
          Linux, Solaris, and other Unix flavors), PowerPC, and IA-64.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="performance-schema-timing-in-events">
       </a>
       Performance Schema Timer Representation in Events
      </h4>
     </div>
    </div>
   </div>
   <p>
    Rows in Performance Schema tables that store current events
          and historical events have three columns to represent timing
          information:
    <code class="literal">
     TIMER_START
    </code>
    and
    <code class="literal">
     TIMER_END
    </code>
    indicate when an event started
          and finished, and
    <code class="literal">
     TIMER_WAIT
    </code>
    indicates
          event duration.
   </p>
   <p>
    The
    <a class="link" href="performance-schema-setup-instruments-table.html" title="29.12.2.3 The setup_instruments Table">
     <code class="literal">
      setup_instruments
     </code>
    </a>
    table has
          an
    <code class="literal">
     ENABLED
    </code>
    column to indicate the
          instruments for which to collect events. The table also has a
    <code class="literal">
     TIMED
    </code>
    column to indicate which instruments
          are timed. If an instrument is not enabled, it produces no
          events. If an enabled instrument is not timed, events produced
          by the instrument have
    <code class="literal">
     NULL
    </code>
    for the
    <code class="literal">
     TIMER_START
    </code>
    ,
    <code class="literal">
     TIMER_END
    </code>
    ,
          and
    <code class="literal">
     TIMER_WAIT
    </code>
    timer values. This in turn
          causes those values to be ignored when calculating aggregate
          time values in summary tables (sum, minimum, maximum, and
          average).
   </p>
   <p>
    Internally, times within events are stored in units given by
          the timer in effect when event timing begins. For display when
          events are retrieved from Performance Schema tables, times are
          shown in picoseconds (trillionths of a second) to normalize
          them to a standard unit, regardless of which timer is
          selected.
   </p>
   <p>
    The timer baseline (
    <span class="quote">
     “
     <span class="quote">
      time zero
     </span>
     ”
    </span>
    ) occurs at
          Performance Schema initialization during server startup.
    <code class="literal">
     TIMER_START
    </code>
    and
    <code class="literal">
     TIMER_END
    </code>
    values in events represent
          picoseconds since the baseline.
    <code class="literal">
     TIMER_WAIT
    </code>
    values are durations in picoseconds.
   </p>
   <p>
    Picosecond values in events are approximate. Their accuracy is
          subject to the usual forms of error associated with conversion
          from one unit to another. If the
    <code class="literal">
     CYCLE
    </code>
    timer is used and the processor rate varies, there might be
          drift. For these reasons, it is not reasonable to look at the
    <code class="literal">
     TIMER_START
    </code>
    value for an event as an
          accurate measure of time elapsed since server startup. On the
          other hand, it is reasonable to use
    <code class="literal">
     TIMER_START
    </code>
    or
    <code class="literal">
     TIMER_WAIT
    </code>
    values in
    <code class="literal">
     ORDER
          BY
    </code>
    clauses to order events by start time or
          duration.
   </p>
   <p>
    The choice of picoseconds in events rather than a value such
          as microseconds has a performance basis. One implementation
          goal was to show results in a uniform time unit, regardless of
          the timer. In an ideal world this time unit would look like a
          wall-clock unit and be reasonably precise; in other words,
          microseconds. But to convert cycles or nanoseconds to
          microseconds, it would be necessary to perform a division for
          every instrumentation. Division is expensive on many
          platforms. Multiplication is not expensive, so that is what is
          used. Therefore, the time unit is an integer multiple of the
          highest possible
    <code class="literal">
     TIMER_FREQUENCY
    </code>
    value,
          using a multiplier large enough to ensure that there is no
          major precision loss. The result is that the time unit is
    <span class="quote">
     “
     <span class="quote">
      picoseconds.
     </span>
     ”
    </span>
    This precision is spurious, but
          the decision enables overhead to be minimized.
   </p>
   <p>
    While a wait, stage, statement, or transaction event is
          executing, the respective current-event tables display
          current-event timing information:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-none"><div class="docs-select-all right" id="sa65160026"><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">events_waits_current
events_stages_current
events_statements_current
events_transactions_current</code></pre>
   </div>
   <p>
    To make it possible to determine how long a not-yet-completed
          event has been running, the timer columns are set as follows:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <code class="literal">
        TIMER_START
       </code>
       is populated.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        TIMER_END
       </code>
       is populated with the current
              timer value.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        TIMER_WAIT
       </code>
       is populated with the time
              elapsed so far (
       <code class="literal">
        TIMER_END
       </code>
       −
       <code class="literal">
        TIMER_START
       </code>
       ).
      </p>
     </li>
    </ul>
   </div>
   <p>
    Events that have not yet completed have an
    <code class="literal">
     END_EVENT_ID
    </code>
    value of
    <code class="literal">
     NULL
    </code>
    . To assess time elapsed so far for an
          event, use the
    <code class="literal">
     TIMER_WAIT
    </code>
    column.
          Therefore, to identify events that have not yet completed and
          have taken longer than
    <em class="replaceable">
     <code>
      N
     </code>
    </em>
    picoseconds thus far, monitoring applications can use this
          expression in queries:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa49503662"><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">WHERE</span> END_EVENT_ID <span class="token operator">IS</span> <span class="token boolean">NULL</span> <span class="token operator">AND</span> TIMER_WAIT <span class="token operator">&gt;</span> <em class="replaceable">N</em></code></pre>
   </div>
   <p>
    Event identification as just described assumes that the
          corresponding instruments have
    <code class="literal">
     ENABLED
    </code>
    and
    <code class="literal">
     TIMED
    </code>
    set to
    <code class="literal">
     YES
    </code>
    and
          that the relevant consumers are enabled.
   </p>
  </div>
 </div>
 <br/>
</div>
