EmergencyEMERGENCY? Get 24/7 Help Now!

Understand InnoDB spin waits, win a Percona Live ticket

 | September 2, 2011 |  Posted In: Events and Announcements, Insight for DBAs, MySQL

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
Baron Schwartz

Baron is the lead author of High Performance MySQL. He is a former Percona employee.

2 Comments

  • 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!

Leave a Reply

 
 

Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.