InnoDB has a mechanism to regulate count of threads working inside InnoDB.
innodb_thread_concurrency is variable which set this count, and there are two friendly variables
innodb_thread_sleep_delay and innodb_concurrency_tickets. I’ll try to explain how it works.

MySQL has pluginable architecture which divides work between mysql common code
(parser, optimizer) and storage engine. From storage engine’s point of view it works how
(very simplified):
mysql calls storage engine’s methods:

(there are a couple of methods like read by index or sequential read or random read);

At start of each of these methods InnoDB checks count of already entered threads,
and if this count is over innodb_thread_concurrency then the thread waits
for innodb_thread_sleep_delay microseconds before a next try.
If secound try still is unsuccess – thread sleeps in thread-queue (FIFO).
Why InnoDB uses two tries? It decreases count of waiting threads and makes count of context switching lower.

Once thread entered – it receives innodb_concurrency_tickets tickets,
so next innodb_concurrency_tickets times thread will not be checked, and will enter free.

Simplified code looks like:

(full source code: srv_conc_enter_innodb function in innobase/srv/srv0srv.c)

So what is the best value for innodb_thread_concurrency?

The value depends on a lot of factors, including kind of workload you have, what type of hardware and software you’re running. If you have 1-2 CPUs you frequently can do very well disabling it (innodb_thread_concurrency=0
since 5.0.19, more about default values). For Multiple CPU boxes (4+ CPU) situation is different.

In theory you could use 2*(NumCPUs+NumDisks) values so there could be up to 2 active threads for each CPU and Disk resource. For Disk IO bound workload you could only account number of disks and for CPU bound only number of CPUs. In practice however this values might be suboptimal, especially with large number of active threads.

So, for certain workloads you might end up with values 1,2 or 4 being optimal even if you have 8 CPUs. Note if this value is less than number of CPUs and you’re using mainly Innodb tables you might be unable to use all of your CPUs effectively as there are not enough number of running threads. Moreover as threads sleep for certain time before entering the queue, the true number of threads inside Innodb might be less than innodb_thread_concurrency especially if number of active threads is just few times larger than it, as many of them might end up sleeping waiting to enter the queue.

The scalability problems with multiple CPUs is well known bug 15815 so do not think it is considered to be normal behavior.

About innodb_commit_concurrency.
As you saw innodb_thread_concurrency protects only access to row, but there is
also commit stage which obviuosly uses internal structures and locks and it remained unprotected by this variable
Under certain workload (e.g. a lot of INSERT threads) thread thrashing could be still observed even with limited innodb_thread_concurrency.
This is why innodb_commit_concurrency variable was added in MySQL 5.0

innodb_commit_concurrency limits number of threads which can be active inside Innodb kernel at commit stage. The optimal value for this variable also depends on a lot of factors. It was designed as separate variable beause log flushing is frequently IO bound operation even if rest of workload is CPU bound so same value for variables would not work well in all cases.

For many workloads default value is enough. It requires intervention less frequently than innodb_thread_concurrency setting.

if you have innodb_flush_logs_at_trx_commit=1 and do not have battery backed up cache on your log volume you might want to play with larger values such as 20. If innodb_log_flush_at_trx_commit=0 or 2 smaller values may make sense. If you have binary log enabled it often does not really matter as Innodb will serialize commit operations anyway.

5 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Ben Clewett

Thanks for the clear posting including the equation on number of threads:

2*(NumCPUs+NumDisks)

I have 4 physical CPUs, in 2x duel cores with shared cache.

But Linux nicely turns these into 8 logical CPUs, where each pair share 80% of CPU resources.

With 4xPhysical and 8xLogical, what value of CPU count should be used?

Regards,

Ben

0
vi_shen

Does a thread which “ENTER” cause (thread->n_tickets_to_enter_innodb > 0) is include in “entered_thread” ?

Does it mean we can have more thread than innodb_thread_concurrency which inside innodb_kernel ?

suppose innodb_thread_concurrency=8, 9 threads have free tickets and all of them ENTER.

0
repls

hi Vadim,

can i ask you quesiton?

i issue the command ‘show engine innodb status’, is shows there are 10 io threads(4 are read, 4 are write) and a log thread and a insert buffer thread .just like follows:
——–
FILE I/O
——–
I/O thread 0 (thread id: 18578) state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 (thread id: 18579) state: waiting for completed aio requests (log thread)
I/O thread 2 (thread id: 18580) state: waiting for completed aio requests (read thread)
I/O thread 3 (thread id: 18581) state: waiting for completed aio requests (read thread)
I/O thread 4 (thread id: 18582) state: waiting for completed aio requests (read thread)
I/O thread 5 (thread id: 18583) state: waiting for completed aio requests (read thread)
I/O thread 6 (thread id: 18584) state: waiting for completed aio requests (write thread)
I/O thread 7 (thread id: 18585) state: waiting for completed aio requests (write thread)
I/O thread 8 (thread id: 18586) state: waiting for completed aio requests (write thread)
I/O thread 9 (thread id: 18587) state: waiting for completed aio requests (write thread)

but i found that the value of the innodb_thread_concurrency is 8.
so, what is wrong with this sitution?
what the innodb_thread_concurrency real meaning?

0
Larry

Vadim you never answer a question. What a selfish person you are. Lame.

0
Gurnish Anand

Is there a way to count the number of threads currently inside innodb?

0