Variable's Day Out #5: innodb_thread_concurrency

Properties:

Applicable To	InnoDB
Server Startup Option	--innodb_thread_concurrency=<value>
Scope	Global
Dynamic	Yes
Possible Values	Integer: Range: 0 - 1000 Interpretation: MySQL Version Value 4.x 0 - sets it to 1 >500 - Infinite Concurrency < 5.0.19 0 - sets it to 1 >= 20 - Infinite Concurrency >= 5.0.19 0 - Infinite Concurrency
Default Value	MySQL Version Default Value < 5.0.8 8 (Finite) 5.0.8 - 5.0.18 20 (Infinite) 5.0.19 - 5.0.20 0 (Infinite) >= 5.0.21 8 (Finite)
Category	Performance

Description:

innodb_thread_concurrency is the variable that limits the number of operating system threads that can run concurrently inside the InnoDB engine. Rest of the threads have to wait in a FIFO queue for execution. Also, threads waiting for locks are not counted in the number of concurrently executing threads.

In the beginning of a thread execution, InnoDB checks the count of already running threads. If it is greater than or equal to the limit set by this variable, then the thread waits for some time and gives a new try. In case the second try also fails, the thread enters a FIFO queue. The sleep time was 50 ms and later on changed to 10 ms, and is managed by innodb_thread_sleep_delay from 5.0.3 onwards. On entering the InnoDB, the thread is given number of "free tickets", number as defined by innodb_concurrency_tickets.

Optimal Setting:

Depends on your environment. The value largely depends on the version of MySQL, number of CPUs, number of Disks, and the kind of load that you are running. As a general principle, you may like to set it to twice the number of CPUs and Disks. And may consider only Disks for Disk IO intensive load and only CPUs for CPU intensive load. Also, if you have greater than or equal to 4 CPUs, you may consider lowering this variable, courtesy [Bug 15815], for un-patched MySQL versions (read the bug for the discussion). For machines with 1 or 2 CPUs, go for the kill, set it to infinite.

Hyperthreading is worse than anything else, it is in many ways different than multi-CPU problems. Because hyperthreading tries to make a single processor work like two while making the instances share a lot of pipelines and cache. You might consider turning hyperthreading off when running MySQL.

In my experience, I have seen a big enough innodb_buffer_pool_size to complement this variable on a multi-CPU machine, of course with the bug resolved. I will be publishing some numbers on it soon.

Read more:

• MySQL manual entry on innodb_thread_concurrency
• InnoDB thread concurrency at mysqlperformanceblog.com
• MySQL InnoDB performance tuning on Solaris 10 OS
• [Bug 15815]

 

Hope this post was helpful to you.