<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="innodb-performance-thread_concurrency">
      </a>
      17.8.4 Configuring Thread Concurrency for InnoDB
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045162782032">
  </a>
  <a class="indexterm" name="idm46045162780992">
  </a>
  <a class="indexterm" name="idm46045162779904">
  </a>
  <a class="indexterm" name="idm46045162778816">
  </a>
  <p>
   <code class="literal">
    InnoDB
   </code>
   uses operating system
   <a class="link" href="glossary.html#glos_thread" title="thread">
    threads
   </a>
   to process requests
      from user transactions. (Transactions may issue many requests to
   <code class="literal">
    InnoDB
   </code>
   before they commit or roll back.) On
      modern operating systems and servers with multi-core processors,
      where context switching is efficient, most workloads run well
      without any limit on the number of concurrent threads.
  </p>
  <p>
   In situations where it is helpful to minimize context switching
      between threads,
   <code class="literal">
    InnoDB
   </code>
   can use a number of
      techniques to limit the number of concurrently executing operating
      system threads (and thus the number of requests that are processed
      at any one time). When
   <code class="literal">
    InnoDB
   </code>
   receives a new
      request from a user session, if the number of threads concurrently
      executing is at a pre-defined limit, the new request sleeps for a
      short time before it tries again. Threads waiting for locks are
      not counted in the number of concurrently executing threads.
  </p>
  <p>
   You can limit the number of concurrent threads by setting the
      configuration parameter
   <a class="link" href="innodb-parameters.html#sysvar_innodb_thread_concurrency">
    <code class="literal">
     innodb_thread_concurrency
    </code>
   </a>
   . Once
      the number of executing threads reaches this limit, additional
      threads sleep for a number of microseconds, set by the
      configuration parameter
   <a class="link" href="innodb-parameters.html#sysvar_innodb_thread_sleep_delay">
    <code class="literal">
     innodb_thread_sleep_delay
    </code>
   </a>
   , before
      being placed into the queue.
  </p>
  <p>
   You can set the configuration option
   <a class="link" href="innodb-parameters.html#sysvar_innodb_adaptive_max_sleep_delay">
    <code class="literal">
     innodb_adaptive_max_sleep_delay
    </code>
   </a>
   to the highest value you would allow for
   <a class="link" href="innodb-parameters.html#sysvar_innodb_thread_sleep_delay">
    <code class="literal">
     innodb_thread_sleep_delay
    </code>
   </a>
   , and
   <code class="literal">
    InnoDB
   </code>
   automatically adjusts
   <a class="link" href="innodb-parameters.html#sysvar_innodb_thread_sleep_delay">
    <code class="literal">
     innodb_thread_sleep_delay
    </code>
   </a>
   up or
      down depending on the current thread-scheduling activity. This
      dynamic adjustment helps the thread scheduling mechanism to work
      smoothly during times when the system is lightly loaded and when
      it is operating near full capacity.
  </p>
  <p>
   The default value for
   <a class="link" href="innodb-parameters.html#sysvar_innodb_thread_concurrency">
    <code class="literal">
     innodb_thread_concurrency
    </code>
   </a>
   and the
      implied default limit on the number of concurrent threads has been
      changed in various releases of MySQL and
   <code class="literal">
    InnoDB
   </code>
   . The default value of
   <a class="link" href="innodb-parameters.html#sysvar_innodb_thread_concurrency">
    <code class="literal">
     innodb_thread_concurrency
    </code>
   </a>
   is
   <code class="literal">
    0
   </code>
   , so that by default there is no limit on the
      number of concurrently executing threads.
  </p>
  <p>
   <code class="literal">
    InnoDB
   </code>
   causes threads to sleep only when the
      number of concurrent threads is limited. When there is no limit on
      the number of threads, all contend equally to be scheduled. That
      is, if
   <a class="link" href="innodb-parameters.html#sysvar_innodb_thread_concurrency">
    <code class="literal">
     innodb_thread_concurrency
    </code>
   </a>
   is
   <code class="literal">
    0
   </code>
   , the value of
   <a class="link" href="innodb-parameters.html#sysvar_innodb_thread_sleep_delay">
    <code class="literal">
     innodb_thread_sleep_delay
    </code>
   </a>
   is
      ignored.
  </p>
  <p>
   When there is a limit on the number of threads (when
   <a class="link" href="innodb-parameters.html#sysvar_innodb_thread_concurrency">
    <code class="literal">
     innodb_thread_concurrency
    </code>
   </a>
   is &gt;
      0),
   <code class="literal">
    InnoDB
   </code>
   reduces context switching overhead
      by permitting multiple requests made during the execution of a
   <span class="emphasis">
    <em>
     single SQL statement
    </em>
   </span>
   to enter
   <code class="literal">
    InnoDB
   </code>
   without observing the limit set by
   <a class="link" href="innodb-parameters.html#sysvar_innodb_thread_concurrency">
    <code class="literal">
     innodb_thread_concurrency
    </code>
   </a>
   . Since
      an SQL statement (such as a join) may comprise multiple row
      operations within
   <code class="literal">
    InnoDB
   </code>
   ,
   <code class="literal">
    InnoDB
   </code>
   assigns a specified number of
   <span class="quote">
    “
    <span class="quote">
     tickets
    </span>
    ”
   </span>
   that allow a thread to be scheduled
      repeatedly with minimal overhead.
  </p>
  <p>
   When a new SQL statement starts, a thread has no tickets, and it
      must observe
   <a class="link" href="innodb-parameters.html#sysvar_innodb_thread_concurrency">
    <code class="literal">
     innodb_thread_concurrency
    </code>
   </a>
   . Once
      the thread is entitled to enter
   <code class="literal">
    InnoDB
   </code>
   , it is
      assigned a number of tickets that it can use for subsequently
      entering
   <code class="literal">
    InnoDB
   </code>
   to perform row operations. If
      the tickets run out, the thread is evicted, and
   <a class="link" href="innodb-parameters.html#sysvar_innodb_thread_concurrency">
    <code class="literal">
     innodb_thread_concurrency
    </code>
   </a>
   is
      observed again which may place the thread back into the
      first-in/first-out queue of waiting threads. When the thread is
      once again entitled to enter
   <code class="literal">
    InnoDB
   </code>
   , tickets
      are assigned again. The number of tickets assigned is specified by
      the global option
   <a class="link" href="innodb-parameters.html#sysvar_innodb_concurrency_tickets">
    <code class="literal">
     innodb_concurrency_tickets
    </code>
   </a>
   , which
      is 5000 by default. A thread that is waiting for a lock is given
      one ticket once the lock becomes available.
  </p>
  <p>
   The correct values of these variables depend on your environment
      and workload. Try a range of different values to determine what
      value works for your applications. Before limiting the number of
      concurrently executing threads, review configuration options that
      may improve the performance of
   <code class="literal">
    InnoDB
   </code>
   on
      multi-core and multi-processor computers, such as
   <a class="link" href="innodb-parameters.html#sysvar_innodb_adaptive_hash_index">
    <code class="literal">
     innodb_adaptive_hash_index
    </code>
   </a>
   .
  </p>
  <p>
   For general performance information about MySQL thread handling,
      see
   <a class="xref" href="connection-interfaces.html" title="7.1.12.1 Connection Interfaces">
    Section 7.1.12.1, “Connection Interfaces”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
