strange semaphore locking issues, just started

MySQL & MariaDB Other MySQL® Questions
1

Running mysql 5.5.21 server on centos 5.7 (we upgraded to from 5.0 to 5.5 about two weeks ago). Fairly high usage implementation, but we weren’t seeing any issues until Friday, when the server appeared to “hang.” Saw this in the innodb status:

----------SEMAPHORES----------OS WAIT ARRAY INFO: reservation count 5716301, signal count 312799955–Thread 1233332544 has waited at btr0sea.c line 882 for 0.00 seconds the semaphore:S-lock on RW-latch at 0x2ab05721de58 created in file btr0sea.c line 178number of readers 6, waiters flag 0, lock_word: ffffaLast time read locked in file btr0sea.c line 882Last time write locked in file /export/home/pb2/build/sb_0-4846558-1328009422.24/rpm/BUILD/mysql-5.5.21/mysql-5.5.21/storage/innobase/btr/btr0sea.c line 633–Thread 1241786688 has waited at buf0buf.c line 2250 for 0.00 seconds the semaphore:Mutex at 0x20d0cae8 created file buf0buf.c line 1159, lock var 0waiters flag 0–Thread 1237031232 has waited at btr0sea.c line 882 for 0.00 seconds the semaphore:S-lock on RW-latch at 0x2ab05721de58 created in file btr0sea.c line 178number of readers 2, waiters flag 0, lock_word: ffffeLast time read locked in file btr0sea.c line 882Last time write locked in file /export/home/pb2/build/sb_0-4846558-1328009422.24/rpm/BUILD/mysql-5.5.21/mysql-5.5.21/storage/innobase/btr/btr0sea.c line 633–Thread 1252354368 has waited at btr0sea.c line 633 for 0.00 seconds the semaphore:X-lock on RW-latch at 0x2ab05721de58 created in file btr0sea.c line 178number of readers 3, waiters flag 0, lock_word: ffffdLast time read locked in file btr0sea.c line 882Last time write locked in file /export/home/pb2/build/sb_0-4846558-1328009422.24/rpm/BUILD/mysql-5.5.21/mysql-5.5.21/storage/innobase/btr/btr0sea.c line 633Mutex spin waits 2455650660, rounds 8103916598, OS waits 1919724RW-shared spins 112419621, rounds 356817756, OS waits 1176007RW-excl spins 15709334, rounds 424093944, OS waits 579485Spin rounds per wait: 3.30 mutex, 3.17 RW-shared, 27.00 RW-excl

After some research, it appeared that the innodb_adaptive_hash_index should be turned off, according to this article:
http://docs.oracle.com/cd/E17952_01/refman-5.5-en/innodb-per formance-adaptive_hash_index.html

I disabled it on Friday night. This morning, early, there were similar symptoms (and looks like database was hanging). The innodb status showed this (I’ve cut out some of the lines because there are a lot:

–Thread 1292212544 has waited at buf0buf.c line 2250 for 0.00 seconds the semaphore:Mutex at 0x44daae8 created file buf0buf.c line 1159, lock var 1waiters flag 0–Thread 1314404672 has waited at buf0buf.c line 2250 for 0.00 seconds the semaphore:Mutex at 0x44daae8 created file buf0buf.c line 1159, lock var 0waiters flag 0–Thread 1248885056 has waited at buf0buf.c line 2250 for 0.00 seconds the semaphore:Mutex at 0x44daae8 created file buf0buf.c line 1159, lock var 1waiters flag 0–Thread 1327614272 has waited at buf0buf.c line 2250 for 0.00 seconds the semaphore:Mutex at 0x44daae8 created file buf0buf.c line 1159, lock var 0waiters flag 0Mutex spin waits 537805198, rounds 3607532492, OS waits 3101709RW-shared spins 4823382, rounds 12144073, OS waits 71610RW-excl spins 722301, rounds 22876801, OS waits 26941Spin rounds per wait: 6.71 mutex, 2.52 RW-shared, 31.67 RW-excl

I immediately turned the innodb_adaptive_hash_index back on and things settled down to normal.

Any idea what could be causing this and how to fix? And how can I monitor?

2

I think you need to capture more information when this happens. I would use pt-stalk. Ensure that oprofile and GDB are installed and enabled – you will need the data from them.

3

I’ve spent several hours with pt-stalk. I’m not understanding how to collect data from it (and it keeps asking for my password every single). Any pointers you could give me with it?