I'm in a plan to increase thread concurrency in my production MySQL server 5.7 but not sure, before what are the params/configurations that need to be measured and configured to increase the thread concurrency to the right optimal value. When checked with the MySQL official documentation, there seems to be a note stating
The correct values of these variables depend on your environment and workload.Try a range of different values to determine what value works for your applications. Before limiting the number of concurrently executing threads, review configuration options that may improve the performance of InnoDB on multi-core and multi-processor computers
When referred with other blogs, came up with some formulas as
concurrency = 2 x no.of cpu
And in some other similar blogs, there they stated along with disk as
concurrency = 2 * (NumCPUs + NumDisks)
How to benchmark my MySQL workload to tune up with the thread concurrency?
Currently, I'm having
thread concurrency = 8 RAM = 64 GB CPU = 20 core
Occasionally, There seems to be sudden increase in overall thread usage by slow queries and all 8 threads were under operation resulting in making normal running queries to get slowed down :(
Hence what's the right way to tune up with thread concurrency ?