理解InnoDB spin waits赢 Percona Live门票

原文链接：http://www.mysqlperformanceblog.com/2011/09/02/understand-innodb-spin-waits-win-a-percona-live-ticket/

It’s Friday again (so soon!) and time for our TGIF contest, to give away a free ticket to Percona Live London. Before we do that, though, just what in the world does this output from SHOW INNODB STATUS mean?

又到周5（真TMD的快）我们TGIF竞赛的时间了，赠送一张免费的Percona Live London门票。在我们做这个之前，到底SHOW INNODB STATUS输出的这些信息是什么意思？

Mutex spin waits 5870888, rounds 19812448, OS waits 375285

To understand this text, you have to understand how InnoDB handles mutexes.  It tries a two-step approach to getting a lock on a mutex.  First, a thread tries to lock the mutex.  If the mutex is locked by someone else, then the thread does a so-called spin wait.  This means that it repeatedly checks “are you free? are you free? are you free?” in a loop.  If this doesn’t work after a while, it gives up and goes to sleep until the mutex is free.  I’m simplifying this a lot, perhaps too much, but it’s a topic that would take a long blog post to explain correctly in detail.  The related source files are in the sync/ InnoDB source code directory if you want to learn more.

要明白这个输出信息，你需要理解INNODB是怎么管理互斥量的。INNODB用二段法去获取一个互斥量锁。首先，一个线程尝试去锁住这个互斥量，如果这个互斥量已经被别人锁住了，那么这个线程做所谓的自旋等待。这个意思是说，它在循环里反复的检查“你是空闲的么？你是空闲的么？你是空闲的么？”。如果等待一会后还是没获取到锁，它就放弃了，然后进入休眠，直到互斥量空闲下来。我把这个简化了很多，可能简化得太过分了，但是如果要把细节正确的解释，需要一篇很长的BOLG才能说清楚。如果你想知道得更多，相关的源文件在源代码的sync/ InnoDB文件夹里。

The reason for the two-step approach is that going to sleep and waking up again is slow.  That’s why it spends a little time doing a spin-wait first.  It’s actually cheaper to keep checking for a little bit than to go to sleep right away.

用二段法的原因是由于进入休眠和重新唤醒比较慢。这是为什么先花一点时间做一个自旋等待。事实上多保持一小会的检查比马上休眠要节约成本得多。

So, back to the output from SHOW INNODB STATUS.  Here’s what it means.

那么，回到 SHOW INNODB STATUS的输出，他们的意思是这样的。

• Mutex spin waits 5870888 is the number of times a thread tried to get a mutex and it wasn’t available, so it waited in a spin-wait.
• rounds 19812448 is the number of times threads looped in the spin-wait cycle, checking the mutex.
• OS waits 375285 is the number of times the thread gave up spin-waiting and went to sleep instead.
• Mutex spin waits 5870888 线程尝试获取互斥量但是没有获取到的次数，所以它进入自旋等待了。
• rounds 19812448 线程在自旋等待中循环检查互斥量状态的次数。
• OS waits 375285 线程放弃自旋等待而进入休眠的次数。

So what we have here is really a kind of “mutex miss rate counter.”  We don’t have a counter for the number of times the mutex was free and could be grabbed right away, but we have the number of times it wasn’t free, and we have the number of times it wasn’t free even after spinning a little while.

所以我们这里说的实际上是“获取互斥量失败率计数器”。我们没有立刻捕获互斥量空闲次数的计数器，但是有它不空闲次数的计数器，还有即使自旋一会后还是没法空闲的次数的计数器。

 

下面这段不翻了。

And now that you know a little bit more about InnoDB locking strategies and how it’s exposed in the status output, wouldn’t you like to come hear Peter Zaitsev talk about InnoDB architecture and performance optimization in London on October 24th?  Well, you can — for free!   Watch our @Percona Twitter stream and retweet the contest to win a free ticket!  We’ll pick a random retweet and give away a free ticket each week.  If you don’t win this time, try next Friday or register and get the early-bird discount (but don’t wait too long: it expires September 18th).  Our tutorial schedule is 100% complete at this time, and the session schedule has two full tracks already with the full schedule to be announced very shortly now that the CfP has ended. Don’t miss your opportunity to learn about MySQL in London from world-famous experts!

转载于:https://www.cnblogs.com/zuoxingyu/archive/2012/10/26/Mutex.html