The setup problem of MySQL parameter thread_concurrency

has been stuck on a very strange database issue for a long time, slow log is full of some very basic SQL statements, the primary key query or update the simple field based on the primary key, should be the milliseconds to return the results of the SQL, incredibly always timed out. InnoDB is clearly row-level lock, originally these one-way operation is InnoDB Advantage Project, should have no pressure, incredibly became the bottleneck.

Adjusted the parameters repeatedly, and consulted the experts still not well resolved, before adding a

Innodb_purge_threads = 32 # 5.6 Before support is greater than 1, 5.5 will automatically become 1
Let every 10 seconds of purge operation Open Independent process has some improvement, but still have a lot of blocking situation and a lot of slow log.

Today, I saw this error message when I installed a new MySQL server.

[Warning] option ' thread_concurrency ': Unsigned value 0 adjusted to 1
I was very surprised because I had always thought thread_concurrency=0 meant not to set thread_concurrency, that is infinity. If thread_concurrency=1, that means that MySQL can always have only one concurrent thread, which obviously causes congestion and severely affects performance.

After changing this parameter to (the number of CPU bus threads), the blocking problem is solved completely. In top you can often see the mysqlcpu occupancy rate more than 200% of the instant, and the original MySQL rarely more than 200%. Slow log also no longer appear in those simple query logs.

A lot of people, including official documents, say that this parameter has no meaning and is valid only for Solaris.

It is also said that this parameter will be discarded after 5.6.1.

There are also some Chinese articles that think this parameter is meaningless

Note that this parameter is for the Solaris system, and if you are using a Linux system, you do not need to set this parameter unless you are using the Solaris system

At least after my practice, this parameter is valid under Linux. The default is 10, I changed to 0 and was forced into 1 by the system, causing frequent blocking problems. I am currently set to 16, equal to the number of CPU threads.

Why do I have the egg pain to think that this parameter to 0 will be effective, probably because of the impact of innodb_thread_concurrency, Innodb_thread_concurrency is can and recommended to change to 0.

In my opinion, this parameter seems to have no effect other than deceptive, the default thread_concurrency=10 is enough to be available, it does not need to be modified. Upgrade to 5.6 earlier to bid farewell to this parameter.