Mysql Server stuck with a long semaphore wait

My Percona server crashed I am not sure what the issue is, but
the server becomes unresponsive and I cannot connect to it.

Server version: 5.5.16-55-log Percona Server (GPL)

Here is the error log out put:

InnoDB: Warning: a long semaphore wait:
–Thread 1404442944 has waited at dict0dict.c line 293 for 241.00 seconds the semaphore:
X-lock on RW-latch at 0x1095518 ‘&dict_table_stats_latches[i]’
a writer (thread id 1339214144) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file dict0dict.c line 290
Last time write locked in file /home/buildbot/slaves/percona-server-51-12/CentOS-5-x86-RPM_ CentOS_5_x86_64/work/BUILD/Percona-Server-5.5.16-rel22.0/Per cona-Server-5.5.16-rel22.0/storage/innobase/dict/dict0dict.c line 293
InnoDB: Warning: a long semaphore wait:
–Thread 1339214144 has waited at btr0sea.c line 1203 for 241.00 seconds the semaphore:
X-lock (wait_ex) on RW-latch at 0x2ab0e694cfe8 ‘btr_search_latch_part[i]’
a writer (thread id 1339214144) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr0sea.c line 1119
Last time write locked in file /home/buildbot/slaves/percona-server-51-12/CentOS-5-x86-RPM_ CentOS_5_x86_64/work/BUILD/Percona-Server-5.5.16-rel22.0/Per cona-Server-5.5.16-rel22.0/storage/innobase/btr/btr0sea.c line 1632
InnoDB: Warning: a long semaphore wait:
–Thread 1419884864 has waited at btr0sea.c line 1119 for 241.00 seconds the semaphore:
S-lock on RW-latch at 0x2ab0e694cfe8 ‘btr_search_latch_part[i]’
a writer (thread id 1339214144) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr0sea.c line 1119
Last time write locked in file /home/buildbot/slaves/percona-server-51-12/CentOS-5-x86-RPM_ CentOS_5_x86_64/work/BUILD/Percona-Server-5.5.16-rel22.0/Per cona-Server-5.5.16-rel22.0/storage/innobase/btr/btr0sea.c line 1632
InnoDB: Warning: a long semaphore wait:
–Thread 1400183104 has waited at btr0sea.c line 1119 for 241.00 seconds the semaphore:
S-lock on RW-latch at 0x2ab0e694cfe8 ‘btr_search_latch_part[i]’
a writer (thread id 1339214144) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr0sea.c line 1119
Last time write locked in file /home/buildbot/slaves/percona-server-51-12/CentOS-5-x86-RPM_ CentOS_5_x86_64/work/BUILD/Percona-Server-5.5.16-rel22.0/Per cona-Server-5.5.16-rel22.0/storage/innobase/btr/btr0sea.c line 1632
InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info:
InnoDB: Pending preads 0, pwrites 0

=====================================
121231 16:32:24 INNODB MONITOR OUTPUT

Per second averages calculated from the last 53 seconds

BACKGROUND THREAD

srv_master_thread loops: 16478529 1_second, 16478463 sleeps, 1645787 10_second, 22932 background, 22930 flush
srv_master_thread log flush and writes: 16575680

SEMAPHORES

