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

最新推荐文章于 2023-03-22 17:09:28 发布

weixin_34417183

最新推荐文章于 2023-03-22 17:09:28 发布

阅读量1.2k

点赞数

CC 4.0 BY-SA版权

文章标签：数据库开发工具操作系统

原文链接：https://yq.aliyun.com/articles/512231

在导入大量数据到MySQL时遇到InnoDB长信号量等待错误，通过调整Linux内核信号量设置解决了该问题。涉及的具体步骤包括修改sysctl.conf文件、重启MySQL等。

mysqldump导入大量数据，error log报这个错

今天查看数据库错误日志，发现有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