MySQL Enterprise Thread Pool •Binary log dump threads that read the binary log and send it to replicas. This is a kind of long-running “statement”  that runs for a very long time, and that should not prevent other statements from executing. •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. •If the primary storage engine is  InnoDB , the optimal  thread_pool_size  setting is likely to be between 16 and 36, with the most common optimal values tending to be from 24 to 36. We have not seen any situation where the setting has been optimal beyond 36. There may be special cases where a value smaller than 16 is optimal. •If the primary storage engine is  MyISAM , the  thread_pool_size  setting 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. 1085 The value of  thread_pool_stall_limit  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 thread_pool_stall_limit  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. thread_pool_stall_limit  also enables the thread pool to handle long-running statements. If a long- running statement was 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. Another system variable,  thread_pool_stall_limit , is important for handling of blocked and long- running statements. If all calls that block the MySQL Server are reported to the thread pool, it would always know when execution threads are blocked. However, 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 that can be tuned using the  thread_pool_stall_limit  system variable, the value of which is measured in 10ms units. This parameter ensures that the server does not become completely blocked. The value of thread_pool_stall_limit  has an upper limit of 6 seconds to prevent the risk of a deadlocked server. For workloads such as DBT2 and Sysbench, the optimum for  InnoDB  seems to be usually around 36. For very write-intensive workloads, the optimal setting can sometimes be lower. thread_pool_size  is the most important parameter controlling thread pool performance. It can be set only at server startup. Our experience in testing the thread pool indicates the following: This section provides guidelines on setting thread pool system variables for best performance, measured using a metric such as transactions per second. The maximum number of threads that can occur is the sum of  max_connections  and thread_pool_size . 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. 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. 5.6.3.4 Thread Pool Tuning