OS WAIT ARRAY INFO: reservation count 7258490, signal count 15414854
–Thread 1404442946 has waited at dict0dict.c line 293 for 246.00 seconds the semaphore:
X-lock on RW-latch at 0x1095518 ‘&dict_table_stats_latches[i]’
a writer (thread id 1339214144) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file dict0dict.c line 290
Last time write locked in file /home/buildbot/slaves/percona-server-51-12/CentOS-5-x86-RPM_ CentOS_5_x86_64/work/BUILD/Percona-Server-5.5.16-rel22.0/Per cona-Server-5.5.16-rel22.0/storage/innobase/dict/dict0dict.c line 293
–Thread 1339214144 has waited at btr0sea.c line 1203 for 246.00 seconds the semaphore:
X-lock (wait_ex) on RW-latch at 0x2ab0e694cfe8 ‘btr_search_latch_part[i]’
a writer (thread id 1339214144) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr0sea.c line 1119
Last time write locked in file /home/buildbot/slaves/percona-server-51-12/CentOS-5-x86-RPM_ CentOS_5_x86_64/work/BUILD/Percona-Server-5.5.16-rel22.0/Per cona-Server-5.5.16-rel22.0/storage/innobase/btr/btr0sea.c line 1632
–Thread 1419884864 has waited at btr0sea.c line 1119 for 246.00 seconds the semaphore:
S-lock on RW-latch at 0x2ab0e694cfe8 ‘btr_search_latch_part[i]’
a writer (thread id 1339214144) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr0sea.c line 1119
Last time write locked in file /home/buildbot/slaves/percona-server-51-12/CentOS-5-x86-RPM_ CentOS_5_x86_64/work/BUILD/Percona-Server-5.5.16-rel22.0/Per cona-Server-5.5.16-rel22.0/storage/innobase/btr/btr0sea.c line 1632
–Thread 1400183104 has waited at btr0sea.c line 1119 for 246.00 seconds the semaphore:
S-lock on RW-latch at 0x2ab0e694cfe8 ‘btr_search_latch_part[i]’
a writer (thread id 1339214144) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
a writer (thread id 1339214144) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr0sea.c line 1119
Last time write locked in file /home/buildbot/slaves/percona-server-51-12/CentOS-5-x86-RPM_ CentOS_5_x86_64/work/BUILD/Percona-Server-5.5.16-rel22.0/Per cona-Server-5.5.16-rel22.0/storage/innobase/btr/btr0sea.c line 1632
–Thread 1317542208 has waited at btr0sea.c line 1119 for 245.00 seconds the semaphore:
S-lock on RW-latch at 0x2ab0e694cfe8 ‘btr_search_latch_part[i]’
a writer (thread id 1339214144) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr0sea.c line 1119
Last time write locked in file /home/buildbot/slaves/percona-server-51-12/CentOS-5-x86-RPM_ CentOS_5_x86_64/work/BUILD/Percona-Server-5.5.16-rel22.0/Per cona-Server-5.5.16-rel22.0/storage/innobase/btr/btr0sea.c line 1632
–Thread 1342675264 has waited at btr0sea.c line 1119 for 244.00 seconds the semaphore:
S-lock on RW-latch at 0x2ab0e694cfe8 ‘btr_search_latch_part[i]’
a writer (thread id 1339214144) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr0sea.c line 1119
Last time write locked in file /home/buildbot/slaves/percona-server-51-12/CentOS-5-x86-RPM_ CentOS_5_x86_64/work/BUILD/Percona-Server-5.5.16-rel22.0/Per cona-Server-5.5.16-rel22.0/storage/innobase/btr/btr0sea.c line 1632
–Thread 1418553664 has waited at dict0dict.c line 290 for 241.00 seconds the semaphore:
S-lock on RW-latch at 0x1095518 ‘&dict_table_stats_latches[i]’
a writer (thread id 1339214144) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file dict0dict.c line 290
Last time write locked in file /home/buildbot/slaves/percona-server-51-12/CentOS-5-x86-RPM_ CentOS_5_x86_64/work/BUILD/Percona-Server-5.5.16-rel22.0/Per cona-Server-5.5.16-rel22.0/storage/innobase/dict/dict0dict.c line 293
–Thread 1344006464 has waited at btr0sea.c line 1526 for 239.00 seconds the semaphore:
S-lock on RW-latch at 0x2ab0e694cfe8 ‘btr_search_latch_part[i]’

Hi,

It looks like you are hitting contention on AHI (Adaptive Hash Index). Are you using AHI partitions > 1 ? If yes then You might have to use latest Percona Server version because earlier versions have bug when no. of partitions > 1.

AHI is ON but we are not using AHI partitions > 1.

mysql> show global variables like ‘%adaptive_hash_index%’;
±--------------------------------------±------+
| Variable_name | Value |
±--------------------------------------±------+
| innodb_adaptive_hash_index | ON |
| innodb_adaptive_hash_index_partitions | 1 |
±--------------------------------------±------+
2 rows in set (0.00 sec)