Tuesday, April 8, 2008

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:

 

Hope this post was helpful to you.

2 comments:

water outbreaks said...

This is perhaps one of the strangest variables for me. Everyone gives the same advice that you are, but for the high concurrency workloads I deal with, setting it to infinity is always the best. I guess there is no substitution for raw benchmark numbers.

Parvesh Garg said...

@water outbreaks
I must say wow!

Definitely would like to know some more details like server version, hardware info, if you can share..

otherwise, i dont think anyone has ever ridiculed *infinity* as an option but of course its subject to conditions.