<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="thread-pool-tuning">
      </a>
      7.6.3.4 Thread Pool Tuning
     </h4>
    </div>
   </div>
  </div>
  <p>
   This section provides guidelines on determining the best
        configuration for thread pool performance, as measured using a
        metric such as transactions per second.
  </p>
  <p>
   Of chief importance is the number of thread groups in the thread
        pool, which can be set on server startup using the
   <a class="link" href="server-system-variables.html#sysvar_thread_pool_size">
    <code class="option">
     --thread-pool-size
    </code>
   </a>
   option; this
        cannot be changed at runtime. Recommended values for this option
        depend on whether the primary storage engine in use is
   <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
    <code class="literal">
     InnoDB
    </code>
   </a>
   or
   <a class="link" href="myisam-storage-engine.html" title="18.2 The MyISAM Storage Engine">
    <code class="literal">
     MyISAM
    </code>
   </a>
   :
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      If the primary storage engine is
      <code class="literal">
       InnoDB
      </code>
      ,
            the recommended value for the thread pool size is the number
            of physical cores available on the host machine, up to a
            maximum of 512.
     </p>
    </li>
    <li class="listitem">
     <p>
      If the primary storage engine is
      <code class="literal">
       MyISAM
      </code>
      ,
            the thread pool size should be fairly low. Optimal
            performance is often seen with values from 4 to 8. Higher
            values tend to have a slightly negative but not dramatic
            impact on performance.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The upper limit on the number of concurrent transactions that
        can be processed by the thread pool plugin is determined by the
        value 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>
   .
        The recommendation initial setting for this system variable is
        the number of physical cores times 32. You may need to adjust
        the value from this starting point to suit a given workload; a
        reasonable upper bound for this value is the maximum number of
        concurrent connections expected; the value of the
   <a class="link" href="server-status-variables.html#statvar_Max_used_connections">
    <code class="literal">
     Max_used_connections
    </code>
   </a>
   status
        variable can serve as a guide to determining this. A good way to
        proceed is to start with
   <code class="literal">
    thread_pool_max_transactions_limit
   </code>
   set to
        this value, then adjust it downwards while observing the effect
        on throughput.
  </p>
  <p>
   The maximum number of query threads permitted in a thread group
        is determined by the value of
   <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>
   ,
        which can be adjusted at runtime. The product of this value and
        the thread pool size is approximately equal to the total number
        of threads available to process queries. Obtaining the best
        performance usually means striking the proper balance for your
        application between
   <code class="literal">
    thread_pool_query_threads_per_group
   </code>
   and the
        thread pool size. Greater values for
   <code class="literal">
    thread_pool_query_threads_per_group
   </code>
   value
        make it less likely that all the threads in the thread group
        simultaneously execute long running queries while blocking
        shorter ones when the workload includes both long and short
        running queries. You should bear in mind that the overhead of
        the connection polling operation for each thread group increases
        when using smaller values for the thread pool size with larger
        values for
   <code class="literal">
    thread_pool_query_threads_per_group
   </code>
   . For this
        reason, we recommend a starting value of 2 for
   <code class="literal">
    thread_pool_query_threads_per_group
   </code>
   ; setting
        this variable to a lower value usually does not offer any
        performance benefit.
  </p>
  <p>
   For best performance under normal conditions, we also recommend
        that you set
   <a class="link" href="server-system-variables.html#sysvar_thread_pool_algorithm">
    <code class="literal">
     thread_pool_algorithm
    </code>
   </a>
   to 1 for
        high concurrency.
  </p>
  <p>
   In addition, 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 handling of blocked and long-running
        statements. If all calls blocking the MySQL Server were reported
        to the thread pool, it would always know when execution threads
        are blocked, but this may not always be true. For example,
        blocks could occur in code that has not been instrumented with
        thread pool callbacks. For such cases, the thread pool must be
        able to identify threads that appear to be blocked. This is done
        by means of a timeout determined by the value of
   <code class="literal">
    thread_pool_stall_limit
   </code>
   , which ensures that
        the server does not become completely blocked. The value of
   <code class="literal">
    thread_pool_stall_limit
   </code>
   represents a number
        of 10-millisecond intervals, so that
   <code class="literal">
    600
   </code>
   (the
        maximum) represents 6 seconds.
  </p>
  <p>
   <a class="link" href="server-system-variables.html#sysvar_thread_pool_stall_limit">
    <code class="literal">
     thread_pool_stall_limit
    </code>
   </a>
   also
        enables the thread pool to handle long-running statements. If a
        long-running statement were permitted to block a thread group,
        all other connections assigned to the group would be blocked and
        unable to start execution until the long-running statement
        completed. In the worst case, this could take hours or even
        days.
  </p>
  <p>
   The value of
   <a class="link" href="server-system-variables.html#sysvar_thread_pool_stall_limit">
    <code class="literal">
     thread_pool_stall_limit
    </code>
   </a>
   should
        be chosen such that statements that execute longer than its
        value are considered stalled. Stalled statements generate a lot
        of extra overhead since they involve extra context switches and
        in some cases even extra thread creations. On the other hand,
        setting the
   <a class="link" href="server-system-variables.html#sysvar_thread_pool_stall_limit">
    <code class="literal">
     thread_pool_stall_limit
    </code>
   </a>
   parameter too high means that long-running statements block a
        number of short-running statements for longer than necessary.
        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>
  <p>
   Suppose a server executes a workload where 99.9% of the
        statements complete within 100ms even when the server is loaded,
        and the remaining statements take between 100ms and 2 hours
        fairly evenly spread. In this case, it would make sense to set
   <a class="link" href="server-system-variables.html#sysvar_thread_pool_stall_limit">
    <code class="literal">
     thread_pool_stall_limit
    </code>
   </a>
   to 10
        (10 × 10ms = 100ms). The default value of 6 (60ms)
        is suitable for servers that primarily execute very simple
        statements.
  </p>
  <p>
   The
   <a class="link" href="server-system-variables.html#sysvar_thread_pool_stall_limit">
    <code class="literal">
     thread_pool_stall_limit
    </code>
   </a>
   parameter can be changed at runtime to enable you to strike a
        balance appropriate for the server work load. Assuming that 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">
     tp_thread_group_stats
    </code>
   </a>
   table is
        enabled, you can use the following query to determine the
        fraction of executed statements that stalled:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa95901415"><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 function">SUM</span><span class="token punctuation">(</span>STALLED_QUERIES_EXECUTED<span class="token punctuation">)</span> <span class="token operator">/</span> <span class="token function">SUM</span><span class="token punctuation">(</span>QUERIES_EXECUTED<span class="token punctuation">)</span>
