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?
1 | 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!
Hi,
I still don’t get very well what the Rounds are, you say:
Mutex spin waits: 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: is the number of times threads looped in the spin-wait cycle, checking the mutex.
Then you say:
“…but we have the number of times it wasn’t free” = Spin Wait
“and we have the number of times it wasn’t free even after spinning a little while” = Round
According to this second definition of Round I get confused.
Isn’t Round simply the total number of wait loops done and Spin Wait the times the mutex was checked and it was locked?
Moreover, every how many Loops the mutex is checked?
If my interpretation of Round is correct and using your numbers:
In 19812448 Loops , 5870888 times the mutex was found locked.
So the mutex was checked at least every 3 rounds, but I get also lower numbers, isn’t it too expensive and useless to check it do often?
Probably I missed something!
Thanks
Claudio
I came here searching for additional info after reading the section on “show innodb status” in the High Performance MySQL book. This was very clear. Thank you!
Replying to a 4 year old comment, and all, but:
5.87 million times, a thread wanted the mutex, found it was locked, and spun to wait for it. This is the only time this counter should be incremented – it shouldn’t be incremented on each spin, only on first check.
(I say “should be” only because I can’t report first-hand that I know it’s only incremented on first-check.)
19.81 million spins were done – meaning that on the average, a small number of spins was enough to acquire the mutex. This is the sort of behavior we like to see – the point of spinning is that you’re so likely to get the mutex if you want just a tiny bit of time, you shouldn’t go to sleep, to avoid a context switch.
375 thousand times, spins were done for a while, and the thread went to sleep rather than continuing to spin (i.e.: “I tried waiting a tiny bit of time, and it didn’t work. Time to stop wasting CPU spinning, and instead sleep and let someone else use the CPU for a bit.”)