EmergencyEMERGENCY? Get 24/7 Help Now!

InnoDB thread concurrency

 | June 5, 2006 |  Posted In: Insight for DBAs

PREVIOUS POST
NEXT POST

InnoDB has a mechanism to regulate count of threads working inside InnoDB.
innodb_thread_concurrency is variable which set this count, and there are two friendly variables
innodb_thread_sleep_delay and innodb_concurrency_tickets. I’ll try to explain how it works.

MySQL has pluginable architecture which divides work between mysql common code
(parser, optimizer) and storage engine. From storage engine’s point of view it works how
(very simplified):
mysql calls storage engine’s methods:

(there are a couple of methods like read by index or sequential read or random read);

At start of each of these methods InnoDB checks count of already entered threads,
and if this count is over innodb_thread_concurrency then the thread waits
for innodb_thread_sleep_delay microseconds before a next try.
If secound try still is unsuccess – thread sleeps in thread-queue (FIFO).
Why InnoDB uses two tries? It decreases count of waiting threads and makes count of context switching lower.

Once thread entered – it receives innodb_concurrency_tickets tickets,
so next innodb_concurrency_tickets times thread will not be checked, and will enter free.

Simplified code looks like:

(full source code: srv_conc_enter_innodb function in innobase/srv/srv0srv.c)

So what is the best value for innodb_thread_concurrency?

The value depends on a lot of factors, including kind of workload you have, what type of hardware and software you’re running. If you have 1-2 CPUs you frequently can do very well disabling it (innodb_thread_concurrency=0
since 5.0.19, more about default values). For Multiple CPU boxes (4+ CPU) situation is different.

In theory you could use 2*(NumCPUs+NumDisks) values so there could be up to 2 active threads for each CPU and Disk resource. For Disk IO bound workload you could only account number of disks and for CPU bound only number of CPUs. In practice however this values might be suboptimal, especially with large number of active threads.

So, for certain workloads you might end up with values 1,2 or 4 being optimal even if you have 8 CPUs. Note if this value is less than number of CPUs and you’re using mainly Innodb tables you might be unable to use all of your CPUs effectively as there are not enough number of running threads. Moreover as threads sleep for certain time before entering the queue, the true number of threads inside Innodb might be less than innodb_thread_concurrency especially if number of active threads is just few times larger than it, as many of them might end up sleeping waiting to enter the queue.

The scalability problems with multiple CPUs is well known bug 15815 so do not think it is considered to be normal behavior.

About innodb_commit_concurrency.
As you saw innodb_thread_concurrency protects only access to row, but there is
also commit stage which obviuosly uses internal structures and locks and it remained unprotected by this variable
Under certain workload (e.g. a lot of INSERT threads) thread thrashing could be still observed even with limited innodb_thread_concurrency.
This is why innodb_commit_concurrency variable was added in MySQL 5.0

innodb_commit_concurrency limits number of threads which can be active inside Innodb kernel at commit stage. The optimal value for this variable also depends on a lot of factors. It was designed as separate variable beause log flushing is frequently IO bound operation even if rest of workload is CPU bound so same value for variables would not work well in all cases.

For many workloads default value is enough. It requires intervention less frequently than innodb_thread_concurrency setting.

if you have innodb_flush_logs_at_trx_commit=1 and do not have battery backed up cache on your log volume you might want to play with larger values such as 20. If innodb_log_flush_at_trx_commit=0 or 2 smaller values may make sense. If you have binary log enabled it often does not really matter as Innodb will serialize commit operations anyway.

PREVIOUS POST
NEXT POST
Vadim Tkachenko

Vadim Tkachenko co-founded Percona in 2006 and serves as its Chief Technology Officer. Vadim leads Percona Labs, which focuses on technology research and performance evaluations of Percona’s and third-party products. Percona Labs designs no-gimmick tests of hardware, filesystems, storage engines, and databases that surpass the standard performance and functionality scenario benchmarks. Vadim’s expertise in LAMP performance and multi-threaded programming help optimize MySQL and InnoDB internals to take full advantage of modern hardware. Oracle Corporation and its predecessors have incorporated Vadim’s source code patches into the mainstream MySQL and InnoDB products. He also co-authored the book High Performance MySQL: Optimization, Backups, and Replication 3rd Edition.

5 Comments

  • Thanks for the clear posting including the equation on number of threads:

    2*(NumCPUs+NumDisks)

    I have 4 physical CPUs, in 2x duel cores with shared cache.

    But Linux nicely turns these into 8 logical CPUs, where each pair share 80% of CPU resources.

    With 4xPhysical and 8xLogical, what value of CPU count should be used?

    Regards,

    Ben

  • Does a thread which “ENTER” cause (thread->n_tickets_to_enter_innodb > 0) is include in “entered_thread” ?

    Does it mean we can have more thread than innodb_thread_concurrency which inside innodb_kernel ?

    suppose innodb_thread_concurrency=8, 9 threads have free tickets and all of them ENTER.

  • hi Vadim,

    can i ask you quesiton?

    i issue the command ‘show engine innodb status’, is shows there are 10 io threads(4 are read, 4 are write) and a log thread and a insert buffer thread .just like follows:
    ——–
    FILE I/O
    ——–
    I/O thread 0 (thread id: 18578) state: waiting for completed aio requests (insert buffer thread)
    I/O thread 1 (thread id: 18579) state: waiting for completed aio requests (log thread)
    I/O thread 2 (thread id: 18580) state: waiting for completed aio requests (read thread)
    I/O thread 3 (thread id: 18581) state: waiting for completed aio requests (read thread)
    I/O thread 4 (thread id: 18582) state: waiting for completed aio requests (read thread)
    I/O thread 5 (thread id: 18583) state: waiting for completed aio requests (read thread)
    I/O thread 6 (thread id: 18584) state: waiting for completed aio requests (write thread)
    I/O thread 7 (thread id: 18585) state: waiting for completed aio requests (write thread)
    I/O thread 8 (thread id: 18586) state: waiting for completed aio requests (write thread)
    I/O thread 9 (thread id: 18587) state: waiting for completed aio requests (write thread)

    but i found that the value of the innodb_thread_concurrency is 8.
    so, what is wrong with this sitution?
    what the innodb_thread_concurrency real meaning?

Leave a Reply

 
 

Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.