InnoDB thread concurrency...

Even this topic was covered several times already, the question is still open - use or not to use InnoDB thread concurrency?.. And the only true answer here - "It depends" :-) and I'll try to make a short summary about the current state here.

The main current InnoDB bottlenecks are all mostly due to a high contention on internal locks. As all multi-threaded (as well multi-process) applications giving a concurrent access to its resources, InnoDB have to guarantee there is only one thread may modify a given resource on the same time. This guarantee is achieved by locking a resource in exclusive mode and implemented with mutexes. Depending on implementation, some locks may become very hot with a growing access concurrency. And it's exactly what's happening with InnoDB...

Things where improved a lot with Google patches and continuing with MySQL 5.4 version. But even if several lock bottlenecks were successfully removed, the lock contention is still here. Why it's a problem?.. - Usually when you have any kind of lock contention it's never linear! And with a growing sessions/users concurrency you may observe a dramatic performance drop (like your 32 users will work slower rather only 2, etc.)..

When you deal with inevitable locking which in any case cannot be avoided, but you still expect to keep the highest possible performance level AND growing workload - the only possible solution you have is to reduce a concurrency on the hottest locks. That's why I'm a part of people who thinking that InnoDB thread concurrency option is a very good feature! :-) I've   about why, and here will just stop on two current InnoDB thread concurrency models:

Default Model

The default model was greatly described  by Percona Team. In short InnoDB places all threads (sessions) into its execution wait queue and keeps no more than a given number of active threads on the same time (set with innodb_thread_concurrency=N option).

Positive/Negative:

• (+) reducing a lock contention!
• (+) each thread (session) is guaranteed to have a CPU time within a given time period!
• (-) uses a lot of locking itself, so not scaling very well...

Timer-Based Model

Introduced within MySQL 5.4, this model is looking very promising and performs much more better! And you may read from Mikael's blog how exactly it works .

Positive/Negative:

• (+) lock contention reduced even better!
• (+) short sleeping used instead of locking, so scales better!
• (-) no guarantee that each thread (session) will have a CPU time within a given time period - may be a problem since 512 concurrent user sessions (until 512 it's still ok)...

As usual, one image says more than many words:

Since 32 active user sessions setting innodb_thread_concurrency=16 helps a lot!

Main problem of both models

But both models are still suffering of the same problem: when any of "active" threads (sessions) is doing an I/O operation (disk read) it continues to keep its "active" state (and I/O operation may take tens ms(!)) - while another session may during this time already get its data cached from a buffer pool and finish its query!... So sometime you may observe a significant global performance drop during a such moment!.. - Ideally each session before doing an I/O operation should remove itself from an "active" state and leave others working. So, there is still a room for improvement :-)

The optimal solution

• There is no optimal solution :-)
• Always monitor your workload and check your buffer pool state, buffer hits, page reads, I/O activity and number of active/concurrent sessions
• If you have more than 32 concurrent sessions and low I/O read activity - setting innodb_thread_concurrency=16 will help
• Otherwise leave innodb_thread_concurrency=0
• And always continue monitoring! :-)