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?
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.
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.
- 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.
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!