InnoDB depends on operating system threads to process the requests from user transactions, These transactions include requests to InnoDB before commit or rollback. The modern operating systems and servers with multi-core processors, where context switching is efficient, most workloads run well without any limit on the number of concurrent threads. InnoDB can efficiently control the number of concurrently executing operating system threads (and thus the number of requests that are processed at any one time) to minimize context switching between threads. if the number of threads concurrently executing is at a pre-defined limit, the new request sleeps for a short time before it tries again. The requests which cannot be rescheduled after the sleep is put in a first-in/first-out queue and eventually is processed. Threads waiting for locks are not counted in the number of concurrently executing threads.To limit the number of concurrent threads, You can configure MySQL system variable innodb_thread_concurrency (system variables are explained well at MySQL documentation here ). Once the number of executing threads reaches the limit, all those waiting threads sleeps for microseconds configured in innodb_thread_sleep_delay before placed into the queue.
The default value of innodb_thread_concurrency is “0” , This mean, there is no limit on the number of concurrently executing threads. To reduce excessive context switching you can set innodb_thread_concurrency > “0” . Now, How do you size your innodb_thread_concurrency ? There are several factors we consider before recommending the values, like your CPU, memory, Linux distribution / kernel, your application’s database architecture and other variables like innodb_adaptive hash_index (AHI) . So we recommend you to benchmark thoroughly before setting the values for innodb_thread_concurrency.
innodb_thread_sleep_delay is applicable only when innodb_thread_concurrency is greater than “0” , That’s why MySQL works faster in Servers with multi-core high performance CPUs compared to several Servers with moderately performing CPUs.
When you have configured innodb_thread_concurrency > “0” , The SQLs that may comprise of multiple row operations are assigned with a specific number of tickets by InnoDB specified in the global system variable innodb_concurrency_tickets (default is 5000), which allows thread to be scheduled repeatedly with an minimal resource usage. If the tickets run out, the thread is evicted and innodb_thread_concurrency is observed again, Which will eventually move the thread back to FIFO (first-in-first-out) queue waiting threads.
