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 ?

  • Additional information request. Post on pastebin.com and share the links. From SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report AND Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions. – Wilson Hauck Nov 14 '19 at 20:46
  • Will check and update with you – CodeWiz Nov 15 '19 at 5:52

Set it to 0 and let InnoDB pick the concurrency.

20 cores with currency=8 means that at least 12 cores will always be idle.

What is the value of Max_used_connections? If that is not very high, the "concurrency" is not your main problem.

Meanwhile, let's see one of the 'slow' queries, together with SHOW CREATE TABLE; there may be ways to speed it up, possibly decreasing the I/O.

For more analysis of your VARIABLES and GLOBAL STATUS, let's see them. http://mysql.rjweb.org/doc.php/mysql_analysis#tuning

  • Thanks.. Will look into it – CodeWiz Nov 15 '19 at 5:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.