<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="thread-pool-operation">
      </a>
      7.6.3.3 Thread Pool Operation
     </h4>
    </div>
   </div>
  </div>
  <p>
   The thread pool consists of a number of thread groups, each of
        which manages a set of client connections. As connections are
        established, the thread pool assigns them to thread groups in
        round-robin fashion.
  </p>
  <p>
   The thread pool exposes system variables that may be used to
        configure its operation:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_thread_pool_algorithm">
       <code class="literal">
        thread_pool_algorithm
       </code>
      </a>
      : The
            concurrency algorithm to use for scheduling.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_thread_pool_dedicated_listeners">
       <code class="literal">
        thread_pool_dedicated_listeners
       </code>
      </a>
      :
            Dedicates a listener thread in each thread group to listen
            for incoming statements from connections assigned to the
            group.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_thread_pool_high_priority_connection">
       <code class="literal">
        thread_pool_high_priority_connection
       </code>
      </a>
      :
            How to schedule statement execution for a session.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_thread_pool_longrun_trx_limit">
       <code class="literal">
        thread_pool_longrun_trx_limit
       </code>
      </a>
      :
            How long to wait while threads using all of
      <a class="link" href="server-system-variables.html#sysvar_thread_pool_max_transactions_limit">
       <code class="literal">
        thread_pool_max_transactions_limit
       </code>
      </a>
      have been executing before suspending the limit for the
            group.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_thread_pool_max_active_query_threads">
       <code class="literal">
        thread_pool_max_active_query_threads
       </code>
      </a>
      :
            How many active threads per group to permit.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_thread_pool_max_transactions_limit">
       <code class="literal">
        thread_pool_max_transactions_limit
       </code>
      </a>
      :
            The maximum number of transactions permitted by the thread
            pool plugin.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_thread_pool_max_unused_threads">
       <code class="literal">
        thread_pool_max_unused_threads
       </code>
      </a>
      :
            How many sleeping threads to permit.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_thread_pool_prio_kickup_timer">
       <code class="literal">
        thread_pool_prio_kickup_timer
       </code>
      </a>
      :
            How long before the thread pool moves a statement awaiting
            execution from the low-priority queue to the high-priority
            queue.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_thread_pool_query_threads_per_group">
       <code class="literal">
        thread_pool_query_threads_per_group
       </code>
      </a>
      :
            The number of query threads permitted in a thread group (the
            default is a single query thread). Consider increasing the
            value if you experience slower response times due to
            long-running transactions.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_thread_pool_size">
       <code class="literal">
        thread_pool_size
       </code>
      </a>
      : The
            number of thread groups in the thread pool. This is the most
            important parameter controlling thread pool performance.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_thread_pool_stall_limit">
       <code class="literal">
        thread_pool_stall_limit
       </code>
      </a>
      :
            The time before an executing statement is considered to be
            stalled.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_thread_pool_transaction_delay">
       <code class="literal">
        thread_pool_transaction_delay
       </code>
      </a>
      :
            The delay period before starting a new transaction.
     </p>
    </li>
   </ul>
  </div>
  <p>
   To configure the number of thread groups, use the
   <a class="link" href="server-system-variables.html#sysvar_thread_pool_size">
    <code class="literal">
     thread_pool_size
    </code>
   </a>
   system
        variable. The default number of groups is 16. For guidelines on
        setting this variable, see
   <a class="xref" href="thread-pool-tuning.html" title="7.6.3.4 Thread Pool Tuning">
    Section 7.6.3.4, “Thread Pool Tuning”
   </a>
   .
  </p>
  <p>
   The maximum number of threads per group is 4096 (or 4095 on some
        systems where one thread is used internally).
  </p>
  <p>
   The thread pool separates connections and threads, so there is
        no fixed relationship between connections and the threads that
        execute statements received from those connections. This differs
        from the default thread-handling model that associates one
        thread with one connection such that a given thread executes all
        statements from its connection.
  </p>
  <p>
   By default, the thread pool tries to ensure a maximum of one
        thread executing in each group at any time, but sometimes
        permits more threads to execute temporarily for best
        performance:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Each thread group has a listener thread that listens for
            incoming statements from the connections assigned to the
            group. When a statement arrives, the thread group either
            begins executing it immediately or queues it for later
            execution:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         Immediate execution occurs if the statement is the only
                one received, and there are no statements queued or
                currently executing.
        </p>
        <p>
         Immediate execution can be delayed by configuring
         <code class="literal">
          thread_pool_transaction_delay
         </code>
         , which
                has a throttling effect on transactions. For more
                information, refer to the description of this variable
                in the discussion that follows.
        </p>
       </li>
       <li class="listitem">
        <p>
         Queuing occurs if the statement cannot begin executing
                immediately due to concurrently queued or executing
                statements.
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      The
      <a class="link" href="server-system-variables.html#sysvar_thread_pool_transaction_delay">
       <code class="literal">
        thread_pool_transaction_delay
       </code>
      </a>
      variable specifies a transaction delay in milliseconds.
            Worker threads sleep for the specified period before
            executing a new transaction.
     </p>
     <p>
      A transaction delay can be used in cases where parallel
            transactions affect the performance of other operations due
            to resource contention. For example, if parallel
            transactions affect index creation or an online buffer pool
            resizing operation, you can configure a transaction delay to
            reduce resource contention while those operations are
            running. The delay has a throttling effect on transactions.
     </p>
     <p>
      The
      <code class="literal">
       thread_pool_transaction_delay
      </code>
      setting
            does not affect queries issued from a privileged connection
            (a connection assigned to the
      <code class="literal">
       Admin
      </code>
      thread group). These queries are not subject to a configured
            transaction delay.
     </p>
    </li>
    <li class="listitem">
     <p>
      If immediate execution occurs, the listener thread performs
            it. (This means that temporarily no thread in the group is
            listening.) If the statement finishes quickly, the executing
            thread returns to listening for statements. Otherwise, the
            thread pool considers the statement stalled and starts
            another thread as a listener thread (creating it if
            necessary). To ensure that no thread group becomes blocked
            by stalled statements, the thread pool has a background
            thread that regularly monitors thread group states.
     </p>
     <p>
      By using the listening thread to execute a statement that
            can begin immediately, there is no need to create an
            additional thread if the statement finishes quickly. This
            ensures the most efficient execution possible in the case of
            a low number of concurrent threads.
     </p>
     <p>
      When the thread pool plugin starts, it creates one thread
            per group (the listener thread), plus the background thread.
            Additional threads are created as necessary to execute
            statements.
     </p>
    </li>
    <li class="listitem">
     <p>
      The value of the
      <a class="link" href="server-system-variables.html#sysvar_thread_pool_stall_limit">
       <code class="literal">
        thread_pool_stall_limit
       </code>
      </a>
      system variable determines the meaning of
      <span class="quote">
       “
       <span class="quote">
        finishes
            quickly
       </span>
       ”
      </span>
      in the previous item. The default time
            before threads are considered stalled is 60ms but can be set
            to a maximum of 6s. This parameter is configurable to enable
            you to strike a balance appropriate for the server work
            load. Short wait values permit threads to start more
            quickly. Short values are also better for avoiding deadlock
            situations. Long wait values are useful for workloads that
            include long-running statements, to avoid starting too many
            new statements while the current ones execute.
     </p>
    </li>
    <li class="listitem">
     <p>
      If
      <a class="link" href="server-system-variables.html#sysvar_thread_pool_max_active_query_threads">
       <code class="literal">
        thread_pool_max_active_query_threads
       </code>
      </a>
      is 0, the default algorithm applies as just described for
            determining the maximum number of active threads per group.
            The default algorithm takes stalled threads into account and
            may temporarily permit more active threads. If
      <a class="link" href="server-system-variables.html#sysvar_thread_pool_max_active_query_threads">
       <code class="literal">
        thread_pool_max_active_query_threads
       </code>
      </a>
      is greater than 0, it places a limit on the number of active
            threads per group.
     </p>
    </li>
    <li class="listitem">
     <p>
      The thread pool focuses on limiting the number of concurrent
            short-running statements. Before an executing statement
            reaches the stall time, it prevents other statements from
            beginning to execute. If the statement executes past the
            stall time, it is permitted to continue but no longer
            prevents other statements from starting. In this way, the
            thread pool tries to ensure that in each thread group there
            is never more than one short-running statement, although
            there might be multiple long-running statements. It is
            undesirable to let long-running statements prevent other
            statements from executing because there is no limit on the
            amount of waiting that might be necessary. For example, on a
            replication source server, a thread that is sending binary
            log events to a replica effectively runs forever.
     </p>
    </li>
    <li class="listitem">
     <p>
      A statement becomes blocked if it encounters a disk I/O
            operation or a user level lock (row lock or table lock). The
            block would cause the thread group to become unused, so
            there are callbacks to the thread pool to ensure that the
            thread pool can immediately start a new thread in this group
            to execute another statement. When a blocked thread returns,
            the thread pool permits it to restart immediately.
     </p>
    </li>
    <li class="listitem">
     <p>
      There are two queues, a high-priority queue and a
            low-priority queue. The first statement in a transaction
            goes to the low-priority queue. Any following statements for
            the transaction go to the high-priority queue if the
            transaction is ongoing (statements for it have begun
            executing), or to the low-priority queue otherwise. Queue
            assignment can be affected by enabling the
      <a class="link" href="server-system-variables.html#sysvar_thread_pool_high_priority_connection">
       <code class="literal">
        thread_pool_high_priority_connection
       </code>
      </a>
      system variable, which causes all queued statements for a
            session to go into the high-priority queue.
     </p>
     <p>
      Statements for a nontransactional storage engine, or a
            transactional engine if
      <a class="link" href="server-system-variables.html#sysvar_autocommit">
       <code class="literal">
        autocommit
       </code>
      </a>
      is enabled, are
            treated as low-priority statements because in this case each
            statement is a transaction. Thus, given a mix of statements
            for
      <code class="literal">
       InnoDB
      </code>
      and
      <code class="literal">
       MyISAM
      </code>
      tables, the thread pool prioritizes those for
      <code class="literal">
       InnoDB
      </code>
      over those for
      <code class="literal">
       MyISAM
      </code>
      unless
      <a class="link" href="server-system-variables.html#sysvar_autocommit">
       <code class="literal">
        autocommit
       </code>
      </a>
      is enabled. With
      <a class="link" href="server-system-variables.html#sysvar_autocommit">
       <code class="literal">
        autocommit
       </code>
      </a>
      enabled, all
            statements have low priority.
     </p>
    </li>
    <li class="listitem">
     <p>
      When the thread group selects a queued statement for
            execution, it first looks in the high-priority queue, then
            in the low-priority queue. If a statement is found, it is
            removed from its queue and begins to execute.
     </p>
    </li>
    <li class="listitem">
     <p>
      If a statement stays in the low-priority queue too long, the
            thread pool moves to the high-priority queue. The value of
            the
      <a class="link" href="server-system-variables.html#sysvar_thread_pool_prio_kickup_timer">
       <code class="literal">
        thread_pool_prio_kickup_timer
       </code>
      </a>
      system variable controls the time before movement. For each
            thread group, a maximum of one statement per 10ms (100 per
            second) is moved from the low-priority queue to the
            high-priority queue.
     </p>
    </li>
    <li class="listitem">
     <p>
      The thread pool reuses the most active threads to obtain a
            much better use of CPU caches. This is a small adjustment
            that has a great impact on performance.
     </p>
    </li>
    <li class="listitem">
     <p>
      While a thread executes a statement from a user connection,
            Performance Schema instrumentation accounts thread activity
            to the user connection. Otherwise, Performance Schema
            accounts activity to the thread pool.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Here are examples of conditions under which a thread group might
        have multiple threads started to execute statements:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      One thread begins executing a statement, but runs long
            enough to be considered stalled. The thread group permits
            another thread to begin executing another statement even
            through the first thread is still executing.
     </p>
    </li>
    <li class="listitem">
     <p>
      One thread begins executing a statement, then becomes
            blocked and reports this back to the thread pool. The thread
            group permits another thread to begin executing another
            statement.
     </p>
    </li>
    <li class="listitem">
     <p>
      One thread begins executing a statement, becomes blocked,
            but does not report back that it is blocked because the
            block does not occur in code that has been instrumented with
            thread pool callbacks. In this case, the thread appears to
            the thread group to be still running. If the block lasts
            long enough for the statement to be considered stalled, the
            group permits another thread to begin executing another
            statement.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The thread pool is designed to be scalable across an increasing
        number of connections. It is also designed to avoid deadlocks
        that can arise from limiting the number of actively executing
        statements. It is important that threads that do not report back
        to the thread pool do not prevent other statements from
        executing and thus cause the thread pool to become deadlocked.
        Examples of such statements follow:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Long-running statements. These would lead to all resources
            used by only a few statements and they could prevent all
            others from accessing the server.
     </p>
    </li>
    <li class="listitem">
     <p>
      Binary log dump threads that read the binary log and send it
            to replicas. This is a kind of long-running
      <span class="quote">
       “
       <span class="quote">
        statement
       </span>
       ”
      </span>
      that runs for a very long time, and
            that should not prevent other statements from executing.
     </p>
    </li>
    <li class="listitem">
     <p>
      Statements blocked on a row lock, table lock, sleep, or any
            other blocking activity that has not been reported back to
            the thread pool by MySQL Server or a storage engine.
     </p>
    </li>
   </ul>
  </div>
  <p>
   In each case, to prevent deadlock, the statement is moved to the
        stalled category when it does not complete quickly, so that the
        thread group can permit another statement to begin executing.
        With this design, when a thread executes or becomes blocked for
        an extended time, the thread pool moves the thread to the
        stalled category and for the rest of the statement's execution,
        it does not prevent other statements from executing.
  </p>
  <p>
   The maximum number of threads that can occur is the sum of
   <a class="link" href="server-system-variables.html#sysvar_max_connections">
    <code class="literal">
     max_connections
    </code>
   </a>
   and
   <a class="link" href="server-system-variables.html#sysvar_thread_pool_size">
    <code class="literal">
     thread_pool_size
    </code>
   </a>
   . This can
        happen in a situation where all connections are in execution
        mode and an extra thread is created per group to listen for more
        statements. This is not necessarily a state that happens often,
        but it is theoretically possible.
  </p>
  <h5>
   <a name="privileged-connections">
   </a>
   Privileged Connections
  </h5>
  <p>
   If the limit defined by
   <a class="link" href="server-system-variables.html#sysvar_thread_pool_max_transactions_limit">
    <code class="literal">
     thread_pool_max_transactions_limit
    </code>
   </a>
   has been reached and new connections or new transactions using
        existing connections appear to hang until one or more existing
        transactions are completed, in spite of any adjustments made to
   <a class="link" href="server-system-variables.html#sysvar_thread_pool_longrun_trx_limit">
    <code class="literal">
     thread_pool_longrun_trx_limit
    </code>
   </a>
   ,
        so that all existing connections are blocked or long-running,
        the only way to access the server may be to use a privileged
        connection.
  </p>
  <p>
   To establish a privileged connection, the user initiating the
        connection must have the
   <a class="link" href="privileges-provided.html#priv_tp-connection-admin">
    <code class="literal">
     TP_CONNECTION_ADMIN
    </code>
   </a>
   privilege. A
        privileged connection ignores the limit defined by
   <a class="link" href="server-system-variables.html#sysvar_thread_pool_max_transactions_limit">
    <code class="literal">
     thread_pool_max_transactions_limit
    </code>
   </a>
   and permits connecting to the server to increase the limit,
        remove the limit, or kill running transactions.
   <a class="link" href="privileges-provided.html#priv_tp-connection-admin">
    <code class="literal">
     TP_CONNECTION_ADMIN
    </code>
   </a>
   privilege
        must be granted explicitly. It is not granted to any user by
        default.
  </p>
  <p>
   A privileged connection can execute statements and start
        transactions, and is assigned to a thread group designated as
        the
   <code class="literal">
    Admin
   </code>
   thread group.
  </p>
  <p>
   When querying the
   <a class="link" href="performance-schema-tp-thread-group-stats-table.html" title="29.12.16.3 The tp_thread_group_stats Table">
    <code class="literal">
     performance_schema.tp_thread_group_stats
    </code>
   </a>
   table, which reports statistics per thread group,
   <code class="literal">
    Admin
   </code>
   thread group statistics are reported in
        the last row of the result set. For example, if
   <code class="literal">
    SELECT
        * FROM performance_schema.tp_thread_group_stats
   </code>
   returns 17 rows (one row per thread group), the
   <code class="literal">
    Admin
   </code>
   thread group statistics are reported in
        the 17th row.
  </p>
 </div>
 <br/>
</div>
