Posted by Vadim |

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:

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

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.
[read more...]