Understand InnoDB spin waits, win a Percona Live ticket

PREVIOUS POST
NEXT POST

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?

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!

PREVIOUS POST
NEXT POST

Comments

  1. says

    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

  2. Valerie Parham-Thompson says

    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!

Leave a Reply

Your email address will not be published. Required fields are marked *