Mysql Server stuck with a long semaphore wait

[Manoj]

Our Percona server crashed I am not sure what the issue is, but the server becomes unresponsive and I cannot connect to it. After Mysql restart, we are able to access the Mysql server.

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]'

[Peter Zaitsev]

Manoj,

Are you using innodb_adaptive_hash_index_partitions more than one ? If yes setting it to 1 might help as old versions had issues with this feature

As general thought though you're running pretty old version and updating to recent Percona Server 5.5 is likely to help.

--
You received this message because you are subscribed to the Google Groups "Percona Discussion" group.
To post to this group, send email to percona-d...@googlegroups.com.
 
 

--
Peter Zaitsev, CEO, Percona Inc.
Tel: +1 888 401 3401 ext 501   Skype:  peter_zaitsev
24/7 Emergency Line +1 888 401 3401 ext 911

[Manoj]

Hi Peter,
 
We are not using AHI partitions more than one.

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)

[Peter Zaitsev]

Majoj,

I see. Still version you're using is pretty old. Can you try the new one before we dig deeper ?