Concurrency refers to the execution of multiple threads or processes simultaneously. In the context of InnoDB, threads are responsible for performing various database operations such as reads, writes, and updates. By controlling the level of concurrency, you can optimize the performance of the InnoDB storage engine, ensuring efficient use of system resources and preventing contention among threads.
The default value of innodb_thread_concurrency is 0, which means there is no limit on the number of threads that can operate concurrently. The system automatically manages thread concurrency, and this default setting often works well for most systems.
However, in cases where there is high concurrency and contention for resources, setting a specific limit on the number of threads that can execute simultaneously may be beneficial.
By setting the innodb_thread_concurrency parameter, you can define the maximum number of threads that are allowed to execute simultaneously. When this limit is reached:
- A new incoming request will sleep (wait) for a short period if the number of currently executing threads has reached the innodb_thread_concurrency limit.
- If the request cannot be rescheduled after sleeping, it will be placed in a first-in/first-out (FIFO) queue and processed when resources become available.
- Threads that are waiting for locks (e.g., due to resource contention) are not counted towards the innodb_thread_concurrency limit.