InnoDB uses operating system threads to process requests from user transactions. (Transactions may issue many requests to InnoDB before they commit or roll back.) On today’s 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. Thanks to several scalability improvements in InnoDB storage engine 1.0.3, and further changes in release 1.0.4, there should be less need to artificially limit the number of concurrently executing threads inside InnoDB.
However, for some situations, it may be helpful to minimize context switching between threads. InnoDB can use a number of techniques to limit the number of concurrently executing operating system threads (and thus the number of requests that are processed at any one time). When InnoDB receives a new request from a user session, 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. A request that 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.
The limit on the number of concurrent threads is given by the
settable global variable
innodb_thread_concurrency. Once the
number of executing threads reaches this limit, additional threads
sleep for a number of microseconds, set by the system
innodb_thread_sleep_delay, before being
placed into the queue.
The default value for
innodb_thread_concurrency and the implied
default limit on the number of concurrent threads has been changed
in various releases of MySQL and the InnoDB storage engine. Starting
with InnoDB storage engine 1.0.3, the default value of
0, so that by
default there is no limit on the number of concurrently executing
threads, as shown in
Table 7.1, “Changes to
Table 7.1. Changes to
|InnoDB Version||MySQL Version||Default value||Default limit of concurrent threads||Value to allow unlimited threads|
|Built-in||Earlier than 5.1.11||20||No limit||20 or higher|
|Built-in||5.1.11 and newer||8||8||0|
|InnoDB storage engine before 1.0.3||(corresponding to Plugin)||8||8||0|
|InnoDB storage engine 1.0.3 and newer||(corresponding to Plugin)||0||No limit||0|
Note that InnoDB causes threads to sleep only when the number of
concurrent threads is limited. When there is no limit on the
number of threads, all contend equally to be scheduled. That is,
0, the value
innodb_thread_sleep_delay is ignored.
When there is a limit on the number of threads, InnoDB reduces
context switching overhead by permitting multiple requests made
during the execution of a single SQL statement to enter InnoDB
without observing the limit set by
Since a SQL statement (such as a join) may comprise multiple row
operations within InnoDB, InnoDB assigns
“tickets” that allow a thread to be scheduled
repeatedly with minimal overhead.
When starting to execute a new SQL statement, a thread has no
tickets, and it must observe
innodb_thread_concurrency. Once the
thread is entitled to enter InnoDB, it is assigned a number of
tickets that it can use for subsequently entering InnoDB. If the
tickets run out,
innodb_thread_concurrency is observed again and
further tickets are assigned. The number of tickets to assign is
specified by the global option
is 500 by default. A thread that is waiting for a lock is given
one ticket once the lock becomes available.
The correct values of these variables depend on your environment
and workload. Try a range of different values to determine what
value works for your applications. Before limiting the number of
concurrently executing threads, review configuration options that
may improve the performance of InnoDB on multi-core and
multi-processor computers, such as
This is the User’s Guide for InnoDB storage engine 1.1 for MySQL 5.5, generated on 2010-04-13 (revision: 19994) .