tag:blogger.com,1999:blog-14455177.post2009462092266278123..comments2024-03-07T18:57:25.977+01:00Comments on Mikael Ronstrom: MySQL Thread Pool: When to use?Mikael Ronstromhttp://www.blogger.com/profile/07134215866292829917noreply@blogger.comBlogger1125tag:blogger.com,1999:blog-14455177.post-47560062194631597222011-10-24T16:34:06.284+02:002011-10-24T16:34:06.284+02:00Mikael, you might want to try some modelling of th...Mikael, you might want to try some modelling of the sort of thing that we see in production workloads, say:<br /><br />1. 1500 mean queries per second with standard deviation 100 and each query running for 20ms with standard deviation 4ms.<br />2. plus 5 mean queries per second with standard deviation 2 and each query running for 25s with standard deviation 5s.<br />3. all connecting over a TCP/IP connection with latency of 1ms mean, standard deviation 0.1s.<br /><br />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.<br /><br />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.<br /><br />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.<br /><br />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.<br /><br />James Day, MySQL Support Engineer, OracleJames Dayhttp://mysql.comnoreply@blogger.com