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
system variable. This parameter ensures that the server does
not become completely blocked. The value of
an upper limit of 6 seconds to prevent the risk of a
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.
The value of
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
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
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
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.