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 ?

2
  • 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. Nov 14, 2019 at 20:46
  • Will check and update with you
    – CodeWiz
    Nov 15, 2019 at 5:52

2 Answers 2

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
3
  • Do you not NEED to know how many CORES on the the equipment before using 64? Some people suggest max of CORES * .8 rounded down to allow other application functions some access to cores. In any case many people are running with no where near 64 cores available. Dec 23, 2021 at 20:02
  • @WilsonHauck In the past, 0 lets InnoDB decide what's best before Oracle MySQL 5.7 and with earlier versions of MariaDB. 64 is the best value now. Leaving it 0 is best for Percona Server and newer versions of Oracle MySQL and MariaDB. Please keep in mind that this is about threads not cores. One can still use that formula but may be unwittingly throttling the application. You could gradually lift it beyond 8 and see top and innotop. Dec 23, 2021 at 21:00
  • Rolando, Throttling helps avoid the 'stampeding herd' of processes trying to be completed and no process getting finished. Thank you for your very helpful details. Dec 24, 2021 at 14:20
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

1
  • Thanks.. Will look into it
    – CodeWiz
    Nov 15, 2019 at 5:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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