2

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 ?

  • 200 threads running SELECTs at the same time -- sounds like you need help optimizing the SELECTs. Or, perhaps it is just one select? Let's see it/them. Speeding them up would avoid the problem you are asking about. – Rick James Dec 16 '15 at 23:27
  • There's nothing inherently wrong with them (most run in <1 second normally), and there's rarely more than 6 at any given time. It's just when they build up for whatever reason we get problems. The bigger issue is table structure (a legacy issue we are dealing with) e.g. a table with 200+ blob fields and a few million rows, that gets hit in about 50% of all queries!!! – IGGt Dec 17 '15 at 8:15
  • Yes a "build up" causes everything to slow down. Think of being in a grocery store when there are so many shoppers that you can't move down the aisles. It would be better to not let so many people into the store. Similarly, decreasing max_connections to well below 200 may help prevent "build ups". – Rick James Dec 17 '15 at 19:26
5

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

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

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

  • you were spot on. I discovered that we had innodb_thread_concurrency set at 12. My guess is that it was a legacy setting from the days we used to run MySQL 5.0, and probably just copied the cnf files over when we upgraded ages ago. – IGGt Dec 15 '15 at 22:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.