The most important variable to monitor is threads_running. This
variable keeps track of the number of concurrent statements
currently executing in the MySQL Server.
If this variable has spikes that put it in a region where the
server won't operate optimally (usually going beyond 40 for
InnoDB workloads) and most particular if it goes well beyond
this into the hundreds or even thousands of concurrent
statements then the thread pool will be something beneficial
to protect the MySQL Server from troubles in overload
situations.
Another indicator that you will benefit from use of the thread
pool is when you already now use the --innodb-thread-concurrency
variable. This variable tries to solve a similar problem and the
thread pool solves it at a better place even before query execution
has started and also provides additional benefits.
Also if your workload is mainly short queries then the thread
pool will be beneficial, long queries isn't bad for the thread
pool but will decrease its positive impact.
Mikael, you might want to try some modelling of the sort of thing that we see in production workloads, say:
ReplyDelete1. 1500 mean queries per second with standard deviation 100 and each query running for 20ms with standard deviation 4ms.
2. plus 5 mean queries per second with standard deviation 2 and each query running for 25s with standard deviation 5s.
3. all connecting over a TCP/IP connection with latency of 1ms mean, standard deviation 0.1s.
Increase the mean for each type of query and see how the system under test stays stable or becomes increasingly unstable and sensitive to small variations in workload as the means approach its sustained throughput capability.
The variations are what can periodically cause otherwise sound servers to move from transient to persistent overloads from time to time, when it happens that a particular pattern of work has caused the server to move into the negative scalability region.
The lack of such variations is a major flaw in most benchmarks and results in somewhat different server designs from stable load benchmarks. Deferring background work matters more because it damps the excursions.
Describing how the combination of thread pool and innodb_thread_concurrency should be used for such workloads would also be useful. Its likely benefit is decreasing the speed at which slower queries accumulate in the server, decreasing the chance of an excursion or sustained excursion into the negative scalability region.
James Day, MySQL Support Engineer, Oracle