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

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