<span class="token keyword">FROM</span> performance_schema<span class="token punctuation">.</span>tp_thread_group_stats<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   This number should be as low as possible. To decrease the
        likelihood of statements stalling, increase the value of
   <a class="link" href="server-system-variables.html#sysvar_thread_pool_stall_limit">
    <code class="literal">
     thread_pool_stall_limit
    </code>
   </a>
   .
  </p>
  <p>
   When a statement arrives, what is the maximum time it can be
        delayed before it actually starts executing? Suppose that the
        following conditions apply:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      There are 200 statements queued in the low-priority queue.
     </p>
    </li>
    <li class="listitem">
     <p>
      There are 10 statements queued in the high-priority queue.
     </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>
      is set to 10000 (10 seconds).
     </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>
      is
            set to 100 (1 second).
     </p>
    </li>
   </ul>
  </div>
  <p>
   In the worst case, the 10 high-priority statements represent 10
        transactions that continue executing for a long time. Thus, in
        the worst case, no statements can be moved to the high-priority
        queue because it always already contains statements awaiting
        execution. After 10 seconds, the new statement is eligible to be
        moved to the high-priority queue. However, before it can be
        moved, all the statements before it must be moved as well. This
        could take another 2 seconds because a maximum of 100 statements
        per second are moved to the high-priority queue. Now when the
        statement reaches the high-priority queue, there could
        potentially be many long-running statements ahead of it. In the
        worst case, every one of those becomes stalled and 1 second is
        required for each statement before the next statement is
        retrieved from the high-priority queue. Thus, in this scenario,
        it takes 222 seconds before the new statement starts executing.
  </p>
  <p>
   This example shows a worst case for an application. How to
        handle it depends on the application. If the application has
        high requirements for the response time, it should most likely
        throttle users at a higher level itself. Otherwise, it can use
        the thread pool configuration parameters to set some kind of a
        maximum waiting time.
  </p>
 </div>
 <br/>
</div>
