This section provides guidelines on setting thread pool system variables for best performance, measured using a metric such as transactions per second.
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:
If the primary storage engine is
InnoDB, the optimal
thread_pool_sizesetting 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.
For workloads such as DBT2 and Sysbench, the optimum for
InnoDBseems to be usually around 36. For very write-intensive workloads, the optimal setting can sometimes be lower.
If the primary storage engine is
thread_pool_sizesetting should be fairly low. We tend to get optimal performance for values from 4 to 8. Higher values tend to have a slightly negative but not dramatic impact on performance.
Another system variable,
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, the length of which can be tuned using
system variable. 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
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
The value of
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,
parameter too high means that long-running statements will 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.
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
thread_pool_stall_limit to 10
(meaning 100ms). The default value of 60ms is okay for servers
that primarily execute very simple statements.
parameter can be changed at runtime to enable you to strike a
balance appropriate for the server work load. Assuming that the
TP_THREAD_GROUP_STATS table is enabled, you
can use the following query to determine the fraction of
executed statements that stalled:
SELECT SUM(STALLED_QUERIES_EXECUTED) / SUM(QUERIES_EXECUTED) FROM INFORMATION_SCHEMA.TP_THREAD_GROUP_STATS;
This number should be as low as possible. To decrease the
likelihood of statements stalling, increase the value of
When a statement arrives, what is the maximum time it can be delayed before it actually starts executing? Suppose that the following conditions apply:
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 will be moved to the high-priority queue because it will always already contain 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 will become stalled and it will take 1 second for each statement before the next statement is retrieved from the high-priority queue. Thus, in this scenario, it will take 222 seconds before the new statement starts executing.
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.