Thread Concurrency Explained: Not a Simple FIFO

Understanding thread concurrency can help troubleshoot performance issues by optimizing how InnoDB multitasks between transaction requests simultaneously, but it is not a simple FIFO (first in: first out) process. That would not be efficient or leave much for a DBA to manipulate. Thus thread concurrency deserves an in-depth look.

What is Thread Concurrency?

Concurrency is the ability to handle multiple requests at the same time without interfering with each other, ideally without sacrificing performance. InnoDB manages requests through threads; each thread is a single unit of processing. The DBA’s role is to configure system variables to get as many threads executed without them piling up on each other.

Configuring InnoDB Variables

The concept of thread concurrency can become complicated when working with system variables. The figure below was created to help keep it simple.

**Figure 1**. Illustrates the thread concurrency process. The InnoDB process is represented by the grey rectangle. The blue rectangles are the threads that have entered InnoDB. The orange rectangles are the threads that are in queue to enter the InnoDB process.

Innodb_thread_concurrency is the maximum number of threads permitted within InnoDB at the same time. If the maximum is reached, threads wait in a queue until a slot is available. In Figure 1, the maximum number of threads allowed to be in InnoDB at one time is 5. The rest of the threads have to wait for a slot to become available.

The default for innodb_thread_concurrency is 0, which means no limit. With the default setting, InnoDB will take and process threads as they come (no waiting in a queue). This might sound more than ideal, a set it and forget it strategy. However, if the CPU or IO subsystem reaches a point of saturation, performance will start to suffer.

Giving innodb_thread_concurrency a value greater than 0 turns on InnoDB’s concurrency control mechanism. It is InnoDB’s internal mechanism that manages and controls threads being executed simultaneously. The maximum value permitted is 1000, but setting innodb_thread_concurrency to 1000 and forgetting all about it is not the answer either. This can overwork the concurrency control mechanisms and decrease performance. It is best to test a range of values on a live test system. Below is some guidance on how to do so.

Guidelines to finding the optimal innodb_thread_concurrency value:

If the concurrent user threads for a workload is consistently small and performance is satisfactory, set the value to 0.
If the typical number of users is between 40 to 50 with a consistent heavy workload and occasionally spikes up to 70 users, set value to 80 and test the performance. If performance is slow, incrementally increase the value by 10 until performance gets better.
If using virtual CPUs, start by setting the value to the number of CPUs. Check the performance and decrease by 10 to test if needed.

To show how many threads are running at a given time run the following script.

Innodb_thread_sleep_delay is the time, in microseconds, a thread will wait before it enters the InnoDB for processing and is only recognized when the innodb_thread_concurrency value is greater than 0. Moreover, the thread will wait for this amount of time even if there is a slot available. It acts as a way to control the workload. If there is no slot available, the threads in the queue follow a FIFO waiting system demonstrated in Figure 1. The first thread in line is the first thread to enter InnoDB when the next slot is available and after it waits 1000ms.

The default is 10000ms with a maximum of 100000ms. InnoDB can increase the sleep delay beyond the value set for innodb_thread_sleep_delay to automatically adjust to overloaded thread scheduling activity, but only when innodb_adaptive_max_sleep_delay is enabled.

Innodb_adaptive_max_sleep_delay gives InnoDB the ability to adjust the sleep delay up or down depending on workload and thread activity. It may improve performance in systems running 16 threads asynchronously however its benefits are seen more in systems running hundreds or thousands of threads at the same time. The default is 15000ms and maximum value is 100000ms.

Innodb_concurrency_tickets is the maximum number of tickets given when a thread enters InnoDB for processing. One ticket will allow a query to perform one row operation. If the query runs out of tickets, it is expelled out of InnoDB and goes back into the waiting queue. In Figure 1, innodb_thread_tickets is set to default (5000 tickets). Three threads will be expelled from InnoDB, the ones with 5001, 10000, and 20000 tickets. However, they will each process 5000 tickets before they leave a spot available for the next thread in the queue. Since one of the threads only has 100 tickets, that thread along with the next 2 threads in the queue will be completed before them.

Now, will the thread with 100 tickets and the next 2 in the queue be completed before the thread with 500 tickets? Maybe! If you add all the tickets together, it is 300 tickets compared to 500 tickets but 2 threads have to wait for the sleep delay. It will be a pretty close call.

Setting the value for innodb_thread_ tickets is a balancing act. The default value is 5000 with a maximum value of 4294967295. If the value is smaller than needed for a long query, the long query will loop through InnoDB until it is completed, extending the query run time. Like the innodb_concurrency_threads, it is not a good idea to set it to the maximum and forget it. The disadvantage is that smaller queries may be waiting in the queue for a long query to complete. A good start is to check how many tickets are currently being used to complete a query using the following script: