1

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 ?

CodeWiz
  • 21
  • 4

2 Answers2

1

For the sake of keeping this topic up-to-date, infinite concurrency is no longer advised for MySQL 5.7 and above. Why ???

In my early years in the DBA StackExchange, I used to recommend setting innodb_thread_concurrency to 0 based on what I learned firsthand at Percona Live 2011 (See my Jun 20, 2012 post MySQL transaction size - how big is too big?).

Five years later, I learned that Percona had benchmarked different values for innodb_thread_concurrency and found out that CPU performance tops out at 64 for Oracle's MySQL:

Therefore, it must be said

  • 0 is the best value before MySQL 5.7
  • 64 is the best value for MySQL 5.7 and beyond
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
0

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

Rick James
  • 80,479
  • 5
  • 52
  • 119