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 will run well without any limit on the number of concurrent threads. Thanks to several scalability improvements in InnoDB Plugin 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 will sleep 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 will be 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 will sleep for a number of microseconds, set by the system
configuration parameter 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 Plugin.
Starting with InnoDB Plugin 1.0.3, the default value of
innodb_thread_concurrency
is 0
, so that by
default there is no limit on the number of concurrently executing
threads, as shown in Table 7.1, “Changes to innodb_thread_concurrency
”.
Table 7.1. Changes to innodb_thread_concurrency
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 Plugin before 1.0.3 | (corresponding to Plugin) | 8 | 8 | 0 |
InnoDB Plugin 1.0.3 and newer | (corresponding to Plugin) | 0 | No limit | 0 |
Note that InnoDB will cause threads to sleep only when the
number of concurrent threads is limited. When there is no limit
on the number of threads, all will contend equally to be
scheduled. That is, if innodb_thread_concurrency
is
0
, the value of 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 innodb_thread_concurrency
.
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 will
have no tickets, and it must observe innodb_thread_concurrency
.
Once the thread is entitled to enter InnoDB, it will be assigned
a number of tickets that it can use for subsequently entering
InnoDB. If the tickets run out, innodb_thread_concurrency
will be observed again and further tickets will be assigned. The
number of tickets to assign is specified by the global option
innodb_concurrency_tickets
, which is 500 by default. A thread
that is waiting for a lock will be given one ticket once the lock
becomes available.
The correct values of these variables are dependent on your
environment and workload. You will need to try a range of
different values to determine what value works for your
applications. Before limiting the number of concurrently
executing threads, you should review configuration options that
may improve the performance of InnoDB on multi-core and
multi-processor computers, such as innodb_use_sys_malloc
and innodb_adaptive_hash_index
.
This is the User’s Guide for InnoDB Plugin 1.0.6 for MySQL 5.1, generated on March 4, 2010 (rev 673:680M).