InnoDB thread concurrency...

cenalulu

      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 already
      wrote  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! :-)
        

               
               
               

本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u3/90603/showart_2030182.html