Contention in MySQL InnoDB: Useful Info From the Semaphores Section

Contention in MySQL InnoDBIn a high concurrency world, where more and more users->connections->threads are used, contention is a given. But how do we identify the contention point easily?

Different approaches had been discussed previously, like the one using Perf and Flame graphs to track down the function taking way more time than expected. That method is great but how can we do it with what one normally has, like the MySQL Client? Enter: the SEMAPHORES section from the SHOW ENGINE INNODB STATUS command output.


The SEMAPHORES section displays all the metrics related to InnoDB mechanics on waits. This section is your best friend if you have a high concurrency workload. In short, it contains 2 kinds of data: Event counters and a list of current waits.

Current Waits

That is a section that should be empty unless your MySQL has a high concurrency that causes InnoDB to start using the waiting mechanism. If you don’t see lines with the form “”– Thread <num> was waited…” then you are good. No contention.

Now, what does it look like? It could be like:

or like

Or incredibly long (not showing here).

The way that particular section was monitored is through the execution of an infinite loop:

But what should you do with that info?

Looking for Info

From the current waits, what we need is the following:

  • The exact version of MySQL (and flavor: Percona Server, Oracle’s MySQL, MariaDB)
  • Filename
  • File line

Let’s use the first example, which is a server that experienced high concurrency for a while as seen in Percona Monitoring and Management:

One can say: “but there’s only a peak of 42 threads and the majority of the throughput distribution is on the low concurrency side!” This is a 2 core VM with small physical memory and thus a pretty small buffer pool. An average of 22 for threads running is high concurrency.

Now, from the SEMAPHORES output of the first example we have:

What’s the MySQL version? The last line tells us: Percona Server 5.7.28-31

What’s the file and line? line 5889 has waited for an S-Lock on an RW-latch created in line 1433 but another thread has reserver in mode exclusive in file line 1121.

Ok, we have directions. Now let’s see what’s inside.

Looking Inside the Code

Do I need to download the source code for inspection? No, you don’t! What you need is to navigate to the code repository, which in this case is a GitHub repo.

And here is where the exact version comes handy. In order to guarantee that we are reading the exact line code, we better make sure we are reading the exact version.

Finding the Repository

GitHub is pretty easy to navigate and in this case, what we are looking for is Release. 5.7.28-31 to be precise. The Percona Server repo URL is:

Once you are there, we need to find the release.

Finding the Release

The release can be found in the link showed in the below graph:

Inside one can see the releases:

Click the link and it will take you to the tag page:

And finally, click the link shown above and you will be at the repo of the release needed:

What’s next? Reading the actual content of the files.

Navigating the Code Tree

The relevant part of the code tree is:


The InnoDB storage engine code is inside the “innobase” directory. Inside that directory, there’s a bunch of other directories. But how do you choose the correct one? Well, the filename has the answer. The files we need to look are:


All files have the same syntax: xxx0xxx.xx and the directory name is the part before the zero. In our case, we need to look inside two directories: btr and buf. Once inside the directories, finding the files is an easy task.

These are our files in the mentioned line numbers:

The file as described in the head of the file is “The index tree adaptive search” a.k.a: the Adaptive Hash Index (AHI)
The as described in the file is “The database buffer buf_pool” a.k.a: the InnoDB buffer Pool
The as described in the file is “The index tree cursor” a.k.a: the actual B-Tree or where the data exists in InnoDB.

What was Going on Then?

At line 5889 InnoDB is inside a function called btr_estimate_n_rows_in_range_low,  and the description is documented as “Estimates the number of rows in a given index range” and the actual line is:

But what is that btr_cur_search_to_nth_level? In the same file, we can find the definition and it is described as “Searches an index tree and positions a tree cursor on a given level”. So basically, it is looking for a row value. But that operation is stalled because it needs to acquire a shared resource that in this case is the buffer pool:

At line 1433, the buffer pool is trying to create a lock over a block

That operation happens inside a function called “buf_block_init” (find it by scrolling up in the code) and is described as “Initializes a buffer control block when the buf_pool is created”. This is actually creating the buffer pool space after an innodb_buffer_pool_size modification and is delayed.

At line 1121 the operation used was:

That line is inside the function btr_search_guess_on_hash is described as “Tries to guess the right search position based on the hash search info of the index.” So, it is using info from the AHI. And the buf_page_get_known_nowait definition is “This is used to get access to a known database page, when no waiting can be done” which is pretty much self-explanatory.

So what do we have here? Contention on the AHI! Is this a problem? Let’s go back to the original line: