Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to configure innodb_thread_concurrency parameters. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Innodb_thread_concurrency:

The maximum number of thread concurrency allowed in the INNODB storage engine.

InnoDB uses operating system threads to handle user transaction requests, and it works like this: when InnoDB receives a user's request, if it has exceeded the number of concurrent threads preset by innodb_thread_concurrency, it will sleep for N seconds according to the preset value of innodb_thread_sleep_delay, and then try to connect again. The mechanism of retrying twice is to reduce the number of CPU context switches to reduce CPU consumption. If the request is accepted, you will get a default innodb_concurrency_tickets pass of500 times, and the thread does not have to check the innodb_thread_concurrency mentioned above when it requests again until these times are used up. If it has not been accepted, it will be queued until it is finally disposed of.

It is recommended that you set this value as follows:

When the number of concurrent user threads is less than 64, it is recommended to set innodb_thread_concurrency=0

If the load is unstable, sometimes low, sometimes high to peak, it is recommended to set innodb_thread_concurrency=128 first and keep lowering this parameter, 96,80,64, etc., until you find the number of threads that provide the best performance, for example, suppose the system usually has 40 to 50 users, but the periodic number increases to 60, 70, or even 200. You will find that performance is stable when set by 80 concurrent users, but degrades if it is higher than this number. In this case, it is recommended to set the innodb_thread_concurrency parameter to 80 to avoid affecting performance

If other applications are allowed on the DB server and you need to limit the thread usage of mysql, you can set the number of threads that can be assigned to DB, but it is not recommended to run other applications on DB, nor is it recommended, as this may result in the database not being optimally used for hardware

If the value is set too high, the performance may be degraded due to internal competition for system resources.

In most cases, the best value is less than and close to the number of virtual CPU

Monitor and analyze DB regularly, because with the change of database load and the increase of business, innodb_thread_concurrency also needs to be adjusted dynamically.

If the number of concurrent user threads in a workload is less than 64, it is recommended to set innodb_thread_concurrency=0

This is the end of the article on "how to configure innodb_thread_concurrency parameters". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.