InnoDB: Warning: a long semaphore wait 解决办法

今天查看数据库错误日志，发现有InnoDB: Warning: a long semaphore wait:  报错：

解决办法：

原来linux内核信号量默认设置太小，造成大量等待， 
默认 
# cat /proc/sys/kernel/sem 
250     32000   32      128 
说明： 
第一列，表示每个信号集中的最大信号量数目。 
第二列，表示系统范围内的最大信号量总数目。 
第三列，表示每个信号发生时的最大系统操作数目。 
第四列，表示系统范围内的最大信号集总数目。


将第三列调大一点，参考网上的数据 
echo "kernel.sem=250 32000 100 128″>>/etc/sysctl.conf 
然后sysctl -p 
重启mysql

错误不再出现，问题即可解决

具体详细的报错如下，详细的对比参考：

InnoDB: Warning: a long semaphore wait:                                                                                 

  813 --Thread 139957495039744 has waited at btr0cur.cc line 545 for 241.00 seconds the semaphore:
  814 X-lock (wait_ex) on RW-latch at 0x7f4a60043da8 created in file dict0dict.cc line 2341
  815 a writer (thread id 139957495039744) has reserved it in mode  wait exclusive
  816 number of readers 2, waiters flag 1, lock_word: fffffffffffffffe
  817 Last time read locked in file btr0cur.cc line 554
  818 Last time write locked in file /root/mysql-5.6.10/storage/innobase/btr/btr0cur.cc line 545
  819 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info:
  820 InnoDB: Pending preads 109, pwrites 0
  821 
  822 =====================================
  823 2014-04-21 18:27:21 7f4899a00700 INNODB MONITOR OUTPUT
  824 =====================================
  825 Per second averages calculated from the last 57 seconds
  826 -----------------
  827 BACKGROUND THREAD
  828 -----------------
  829 srv_master_thread loops: 704 srv_active, 0 srv_shutdown, 184 srv_idle
cndb012.5405.zr.sh.gcimg.net.err [RO]                                                                       812,1           6%


[root@cndb012 mysql]# cat /proc/sys/kernel/sem                                                                     
250     32000   32      128
[root@cndb012 mysql]# vim cndb012.5405.zr.sh.gcimg.net.err


  805 2014-04-21 18:04:55 16100 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000124', position 61975299
  812 InnoDB: Warning: a long semaphore wait:
  813 --Thread 139957495039744 has waited at btr0cur.cc line 545 for 241.00 seconds the semaphore:
  814 X-lock (wait_ex) on RW-latch at 0x7f4a60043da8 created in file dict0dict.cc line 2341
  815 a writer (thread id 139957495039744) has reserved it in mode  wait exclusive
  816 number of readers 2, waiters flag 1, lock_word: fffffffffffffffe
  817 Last time read locked in file btr0cur.cc line 554
  818 Last time write locked in file /root/mysql-5.6.10/storage/innobase/btr/btr0cur.cc line 545
  819 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info:
  820 InnoDB: Pending preads 109, pwrites 0
  821 
  822 =====================================
  823 2014-04-21 18:27:21 7f4899a00700 INNODB MONITOR OUTPUT
  824 =====================================
  825 Per second averages calculated from the last 57 seconds
  826 -----------------
  827 BACKGROUND THREAD
  828 -----------------
  829 srv_master_thread loops: 704 srv_active, 0 srv_shutdown, 184 srv_idle
  830 srv_master_thread log flush and writes: 887
  831 ----------
  832 SEMAPHORES
  833 ----------
  834 OS WAIT ARRAY INFO: reservation count 75098
  835 --Thread 139948735776512 has waited at row0sel.cc line 3048 for 0.00 seconds the semaphore:
  836 S-lock on RW-latch at 0x7f48aa6535c0 created in file buf0buf.cc line 994
  837 a writer (thread id 139957491046144) has reserved it in mode  exclusive
  838 number of readers 0, waiters flag 1, lock_word: 0
  839 Last time read locked in file not yet reserved line 0
  840 Last time write locked in file /root/mysql-5.6.10/storage/innobase/buf/buf0buf.cc line 3466
  841 --Thread 139949644658432 has waited at row0sel.cc line 4111 for 1.00 seconds the semaphore:
  842 S-lock on RW-latch at 0x7f49d1bd1ec0 created in file buf0buf.cc line 994
  843 a writer (thread id 139948734711552) has reserved it in mode  exclusive
  844 number of readers 0, waiters flag 1, lock_word: 0
  845 Last time read locked in file not yet reserved line 0
  846 Last time write locked in file /root/mysql-5.6.10/storage/innobase/buf/buf0buf.cc line 3466
  847 --Thread 139949722711808 has waited at btr0cur.cc line 554 for 77.00 seconds the semaphore:
  848 S-lock on RW-latch at 0x7f4a60043da8 created in file dict0dict.cc line 2341
  849 a writer (thread id 139957495039744) has reserved it in mode  wait exclusive
  850 number of readers 2, waiters flag 1, lock_word: fffffffffffffffe                                                        
  851 Last time read locked in file btr0cur.cc line 554
  852 Last time write locked in file /root/mysql-5.6.10/storage/innobase/btr/btr0cur.cc line 545
  853 --Thread 139945323890432 has waited at btr0cur.cc line 554 for 77.00 seconds the semaphore:
  854 S-lock on RW-latch at 0x7f4a60043da8 created in file dict0dict.cc line 2341
  855 a writer (thread id 139957495039744) has reserved it in mode  wait exclusive
  856 number of readers 2, waiters flag 1, lock_word: fffffffffffffffe

  857 Last time read locked in file btr0cur.cc line 554

.....................