InnoDB: Warning: a long semaphore wait 解決辦法

mysqldump導入大量資料，error log報這個錯

解決辦法：

預設 

# cat /proc/sys/kernel/sem 

250     32000   32      128 

說明： 

第一列，表示每個信号集中的最大信号量數目。 

第二列，表示系統範圍内的最大信号量總數目。 

第三列，表示每個信号發生時的最大系統操作數目。 

第四列，表示系統範圍内的最大信号集總數目。

将第三列調大一點，參考網上的資料 

echo "kernel.sem=250 32000 100 128″>>/etc/sysctl.conf 

然後sysctl -p 

錯誤不再出現，問題即可解決

具體詳細的報錯如下，詳細的對比參考：

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:

  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

本文轉自 liqius 51CTO部落格，原文連結：http://blog.51cto.com/szgb17/1919482，如需轉載請自行聯系原作者