Query about MySQL Semaphores in Engine Status

Question

During a recent problem / slow down with one of our slave databases I noticed that when I ran SHOW ENGINE INNODB STATUS. I am getting the following:

----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 822986039, signal count 17045541908
--Thread 140562108442368 has waited at row0sel.c line 2930 for 0.00 seconds the semaphore:
S-lock on RW-latch at 0x7fd752e55c40 created in file buf0buf.c line 938
a writer (thread id 140562088974080) has reserved it in mode  exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file row0sel.c line 2930
Last time write locked in file /tmp/buildd/mysql-5.5-5.5.44/storage/innobase/buf/buf0buf.c line 3168

In fact, there are about 15 instances of this.

Looking at the transactions there are apx 200 transactions listed, of which there are apx half showing as waiting in InnoDB queue, and the other half are fetching data.

All the transactions are SELECT queries, and none of them are showing any locks.

Can anyone tell me what the SEMAPHORES (above) relate to ?

Answer 1

Semaphores have to do with waiting for internal thread locks in the OS

You can find a nice discussion in Peter Zaitsev's SHOW INNODB STATUS walk through

The way I see it, this is related to two things

• innodb_concurrency_tickets
• innodb_thread_concurrency

My guess is that you set innodb_thread_concurrency to a nonzero number. This causes the InnoDB storage engine to throttle its use of internal threads. Consequently, this will cause transactions to go sit in the queue.

Way back in 2011, I wrote posts about this

• Sep 12, 2011 : Possible to make MySQL use more than one core?
• Aug 16, 2011 : Why do MySQL threads often show "freeing items" status when the query cache is disabled?
• May 26, 2011 : About single threaded versus multithreaded databases performance

I went to Percona Live NYC in 2011. Ronald Bradford told me during a workshop to leave innodb_thread_concurrency at zero(0). Why ?

As I mentioned in the Aug 16, 2011 post:

He plainly told me that I should never set values against innodb_thread_concurrency. Let it always be the default value, which is now zero(0). By doing so, you let the InnoDB storage decide how many innodb_concurrency_tickets to generate on its own. This is what infinite concurrency does.

If you have innodb_thread_concurrency as a nonzero value, please set it to 0 or remove it from my.cnf. Please note that MySQL 5.6 allows for 5000 tickets while MySQL 5.5 only allows 500.

If you want to change these values, you can do so dynamically with

SET GLOBAL innodb_thread_concurrency = 0;

I would recommend not setting innodb_concurrency_tickets at all.

I would also recommend reading Pythian's ONCE AGAIN ABOUT INNODB-CONCURRENCY-TICKETS